Multi tool use

Clash Royale CLAN TAG

Lambda expression in place of a left join

I try to convert T-SQL to lambda expression but I met a problem. Data is not correct.

This is my query

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


This is my lambda expression

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});


Data returned is not correct.

FingerTimeSheets.UserNo

NULL

B.UserNo IS NULL

1 Answer
1

The linq query should be:

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;


The query is more or less equivalent to:

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


(where TimeSheetId is the primary key of FingerTimeSheets)

TimeSheetId

FingerTimeSheets

There are some open points about the nullability of FingerTimeSheets.UserNo and about the meaning of B.UserNo IS NULL. Is FingerTimeSheets.UserNo nullable? Then the query is correct as is. If FingerTimeSheets.UserNo isn't nullable then change the where to:

FingerTimeSheets.UserNo

B.UserNo IS NULL

FingerTimeSheets.UserNo

FingerTimeSheets.UserNo

where

where y == null


Another small problem that needs fixing is if both FingerMachineUsers.ID is nullable and FingerTimeSheets.UserNo is nullable. Change the moddile from y in z.Where() to:

FingerMachineUsers.ID

FingerTimeSheets.UserNo

from y in z.Where()

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


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.