Why does Linq ignore my where clause?

Question

I see several StackOverflow questions on this already but not of them seem to match my scenario. I promise I looked.

I have some queries against my database that I'm using linq to do and I can't figure out why the incorrect SQL is being generated. This is happening in several places in my code. I'm hoping we're just falling into some well known gotcha but I can't wrap my head around why Linq seemingly decides my where clause is dumb and shouldn't add it to the generated SQL query.

Why is this?

Example:

var testing = (from i in context.TableName1 where i.Param1 == object1.GuidParam select i).ToList();

The above query returns the following SQL

{SELECT 
    [Extent1].[RecordId] AS [RecordId], 
    [Extent1].[AnotherId] AS [AnotherId], 
    [Extent1].[YetAnotherId] AS [YetAnotherId], 
    [Extent1].[WeLikeIds] AS [WeLikeIds], 
    [Extent1].[WeReallyLikeIds] AS [WeReallyLikeIds]
    FROM [dbo].[SomeTable] AS [Extent1]}

However the following query:

var testing = (from i in context.TableName1 where i.Param1 == object1.GuidParam select i);
var testingToList = testing.ToList();

Generates the following correct SQL

{SELECT 
    [Extent1].[RecordId] AS [RecordId], 
    [Extent1].[AnotherId] AS [AnotherId], 
    [Extent1].[YetAnotherId] AS [YetAnotherId], 
    [Extent1].[WeLikeIds] AS [WeLikeIds], 
    [Extent1].[WeReallyLikeIds] AS [WeReallyLikeIds]
    FROM [dbo].[SomeTable] AS [Extent1]
WHERE [Extent1].[RecordId] = '78e49f5c-0ff8-e311-93f4-00155d514a6d'}

What are the data types of `Param1` and `GuidParam`? Is it ever the case that LINQ could decide that the where clause is always true? — John Saunders, Jun 20 '14 at 16:56
Yes it is. The object matches the DB perfectly. It's not limited to just this one example either. I have at least one other linq query I've identified that's doing the same thing. — Justin williams, Jun 20 '14 at 17:32
@JohnSaunders No, it's not possible for the where clause to always be true. — Justin williams, Jun 20 '14 at 18:56
As an experiment, try `var testing = (from i in context.TableName1 where i.Param1 == object1.GuidParam select i); var testingToList = testing.ToList().Where(i=>i.Param1 == object1.GuidParam);` See how that works. — John Saunders, Jun 20 '14 at 18:58
What happens if you try it in the lambda form `context.TableName1.Where(i => i.Param1 == object1.GuidParam);` ? — IEatBagels, Jun 20 '14 at 19:22
I just noticed that if I have the .ToList() on the same line the where statement is ignored. If I drop it to the second line the correct SQL is returned. That's what the discrepancy has been thus far where it works sometimes and others it does not. @TopinFrassi I will check now — Justin williams, Jun 20 '14 at 20:20
Using a lambda expression does work correctly and will probably be the solution I go with however I still want to know why linq decided to drop the where statement when I have a .ToList() or .SingleOrDefault() on the same line as the query. We use linq pretty often and I really need to understand what we did to make link do what it did. — Justin williams, Jun 20 '14 at 20:26
This may be something to do with the .Where extension method being exposed on both IQueryable and IEnumerable. But it definitely looks like a bug in linq-to-sql. — Michael Parker, May 01 '15 at 17:20
A `where` expression will only generate an object which contains all information to perform the action. The actual filter action is only executed by using `foreach` or call the `GetEnumerator` method. I guess by creating another object (your second line with `ToList`) there is some internal copying with `foreach` which executes the actual `where` expression. Did you tried your first one-line example with a `foreach` loop? — Robert S., May 05 '15 at 22:44
Can you please try following? var testing = (from i in context.TableName1 where i.Param1 == object1.GuidParam select i).AsEnumerable(); — Nazmul, May 11 '15 at 16:49
I'm having difficulty believing the behavior you're describing. Can you share details on how you're testing these, and how you're capturing the SQL? Does the code you've provided represent actual code and actual results, or did you write it as an example of the sorts of patterns you're seeing in your real code? — StriplingWarrior, May 14 '15 at 15:23
This is a 10 month old ticket... Why is it getting so much attention now? StriplingWarrior: It was happening in our application. I tested by running the code in VS against production to identify the issue and created an integration test with the same data. The code listed came directly from our source code with variables/tables renamed. The SQL came from examining the sql param on the testing variable but again, this was almost a year ago. Converting the query to a lambda expression resolved the issue so we've avoided using the original syntax and stuck to lambdas. — Justin williams, May 15 '15 at 17:57

score -3 · Accepted Answer · answered May 14 '15 at 15:17

-3

I prefer the lambda notation, and I don't see why this wouldn't work...

var testing = context.TableName1.Where(i => i.Param1 == object1.GuidParam).ToList();

Cleaner, concise and it should work.

answered May 14 '15 at 15:17

mgrenier

1,409
3
21
45

1

It should be exactly the same, because the OP's code is transformed by the compiler into exactly this. – Servy May 14 '15 at 15:21
1

If the OP's code won't work, then this won't work. If this would work, then the OP's code will work. Either way, you haven't solved his problem. – Servy May 14 '15 at 18:10
that's interesting, because just reading through all the comments now I see someone suggested lambda notation and OP said it works and it probably going to be his solution – mgrenier May 14 '15 at 18:15
1

It sounds like just doing a recompile of the code solved the problem, as that change is not a functional change. That or the problem was with something else not shown that also changed when he altered the solution. He could also have been improperly observing what was going on, as another commentor mentioned. Without the OP we just don't have the information to actually figure out what the problem is. *This* change isn't what would have fixed the problem though. – Servy May 14 '15 at 18:26

Why does Linq ignore my where clause?

1 Answers1