c# linq, what is the effect of using .where() on .Get()?

Question

A question asked to the floor in our office was:

What is the difference between

Method 1

var team = _db.Get<ClientCreateDefaults>(c => c.ClientRef == newCase.ClientRef &&
    c.WorkTypeCode == newCase.WorkTypeCode);

Method 2

var team = _db.Get<ClientCreateDefaults>(c => c.ClientRef ==
    newCase.ClientRef).Where(w => w.WorkTypeCode == newCase.WorkTypeCode);

Method 3

var team = _db.Get<ClientCreateDefaults>().Where(w => w.WorkTypeCode ==
    newCase.WorkTypeCode).Where(c => c.ClientRef == newCase.ClientRef);

An answer was given:

If this is our DataContext:

The first one, the conditions will all be translated into SQL, so the filtering will happen on the db server.

The second one only does the first one on the db server and then filters by WorkTypeCode on the client side.

The third one will filter all client side and get all of the items from the db.

Is this correct? Does linq really bring whole datasets to the client side?

It would rather depend whether it's `Enumerable.Where` or `Queryable.Where`. You might want to look at [this related question](http://stackoverflow.com/questions/1578778/using-iqueryable-with-linq). — Charles Mager, Jun 03 '16 at 16:39
Why don't you use a Sql Profiler, and then you can answer your own question? — stuartd, Jun 03 '16 at 16:46
You're asking strangers on the internet whether the experts on your own systems are correct or not? How should we know; we're not experts on your system! — Eric Lippert, Jun 03 '16 at 16:47
I believe all of those will be the same. If your collection is implementing `IQueryable`, it will filter the data in SQL (db). If your collection is using `IEnumerable`, it's pretty much doing a `select *` in SQL so you will get every record unfiltered; the filtering will then occur outside of SQL and when requested at runtime. I think [this article](http://blog.falafel.com/understanding-ienumerable-iqueryable-c/) can explain it better. — Arman Peiravi, Jun 03 '16 at 17:18

score 2 · Accepted Answer · answered Jun 03 '16 at 17:36

Get is your code, not the code of LINQ 2 SQL. According to the experts explanation it returns an IEnumerable. Once it returns you leave the realm of LINQ 2 SQL and enter LINQ to Objects. That's why the appended filtering is done on the client.

This looks like a generic repository pattern.

c# linq, what is the effect of using .where() on .Get()?

1 Answers1