how to join then group by in ef core?

Question

I want to join a table and then group by key.

var query=from q in TableA
join m in TableB
on q.Id equals m.UserId
select new {
q.Id,
m.UserName,
Hours=m.Hours
}

and then group by

var q = from f in query group f by f.Id into g
select new {
g.FirstOrDefault().UserName,
Total=g.Sum(x=>x.Hours)
}
var data=await q.Take(10).ToListAsync();

but there will be an error

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 either AsEnumerable(), AsAsyncEnumerable()

why?

Answer 1

Most likely FirstOrDefault can not be translated. Although the following uses records conceptually this is how to write the code. Records are easier to write the demo.

internal class Program
{
    static void Main(string[] args)
    {
        Example();
        Console.ReadLine();
    }

    private static void Example()
    {
        Person magnus = new(FirstName: "Magnus", LastName: "Hedlund");
        Person terry = new("Terry", "Adams");
        Person charlotte = new("Charlotte", "Weiss");
        Person arlene = new("Arlene", "Huff");

        List<Person> people = new() { magnus, terry, charlotte, arlene };

        List<Pet> pets = new()
        {
            new(Name: "Barley", Owner: terry),
            new("Boots", terry),
            new("Whiskers", charlotte),
            new("Blue Moon", terry),
            new("Daisy", magnus),
        };

        // Create a list where each element is a strong type
        // that contains the person's first name and a collection of
        // pets that are owned by them.
        var query =
            from person in people
            join pet in pets on person equals pet.Owner into gj
            select new PersonPetContainer(person.FirstName, gj);

        foreach (var item in query)
        {
            // Output the owner's name.
            Console.WriteLine($"{item.OwnerName}:");

            // Output each of the owner's pet's names.
            foreach (var pet in item.Pets)
            {
                Console.WriteLine($"  {pet.Name}");
            }
        }
    }
}

public record PersonPetContainer(string OwnerName, IEnumerable<Pet> Pets);
public record Person(string FirstName, string LastName);
public record Pet(string Name, Person Owner);

Answer 2

as the user name is same for the group, just use an aggregate:

var q = from f in query group f by f.Id into g
select new {
   UserName=g.Max(x=> x.UserName),
   Total=g.Sum(x=>x.Hours)
}
var data=await q.Take(10).ToListAsync();

Answer 3

Hi @mc

According to your query statement, I create related table in database, and test your code, it seems that your query statement works well (I'm using EF core 7), the result as below:

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 either AsEnumerable(), AsAsyncEnumerable()

For the above issue, I think the issue might be related to your data set TableA and TableB. You can see that, in my above example, I got it from DBContext and nothing was done.

So, according to the error message, I suggest you can try to change the query statement as below: add the AsEnumerable() method.

            var query = from q in TableA.AsEnumerable()
                        join m in TableB.AsEnumerable()
                        on q.Id equals m.UserId
                        select new
                        {
                            q.Id,
                            m.UserName,
                            Hours = m.Hours
                        };

            var qu = from f in query.AsEnumerable()
                    group f by f.Id into g
                    select new
                    {
                        g.FirstOrDefault().UserName,
                        Total = g.Sum(x => x.Hours)
                    };
            var data = qu.Take(10).ToList();

The code also works well on my side:

If the problem still can't be solved, please explain more detail about the TableA and TableB, or you can create a minimal, complete sample to reproduce the problem.

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Best regards,

Dillion