LINQ之LEFT JOIN示例

以下是使用 LINQ 实现左外连接的方式。 您应该使用 GroupJoin (join...into 语法)：

from d in context.dc_tpatient_bookingd
join bookingm in context.dc_tpatient_bookingm
     on d.bookingid equals bookingm.bookingid into bookingmGroup
from m in bookingmGroup.DefaultIfEmpty()
join patient in dc_tpatient
     on m.prid equals patient.prid into patientGroup
from p in patientGroup.DefaultIfEmpty()
// ... other joins here
where d.processid == 6 &&
      ((m.branchId == 1 && d.DestinationBranchID == 0) ||
       (d.DestinationBranchID == 1 && d.sendstatus == "R"))
// ... other conditions here
orderby d.priority descending, m.bookingid
select new {
   d.bookingid,
   d.labid,
   d.processid,
   p.prid,
   p.prno,
   m.bookingid // need for grouping
} into x
group x by x.bookingid into g
select g

此查询连接三个表。 您可以以相同的方式加入其余的表。

---

使用 LinQ 进行多个左连接和内连接

var queryEncFor = (from enc in db.encf
                   join forF in db.forf on
                   new
                   {
                       enc.numero,
                       enc.manual,
                       enc.comproba,
                       enc.tipo_fac,
                       enc.sucursal
                   }
                   equals
                   new
                   {
                       forF.numero,
                       forF.manual,
                       forF.comproba,
                       forF.tipo_fac,
                       forF.sucursal
                   }
                   join plane in db.Planes on forF.num_pla equals plane.Numero into leftedPlanes
                   from leftedPlane in leftedPlanes.DefaultIfEmpty()

                   join tarjeta in db.Tarjetas on leftedPlane?.IdTarjeta equals tarjeta.ID into leftedTarjetas
                   from leftedTarjeta in leftedTarjetas.DefaultIfEmpty()

                   where enc.documento == 1
                   select new UnionEncForFact
                   {
                       numero = enc.numero,
                       cuo_pla = forF.cuo_pla == null ? 0 : forF.cuo_pla.Value,
                       num_pla = forF.num_pla,
                       Descripcion = leftedPlane?.Descripcion,
                       Nombre = leftedTarjeta?.Nombre ?? 0
                   }).ToList();

---

如何使用 LINQ 左连接多个表

IQueryable<Job> jobs = (from j in _db.Jobs

                join jt in _db.JobTranslators on j.Id equals jt.JobId into jts
                from jtResult in jts.DefaultIfEmpty()

                join jr in _db.JobRevisors on jtResult.Id equals jr.JobId into jrs
                from jrResult in jrs.DefaultIfEmpty()


                join u in _db.Users on jtResult.UserId equals u.Id into jtU
                from jtUResult in jtU.DefaultIfEmpty()

                where jtUResult.Id == userId

                orderby j.Id

                select j).Concat(
                from j in _db.Jobs

                join jt in _db.JobTranslators on j.Id equals jt.JobId into jts
                from jtResult in jts.DefaultIfEmpty()

                join jr in _db.JobRevisors on jtResult.Id equals jr.JobId into jrs
                from jrResult in jrs.DefaultIfEmpty()

                join u in _db.Users on jrResult.UserId equals u.Id into jrU
                from jrUResult in jrU.DefaultIfEmpty()

                where jtUResult.Id == userId

                orderby j.Id

                select j
                ).Distinct()

---

多连接应该看起来非常相似 - 它变得非常冗长，但我会尝试一下。 您可能还需要在最后的 where 行中进行一些 null 检查。

var personalInfoQuery = from t in crnnsupContext.Tombstones
                        join p in crnnsupContext.ProvStates on t.ProvinceState equals p.ProvinceStateID into group1
                        from g1 ini group1.DefaultIfEmpty()
                        join n in crnnsupContext.NursingSchools on g1.NursingSchool equals n.SchoolID into group2
                        from g2 in group2.DefaultIfEmpty()
                        join i in crnnsupContext.InitialEducations on g2.InitialEducation equals                                   SqlFunctions.StringConvert((double)i.InitalEducationID, 1) into group3
                        from g3 in group3.DefaultIfEmpty()
                        join g in crnnsupContext.tbl_GraduatedProvCountry on g3.GradPovCountry equals g.id into group4
                        from g4 in group4.DefaultIfEmpty()
                        where g4 == null || g4.RegNumber == _username
                        select new CPersonalInfo
                        {
                            ProvState = p,
                            Tombstone = t,
                            NursingSchool = n,
                            InitialEducation = i,
                            GraduatedProvCountry = g,
                         };