使用Linq进行左连接查询-CSDN博客

本文介绍了在VS和MVC环境下，如何利用Linq的左查询方法来处理数据库操作。当一张表的数据需要全部展示，即使另一张表中没有对应的匹配项，也能通过左查询填充空缺，达到完整显示的效果。示例代码展示了控制器中如何应用DefaultIfEmpty()方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

开发工具与关键技术：VS、MVC
作者：何德润
撰写时间：2019.6.27

假如有两张表，第二张表有第一张表的外键ID。当我们连接这两张表时，要求要全部
查询出第一张表的数据，而第二张表只有两条数据时，就将没有的对应数据补空。要实现这样的功能时，我们就要用到linq查询中的左查询方法。
下面为控制器的代码：

 var list = (from tbluggageDeposit in myModel.B_luggageDeposit
                            join tbpassenger in myModel.S_passenger on
                            tbluggageDeposit.passengerID equals tbpassenger.passengerID
                            join tbbaggageType in myModel.B_baggageType on
                            tbluggageDeposit.baggageTypeID equals tbbaggageType.baggageTypeID
                            join tbpersonnel in myModel.S_personnel on
                            tbluggageDeposit.personnelID equals tbpersonnel.personnelID
                            join tbuser in myModel.S_User on
                            tbpersonnel.UserID equals tbuser.UserID
                            join tbbaggageClaim in myModel.B_baggageClaim on
                            tbluggageDeposit.luggageID equals tbbaggageClaim.luggageID into tbbClaim
                            from tbtbbClaim in tbbClaim.DefaultIfEmpty()
                            join tbpersonnel1 in myModel.S_personnel on
                            tbtbbClaim.personnelID equals tbpersonnel1.personnelID into tbbpersonnel1
                            from tbper1 in tbbpersonnel1.DefaultIfEmpty()                               
                            orderby tbluggageDeposit.luggageID
                            select new luggageDeposit
                            {
                                luggageID = tbluggageDeposit.luggageID,                      
                                storageNumber = tbluggageDeposit.storageNumber,                         
                                storageFee = tbluggageDeposit.storageFee,
                                baggageCount = tbluggageDeposit.baggageCount,                       
                                passengerName = tbpassenger.passengerName,
                                passengerNumber = tbpassenger.passengerNumber,
                                baggageType = tbbaggageType.baggageType,
                                baggageTypeID = tbluggageDeposit.baggageTypeID,
                                personnelNumber = tbpersonnel.personnelNumber,
                                storageName = tbuser.UserName,
                                storageTime1 = tbluggageDeposit.storageTime.ToString(),                                                                                                
                                baggPersonnelNumber = tbper1.personnelNumber,                     
                                Remarks = tbluggageDeposit.Remarks,
                                storageState = tbluggageDeposit.storageState,
                                storageDate = tbluggageDeposit.storageDate,
                            }).ToList();

DefaultIfEmpty()的定义为：返回指定序列的元素；如果序列为空，则返回单一实例集合中的类型参数的默认值。
在这里插入图片描述