Does LINQ to Entities expression with inner object context instance translated into sequence of SQL client-server requests?

Question

I have ADO.NET EF expression like:

db.Table1.Select(
  x => new { ..., count = db.Table2.Count(y => y.ForeignKey.ID == x.ID) })

Does I understand correctly it's translated into several SQL client-server requests and may be refactored for better performance?

Thank you in advance!

Answer 1

Yes - the expression will get translated (in the best way it can) to a SQL query.

And just like any T-SQL query, an EF (or L2SQL) query expression can be refactored for performance.

Why not run SQL profiler in the background to see what it is getting executed, and try and optimize the raw T-SQL first - which will help optimize the expression.

Or if you have LinqPad, just optimize the T-SQL query and get LinqPad to write your query for you.

Also, im not really sure why you have specified the delegate for the Count() expression.

You can simply do this:

var query= from c in db.Table1
                  select new { c.CustomerID, OrderCount = c.Table2s.Count() };

Answer 2

The answer is NO - this query will be translated into one client-to-RDBMS request.

RPM1984 advised to use LinqPad. LinqPad showed that the query will be translated into very straightforward SQL expression. Approach with grouping will be translated into another SQL expression but still will be executed in one request.