LINQ to SQL LEFT JOIN, GROUP BY, COUNT, MAX and subquery

Question

I have 3 tables: Users, Roles, Bids.
Two of them is created by ASP.NET Identity, Bids is my custom table (it is secondary to Users table)

I'm trying to get all users that aren't in specific role (all non admin users) and show number of bids (they can be null - I have left join), show highest bid per user and show if that bid is max.

SQL query was quire easy:

SELECT
    U.Id
   ,U.UserName
   ,U.Email
   ,ISNULL(MAX(B.PriceInPLN), 0)
   ,COUNT(B.BidID)
   ,IIF(MAX(B.PriceInPLN)=(SELECT TOP 1 PriceInPLN FROM Bids B2 (NOLOCK) ORDER BY PriceInPLN DESC),1,0 ) AS Max
FROM
    AspNetUsers U ( NOLOCK )
    JOIN AspNetUserRoles UR ( NOLOCK ) ON U.Id = UR.UserId
    LEFT JOIN Bids B ( NOLOCK ) ON B.ApplicationUserId = U.Id
WHERE
    UR.RoleId != '9508f9d2-12fb-4175-89a7-3275cb7616ae'
GROUP BY
    U.Id
   ,U.UserName
   ,U.Email

But I have trouble creating correct LINQ query. Here is what I have so far:

var users =(
from u in db.Users
where !u.Roles.Select(r => r.RoleId).Contains(adminRoleId)//everyone except admins
join b in db.Bids on u equals b.ApplicationUser into ub
from subset in ub.DefaultIfEmpty()//LEFT JOIN
group subset by new { u.Id, u.UserName, u.Email, u.EmailConfirmed, u.Online } into grouped
select new UserReturnModel
{
    Id = grouped.Key.Id,
    Name = grouped.Key.UserName,
    Email = grouped.Key.Email,
    EmailConfirmed = grouped.Key.EmailConfirmed,
    Online = grouped.Key.Online,
    BidsCount = grouped.Count(c => c.ApplicationUserId == grouped.Key.Id),
    PriceInPLN = grouped.Max(c => c.PriceInPLN),
    IsMax = (grouped.Max(c=>c.PriceInPLN) == db.Bids.Max(b=>b.PriceInPLN))
}).ToList();

I can't get Max column to work correctly - it always says true.
Here are column definitions to easily show what I want to get:

Id = user id,
Name = user name,
Email = user email,
BidsCount = number of bids created by that user,
PriceInPLN = max price for that user,
IsMax = is this user offer highest comparing to other offers, if there are no offers this should be false

I'm using that in ASP.NET, when debugging I've noticed that SQL query created by LINQ is large:

SELECT 
    1 AS [C1], 
    [Project6].[Id] AS [Id], 
    [Project6].[UserName] AS [UserName], 
    [Project6].[Email] AS [Email], 
    [Project6].[EmailConfirmed] AS [EmailConfirmed], 
    [Project6].[Online] AS [Online], 
    [Project6].[C4] AS [C2], 
    [Project6].[C1] AS [C3], 
    CASE WHEN (([Project6].[C2] = [GroupBy3].[A1]) OR (([Project6].[C2] IS NULL) AND ([GroupBy3].[A1] IS NULL))) THEN cast(1 as bit) WHEN ( NOT (([Project6].[C3] = [GroupBy4].[A1]) AND ((CASE WHEN ([Project6].[C3] IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END) = (CASE WHEN ([GroupBy4].[A1] IS NULL) THEN cast(1 as bit) ELSE cast(0 as bit) END)))) THEN cast(0 as bit) END AS [C4]
    FROM    (SELECT 
        [Project3].[C1] AS [C1], 
        [Project3].[C2] AS [C2], 
        [Project3].[C3] AS [C3], 
        [Project3].[Id] AS [Id], 
        [Project3].[Online] AS [Online], 
        [Project3].[Email] AS [Email], 
        [Project3].[EmailConfirmed] AS [EmailConfirmed], 
        [Project3].[UserName] AS [UserName], 
        (SELECT 
            COUNT(1) AS [A1]
            FROM ( SELECT 
                [Extent4].[Id] AS [Id], 
                [Extent4].[Online] AS [Online], 
                [Extent4].[Email] AS [Email], 
                [Extent4].[EmailConfirmed] AS [EmailConfirmed], 
                [Extent4].[UserName] AS [UserName], 
                [Extent5].[ApplicationUserId] AS [ApplicationUserId]
                FROM  [dbo].[AspNetUsers] AS [Extent4]
                INNER JOIN [dbo].[Bids] AS [Extent5] ON [Extent5].[ApplicationUserId] = [Extent4].[Id]
                WHERE ( NOT EXISTS (SELECT 
                    1 AS [C1]
                    FROM [dbo].[AspNetUserRoles] AS [Extent6]
                    WHERE ([Extent4].[Id] = [Extent6].[UserId]) AND ([Extent6].[RoleId] = @p__linq__0)
                )) AND ([Project3].[Id] = [Extent4].[Id]) AND ([Project3].[UserName] = [Extent4].[UserName]) AND (([Project3].[Email] = [Extent4].[Email]) OR (([Project3].[Email] IS NULL) AND ([Extent4].[Email] IS NULL))) AND ([Project3].[EmailConfirmed] = [Extent4].[EmailConfirmed]) AND ([Project3].[Online] = [Extent4].[Online]) AND ([Extent5].[ApplicationUserId] = [Project3].[Id])
            )  AS [Project5]) AS [C4]
        FROM ( SELECT 
            [GroupBy1].[A1] AS [C1], 
            [GroupBy1].[A2] AS [C2], 
            [GroupBy1].[A3] AS [C3], 
            [GroupBy1].[K1] AS [Id], 
            [GroupBy1].[K2] AS [Online], 
            [GroupBy1].[K3] AS [Email], 
            [GroupBy1].[K4] AS [EmailConfirmed], 
            [GroupBy1].[K5] AS [UserName]
            FROM ( SELECT 
                [Project2].[Id] AS [K1], 
                [Project2].[Online] AS [K2], 
                [Project2].[Email] AS [K3], 
                [Project2].[EmailConfirmed] AS [K4], 
                [Project2].[UserName] AS [K5], 
                MAX([Project2].[PriceInPLN]) AS [A1], 
                MAX([Project2].[PriceInPLN]) AS [A2], 
                MAX([Project2].[PriceInPLN]) AS [A3]
                FROM ( SELECT 
                    [Extent1].[Id] AS [Id], 
                    [Extent1].[Online] AS [Online], 
                    [Extent1].[Email] AS [Email], 
                    [Extent1].[EmailConfirmed] AS [EmailConfirmed], 
                    [Extent1].[UserName] AS [UserName], 
                    [Extent2].[PriceInPLN] AS [PriceInPLN], 
                    [Extent2].[ApplicationUserId] AS [ApplicationUserId]
                    FROM  [dbo].[AspNetUsers] AS [Extent1]
                    LEFT OUTER JOIN [dbo].[Bids] AS [Extent2] ON [Extent1].[Id] = [Extent2].[ApplicationUserId]
                    WHERE  NOT EXISTS (SELECT 
                        1 AS [C1]
                        FROM [dbo].[AspNetUserRoles] AS [Extent3]
                        WHERE ([Extent1].[Id] = [Extent3].[UserId]) AND ([Extent3].[RoleId] = @p__linq__0)
                    )
                )  AS [Project2]
                GROUP BY [Project2].[Id], [Project2].[Online], [Project2].[Email], [Project2].[EmailConfirmed], [Project2].[UserName]
            )  AS [GroupBy1]
        )  AS [Project3] ) AS [Project6]
    CROSS JOIN  (SELECT 
        MAX([Extent7].[PriceInPLN]) AS [A1]
        FROM [dbo].[Bids] AS [Extent7] ) AS [GroupBy3]
    CROSS JOIN  (SELECT 
        MAX([Extent8].[PriceInPLN]) AS [A1]
        FROM [dbo].[Bids] AS [Extent8] ) AS [GroupBy4]

Can this be simplified?

Here is model of my my database generated by Entity Framework Power Tools:

I realize you are trying to do this in LINQ but you should still probably read this article about NOLOCK. It is far more sinister than most people realize. https://blogs.sentryone.com/aaronbertrand/bad-habits-nolock-everywhere/ — Sean Lange, Apr 18 '18 at 14:29
@SeanLange I've created that SQL query just to have idea what I'm trying to do with LINQ. I'm aware that I shouldn't add NOLOCK everywhere. I'll definitively will read article You linked :) — Misiu, Apr 18 '18 at 14:31
I wish I could help you with the LINQ stuff but I can't even spell it correctly. I have seen queries generated from LINQ that are just shockingly complicated and inefficient. This is pretty common with LINQ and other ORMs I have been exposed to. Such is the nature of writing code that writes code. It isn't going to be as efficient as a person doing it, assuming that person knows what they are doing. — Sean Lange, Apr 18 '18 at 14:44
Do you not have a Navigation property from `Users` to `Bids`? — NetMage, Apr 18 '18 at 21:01
@NetMage yes I have. Can I use it somehow in LINQ? I thought that I'm using it already. — Misiu, Apr 18 '18 at 21:04
Yes, you shouldn't need to join `db.Bids` in that case. Can you show the models? — NetMage, Apr 18 '18 at 21:43

NetMage · Accepted Answer · 2018-04-19T20:41:51.577

I'm not sure why you modified your LINQ query so much from the original SQL query, but translating the SQL query closer to literally using the rules from my SQL to LINQ Recipe I get

var MaxPriceInPLN = db.Bids.Max(b => b.PriceInPLN);
var ans = from U in db.Users
          where !U.Roles.Any(r => r.RoleId == adminRoleId)
          join b in db.Bids on U.Id equals b.ApplicationUserId into bj
          from b in bj.DefaultIfEmpty()
          group new { U, b } by new { U.Id, U.UserName, U.Email } into Ubg
          let maxBidPriceInPLN = Ubg.Max(Ub => Ub.b.PriceInPLN)
          select new {
              Ubg.Key.Id,
              Ubg.Key.UserName,
              Ubg.Key.Email,
              PriceInPLN = maxBidPriceInPLN ?? 0,
              BidsCount = Ubg.Count(Ub => Ub.b != null),
              IsMax = maxBidPriceInPLN == MaxPriceInPLN
          };

You can simplify the LINQ query by using the EF navigation properties to hide the join from your query:

var MaxPriceInPLN = db.Bids.Max(b => b.PriceInPLN);
var ans = from U in db.Users
          where !U.Roles.Any(r => r.RoleId == adminRoleId)
          let maxBidPriceInPLN = U.Bids.Max(b => b.PriceInPLN)
          select new {
              U.Id,
              U.UserName,
              U.Email,
              PriceInPLN = maxBidPriceInPLN ?? 0,
              BidsCount = U.Bids.Count(),
              IsMax = maxBidPriceInPLN == MaxPriceInPLN
          };

Why do You group two times? I don't use LINQ so often, so it's still quite new to me. — Misiu, Apr 19 '18 at 14:25
@Misiu Where do you see two groupings? There is just one `group`...`by`. — NetMage, Apr 19 '18 at 17:40
Sorry about that, there is one group new { } and then by. I've modified Your query a bit (I've added default value to MaxPriceInPLN) and it works perfect. Thank You. Now I must search for more materials about LINQ. — Misiu, Apr 19 '18 at 19:04
@Misiu Added another version that uses the Navigation property instead of the explicit join, which also removes the need for a group by (though that wasn't actually needed in LINQ in this case). — NetMage, Apr 19 '18 at 20:43

LINQ to SQL LEFT JOIN, GROUP BY, COUNT, MAX and subquery

1 Answers1