时间戳

首页 > 解决方案 > 代替左连接的 Lambda 表达式

c# - 代替左连接的 Lambda 表达式

问题描述

我尝试将 T-SQL 转换为 lambda 表达式，但遇到了问题。数据不正确。

这是我的查询

SELECT A.* 
FROM 
    (SELECT UserId, MIN(ID) AS ID 
     FROM FingerMachineUsers  
     GROUP BY UserId ) A
LEFT OUTER JOIN 
    FingerTimeSheets B ON A.ID = B.UserNo 
                       AND B.DayOfCheck = '2018-08-02 00:00:00.000'
WHERE 
    B.UserNo IS NULL

这是我的 lambda 表达式

dbContext.FingerMachineUsers
         .GroupBy(x => x.UserId)
         .Select(g => new { ID = g.Min(p => p.ID), UserId=g.Select(p => p.UserId) })
         .GroupJoin(dbContext.FingerTimeSheets.Where(x=>x.DayOfCheck==shortDate),x=>x.ID,y=>y.UserNo,(x,y)=> new { ID = x, UserNo = y })
         .SelectMany(x=>x.UserNo.DefaultIfEmpty(),(x,y)=>new { x.ID,y.UserNo});

返回的数据不正确。

标签： c#.netlambda

解决方案

linq 查询应该是：

DateTime date = DateTime.Today;

var innerQuery = from x in db.FingerMachineUsers
                    group x.UserId by x.UserId into y
                    select new { UserId = y.Key, ID = y.Min() };

var query = from x in innerQuery
            join y in db.FingerTimeSheets on x.ID equals y.UserNo into z
            from y in z.Where(a => a.DayOfCheck == date).DefaultIfEmpty()
            where y == null || y.UserNo == null
            select x;

该查询或多或少等同于：

SELECT 
    [GroupBy1].[K1] AS [UserId], 
    [GroupBy1].[A1] AS [C1]
    FROM   (SELECT 
        [Extent1].[UserId] AS [K1], 
        MIN([Extent1].[UserId]) AS [A1]
        FROM [dbo].[FingerMachineUsers] AS [Extent1]
        GROUP BY [Extent1].[UserId] ) AS [GroupBy1]
    LEFT OUTER JOIN [dbo].[FingerTimeSheets] AS [Extent2] ON (([GroupBy1].[A1] = [Extent2].[UserNo]) OR (([GroupBy1].[A1] IS NULL) AND ([Extent2].[UserNo] IS NULL))) AND ([Extent2].[DayOfCheck] = @p__linq__0)
    WHERE [Extent2].[TimeSheetId] IS NULL OR [Extent2].[UserNo] IS NULL

(TimeSheetId的主键在哪里FingerTimeSheets)

FingerTimeSheets.UserNo关于 的可空性和 的含义有一些开放的观点B.UserNo IS NULL。可以为FingerTimeSheets.UserNo空吗？然后查询是正确的。如果FingerTimeSheets.UserNo不可为空，则将其更改where为：

where y == null

另一个需要解决的小问题是两者FingerMachineUsers.ID是否都可以为FingerTimeSheets.UserNo空。将 moddile 更改from y in z.Where()为：

from y in z.Where(a => UserNo != null && a.DayOfCheck == date).DefaultIfEmpty()

推荐阅读