如何用LINQ to SQL实现含LEFT JOIN等操作的非管理员用户投标查询

嘿，我来帮你把目标SQL转换成对应的LINQ to SQL实现，完全匹配你的需求！

首先假设你的实体类结构和ASP.NET Identity默认一致：Users是IdentityUser，Roles是IdentityRole，UserRoles是关联表，Bids包含BidID、PriceInPLN和外键UserId关联到Users.Id。

实现代码 #

// 先预计算全局最高投标价，避免重复执行子查询提升效率
var globalMaxBidPrice = dbContext.Bids.Max(b => (decimal?)b.PriceInPLN) ?? 0;

var nonAdminUsersWithBidStats = from user in dbContext.Users
                                // 左连接用户角色表，处理无角色的用户
                                join userRole in dbContext.UserRoles on user.Id equals userRole.UserId into userRoleGroup
                                from userRole in userRoleGroup.DefaultIfEmpty()
                                // 左连接角色表，筛选非管理员
                                join role in dbContext.Roles on userRole.RoleId equals role.Id into roleGroup
                                from role in roleGroup.DefaultIfEmpty()
                                where role == null || role.Name != "Admin"
                                // 左连接投标表，保留无投标的用户
                                join bid in dbContext.Bids on user.Id equals bid.UserId into bidGroup
                                from bid in bidGroup.DefaultIfEmpty()
                                // 按用户唯一信息分组
                                group bid by new { user.Id, user.UserName, user.Email } into userBidGroup
                                select new 
                                {
                                    UserId = userBidGroup.Key.Id,
                                    UserName = userBidGroup.Key.UserName,
                                    Email = userBidGroup.Key.Email,
                                    // 最高投标价，无投标则返回0
                                    HighestBidPrice = userBidGroup.Max(b => (decimal?)b.PriceInPLN) ?? 0,
                                    // 投标数量，只统计存在的投标记录
                                    BidCount = userBidGroup.Count(b => b != null),
                                    // 判断当前用户的最高投标是否为全局最高价
                                    IsGlobalHighestBid = (userBidGroup.Max(b => (decimal?)b.PriceInPLN) ?? 0) == globalMaxBidPrice
                                };

关键逻辑说明 #

• 筛选非管理员用户：通过两次左连接UserRoles和Roles，确保没有角色的用户也被保留，同时排除角色名称为Admin的用户（如果你的管理员角色名不同，记得修改这里）。
• 左连接投标表：使用into ... DefaultIfEmpty()实现SQL的LEFT JOIN，保证即使用户没有投标也会出现在结果中。
• 分组统计：按用户的Id、UserName、Email分组后，用Max计算最高投标价，Count统计有效投标数，最后对比全局最高投标价判断是否为全局最高价。
• 空值处理：用(decimal?)转换后再用?? 0处理无投标或无全局投标的情况，避免空引用异常。

如果不想提前计算全局最高价，也可以把子查询直接写在select语句里（不过预计算效率更高）：

// 子查询版，无需预计算全局最高价
var nonAdminUsersWithBidStats = from user in dbContext.Users
                                join userRole in dbContext.UserRoles on user.Id equals userRole.UserId into userRoleGroup
                                from userRole in userRoleGroup.DefaultIfEmpty()
                                join role in dbContext.Roles on userRole.RoleId equals role.Id into roleGroup
                                from role in roleGroup.DefaultIfEmpty()
                                where role == null || role.Name != "Admin"
                                join bid in dbContext.Bids on user.Id equals bid.UserId into bidGroup
                                from bid in bidGroup.DefaultIfEmpty()
                                group bid by new { user.Id, user.UserName, user.Email } into userBidGroup
                                select new 
                                {
                                    UserId = userBidGroup.Key.Id,
                                    UserName = userBidGroup.Key.UserName,
                                    Email = userBidGroup.Key.Email,
                                    HighestBidPrice = userBidGroup.Max(b => (decimal?)b.PriceInPLN) ?? 0,
                                    BidCount = userBidGroup.Count(b => b != null),
                                    IsGlobalHighestBid = (userBidGroup.Max(b => (decimal?)b.PriceInPLN) ?? 0) == 
                                                        (dbContext.Bids.Max(b2 => (decimal?)b2.PriceInPLN) ?? 0)
                                };

内容的提问来源于stack exchange，提问作者Misiu