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

Question

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?

Your first block of code looks almost correct, you just need to do `new Rha {....` instead of creating an anonymous object. — DavidG, Sep 02 '21 at 10:30
@DavidG where do I have to add the `new Rha {. . . `, I'm not familiar with LINQ before — NeoSoul, Sep 02 '21 at 10:34
If you're not familiar with Linq and anonymous types, then you should really go do some reading and run through some tutorials. That will benefit you far more than asking questions on SO. — DavidG, Sep 02 '21 at 10:35
I will read some tutorials later, I guess. But for now, kinda need solution for this because I have a deadline from work and can't find any perfect solution. @DavidG — NeoSoul, Sep 02 '21 at 10:38
@GertArnold I added `Include` in the Linq and use the `Where` clause on the included table, but it returns all the first table and only filtered second table when even if it doesn't have any references on the second table — NeoSoul, Sep 02 '21 at 14:04
Please show in the question what you actually do. I.e. show the actual class model + query code, and please not as image but as text. But maybe first carefully read [this](https://stackoverflow.com/a/61147681/861716) to see the difference between filtering the Include and filtering the query. — Gert Arnold, Sep 02 '21 at 14:34

mohammadSayadi · Answer 1 · 2021-09-02T13:52:06.050

0

try to modify your select as following

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

edited Sep 02 '21 at 13:52

answered Sep 02 '21 at 11:38

mohammadSayadi

31
3

It's not even a correct lambda expression – NeoSoul Sep 02 '21 at 12:17
I eddited the code there where clause in linq start with capital W and instead of "==" you should use contains or any expression you want to check. it is correct expression. – mohammadSayadi Sep 02 '21 at 13:51
I got an error on this code `x=>x.SubRhas.Assign.Contains(assign)` since SubRhas is only a `Collection` inside Rhas – NeoSoul Sep 02 '21 at 14:09

score 0 · Answer 2 · answered Sep 02 '21 at 15:27

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.

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

2 Answers2