MVC外连接查询

开发工具与关键技术：MVC、C#、Layui
作者：张俊辉
撰写时间：2019年04月8日

• 在写代码时，有时候会发生在某些地方新增数据，然后在其他地方完善数据的情况，但偶尔会碰到需要联表新增的数据时，在所关联的表中没有查到数据那么整条数据都不会显示，那么也就意味着无法完善该条数据，这时候就要使用外连接查询了，如下是左外连接：

public ActionResult SelectSupplier(BsgridPage bsgridPage
{
     List<ListSupplierVo> listSupplier = (from tbSupplier in myModels.SYS_Supplier
                        join tbUser in myModels.SYS_User on tbSupplier.UserID equals tbUser.UserID into t1
                        from tb1 in t1.DefaultIfEmpty()
                        join tbSourceChannel in myModels.BS_SourceChannel on tbSupplier.SourceChannelID equals tbSourceChannel.SourceChannelID into t2
                        from tb2 in t2.DefaultIfEmpty()
                        join tbDataSource in myModels.BS_DataSource on tbSupplier.DataSourceID equals tbDataSource.DataSourceID into t3
                        from tb3 in t3.DefaultIfEmpty()
                        join tbPaymentMode in myModels.BS_PaymentMode on tbSupplier.PaymentModeID equals tbPaymentMode.PaymentModeID into t4
                        from tb4 in t4.DefaultIfEmpty()
                        join tbCurrency in myModels.BS_Currency on tbSupplier.CurrencyID equals tbCurrency.CurrencyID into t5
                        from tb5 in t5.DefaultIfEmpty()
                        join tbIndustryInvolved in myModels.BS_IndustryInvolved on tbSupplier.IndustryInvolvedID equals tbIndustryInvolved.IndustryInvolvedID into t6
                        from tb6 in t6.DefaultIfEmpty()
                        orderby tbSupplier.SupplierID
                        select new ListSupplierVo
                        {
	写入所查询的字段，联表后的表名为from关键字后的表名，例如：
	BankAccount=tbSupplier.BankAccount,//银行账户
	DataSource =tb3.DataSource,//数据来源
                        }).ToList(); 
     int numbet = listSupplier.Count();
     List<ListSupplierVo> mySupplier = listSupplier.Skip(bsgridPage.GetStartIndex()).Take(bsgridPage.pageSize).ToList();
     Bsgrids<ListSupplierVo> bsgrid = new Bsgrids<ListSupplierVo>(true, numbet, bsgridPage.curPage, mySupplier);
     return Json(bsgrid, JsonRequestBehavior.AllowGet);

 }

• 相对应的右外连接的写法与左外连接的区别是查询表的先后顺序不同例如：

左外连接：

List<ListSupplierVo> listSupplier = (from tbSupplier in myModels.SYS_Supplier
                               join tbUser in myModels.SYS_User on tbSupplier.UserID equals tbUser.UserID into t1
                               from tb1 in t1.DefaultIfEmpty()
								select new ListSupplierVo
                               {
								写入所查询的字段，联表后的表名为from关键字后的表名，例如：
								BankAccount=tbSupplier.BankAccount,//银行账户
								UserName =tb1.UserName,//跟进人
                               }).ToList(); 

右外连接：

List<ListSupplierVo> listSupplier = (from tbUser in myModels.SYS_User
                               join  tbSupplier in myModels.SYS_Supplier  on tbUser.UserID equals tbSupplier.UserID into t1
                               from tb1 in t1.DefaultIfEmpty()
								select new ListSupplierVo
                               {
								写入所查询的字段，联表后的表名为from关键字后的表名，例如：
								BankAccount=tbSupplier.BankAccount,//银行账户
								UserName =tb1.UserName,//跟进人
                               }).ToList();

效果图如下，方框内为未完善数据：