CODEWITHSUNDEEP
c#linqwhere-clauseiqueryable
BLoB
BLoB

Reputation: 9725

IQueryable different results when adding where clause later

I'm experiencing variations in results depending on when I'm specifying a where clause...

If I use:

query1 = from ct in CustomerTransfers
         join j in Jobs on ct.Stock.JobNo equals j.JobNo
         join o in Organisations on j.OrganisationId equals o.OrganisationId into oGroup
         from o in oGroup.DefaultIfEmpty()
         where ct.OrganisationId == intCustomerB &&
                  ct.NewOrganisationId == intCustomerA
         group new { ct, j, o } by ct.WedNo into g
         let largestValue = g.OrderByDescending(x => x.ct.TransferNo).FirstOrDefault()
         select new
         {
             Id = g.Key,
             OrganisationId = largestValue.ct.OrganisationId,
             NewOrganisationId = largestValue.ct.NewOrganisationId,
         };

query1.ToList();

it gives two results... but if i remove the following from the initial IQueryable construction:

where ct.OrganisationId == intCustomerB &&
ct.NewOrganisationId == intCustomerA

and add them in immediately later using where clause like so:

query2 = from ct in CustomerTransfers
         join j in Jobs on ct.Stock.JobNo equals j.JobNo
         join o in Organisations on j.OrganisationId equals o.OrganisationId into oGroup
         from o in oGroup.DefaultIfEmpty()
         group new { ct, j, o } by ct.WedNo into g
         let largestValue = g.OrderByDescending(x => x.ct.TransferNo).FirstOrDefault()
         select new
         {
             Id = g.Key,
             OrganisationId = largestValue.ct.OrganisationId,
             NewOrganisationId = largestValue.ct.NewOrganisationId,
         };

query2 = query2.Where (q => q.OrganisationId == intCustomerB && q.NewOrganisationId == intCustomerA);

query2.ToList();

I get one result.

So am I correct in assuming that using query2 method it is wrapping the whole IQueryable with the where clause as opposed to adding the where clause into the IQueryable (which would result in an IQueryable identical to query1)? This seems the only logical explanation.

Prior to this 'revelation' I would have thought that to achieve 1 result (query2) I would have had to evaluate query2 with a .ToList() and then apply a where statement to that list.

Upvotes: 1

Views: 284

Answers (1)

Adam Short
Adam Short

Reputation: 156

Yes, yes you are.

Or in a slightly longer form: The second example creates an IQueryable which is then further operated on by the Where method, so everything you've got from the first IQueryable is being filtered instead of the filtering happening within your first query. It's not immediately clear why this actually returns different results, but in the second example the grouping happens before the filtering, which could be significant.

Upvotes: 2

Related Questions