时间戳

首页 > 解决方案 > Linq 中的 Join 链

c# - Linq 中的 Join 链

问题描述

我有这种情况：

SELECT I.CHFID InsuranceNumber, I.OtherNames, I.LastName, I.DOB BirthDate, I.Gender, F.Poverty PoveryStatus, C.ConfirmationTypeCode ConfirmationType, F.FamilyType GroupType, 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 tblFamilies F
LEFT OUTER JOIN tblInsuree I ON F.InsureeID = I.InsureeID
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 
I.CHFID=@CHFID

我在 LINQ 中写了这个：

var x = imisContext.TblFamilies
                        .Join(imisContext.TblInsuree,
                            f => f.InsureeId,
                            i => i.InsureeId,
                            (f, i) => new { TblFamilies = f, TblInsuree = i })
                        .Join(imisContext.TblLocations,
                            l => l.TblFamilies.LocationId,
                            i => i.LocationId,
                            (f, i) => new { TblFamilies = f, TblLocations = i })
                        .Join(imisContext.TblConfirmationTypes,
                            c => c.TblFamilies.TblFamilies.ConfirmationType,
                            i => i.ConfirmationType,
                            (c, i) => new { TblFamilies = c, TblConfirmationTypes = i })
                        .Join(imisContext.TblFamilyTypes,
                            c => c.TblFamilies.TblFamilies.TblFamilies.FamilyType,
                            i => i.FamilyType,
                            (c, i) => new { TblFamilies = c, TblFamilyTypes = i })
                        .Join(imisContext.TblProfessions,
                            c => c.TblFamilies.TblFamilies.TblFamilies.TblInsuree.Profession,
                            i => i.ProfessionId,
                            (c, i) => new { TblInsuree = c, TblProfessions = i })
                        .Join(imisContext.TblHf,
                            c => c.TblInsuree.TblFamilies.TblFamilies.TblFamilies.TblInsuree.Hfid,
                            i => i.HfId,
                            (c, i) => new { TblInsuree = c, TblHf = i })
                        .Where(r => r.TblInsuree.TblInsuree.TblFamilies.TblFamilies.TblFamilies.TblInsuree.Hfid == insureeNumberINT);

我对此有疑问：

c => c.TblInsuree.TblFamilies.TblFamilies.TblFamilies.TblInsuree.Hfid

这是应该的吗？这些表中有太多嵌套

这只是整体的一部分

标签： c#linq

解决方案

与其在加入时收集，不如在加入时展平：

var ans = imisContext.TblFamilies
                     .Join(imisContext.TblInsuree,
                         f => f.InsureeId,
                         i => i.InsureeId,
                         (f, i) => new { TblFamilies = f, TblInsuree = i })
                     .Join(imisContext.TblLocations,
                         fi => fi.TblFamilies.LocationId,
                         l => l.LocationId,
                         (fi, l) => new { fi.TblFamilies, fi.TblInsuree, TblLocations = l })
                     .Join(imisContext.TblConfirmationTypes,
                         fil => fil.TblFamilies.ConfirmationType,
                         c => c.ConfirmationType,
                         (fil, c) => new { fil.TblFamilies, fil.TblInsuree, fil.TblLocations, TblConfirmationTypes = c })
                     .Join(imisContext.TblFamilyTypes,
                         filc => filc.TblFamilies.FamilyType,
                         ft => ft.FamilyType,
                         (filc, ft) => new { filc.TblFamilies, filc.TblInsuree, filc.TblLocations, filc.TblConfirmationTypes, TblFamilyTypes = ft })
                     .Join(imisContext.TblProfessions,
                         filcft => filcft.TblInsuree.Profession,
                         p => p.ProfessionId,
                         (filcft, p) => new { filcft.TblFamilies, filcft.TblInsuree, filcft.TblLocations, filcft.TblConfirmationTypes, filcft.TblFamilyTypes, TblProfessions = p })
                     .Join(imisContext.TblHf,
                         c => c.TblInsuree.Hfid,
                         h => h.HfId,
                         (filcftp, h) => new { filcftp.TblFamilies, filcftp.TblInsuree, filcftp.TblLocations, filcftp.TblConfirmationTypes, filcftp.TblFamilyTypes,filcftp.TblProfessions, TblHf = h })
                     .Where(filcftph => filcftph.TblInsuree.Hfid == insureeNumberINT);

这是查询语法具有优势的一种情况，因为它可以透明地处理展平：

var qans = from f in imisContext.TblFamilies
           join i in imisContext.TblInsuree on f.InsureeId equals i.InsureeId
           join l in imisContext.TblLocations on f.LocationId equals l.LocationId
           join c in imisContext.TblConfirmationTypes on f.ConfirmationType equals c.ConfirmationType
           join ft in imisContext.TblFamilyTypes on f.FamilyType equals ft.FamilyType
           join p in imisContext.TblProfessions on i.Profession equals p.ProfessionId
           join h in imisContext.TblHf on i.Hfid equals h.HfId
           where i.Hfid == insureeNumberINT
           select new { f, i, l, c, ft, p, h };

推荐阅读