''Include'' in Projection
I use EF and have a model with two entities
Factor and
FactorItem having one-to-many relationship with each other. When I execute the following chunk of code:
var db = new TestEntities();
var query = (from f in db.Factors.Include("FactorItem") select f).ToList();
foreach (Factor resultFactor in query)
{
foreach (FactorItem resultItem in resultFactor.FactorItems)
Console.WriteLine(resultItem.ID);
}
Everything seems to work and it outputs all the factor items. But when I execute this one:
var db = new TestEntities();
var query = (from f in db.Factors.Include("FactorItem") selectnew {f}).ToList();
foreach (var resultFactor in query)
{
foreach (FactorItem resultItem in resultFactor.f.FactorItems)
Console.WriteLine(resultItem.ID);
}
No factor items are printed in console. In other words, when I project the results to some other type, the related EntityCollections become empty despite the fact that I've explicitly indicated that I need them usingInclude method.
Is it a bug?
[2632 byte] By [
CompuBoy] at [2008-1-10]
You are right that the span (or Include) is not propogated during SELECT. The reason it is not included in the resultant query is because its difficult to analyze if the resultant projection does or doesn't have any spanned (In your case, result.f.FactorItems) members in the select list. As a workaround, you can always include the span again on the resultant query.
Since, a identity select (like- Selet(c => c)) is an obvious case were span should work, its enabled for only that case of project.
Hope this helps,
Sushil.
Thanks for the information!
Well! First of all, in my opinion, from the user's point of view, an argument such as something is not supported because it is difficult to implement does not seem to be logical esp. in an API like Entity Framework. When some user is using some API and the API provides him/her with some interfaces to do something without any condition, he/she should not think about whether it is implemented or not due to some technical detail or not. In this case, since LINQ gives me the right to do so, I expect it to work, and if it does not work I consider it a bug or a flaw in the design of the API.
Now, let me look more deeply into the subject. May be the example I used to show the problem with this restriction was not so good. Projection may not seem to be necessary in my example query but of course, it IS in the result of a join like the following:
var query = from f in db.Fators.Include("FactorItems")
join c in db.Customers
on f.CustomerRef equals c.ID
select ?
Suppose I need the pair of o and c in the resultant query. Here, instead of ? I should use a projection like new {f, c} and suddenly I lose my FactorItems.
After I read this answer, I tried to include the span path to my resultant query and suddenly I found that I was not able to cast it to ObjectQuery<T> since T is an anonymous type. The only way to call Include on the resultant query was to use reflection. So, instead of using reflection, I implemented a class like Pair<A,B> which took f and c in its constructor and exposed two properties First and Second corresponding to the first object (Factor) and the second one (Customer) passed to its constructor. The I tried to rewrite the query as follows:
var query = from f in db.Factors.Include("FactorItems")
join c in db.Customers
on f.CustomerRef equals c.ID
select new Pair<Factor,Customer>(f, c)
So that I would be able to cast query to ObjectQuery<Pair<Factor,Customer>> and call its Include method. This time I faced the following exception:
Only parameterless constructors and initializers are supported by LINQ to Entities.well! Another lack of support (As a side question, is this lack of support just for the current beta versions? Or may be it won't be supported at all in the future?).
OK! This time I added a parameterless constructor to my Pair<A,B> class and added setter to its First and Second properties. And wrote my query as follows:
var query = from f in db.Factors.Include("FactorItems")
join c in db.Customers
on f.CustomerRef equals c.ID
select new Pair<Factor,Customer>() { First = f, Second = c};
and then casted the resultant query to ObjectQuery<Pair<Factor,Customer>> and executed its Include method as follows:
((ObjectQuery<Pair<Factor,Customer>>) query).Include("FactorItems");
Executing this code, I faced the following exception:
A span path can only be specified for a query with an entity or entity collection result type.
well! I conclude that I have no way to have my FactorItems included in such a simple query.
Thanks for your comments.
Just to add to my previous reply and make sure you understand, we did not implement the required behavior not because it was hard but because it wasn't expected in most of the core scenario cases. Consider the following query:
var query = from f in db.Factors.Include("FactorItems")
select f.Id;
In the above query, there is no reason to execute a statement to the store that brings in all the FactorItems related to a particular Factor record since we are just selecting the Id. This makes the LINQ to Store SQL translation less performant and less scalable. Now, we can argue that it would be easy to analyze the projection list and then make a decision to include the span or not. On a cursory look, it feels right; but if you dig deeper it leads to a point where the behavior is not easily defined in some cases and leads to inconsistency in other cases. In addition, coming up with such a costly analysis on the projection more often costs performance in a important scenario, like: select.
The idea of supporting only parameter-less constructor was one of the hard decisions we made as a product team. The main idea with this approach was we shouldn't open up new surface area in LINQ over Entities that is not supported by EDM. EDM in general doesn't allow you to construct in random objects (using the NEW constructor in eSQL). To make it consistent with the whole stack, we decided to implement it like wise. Limiting the constructions only to:
- Parameter less constructors
- Anonymous types (as it’s the only way to do multi-project in LINQ over Entities)
Thanks for the feedback, let me know if you still have concerns. I will be glad to hear.
-Sushil Chordia
Thanks for your more thorough descriptions!
I would like to ask a question which is why didn't EF put the responsibility of thinking about whether it would be suitable to include some related entity or not on the shoulders of the developer? In other words, the user of EF should be aware of the consequences or performance problems of his/her queries and the API should not try to out-smart the user in all the possible scenarios. When the user writes the following query:
var query = from f in db.Factors.Include("FactorItems")
select f.Id;
He/She explicitly indicates that he needs to include all the related FactorItems of each Factor. So, in my opinion EF should
either:
- Analyze the select part and determine whether it is necessary to include the FactorItems or not.
- Include the related FactorItems regardless of the select part since the user has requested so.
And a behaviour such as not including the related FactorItems since the analysis is costly would not be acceptable since
it may result the original query not correctly answered by the API. So, if the first choice is not feasible then I think the second choice would be done. A user may not blame the API for not out-smarting him/her in creating the query but he/she will certainly blame the API for not fulfilling what he/she queried.
BTW, I still have the question of how I should get correct results for the query I've indicated in my second post.
There are other ways to accomplish what you may want. Here's an example that uses annoymous types, but projects the collection into a specific item. Not exactly your scenario, but similar.
regards,
Brian
c#
NorthwindEntities context = new NorthwindEntities();
var categories =from c in context.Categories
select new {c.CategoryName,
c.Description,
c.CategoryID,
c.Products};
foreach (var ct in categories)
{
Console.WriteLine(ct.CategoryName);
foreach (Product p in ct.Products)
{
Console.WriteLine("**" + p.ProductName);
}
}
vb
Using entities As New NorthwindEntities
Dim categories = From ca In entities.Categories _
Select ca.CategoryName, _
ca.CategoryID, _
Prod = ca.Products
For Each cat In categories
Console.WriteLine(cat.CategoryName)
For Each p In cat.Prod
Console.WriteLine("**" & p.ProductName)
Next
Next
End Using
