使用 LINQ 的临时表

debugcn 发表于 Dev

网络开发

我有创建临时表的 SQL，稍后将其用于进一步的 SELECT

; WITH tblOrder AS
                (
                SELECT ROW_NUMBER() OVER(ORDER BY I.IsHead DESC, I.InsureeID ASC) AS RowNo, I.InsureeID FROM tblInsuree I
                INNER JOIN(SELECT FamilyID FROM tblInsuree WHERE ValidityTo IS NULL AND CHFID = 1111 AND IsHead = 1) H ON H.FamilyID=I.FamilyID
                WHERE I.ValidityTo IS NULL 
                )

                SELECT I.CHFID InsuranceNumber, I.OtherNames, I.LastName, I.DOB BirthDate, I.Gender, F.Poverty PoveryStatus, C.ConfirmationTypeCode ConfirmationType, F.ConfirmationNo ConfirmationNo, F.FamilyAddress PermanentAddress, I.Marital MaritalStatus, I.CardIssued BeneficiaryCard, l.LocationCode CurrentVillageCode, I.CurrentAddress CurrentAddress, P.Profession, I.Education, I.Phone PhoneNumber, I.Email, I.TypeOfId IdentificationType, I.passport IdentificationNumber, HF.HFCode FSPCode  FROM tblInsuree I
                INNER JOIN tblOrder O ON O.InsureeID = I.InsureeID
                INNER JOIN tblFamilies F ON F.FamilyID = I.FamilyID
                LEFT OUTER JOIN tblLocations L ON L.LocationId = F.LocationId
                LEFT OUTER JOIN tblConfirmationTypes C ON C.ConfirmationTypeCode = F.ConfirmationType
                LEFT OUTER JOIN tblFamilyTypes G ON G.FamilyTypeCode= F.FamilyType
                LEFT OUTER JOIN tblProfessions P ON P.ProfessionId= I.Profession
                LEFT OUTER JOIN tblHF HF ON HF.HfID= I.HFID
                WHERE
                O.RowNo = 1
                AND F.ValidityTo IS NULL
                AND I.ValidityTo IS NULL
                AND L.ValidityTo IS NULL
                AND HF.ValidityTo IS NULL

我想用 LINQ 替换代码

; WITH tblOrder AS
                (
                SELECT ROW_NUMBER() OVER(ORDER BY I.IsHead DESC, I.InsureeID ASC) AS RowNo, I.InsureeID FROM tblInsuree I
                INNER JOIN(SELECT FamilyID FROM tblInsuree WHERE ValidityTo IS NULL AND CHFID = 1111 AND IsHead = 1) H ON H.FamilyID=I.FamilyID
                WHERE I.ValidityTo IS NULL 
                )

您如何将这部分代码转移到 LINQ，然后再与其余部分一起使用？

雷纳特

看起来这可能有效：

   var tblOrder = (from I in dbContext.tblInsuree
                      join H in dbContext.tblInsuree.Where(a => a.ValidityTo == null && a.CHFID == 1111 && a.IsHead == 1) on I.FamilyID equals H.FamilyID
                  where I.ValidityTo == null
                  orderby I.IsHead descending, I.InsureeID
                  select I.InsureeID)
                      .ToArray()
                      .Select((InsureeID, RowNo) => new {RowNo = RowNo + 1, InsureeID })
                      .ToArray();

根据@NetDev 的评论编辑

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。