Linq to Entities 如何从父级返回所有记录?
员工表
Employee_ID int
Employee_Name varchar(50)
销售表:
Sales_ID int
Employee_ID int
Sale_Amount 金额
标准 SQL select
Select
*
从 员工 emp
左外连接
销售人员
上
s.Employee_ID = emp.Employee_ID
标准 SQL 结果(我想要使用 Linq to Entites 的确切结果)
1 Emp1 1 1 150.00
1 员工1 2 1 500.00
2 雇员2 3 2 250.00
3 Emp3 NULL NULL NULL
4 Emp4 NULL NULL NULL
5 Emp5 4 5 700.00
现在解决 Linq To Entities
Dim query = From emp In entiites.Employee _
From sales In emp.Sales _
Select _
emp, _
sales
Linq To Entities 结果(Employee_ID 3 和 4 在哪里)
1: Emp1: 150.0000
1:员工1:500.0000
2:Emp2:250.0000
5: Emp5: 700.0000
使用具有左外连接的 Linq to Entities 进行尝试:
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
然后我使用 DefaultIfEmpty 收到此错误:
LINQ to Entities 无法识别方法“System.Collections.Generic.5”。 IEnumerable1[m12Model.Sales] DefaultIfEmpty[Sales](System.Collections.Generic.IEnumerable
1[m12Model.Sales])' 方法,并且此方法无法转换为存储表达式。
Linq to Entites:不支持 DefaultIfEmpty()。
Employee table
Employee_ID int
Employee_Name varchar(50)
Sales table:
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
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
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
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:
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 Entites: does not support DefaultIfEmpty().
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
您需要使用“Into”和“DefaultIfEmpty()”关键字执行左连接:
此站点显示了另一个简单的示例:GeeksWithBlogs。 虽然是用C#写的。
关键是您需要加入一个新名称,以防止它过滤第一个表中的结果(该表隐藏未加入的行),然后使用 DefaultIfEmpty() 函数从中选择,该函数提供默认值没有连接的情况下为 (null) 值。
You need to perform a left join by using the "Into" and "DefaultIfEmpty()" keywords:
This site shows another simple example: GeeksWithBlogs. Though it is in 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.
怎么样:
您可以选择所需的列,而不是“选择新的 { emp, sales }”。 这应该是使用 LINQ 的标准左连接。
How about this:
Instead of "select new { emp, sales }" you can select the columns you want. That should be a standard left join using LINQ.
您现在可能已经找到了解决方案,但我有一个建议,使用与连接不同的方法:
结果是:
我希望这可以帮助您
You probably have found a solution by now, but I have a suggestion, using a different approach than joins:
The result of this is:
I hope this can help you