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

CodePudding user response：

"query could not be translated" usually means that EF doesn't support a certain LINQ or language construct as it can't translate it into SQL. One way to make this work is to force the evaluation of the expression client-side by adding e.g. ToList() or likes on the query parts before executing the UnionBy:

IQueryable<Something> C = A.ToList().UnionBy(B.ToList(),c=>c.Id);

CodePudding user response：

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