英文:
Filter both master and details record and include details in master
问题
请考虑以下情况：
我有一个“Master”表格：
Id Country--------------------1 Ireland2 Germany3 Japan
和一个“Details”表格：
Id MasterId SellAmount------------------------------------1 1 10002 1 20003 1 30004 2 5005 2 15006 2 40007 3 50008 3 55009 3 100010 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.MasterIdselect m).ToList();
但它返回了所有6条主记录！！尽管在这些主记录中，我有所有的细节记录，但筛选没有发生。
我如何才能获得这个结果：
2 Germany6 2 40003 Japan7 3 50008 3 5500
谢谢。
英文:
Please consider this scanrio:
I have a Master table:
Id Country--------------------1 Ireland2 Germany3 Japan
and Detials table:
Id MasterId SellAmount------------------------------------1 1 10002 1 20003 1 30004 2 5005 2 15006 2 40007 3 50008 3 55009 3 100010 3 2000
There is a foreign key between these tow table. I want to filter both master and details tables. For example:
Master Id >= 2Details 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.MasterIdselect 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 Germany6 2 40003 Japan7 3 50008 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.CountryDetails = r.Details.Where(t => t.SellAmount >= 3000).ToList()}).ToList();
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论