How to get data from two tables but with where clause on the second (FOREIGN KEY) table? [LINQ]-CodePudding

I have two tables, like this:

I want to select the Table2 where the city is "DDD" but also I want to include the Table1. The expected output would be like this:

I'm working with EF Core Web API using LINQ. I've tried some solutions like this:

public async Task<IEnumerable<Rha>> GetSubRHAByAssign(string assign)
        {
            var result = await _db.Rhas.Select(x => new { Rha = x, Things = x.SubRhas.Where(p=> p.Assign == assign) }).AsNoTracking().ToListAsync();
            return result;
        }

But it gives me an error: Unable to cast object of type 'System.Collections.Generic.List`1[<>f__AnonymousType11`2[GesitAPI.Models.Rha,System.Collections.Generic.IEnumerable`1[GesitAPI.Models.SubRha]]]' to type 'System.Collections.Generic.IList`1[GesitAPI.Models.Rha]'.

I'm using an interface like this:

Task<IEnumerable<Rha>> GetSubRHAByAssign(string assign);

Also, as you can see my real problem is different from the example. Here I have two tables: RHA and SubRHA. The main problem is still the same, I want to get SubRHA based on Assign but I want to include RHA too. RHA is the main table, SubRHA is the table with foreign key from RHA table.

I've tried to use JOIN, but still gives me same error. Any idea?

CodePudding user response：

try to modify your select as following

var result = await _db.Rhas.Include("SubRhas").Where(x=>x.SubRhas.Assign.Contains(assign)).ToListAsync();

CodePudding user response：

I have found my own solution. I use this query:

var result = await _db.Rhas.Include(c => c.SubRhas.Where(o => o.Assign == assign))
                                       .ThenInclude(o => o.SubRhaevidences)
                                       .Where(x => x.SubRhas.Any())
                                       .AsNoTracking()
                                       .ToListAsync();

So it will get SubRhas with condition, but my problem is even though I have put the where condition inside SubRhas, the query still return another data that has no relation to SubRhas. That's why I add another condition on the first table to get the data where SubRhas is not empty.