Filter both master and details record and include details in master

Question

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

Answer 1

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();