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
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.
Comments