需要一些帮助lambda表达式从SQL查询

本文关键字：表达式 SQL 查询 lambda 帮助 | 更新日期: 2023-09-27 18:14:50

我有这个SQL查询:

select 
    SubCaseNumber, MySaleries.WorkDate, stscreator.name,  Status, SheetId
from 
    (select * 
     from 
         (select distinct 
              Sheets.SheetID SheetID, ShtInfos.SheetInfoID SheetInfoID, 
              ShtInfos.Creator_Id STSCreatorID, ShtUsers.User_ID  STSFitterID, 
              Sheets.Status, ShtInfos.workdate, ShtInfos.SubCase_SubCaseId 
          from [Sheets]
          left join [SheetInfoes] ShtInfos on sheets.SheetInfo_SheetInfoId = ShtInfos.SheetInfoId
          left join [SheetUsers] ShtUsers on Sheets.SheetID = ShtUsers.Sheet_SheetID) AllSheets 
      where 
          (stsfitterID = '08153661-6520-4435-81e6-6064084db74d' 
           or stscreatorid = '08153661-6520-4435-81e6-6064084db74d') 
          and Status = 1) MySaleries
join 
    [SubCases] SubCases on SubCase_SubCaseId = SubCases.SubCaseId
join 
    [AspNetUsers] STSCreator on STSCreator.Id = MySaleries.STSCreatorID;

(返回8行-正确)

我写了下面的Linq查询来实现Linq中的SQL:

from Sht in Sheets
join SI in SheetInfoes on Sht.SheetInfo_SheetInfoId equals SI.SheetInfoId
join SC in SubCases on SI.SubCase_SubCaseId equals SC.SubCaseId 
join ShtUsr in SheetUsers on Sht.SheetId equals ShtUsr.Sheet_SheetId
join STSUsr in AspNetUsers on SI.Creator_Id equals STSUsr.Id
where (ShtUsr.User_Id == "08153661-6520-4435-81e6-6064084db74d" || SI.Creator_Id == "08153661-6520-4435-81e6-6064084db74d") && SI.Status == 1
select new {SC.SubCaseNumber, SI.WorkDate, STSUsr.Name, Sht.Status, Sht.SheetId}

返回50行-应该是8行。

我有以下表格:

[SheetInfoes], [Sheets], [SubCases], [AspNetUsers], [SheetUsers]

我做错了什么?

需要一些帮助lambda表达式从SQL查询

您发布的SQL中的连接条件是左连接。代码中的连接只是没有任何过滤的连接。

的例子:

from maintable in Repo.T_Whatever 
from xxx in Repo.T_ANY_TABLE.Where(join condition).DefaultIfEmpty()

我会这样设置:

var query = (
from Sht in Sheets
from SheetInfoes
    .where(x => x.SheetInfoId == Sht.SheetInfo_SheetInfoId)
    .DefaultIfEmpty() // <== left join
from ShtUsr in SheetUsers
    .where(x => x.SheetId == ShtUsr.Sheet_SheetId)
    .DefaultIfEmpty() // <== left join
//You'll need to finish it

查看以下信息:Left Outer Joins

相关文章：