Counting properly with database Linq/lambda

This query is not translated to back-end version, materializing any of the class in a linq expression will result in all dependent objects being materialized to in-memory objects, and that includes the list

db.Set<Order>().Single(x => x.OrderId == 1).Customer.Country.Languages.Count

To make that efficient, do that query in this manner. Do note that none of the classes are materialized to an object:

int c = (from x in db.Set<Order>()
         where x.OrderId == 1
         from l in x.Customer.Country.Languages
         select l).Count();

Here's the query generated by that:

SELECT 
[GroupBy1].[A1] AS [C1]
FROM ( SELECT 
 COUNT(1) AS [A1]
 FROM   (SELECT [Extent1].[Customer_CustomerId] AS [Customer_CustomerId]
  FROM [dbo].[Order] AS [Extent1]
  WHERE 1 = [Extent1].[OrderId] ) AS [Filter1]
 CROSS APPLY  (SELECT [Extent2].[AssocCountryId] AS [AssocCountryId]
  FROM  [dbo].[LanguageAssocCountry] AS [Extent2]
  INNER JOIN [dbo].[Customer] AS [Extent3] ON [Extent3].[Country_CountryId] = [Extent2].[AssocCountryId]
  WHERE [Filter1].[Customer_CustomerId] = [Extent3].[CustomerId] ) AS [Filter2]
)  AS [GroupBy1]

If you want to simplify the Linq, don't do it this way, this will materialize the dependent object and rows, as the very first object(via Single) was materialized already, not efficient:

int c = (from l in db.Set<Order>().Single(x => x.OrderId == 1).Customer.Country.Languages
         select l).Count()

To shorten it up a bit, do it this way, none of the classes is materialized to object, this is efficient:

int c = (from x in db.Set<Order>().Where(x => x.OrderId == 1)
         from l in x.Customer.Country.Languages
         select l).Count()

Query-wise, that code generates exactly the same query as the first Linq code of this post.

If you want to write it in pure lambda approach, use the following code. This code also generates the exact same query of the second Linq code of this post:

int c = db.Set<Order>().Where(x => x.OrderId == 1)
          .SelectMany(x => x.Customer.Country.Languages).Count();

This is the generated query when you use NHibernate ( just change the db.Set<Order>() to db.Query<Order>() ):

exec sp_executesql 
N'select cast(count(*) as INT) as col_0_0_ 
from [Order] order0_ 
inner join [Customer] customer1_ on order0_.Customer_CustomerId=customer1_.CustomerId 
inner join [Country] country2_ on customer1_.Country_CountryId=country2_.CountryId 
inner join LanguageAssocCountry languages3_ on country2_.CountryId=languages3_.AssocCountryId 
inner join [Language] language4_ on languages3_.AssocLanguageId=language4_.LanguageId 
where order0_.OrderId=@p0',N'@p0 int',@p0=1