英文:
Filter both master and details record and include details in master
问题
请考虑以下情况：
我有一个“Master”表格：
Id Country
--------------------
1 Ireland
2 Germany
3 Japan
和一个“Details”表格：
Id MasterId SellAmount
------------------------------------
1 1 1000
2 1 2000
3 1 3000
4 2 500
5 2 1500
6 2 4000
7 3 5000
8 3 5500
9 3 1000
10 3 2000
这两个表格之间有一个外键关联。我想要筛选主表和细节表。例如：
主表 Id >= 2
细节表 SellAmount >= 3000
我想要包括符合上述条件的细节记录。
我编写了以下查询：
var list = (from m in Master.Where(r => r.Id >= 2).Include("Details")
join d in Details.Where(t => t.SellAmount >= 3000)
on m.Id equals d.MasterId
select m).ToList();
但它返回了所有6条主记录！！尽管在这些主记录中，我有所有的细节记录，但筛选没有发生。
我如何才能获得这个结果：
2 Germany
6 2 4000
3 Japan
7 3 5000
8 3 5500
谢谢。
英文:
Please consider this scanrio:
I have a Master table:
Id Country
--------------------
1 Ireland
2 Germany
3 Japan
and Detials table:
Id MasterId SellAmount
------------------------------------
1 1 1000
2 1 2000
3 1 3000
4 2 500
5 2 1500
6 2 4000
7 3 5000
8 3 5500
9 3 1000
10 3 2000
There is a foreign key between these tow table. I want to filter both master and details tables. For example:
Master Id >= 2
Details SellAmount >= 3000
I want to inclsude those details records that have above criteria.
I wrote this query:
var list = (from m in Master.Where(r=>r.Id >= 2).Include("Details")
join d in Details.Where(t=>t.SellAmount >= 3000)
on m.Id equals d.MasterId
select m).ToList();
but it returned all 6 master records!!!! Although In those master records I had all details record and filtering didn't take place.
How can I have this result:
2 Germany
6 2 4000
3 Japan
7 3 5000
8 3 5500
Thanks
答案1
得分: 3
使用联接操作时，如果联接对中存在多个记录，则会将数据进行复制。
如果您使用EF Core，请使用适当的Include重写您的查询：
var list = Master
.Include(r => r.Details.Where(t => t.SellAmount >= 3000))
.Where(r => r.Id >= 2)
.ToList();
对于EF6，需要通过Select进行自定义投影到DTO类中：
var list = Master
.Where(r => r.Id >= 2)
.Select(r => new MasterDTO
{
Id = r.Id,
Country = r.Country,
Details = r.Details.Where(t => t.SellAmount >= 3000).ToList()
})
.ToList();
英文:
Join operator multiplicate data if there is more than one record for joined pair.
If you use EF Core, rewrite your query using appropriate Include:
var list = Master
.Include(r => r.Details.Where(t => t.SellAmount >= 3000))
.Where(r => r.Id >= 2)
.ToList();
With EF6 it is needed custom projection via Select into DTO class
var list = Master
.Where(r => r.Id >= 2)
.Select(r => new MasterDTO
{
Id = r.Id,
Country = r.Country
Details = r.Details.Where(t => t.SellAmount >= 3000).ToList()
})
.ToList();
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论