Employee table
Employee_ID int
Employee_Name varchar(50)
Employee_ID int Employee_Name varchar(50)
Sales table:
Sales_ID int
Employee_ID int
Sale_Amount money
Sales_ID int Employee_ID int Sale_Amount money
Standard SQL select
Select
*
From Employee emp
left outer join
Sales s
on
s.Employee_ID = emp.Employee_ID
在s.Employee_ID = emp.Employee_ID上选择* From Employee emp left left join Sales s
Standard SQL Results (The exact results I want using Linq to Entites)
1 Emp1 1 1 150.00
1 Emp1 2 1 500.00
2 Emp2 3 2 250.00
3 Emp3 NULL NULL NULL
4 Emp4 NULL NULL NULL
5 Emp5 4 5 700.00
1 Emp1 1 1 150.00 1 Emp1 2 1 500.00 2 Emp2 3 2 250.00 3 Emp3 NULL NULL NULL 4 Emp4 NULL NULL NULL 5 Emp5 4 5 700.00
Now to tackle Linq To Entities
Dim query = From emp In entiites.Employee _
From sales In emp.Sales _
Select _
emp, _
sales
Linq To Entities Result (Where is Employee_ID 3 and 4)
1: Emp1: 150.0000
1: Emp1: 500.0000
2: Emp2: 250.0000
5: Emp5: 700.0000
1:Emp1:150.0000 1:Emp1:500.0000 2:Emp2:250.0000 5:Emp5:700.0000
Try it with Linq to Entities with a left outer join:
Dim query = From emp In entiites.Employee _
Group Join sales In entiites.Sales _
On emp.Employee_ID Equals sales.Employee.Employee_ID _
Into sales_grp = Group _
From Sel_SalesGrp In sales_grp.DefaultIfEmpty() _
Select _
emp, _
Sel_SalesGrp
Then I get this error using DefaultIfEmpty:
然后我使用DefaultIfEmpty得到此错误:
LINQ to Entities does not recognize the method 'System.Collections.Generic.IEnumerable1[m12Model.Sales] DefaultIfEmpty[Sales](System.Collections.Generic.IEnumerable
1[m12Model.Sales])' method, and this method cannot be translated into a store expression.
LINQ to Entities无法识别方法'System.Collections.Generic.IEnumerable1 [m12Model.Sales] DefaultIfEmpty [Sales](System.Collections.Generic.IEnumerable1 [m12Model.Sales])'方法,并且此方法无法转换为商店表达。
Linq to Entites: does not support DefaultIfEmpty().
3 个解决方案
#1
You need to perform a left join by using the "Into" and "DefaultIfEmpty()" keywords:
您需要使用“Into”和“DefaultIfEmpty()”关键字执行左连接:
Dim query = From emp In entities.Employee _
Join sal In entities.Sales On emp.Employee_ID Equals sal.Employee_ID Into LeftJoinSal _
From ljs In LeftJoinSal.DefaultIfEmpty() _
Select _
emp, _
ljs
This site shows another simple example: GeeksWithBlogs. Though it is in C#.
这个站点显示了另一个简单的例子:GeeksWithBlogs。虽然它是在C#中。
The key is that you need to join into a new name to prevent it from filtering the results from your first table (which hides the rows that don't join), then select from that using the DefaultIfEmpty() function, which provides a default (null) value in the cases where there is no join.
关键是你需要加入一个新名称,以防止它过滤你的第一个表(隐藏不加入的行)的结果,然后使用DefaultIfEmpty()函数从中选择,该函数提供默认值(null)没有连接的情况下的值。
#2
How about this:
这个怎么样:
Dim query = From emp In entiites.Employee Join sales In In entiites.Sales _
On emp.Employee_ID Equals sales.Employee_ID select new { emp, sales }
Instead of "select new { emp, sales }" you can select the columns you want. That should be a standard left join using LINQ.
您可以选择所需的列,而不是“选择新的{emp,sales}”。这应该是使用LINQ的标准左连接。
#3
You probably have found a solution by now, but I have a suggestion, using a different approach than joins:
您可能现在已经找到了解决方案,但我有一个建议,使用与连接不同的方法:
Dim db As New TestEntities
Dim EmptySale As Decimal? = Nothing
Dim Q = (From emp In db.Employee From sale In emp.Sale _
Select emp.Name, amount =sale.SaleAmount).ToList
Dim Q2 = (From emp In db.Employee Where Not emp.Sale.Any _
Select emp.Name, amount = EmptySale)
Dim Q3 = From emp In Q.Union(Q2) Order By emp.Name, emp.amount
For Each e In Q3
Console.WriteLine("{0} {1}", e.Name, e.amount)
Next
The result of this is:
结果是:
Emp1 150,00
Emp1 500,00
Emp2 250,00
Emp3
Emp4
Emp5 750,00
I hope this can help you
我希望这可以帮到你
#1
You need to perform a left join by using the "Into" and "DefaultIfEmpty()" keywords:
您需要使用“Into”和“DefaultIfEmpty()”关键字执行左连接:
Dim query = From emp In entities.Employee _
Join sal In entities.Sales On emp.Employee_ID Equals sal.Employee_ID Into LeftJoinSal _
From ljs In LeftJoinSal.DefaultIfEmpty() _
Select _
emp, _
ljs
This site shows another simple example: GeeksWithBlogs. Though it is in C#.
这个站点显示了另一个简单的例子:GeeksWithBlogs。虽然它是在C#中。
The key is that you need to join into a new name to prevent it from filtering the results from your first table (which hides the rows that don't join), then select from that using the DefaultIfEmpty() function, which provides a default (null) value in the cases where there is no join.
关键是你需要加入一个新名称,以防止它过滤你的第一个表(隐藏不加入的行)的结果,然后使用DefaultIfEmpty()函数从中选择,该函数提供默认值(null)没有连接的情况下的值。
#2
How about this:
这个怎么样:
Dim query = From emp In entiites.Employee Join sales In In entiites.Sales _
On emp.Employee_ID Equals sales.Employee_ID select new { emp, sales }
Instead of "select new { emp, sales }" you can select the columns you want. That should be a standard left join using LINQ.
您可以选择所需的列,而不是“选择新的{emp,sales}”。这应该是使用LINQ的标准左连接。
#3
You probably have found a solution by now, but I have a suggestion, using a different approach than joins:
您可能现在已经找到了解决方案,但我有一个建议,使用与连接不同的方法:
Dim db As New TestEntities
Dim EmptySale As Decimal? = Nothing
Dim Q = (From emp In db.Employee From sale In emp.Sale _
Select emp.Name, amount =sale.SaleAmount).ToList
Dim Q2 = (From emp In db.Employee Where Not emp.Sale.Any _
Select emp.Name, amount = EmptySale)
Dim Q3 = From emp In Q.Union(Q2) Order By emp.Name, emp.amount
For Each e In Q3
Console.WriteLine("{0} {1}", e.Name, e.amount)
Next
The result of this is:
结果是:
Emp1 150,00
Emp1 500,00
Emp2 250,00
Emp3
Emp4
Emp5 750,00
I hope this can help you
我希望这可以帮到你