c# - How to Join two entities and store results in a list of an entity in EF Core - Stack Overflow

I have two entities: Header and Item. I want to join two tables and store the results in a list of Item entity. here is my code:

List<Item> items = _dbContext.Items.Join(_dbContext.Header, l => l.HeaderId, r => r.Id, (l, r) => new { items = l, header = r })                
                      .Where(p => string.IsNullOrEmpty(reportFilterDTO.HeaderDescription) || p.header.Description.Contains(reportFilterDTO.HeaderDescription));

How can I properly store the results in the items list?

I have two entities: Header and Item. I want to join two tables and store the results in a list of Item entity. here is my code:

List<Item> items = _dbContext.Items.Join(_dbContext.Header, l => l.HeaderId, r => r.Id, (l, r) => new { items = l, header = r })                
                      .Where(p => string.IsNullOrEmpty(reportFilterDTO.HeaderDescription) || p.header.Description.Contains(reportFilterDTO.HeaderDescription));

How can I properly store the results in the items list?

• c#
• entity-framework-core

Share Improve this question Follow edited Feb 2 at 4:59 Dot NET Lover asked Feb 1 at 8:31 Dot NET LoverDot NET Lover 1711 silver badge55 bronze badges 1

• Use navigation properties. Don't join manually. It's not necessary when using EF. – Gert Arnold Commented Feb 1 at 12:06

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

You can use Join method as follows:

var items = _dbContext.Header.AsNoTracking()
                    .Where(p => string.IsNullOrEmpty(ReportFilterDTO.HeaderDescription)  || ReportFilterDTO.HeaderDescription.Contains(reportFilterDTO.HeaderDescription))
                    .Join(_dbContext.Item, l => l.Id, r => r.HeaderId, (l, r) => items = r );

and the second approach, using navigation properties:

items = _dbContext.Header.AsNoTracking()
        .Include(s => s.Items)
        .Where(s=>string.IsNullOrEmpty(FilterDTO.HeaderDescription)  || FilterDTO.HeaderDescription.Contains(FilterDTO.HeaderDescription)))
        .SelectMany(s => s.Items,(h,i)=>i);

Both codes return the same result. The resulted query is:

SELECT i.* FROM  Headers h
INNER JOIN Items i ON h.Id=I.HeaderId 
WHERE h.Description IS NULL OR h.Description LIKE '%@p1%'