UnionBy() EF Core

Question

I have this ef query that give me the following result

IQueryable<A>
| Id      | count |
| 1       | 5     |
| 2       | 6     |

IQueryable<B>
| Id      | count |
| 1       | 1     |
| 2       | 2     |
| 3       | 9     |

When I do

IQueryable<Something> C = A.union(B)

Result that I got is this

| Id      | count |
| 1       | 5     |
| 2       | 6     |
| 1       | 1     |
| 2       | 2     |
| 3       | 9     |

Whish is logical.

What I want is a UnionBy(Id)

IQueryable<Something> C = A.unionBy(B,c=>c.Id) 

and this work perfectly in my case

| Id      | count |
| 1       | 5     |  -- FROM A
| 2       | 6     |  -- FROM A
| 3       | 9     |  -- FROM B

If the Query A or B are already executed by that I mean a ToList() was made it work perfectly and I have no problem in anyway. But in my case, both queries are not executed and thus using this function result in.

System.InvalidOperationException query could not be translated.

the alternative is to use a GroupBy however I have no idea how to replacte UnionBy behavior with the GroupBy

FYI: the query works perfectly using the IQueryable.Union and it's mandatory in my case that the request stay in IQueryable and not executed until later

UPDATE

⚠️ The solution that I'm looking for must stay in IQueryable without a toList() execution

Answer 1

The solution is simple you filtre A From B using the following

IQueryable<Something> C = A.Union(B.where(b=> A.All(a=>a.Id != b.Id))