Unable to perform basic JOIN using LINQ Query syntax

Question

I want to join another table using the query syntax. I am getting the following error:

The LINQ expression 'DbSet() .GroupJoin( inner: DbSet(), outerKeySelector: a => (int?)a.ID, innerKeySelector: b => b.aID, resultSelector: (a, b) => new { // my properties here })' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

My code looks along the lines of:

var q =
    from a in As
    join b in Bs on a.ID equals b.aID into bs
    select new
    {
        // my properties here
    };

I want to join the Bs and access bs to perform Count() on it in my list of properpties.

EDIT: Here is an example using LINQPad 7 (default database).

This should help you: stackoverflow.com/questions/7285714/linq-with-groupby-and-count — GH DevOps
– GH DevOps, Commented Oct 26, 2022 at 14:54
I believe the GroupJoin is applied internally by EF Core. I posted by query above (using query syntax). That is all the code. I want to basically have a property TotalBCount = bs.Count() as my final select. — user746019
– user746019, Commented Oct 26, 2022 at 15:11

Svyatoslav Danyliv · Accepted Answer · 2022-10-26 15:58:30Z

0

Do not use GroupJoin (join which ends with into) with EF Core, except situation when you need LEFT JOIN. It's translation is strictly limited.

var q =
    from a in As
    select new
    {
        ...
        TotalBCount = Bs.Count(b => b.aID == a.ID)
    };

edited Oct 26, 2022 at 15:58

answered Oct 26, 2022 at 15:52

Svyatoslav Danyliv

28.2k4 gold badges24 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Unable to perform basic JOIN using LINQ Query syntax

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related