0

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). enter image description here

user746019
  • 23
  • 5
  • This should help you: https://stackoverflow.com/questions/7285714/linq-with-groupby-and-count – GH DevOps Oct 26 '22 at 14:54
  • Instead of `GroupJoin` did you try only using [`Join()`](https://learn.microsoft.com/en-us/dotnet/api/system.linq.enumerable.join?view=net-6.0) ? – Irwene Oct 26 '22 at 15:02
  • 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 Oct 26 '22 at 15:11

1 Answers1

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)
    };
Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32