Question
I'm trying to get a list of some entity with all sub-entities using LINQ but none of the methods I used works correctly. Here is the classes structure:
public class License {
public virtual Product Product {get; set}
. . . . . .
//some other License properties
. . . . . .
}
public class LicenseUser {
public virtual Account User { get; set;}
public virtual License License { get; set; }
}
I'm trying to get the list of licenses for some user but each License object must contain Product as well. Any query I've tried returned list of license with "null" value in Product property.
Here are the examples LINQ queries what I've tried:
var licenses = context.LicenseUsers
.Include(lu => lu.License.Product)
.Where(lu => lu.User.Id == 1)
.Select(lu => lu.License)
.ToList();
or
var licenses = context.LicenseUsers
.Include(lu => lu.License).ThenInclude(l => l.Product)
.Where(lu => lu.User.Id == 1)
.Select(lu => lu.License)
.ToList();
I'm sure that the records we get by these queries contains valid reference to Products table (ProductId field is not null). I also checked the SQL generated by this LINQ request (using Diagnostic Tools). As expected it does not contains a JOIN statement for Products table.
Is there a correct way to get necessary result?
Accepted Answer
This is a bit counter-intuitive, but the problem is that your Select is filtering out what the SQL query contains.
So you may want to force EF to include the product in the SQL too (using an anonymous object that contains both the license and its product) and then convert to a License collection in-memory:
var licenses = context.LicenseUsers
.Include(lu => lu.License.Product)
.Where(lu => lu.User.Id == 1)
.Select(lu => new { lic = lu.License, prod = lu.License.Product } )
.AsEnumerable() // Here we have forced the SQL to include the product too
.Select(lu => lu.lic)
.ToList(); // Then we select (locally) only the license for convenience
// (so that our collection is of type License)
// Since the SQL Query actually loaded the products
// the references will be ok
Popular Answer
Well, I'm quite new to this myself but this might give you an idea.
According to this MSDN article, to eagerly load multiple levels, you could try something like this:
var licenses = context.LicenseUsers
.Where(lu => lu.User.Id == 1)
.Include(lu => lu.License.Select(p => p.Product))
.ToList();
And checking with other examples, as Alexander said, Where() should be before Include() in any case.
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow