'let' in Linq Statement generates Cross Join

Question

Considering Following linq statement

var users = from a in dbContext.Users
              select a;

var list = (from a in users
             let count = users.Count()
             where a.IsActive == true
             select new { a.UserId, count }).ToList();

If we check profiler for this linq statement , it shows cross join to get count for every record.

SELECT 
    [Extent1].[UserId] AS [UserId], 
    [GroupBy1].[A1] AS [C1]
    FROM  [dbo].[Users] AS [Extent1]
    CROSS JOIN  (SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[Users] AS [Extent2] ) AS [GroupBy1]
    WHERE 1 = [Extent1].[IsActive]

I think cross join overhead for sql statement and may cause a performance issue when records are in huge amounts.

As a solution I can move that data.Count() outside of linq statment and then put in in select , but it cause two db operation.

var count = (from a in dbContext.Users
                            select a).Count();

var list = (from a in dbContext.Users
                            where a.IsActive == true
                            select new { a.UserId, count }).ToList();

By looking into profiler ,It will generate below two operation.

SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        COUNT(1) AS [A1]
        FROM [dbo].[Users] AS [Extent1]
    )  AS [GroupBy1]

exec sp_executesql N'SELECT 
    [Extent1].[UserId] AS [UserId], 
    @p__linq__0 AS [C1]
    FROM [dbo].[Users] AS [Extent1]
    WHERE 1 = [Extent1].[IsActive]',N'@p__linq__0 int',@p__linq__0=26

Can anybody have better solution than this. Or can anybody suggest best way among putting let inside linq or getting it previously?

How is `data` declared? – MarcinJuraszek Nov 18 '15 at 06:52 — MarcinJuraszek, Nov 18 '15 at 06:52
Which version of EF are you using? – Dai Nov 18 '15 at 06:56 — Dai, Nov 18 '15 at 06:56
I M using EF 6, With .Net Framework 4.5. – Harshad Vekariya Nov 18 '15 at 06:57 — Harshad Vekariya, Nov 18 '15 at 06:57
@JenishRabadiya: Yup. It also generates cross join. – Harshad Vekariya Nov 18 '15 at 07:00 — Harshad Vekariya, Nov 18 '15 at 07:00

score 3 · Answer 1 · edited May 23 '17 at 12:23

3

I think cross join overhead for sql statement and may cause a performance issue when records are in huge amounts.

Not necessarily. Notice that this is joining to a sub-query, which is a single row/column of data (count). You can write this query in different ways, but in the end, it needs to join in order to return {UserId,count}. You can't return that data without a join. And the join it's doing right now is pretty efficient. So, I would recommend to not try'n optimize a problem you don't have (i.e. premature optimization).

UPDATE: adding an actual execution plan (see how to) for the following query. You can see that it's joining to a scalar value (e.g. only running the Count select query once).

Query:

SELECT 
[Extent1].[UserId] AS [UserId], 
[GroupBy1].[A1] AS [C1]
FROM  [dbo].[Users] AS [Extent1]
CROSS JOIN  (SELECT 
    COUNT(1) AS [A1]
    FROM [dbo].[Users] AS [Extent2] ) AS [GroupBy1]
WHERE 1 = [Extent1].[IsActive]

Execution plan:

edited May 23 '17 at 12:23

Community

1
1

answered Nov 18 '15 at 07:19

Eren Ersönmez

38,383
7
71
92

Agreed but dont you think that below statement generated as many select statement in join. i.e 10 records than 10 times select statement will execute. CROSS JOIN (SELECT COUNT(1) AS [A1] FROM [dbo].[Users] AS [Extent2] ) AS [GroupBy1]) – Harshad Vekariya Nov 18 '15 at 09:08
@HarshadVekariya no, it shouldn't. Updated the answer to show you an example execution plan. Notice it computes a scalar value (Count), and joins the other query to that scalar value (only running the count query once). – Eren Ersönmez Nov 18 '15 at 09:30
Thanks for posting execution plan but if u check it is taking almost half (50%) of total execution. – Harshad Vekariya Nov 18 '15 at 10:55
@HarshadVekariya that's a good thing. it has to scan twice: _once_ for the count and once for the other query. If it had to do a scan for every single user, it would have been more than 50%. – Eren Ersönmez Nov 18 '15 at 11:33

score 2 · Answer 2 · answered Nov 18 '15 at 07:19

There shouldn't be any performance issues with the generated sql. The cross join results in one record and the optimizer will only have to calculate it once regardless of the amount of active users in your table.

If you are not convinced compare the execution plan to your alternative. I can only think of using a sub select, but it doesn't look better to me.

Sub Select

SELECT 
    [UserId],
    (SELECT count(*) FROM [dbo].[Users]) as [Cnt]
FROM  [dbo].[Users]    
WHERE 1 = [IsActive]

'let' in Linq Statement generates Cross Join

2 Answers2