none
How to set a linked entity to null when creating a query? RRS feed

  • Question

  • Hello all,

    in an application I am developing, I have a query for Project entities (this is a very simplified method of a domain service):

    public IQueryable<Project> GetProjectsWithIncludes() { var companies = this.GetCompanies(); // returns IQueryable<Company> var result = from row in this.dataContext.Projects select new Project() { ProjectId = row.ProjectId, Name = row.Name, CompanyId = row.CompanyId, Company = companies.FirstOrDefault(e => e.CompanyId == row.CompanyId) }

    return result; }

    Database table Project consists of three columns: ProjectId, Name and CompanyId.

    CompanyId is a nullable FK to Company table.

    So far so good. But I have a requirement to not include a Company in the resulting query, in certain circumstances (thus, not generating a join for that in the SQL from EF), for performance reasons. I found out that I am unable to do this:

    public IQueryable<Project> GetProjectsWithIncludes()
    {
        var companies = this.GetCompanies(); // returns IQueryable<Company>
    
        var result =
            from row in this.dataContext.Projects
            select new Project()
            {
                ProjectId = row.ProjectId,
                Name = row.Name,
                CompanyId = row.CompanyId,
                Company = null
            }
    
        return result;
    }

    (note the null assignment to Company). EF then fails on runtime, complaining that "Unable to create a constant value of type 'Product.Web.BusinessObjects.Company'. Only primitive types ('such as Int32, String, and Guid') are supported in this context."

    How can I assign a null value to the linked entity? I found out it works if I do not assign it at all but this is not the way I want to go because 1) there are 8 linked entities, this example only shows one, and 2) they can be assigned/not assigned under various conditions.

    Please, can anybody help?

    Wednesday, March 14, 2012 3:00 PM

Answers

  • No because I needed to make those objects business objects first. I already knew about the Include method.

    I have now solved it by creating the query manually with all linked entities and then modifying the expression tree according to which linked entities were requested. This keeps the generated SQL much simpler and everything is faster.

    Monday, March 19, 2012 7:18 AM

All replies

  • Why do you not want to select the row directly (without creating a new Project)? That does not query the linked entities unless you call them explicitly.

    That's one option, else you can return a not generic IQueryable and do the following:

     var result =
            from row in this.dataContext.Projects
            select new {
                row.ProjectId,
                row.Name,
                row.CompanyId
            }
    

    Wednesday, March 14, 2012 3:23 PM
  • Because I want to load a list of Projects to the client (and, as I wrote, there are more linked entities than just Company). On the client, various properties of Project, Company and other linked entities are displayed.

    Wednesday, March 14, 2012 3:26 PM
  • I don't know if I understand your problem right, but when you use Include you can specify which linked entities you want to retrieve with your query. The other ones are not retrieved from the database.

    The following example includes Companies in the query, if you not want companies you should not add the Include.

    public IQueryable<Project> GetProjectsWithIncludes() {
        return this.dataContext.Projects.Inlude("Companies");
    }
    

    Thursday, March 15, 2012 8:51 AM
  • Hi Mek7,

    Does @Frank's solution can solve the issue? If you have any problems, please feel free to post here.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Monday, March 19, 2012 2:35 AM
    Moderator
  • No because I needed to make those objects business objects first. I already knew about the Include method.

    I have now solved it by creating the query manually with all linked entities and then modifying the expression tree according to which linked entities were requested. This keeps the generated SQL much simpler and everything is faster.

    Monday, March 19, 2012 7:18 AM
  • Hi Mek7,

    I'm glad to hear that you have found the way to solve it, and thanks for sharing the experience.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Tuesday, March 20, 2012 4:03 AM
    Moderator