views:

45

answers:

1

In Entity framework, would the statement

MyDataContext.Products.OrderByDescending( (p) => p.Id ).GroupBy<Product,int>( (p) => p.ProductId ).Select( (g) => g.FirstOrDefault()).Where( (p) => p.Name.Equals("Something") );

result in another database query than

MyDataContext.Products.Where( (p) => p.Name.Equals("Something") ).OrderByDescending( (p) => p.Id ).GroupBy<Product,int>( (p) => p.ProductId ).Select( (g) => g.FirstOrDefault());

In other words, does the ordering of the calls to Where() and GroupBy() affect the final query to the db? Or is the entity framework smart enough to solve this?

The reason I wonder is that in the system I'm developing, we need to keep track of all changes to a product row. The solution we use in order to achieve this is to insert a new product row into the table rather than updating it. The different versions of a product is then grouped together by using the "ProductId"-field. Thus, I would like to move the "Grouping"-logic out to an external method, but still allow the caller to specify the Where-conditions...so my aim is to use the first approach.

+1  A: 

Yes, it's important. The EF may silently ignore OrderBy calls which come before Where calls. This shouldn't be terribly surprising, since SQL queries don't support ORDER BY before WHERE at all, outside of a subquery.

Note that you can still "move the 'Grouping'-logic out to an external method, but still allow the caller to specify the Where-conditions," even with this consideration:

var q = SomeQuery
        .Where(GeneratePredicate())
        .GroupBy(GenerateSelector<string>());

Where you have methods like:

public Expresssion<Func<SomeEntity, bool>> GeneratePredicate
{
     return e => e.Id == 123;
}

public Expression<Func<SomeEntity, T>> GenerateSelector<T>()
{
     return e => e.GroupField;
}

Importantly, these Where and GroupBy calls are not methods of IEnumerable<T>; they're methods of IQueryable<T>, which is why they can be converted to SQL at all.

Craig Stuntz
Thanks for a good answer! Hmm...the call to OrderByDescending() is done since I want to retrieve the correct (or last) version of the product. Then I simply pick the first item in the group to get it right. Do you mean that this ordering could be ignored if I call Where after? Haven't thought of that so thank you for the tip!
Ozzy
Yes, it does. You might want to use `First(Expression)` instead of `OrderBy().First()`.
Craig Stuntz
ah, ofcourse! :) thanks yet again
Ozzy