Linq Left Outer Join with Count

EpoWilliam

I want to create this SQL query:

SELECT 
    a.[Seat], 
    b.[PlayerId], 
    b.[UserName], 
    b.[NickName],
    COUNT(c.PlayerId) AS Trophy
    FROM   [dbo].[tbl_PlayerTableSeat] AS a
    INNER JOIN [dbo].[tbl_Player] AS b ON a.[PlayerId] = b.[PlayerId]               
    INNER JOIN [dbo].[tbl_GameVirtualTable] AS d ON d.GameVirtualTableId = a.GameVirtualTableId
    LEFT OUTER JOIN [dbo].[tbl_PlayerTableWinning] AS c ON a.[PlayerId] = c.[PlayerId] AND c.GameTableId = d.GameTableId                
    WHERE a.GameVirtualTableId = 36
    GROUP BY a.[Seat], b.[PlayerId], b.[UserName], b.[NickName]

I have this Linq

var virtualTableSeatList = (from s in db.PlayerTableSeat
                        join p in db.Player on s.PlayerId equals p.PlayerId
                        join v in db.GameVirtualTable on s.GameVirtualTableId equals v.GameVirtualTableId
                        join w in db.PlayerTableWinning on new { X1 = s.PlayerId, X2 = v.GameTableId } equals new { X1 = w.PlayerId, X2 = w.GameTableId } into gj

                        from g in gj.DefaultIfEmpty()
                        where s.GameVirtualTableId == virtualGameTableId
                        group new { p, s } by new { p.PlayerId, s.Seat, p.NickName, p.UserName } into grp

                        select new VirtualTableSeatDto
                        {
                            PlayerId = grp.Key.PlayerId,
                            Seat = grp.Key.Seat,
                            NickName = grp.Key.NickName,
                            UserName = grp.Key.UserName,                                            
                            Trophy = grp.Count()
                        }
               ).ToList();

From SQL Profiler, the Linq generates this SQL query:

exec sp_executesql N'SELECT 
[GroupBy1].[K2] AS [PlayerId], 
 CAST( [GroupBy1].[K1] AS int) AS [C1], 
[GroupBy1].[K4] AS [NickName], 
[GroupBy1].[K3] AS [UserName], 
[GroupBy1].[A1] AS [C2]
FROM ( SELECT 
    [Extent1].[Seat] AS [K1], 
    [Extent2].[PlayerId] AS [K2], 
    [Extent2].[UserName] AS [K3], 
    [Extent2].[NickName] AS [K4], 
    COUNT(1) AS [A1]
    FROM    [dbo].[tbl_PlayerTableSeat] AS [Extent1]
    INNER JOIN [dbo].[tbl_Player] AS [Extent2] ON [Extent1].[PlayerId] = [Extent2].[PlayerId]
    INNER JOIN [dbo].[tbl_GameVirtualTable] AS [Extent3] ON [Extent1].[GameVirtualTableId] = [Extent3].[GameVirtualTableId]
    LEFT OUTER JOIN [dbo].[tbl_PlayerTableWinning] AS [Extent4] ON ([Extent1].[PlayerId] = [Extent4].[PlayerId]) AND ([Extent3].[GameTableId] = [Extent4].[GameTableId])
    WHERE [Extent1].[GameVirtualTableId] = @p__linq__0
    GROUP BY [Extent1].[Seat], [Extent2].[PlayerId], [Extent2].[UserName], [Extent2].[NickName]
)  AS [GroupBy1]',N'@p__linq__0 int',@p__linq__0=36

I want to change COUNT(1) AS [A1] to COUNT([Extent4].[PlayerId]) AS [A1]

so it can return correct data. I have no idea how to change the LinQ

Trophy = grp.Count()

so that it can count PlayerId of PlayerTableWinning instead of COUNT(1)


Updated: @Ivan Stoev

By adding the g into the group.

group new { p, s, g }

And sum the group

Trophy = grp.Sum(item => item.w != null ? 1 : 0)

It return the correct answer. However, it is using SUM instead of count. The SQL query generated is as below:

exec sp_executesql N'SELECT 
[GroupBy1].[K2] AS [PlayerId], 
 CAST( [GroupBy1].[K1] AS int) AS [C1], 
[GroupBy1].[K4] AS [NickName], 
[GroupBy1].[K3] AS [UserName], 
[GroupBy1].[A1] AS [C2]
FROM ( SELECT 
    [Filter1].[K1] AS [K1], 
    [Filter1].[K2] AS [K2], 
    [Filter1].[K3] AS [K3], 
    [Filter1].[K4] AS [K4], 
    SUM([Filter1].[A1]) AS [A1]
    FROM ( SELECT 
        [Extent1].[Seat] AS [K1], 
        [Extent2].[PlayerId] AS [K2], 
        [Extent2].[UserName] AS [K3], 
        [Extent2].[NickName] AS [K4], 
        CASE WHEN ( NOT (([Extent4].[GameTableId] IS NULL) AND ([Extent4].[PlayerId] IS NULL) AND ([Extent4].[GameRoundId] IS NULL))) THEN 1 ELSE 0 END AS [A1]
        FROM    [dbo].[tbl_PlayerTableSeat] AS [Extent1]
        INNER JOIN [dbo].[tbl_Player] AS [Extent2] ON [Extent1].[PlayerId] = [Extent2].[PlayerId]
        INNER JOIN [dbo].[tbl_GameVirtualTable] AS [Extent3] ON [Extent1].[GameVirtualTableId] = [Extent3].[GameVirtualTableId]
        LEFT OUTER JOIN [dbo].[tbl_PlayerTableWinning] AS [Extent4] ON ([Extent1].[PlayerId] = [Extent4].[PlayerId]) AND ([Extent3].[GameTableId] = [Extent4].[GameTableId])
        WHERE [Extent1].[GameVirtualTableId] = @p__linq__0
    )  AS [Filter1]
    GROUP BY [K1], [K2], [K3], [K4]
)  AS [GroupBy1]',N'@p__linq__0 int',@p__linq__0=36
Ivan Stoev

The only (but significant) difference between SQL COUNT(field) and COUNT(1) is that the former is excluding the NULL values, which when applied to the normally required field from the right side of a left outer join like in your case produces a different result when there are no matching records - the former returns 0 while the latter returns 1.

The "natural" LINQ equivalent would be Count(field != null), but that unfortunately is translated to a quite different SQL by the current EF query provider. So in such cases I personally use the closer equivalent expression Sum(field != null ? 1 : 0) which produces a much better SQL.

In order to apply the above to your query, you'll need an access to w inside the grouping, so change

group new { p, s }

to

group new { p, s, w }

and then use

Trophy = grp.Sum(item => item.w != null ? 1 : 0)

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Equivalent of Left outer join in LINQ

From Dev

Linq left outer join with custom comparator

From Dev

Left outer join using LINQ -- understanding the code

From Dev

Convert SQL (LEFT OUTER JOIN) to LinQ

From Dev

Linq left outer join with multiple condition

From Dev

How to use left outer join in Linq

From Dev

Convert SQL (Left Outer Join's) to LINQ

From Dev

Translate SQL to LINQ using LEFT OUTER JOIN

From Dev

SQL incremential count for X with a Left Outer Join

From Dev

SQL COUNT() function and LEFT OUTER JOIN

From Dev

Mysql double left outer join with count

From Dev

LINQ Left Join, Group By and Count

From Dev

Converting SQL to LINQ (inner join into a left outer join) C#

From Dev

Converting SQL to LINQ (inner join into a left outer join) C#

From Dev

MYSQL Count, LEFT OUTER JOIN with 0 count on a simple case

From Dev

LINQ left join with group by and correct count

From Dev

LINQ left join with group by and correct count

From Dev

LINQ with Lambda expression - Left Join ,GroupBy ,Count

From Dev

Unable to convert SQL Query to LINQ Query for Left Outer Join

From Dev

Issue with joining tables in LINQ when needing a left outer join type

From Dev

Extension method for IQueryable left outer join using LINQ

From Dev

LINQ to Sql Left Outer Join with Group By and Having Clause

From Dev

LINQ Left outer join - Object reference not set to an instance of an object

From Dev

Linq 2 Left Outer Join With Multiple Columns in second one

From Dev

Extension method for IQueryable left outer join using LINQ

From Dev

Null value in the result of a left outer join linq causes error

From Dev

How to do a double left outer join in Linq query syntax(or fluent)

From Dev

Conversion Of left Outer join with a where clause to Linq-to-sql

From Dev

Linq Left Outer Join Two Tables on Two Fields

Related Related

  1. 1

    Equivalent of Left outer join in LINQ

  2. 2

    Linq left outer join with custom comparator

  3. 3

    Left outer join using LINQ -- understanding the code

  4. 4

    Convert SQL (LEFT OUTER JOIN) to LinQ

  5. 5

    Linq left outer join with multiple condition

  6. 6

    How to use left outer join in Linq

  7. 7

    Convert SQL (Left Outer Join's) to LINQ

  8. 8

    Translate SQL to LINQ using LEFT OUTER JOIN

  9. 9

    SQL incremential count for X with a Left Outer Join

  10. 10

    SQL COUNT() function and LEFT OUTER JOIN

  11. 11

    Mysql double left outer join with count

  12. 12

    LINQ Left Join, Group By and Count

  13. 13

    Converting SQL to LINQ (inner join into a left outer join) C#

  14. 14

    Converting SQL to LINQ (inner join into a left outer join) C#

  15. 15

    MYSQL Count, LEFT OUTER JOIN with 0 count on a simple case

  16. 16

    LINQ left join with group by and correct count

  17. 17

    LINQ left join with group by and correct count

  18. 18

    LINQ with Lambda expression - Left Join ,GroupBy ,Count

  19. 19

    Unable to convert SQL Query to LINQ Query for Left Outer Join

  20. 20

    Issue with joining tables in LINQ when needing a left outer join type

  21. 21

    Extension method for IQueryable left outer join using LINQ

  22. 22

    LINQ to Sql Left Outer Join with Group By and Having Clause

  23. 23

    LINQ Left outer join - Object reference not set to an instance of an object

  24. 24

    Linq 2 Left Outer Join With Multiple Columns in second one

  25. 25

    Extension method for IQueryable left outer join using LINQ

  26. 26

    Null value in the result of a left outer join linq causes error

  27. 27

    How to do a double left outer join in Linq query syntax(or fluent)

  28. 28

    Conversion Of left Outer join with a where clause to Linq-to-sql

  29. 29

    Linq Left Outer Join Two Tables on Two Fields

HotTag

Archive