如何在mvc中使用LINQ应用外连接。内连接正在工作，但外连接给出以下错误..为什么？ [英] How can I apply outer join using LINQ in mvc. Inner join is working but outer join gives following error..why?

问题描述

这个错误即将来临。可以尝试解决它。​​

发生了'System.NullReferenceException'类型的异常App_Web_linqquery.cshtml.a8d08dba.0r5s74cf.dll但未在用户代码中处理< br /> 
< br /> 
附加信息：对象引用未设置为对象的实例。

我的尝试：

 命名空间 WebApplication4Linq.Models 
 {
  public   class 员工
 {
 [Key] 
  public   int  EmpId { get ;  set ; } 
  public   string  EmpName { get ;  set ; } 
  public   string  EmpCode { get ;  set ; } 
  public   string  DeptId { get ;  set ; } 
 
  public   static 列表< employee> GetEmployees（）
 {
 List< employee> employees =  new 列表< employee>（）; 
 employees.Add（ new 员工{EmpId =  1 ，EmpName =   Shyam，EmpCode =   AAA，DeptId =   D01}）; 
 employees.Add（ new 员工{EmpId =  2 ，EmpName =   Raja，EmpCode =   BBB，DeptId =   D03}）; 
 employees.Add（ new 员工{EmpId =  3 ，EmpName =   Hari，EmpCode =   CCC，DeptId =   D04}）; 
 employees.Add（ new 员工{EmpId =  4 ，EmpName =   Krushna，EmpCode =   DDD，DeptId =   D03}）; 
 employees.Add（ new 员工{EmpId =  5 ，EmpName =   Mohan，EmpCode =   EEE，DeptId =   D02}）; 
 
 返回员工; 
} 
} 
 
} 

--------------

 命名空间 WebApplication4Linq.Models 
 {
  public   class 部门
 {
 [Key] 
 
  public   string  DeptName { get ;  set ; } 
  public   string  DeptCode { get ;  set ; } 
  public   string  DeptId { get ;  set ; } 
 
  public   static 列表< department> GetDepts（）
 {
 List< department> depts =  new  List< department>（）; 
 depts.Add（ new 部门{DeptName =   CS，DeptCode =   ZZ，DeptId =   D01}）; 
 depts.Add（ new 部门{DeptName =   EEE，DeptCode =   XX，DeptId =   D02}）; 
 depts.Add（ new 部门{DeptName =   MCA，DeptCode =   YY，DeptId =   D03}）; 
 
  return  depts; 
} 
} 
} 

-------------------- --- viewmodel = ----------

 命名空间 WebApplication4Linq.Models 
 {
  public   class  ViewModel 
 {
  public 员工员工{ get ;  set ; } 
  public 部门depts { get ;  set ; } 
} 
} 
 -------------控制器--------------------- 
  public   class  HomeController：Controller 
 {
  public  ActionResult LinqQuery（）
 {
 ViewModel mymodel =  new  ViewModel（）; 
 
列表< employee> emps = Employee.GetEmployees（）; 
列表< department> dept = Department.GetDepts（）; 
 
  var  outerjoin =（ from  empn  in  emps 
  join  den  in  dept 
 on empn .DeptId等于den.DeptId 
 进入 abc 
 来自 den   abc.DefaultIfEmpty（）
 选择  new  ViewModel（）
 {
 
 employees = empn，
 depts = den 
}）; 
  return 查看（outerjoin）; 

----------- --------（.cshtml）------------------

 @ model IEnumerable < span class =code-keyword><   webapplication4linq.models.viewmodel  >  
 
 @using（Html.BeginForm（））
 {
 <   h2  > 索引页<   / h2  >  
 
 <   hr     /  >  
 <   h2  > 员工数据<   / h2  >  <   hr     /  >  
 <  表 >  
 <   tr  >  
 
 <  < span class =code-leadattribute> th  >  EmpName <   / th  >  
 <  < span class =code-leadattribute> th  >  EmpCode <   / th  >  
 <   th  >  DeptName <   / th  >  
 <   th  >  DeptCode <   / th  >  
 <   / tr  >  
 @foreach（模型中的var emp）
 {
 <   tr  >  
 
 <   td  >  @ emp.employees.EmpName <   / td  >  
 <   td  >  @ emp.employees.EmpCode <   / td  >  
 <   td  >  @ emp.depts.DeptName <   / td  >  
 <   td  >  @ emp.depts.DeptCode <   / td  >  
 <   / tr  >  
} 
 <   / table  >  
} 

解决方案

问题是因为您正在对表进行左连接，因此无论是否匹配，您都要在连接的左侧包含所有记录。我写的以下sql非常快。请注意D04员工Hari如何为Department提供NULL信息。这就是你得到一个空引用异常的原因。

  DECLARE   @ Empoyees   TABLE （
 EmpId  int   PRIMARY   KEY  不  NULL ，
 DeptId  VARCHAR （ 10  ） NULL ，
 EmpName  VARCHAR （ 25 ） NULL  
）; 
 
 
  DECLARE   @ Department   TABLE （
 DeptName  VARCHAR （ 10 ） PRIMARY   KEY   NOT   NULL ，
 DeptId  VARCHAR （ 10 ） NULL ，
 DeptCode  VARCHAR （ 25 ） NULL  
）; 
 
 
  INSERT   INTO   @Empoyees （EmpId，DeptId，EmpName） VALUES （ 1 ，'  D01'，'  Shyam'）
  INSERT   INTO   @Empoyees （EmpId，DeptId，EmpName） VALUES （ 2 ， '  D03'，'  Raja'）
  INSERT   INTO   @ Empoyees （EmpId，DeptId，EmpName） VALUES （ 3 ，'  D04'，'  Hari'）
  INSERT   INTO   @ Empoyees （EmpId，DeptId，EmpName） VALUES （ 4 ，'  D03'，'  Krushna'）
  INSERT   INTO   @ Empoyees （EmpId，DeptId，EmpName） VALUES （ 5 ，'  D02'，'  Mohan'）
 
  INSERT   INTO   @ Department （DeptName，DeptId，DeptCode） VALUES （'  CS'，'  D01'，'  ZZ'）
  INSERT   INTO   @ Department （DeptName，DeptId，DeptCode） VALUES （'  EEE'，'  D02'，'  XX'）
  INSERT   INTO   @Department （DeptName，DeptId，DeptCode） VALUES （'  MCA'，'  D03' ，'  YY'）
 
 
 < span class =code-keyword> SELECT  *  FROM   @ Empoyees   AS  A 
  LEFT   JOIN   @ Department   AS  B  ON  B.DeptId = A.DeptId 

此查询提供示例输出（确定它不会显示良好）

 EmpId DeptId EmpName DeptName DeptId DeptCode 
  1  D01 Shyam CS D01 ZZ 
  2  D03 Raja MCA D03 YY 
  3  D04 Hari NULL NULL NULL 
  4  D03 Krushna MCA D03 YY 
  5  D02 Mohan EEE D02 XX 

由于我不确定你想要显示什么，只是简单地通过这个空引用，你可以检查是否< pre lang =c＃> depts

在访问这些值之前为null以防止异常，或者您可以在foreach循环中包含where子句以不包括depts == null

  foreach （ var 项目  outerjoin.Where（m => m.depts！=  null ））
 { 
 counter + =  1 ; 
 
 Console.WriteLine（  counter： + counter）; 
 Console.WriteLine（item.employees.EmpName）; 
 Console.WriteLine（item.employees.EmpCode）; 
 Console.WriteLine（item.depts.DeptName）; 
 Console.WriteLine（item.depts.DeptCode）; 
} 

this error is coming..Can some one try for solving it.

An exception of type 'System.NullReferenceException' occurred in App_Web_linqquery.cshtml.a8d08dba.0r5s74cf.dll but was not handled in user code<br />
<br />
Additional information: Object reference not set to an instance of an object.

What I have tried:

namespace WebApplication4Linq.Models
{
    public class Employee
    {
        [Key]
        public int EmpId { get; set; }
        public string EmpName { get; set; }
        public string EmpCode { get; set; }
        public string DeptId { get; set; }

        public static List<employee> GetEmployees()
        {
            List<employee> employees = new List<employee>();
            employees.Add(new Employee { EmpId = 1, EmpName = "Shyam", EmpCode = "AAA", DeptId = "D01" });
            employees.Add(new Employee { EmpId = 2, EmpName = "Raja", EmpCode = "BBB", DeptId = "D03" });
            employees.Add(new Employee { EmpId = 3, EmpName = "Hari", EmpCode = "CCC", DeptId = "D04" });
            employees.Add(new Employee { EmpId = 4, EmpName = "Krushna", EmpCode = "DDD", DeptId = "D03" });
            employees.Add(new Employee { EmpId = 5, EmpName = "Mohan", EmpCode = "EEE", DeptId = "D02" });

            return employees;
        }
    }

}

--------------

namespace WebApplication4Linq.Models
{
    public class Department
    {
        [Key]
       
        public string DeptName { get; set; }
        public string DeptCode { get; set; }
        public string DeptId { get; set; }

        public static List<department> GetDepts()
        {
            List<department> depts = new List<department>();
            depts.Add(new Department { DeptName = "CS", DeptCode = "ZZ", DeptId = "D01" });
            depts.Add(new Department {  DeptName = "EEE", DeptCode = "XX", DeptId = "D02" });
            depts.Add(new Department {  DeptName = "MCA", DeptCode = "YY", DeptId = "D03" });

            return depts;
        }
    }
}

-----------------------viewmodel=----------

namespace WebApplication4Linq.Models
{
    public class ViewModel
    {
        public Employee employees { get; set; }
        public Department depts { get; set; }
    }
}
-------------Controller---------------------
  public class HomeController : Controller
    {
        public ActionResult LinqQuery()
        {
            ViewModel mymodel = new ViewModel();

            List<employee> emps = Employee.GetEmployees();
            List<department> dept = Department.GetDepts();

            var outerjoin = (from empn in emps
                             join den in dept
                             on empn.DeptId equals den.DeptId
                             into abc
                             from den in abc.DefaultIfEmpty()
                             select new ViewModel()
                             {
                                 
                                 employees = empn,
                                 depts = den
                             });
            return View(outerjoin);

-------------------(.cshtml)------------------

@model IEnumerable<webapplication4linq.models.viewmodel>

@using (Html.BeginForm())
{
        <h2>Index Page</h2>

        <hr />
        <h2>Employee Data</h2><hr />
        <table>
            <tr>
                
                <th>EmpName</th>
                <th>EmpCode</th>
                <th>DeptName</th>
                <th>DeptCode</th>
            </tr>
            @foreach(var emp in Model)
            {
                <tr>
                    
                    <td>@emp.employees.EmpName</td>
                    <td>@emp.employees.EmpCode</td>
                    <td>@emp.depts.DeptName</td>
                    <td>@emp.depts.DeptCode</td>
                </tr>
            }
        </table>
}

解决方案

The issue is because you are doing an left join on your table so you are including all records on the left side of your join regardless of if there is a match. I wrote the following sql really quick. Notice how D04 employee Hari provides NULL information for Department. This would be why you are getting a null reference exception.

DECLARE @Empoyees TABLE (
	EmpId int PRIMARY KEY NOT NULL,
	DeptId VARCHAR(10) NULL,
	EmpName VARCHAR(25) NULL
);


DECLARE @Department TABLE (
	DeptName VARCHAR(10) PRIMARY KEY NOT NULL,
	DeptId VARCHAR(10) NULL,
	DeptCode VARCHAR(25) NULL
);


INSERT INTO @Empoyees (EmpId, DeptId, EmpName) VALUES (1,'D01', 'Shyam')
INSERT INTO @Empoyees (EmpId, DeptId, EmpName) VALUES (2,'D03', 'Raja')
INSERT INTO @Empoyees (EmpId, DeptId, EmpName) VALUES (3,'D04', 'Hari')
INSERT INTO @Empoyees (EmpId, DeptId, EmpName) VALUES (4,'D03', 'Krushna')
INSERT INTO @Empoyees (EmpId, DeptId, EmpName) VALUES (5,'D02', 'Mohan')

INSERT INTO @Department ( DeptName, DeptId, DeptCode ) VALUES ('CS','D01','ZZ')
INSERT INTO @Department ( DeptName, DeptId, DeptCode ) VALUES ('EEE','D02','XX')
INSERT INTO @Department ( DeptName, DeptId, DeptCode ) VALUES ('MCA','D03','YY')


SELECT * FROM @Empoyees AS A
LEFT JOIN @Department AS B ON B.DeptId = A.DeptId

This query provides the sample output (im sure it won't display well)

EmpId	DeptId	EmpName	DeptName	DeptId	DeptCode
1	D01	Shyam	CS	D01	ZZ
2	D03	Raja	MCA	D03	YY
3	D04	Hari	NULL	NULL	NULL
4	D03	Krushna	MCA	D03	YY
5	D02	Mohan	EEE	D02	XX

As i'm not sure what you are wanting to display, to simply get past this null reference, you could check if

depts

is null prior to accessing those values to prevent the exception or you could include a where clause in the foreach loop to not include depts == null

foreach (var item in outerjoin.Where(m=>m.depts != null))
            {
                counter += 1;

                Console.WriteLine("counter: " + counter);
                Console.WriteLine(item.employees.EmpName);
                Console.WriteLine(item.employees.EmpCode);
                Console.WriteLine(item.depts.DeptName);
                Console.WriteLine(item.depts.DeptCode);
            }

这篇关于如何在mvc中使用LINQ应用外连接。内连接正在工作，但外连接给出以下错误..为什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！