Why let with where clause can't translate to SQL?

Question

I have a code snippet that uses let with a where clause.

private List<string> Example2()
{
    var query1 =
        from c in _context.Customers
        let custPurchases = _context.Purchases.Where(p => p.CustomerId == c.Id)
        where custPurchases.Any(x => x.Price > 500)
        select c.Name;

    return query1.ToList();
}

It compiles well, but I get such an error during execution:

System.InvalidOperationException: The query contains a projection 'c => DbSet() .Where(p => p.CustomerId == c.Id)' of type 'IQueryable'. Collections in the final projection must be an 'IEnumerable' type such as 'List'. Consider using 'ToList' or some other mechanism to convert the 'IQueryable' or 'IOrderedEnumerable' into an 'IEnumerable'.

Why does EF Core try to execute this code on the client side instead of on the server (database)? Where does EF Core encounter the problem during translation IQueryable to SQL?

P.S.

LINQPad 8 by default easily executes this code, but that's LINQ-to-SQL:

Maybe you can provide some literature or links to dig deeper into the expression tree world?

I use SQL Server provider at LinqPad. Connection string is "Data Source=.;Integrated Security=SSPI;TrustServerCertificate=true;app=LINQPad".

I think LinqPad just does the final materialization for you, so you are comparing apples and oranges. (Not 100% sure, though) — Fildor
– Fildor, Commented Oct 6 at 10:42
How did you set up the connection in LinqPad, though? Is it using EF or LINQ to SQL? — DavidG
– DavidG, Commented Oct 6 at 10:47
What are the actual Customer and Purchase entities? Not the tables, the C# classes ? Also what are you actually using? EF Core is a completely different ORM from LINQ-to-SQL, which came out in 2007 more as a demo of LINQ than a full featured ORM. It was never migrated to .NET Core — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 6 at 11:22
I wouldn't call this a bug, it's a VERY GOOD feature - the question's code is trying to combine two other LINQ queries. That's not a problem with LINQ-to-Objects (ie collections). With databases, it results in terrible performance and unexpected problems: the entire Customers and Purchases tables will be loaded before joining in memory. The 20-year old LINQ-to-SQL allowed this for convenience but EF and EF Core don't, because the production consequences are too high. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 8 at 7:35
So, instead of trying to be clever, EF Core is telling you what's wrong and asks you to fix it. Remember, EF Core is an ORM, not a query builder. It's job is to load graphs of related objects, not emulate SQL. — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Oct 8 at 7:37

JonasH · Accepted Answer · 2025-10-07 06:24:53Z

1

I would try to change the query to:

var query1 = _context.Customers
                     .Where(c => c.Purchases.Any(p => p.Price > 500))
                     .Select(c => c.Name);

This assumes there is a navigational property from a customer to its purchases.

There are limitations to what kind of expressions Entity Framework can translate to SQL, simple filters and selects are generally fine, but even nested expressions like this can be problematic in some cases. I would at least inspect the produced SQL to verify that it looks reasonable, but that may be because I'm used to old EF versions that are worse at query generation.

The simplest way I know to check if a query can be translated is to just test it, preferably with a unit test. It is fairly easy to setup a SQLite in-memory database provider for testing, but some features depend on the specific database provider, so the tests will be more accurate if you are using your actual database provider.

edited Oct 7 at 6:24

answered Oct 6 at 11:26

JonasH

39k2 gold badges16 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Svyatoslav Danyliv Oct 6 at 14:35

Using an in-memory database provider for testing is a terrible idea — EF doesn’t translate queries for this provider and executes them as-is.

JonasH Oct 7 at 6:27

Thanks, I was not aware of that distinction. SQLite also offers an in memory database, and I presume that at least ensures queries are translated.

Guru Stron Oct 8 at 6:50

Yes, SQLite using translation, it is easy to check by enabling query logging. Though given that it still does not prove that query will be translatable for the destination database)

phuzi · Accepted Answer · 2025-10-09 08:45:39Z

0

You should probably be using a join instead. You can then filter by purchase price and then group the results by customer before selecting the customer's name.

    var query1 =
        from c in _context.Customers
        join p in _context.Purchases on c.Id equals p.CustomerId
        where p.Price > 500
        group c by c.Name into g
        select g.Key;

With SQL Server and EF Core 8, this will translate into the following SQL*:

SELECT [c].[Name]
  FROM [dbo].[Customer] AS [c]
 INNER JOIN [dbo].[Purchase] AS [p] ON [c].[Id] = [p].[CustomerId]
 WHERE [p].[Price] > 500
 GROUP BY [c].[Customer]

* Schema, table and column names may be different of course

edited Oct 9 at 8:45

answered Oct 8 at 8:12

phuzi

13.3k4 gold badges31 silver badges66 bronze badges

Collectives™ on Stack Overflow

Why let with where clause can't translate to SQL?

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related