Group By then Select inner elements Linq asp.net Core 3

Question

before every thing i tried a lot of answers and fixes but didnt work for me, the problem is im trying to get a list of this Dto:

        public string Ministry { get; set; }
        public string Department { get; set; }
        public string EmployeeCount { get; set; }
        public List<string> Accounts { get; set; }

using this linq query:

var result = await (from emp in _db.Employees
                                join c in _db.EmployeeBlackList
                                on emp.AccountNo equals c.AccountNo into joined
                                from c in joined.DefaultIfEmpty()
                                group emp by new { emp.Ministry, emp.Department }
                                    into groups
                                select new MinstryDepartmentEmployeeCount()
                                {
                                    Ministry = groups.Key.Ministry,
                                    Department = groups.Key.Department,
                                    EmployeeCount = groups.Count().ToString(),
                                    Accounts = groups.Select(x => x.AccountNo).ToList()
                                }).ToListAsync();

the error exception i am getting is this :

One or more errors occurred. (The LINQ expression 'Select<Employee, string>(\r\n source: GroupByShaperExpression:\r\n KeySelector: new { \r\n Ministry = e.Ministry, \r\n Department = e.Department\r\n }, \r\n ElementSelector:EntityShaperExpression: \r\n EntityType: Employee\r\n ValueBufferExpression: \r\n ProjectionBindingExpression: EmptyProjectionMember\r\n IsNullable: False\r\n , \r\n selector: (x) => x.AccountNo)' 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(), ToList(), or ToListAsync(). See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.)

i already know that this statement is generating the error:

Accounts = groups.Select(x => x.AccountNo).ToList()

i can not take all info in the employee table because it has more than 2 million rows and more than 7 columns.

the main objective of this query is to convert this :

Ministry    Department   AccountNo
A           AA           1111
A           AA           2222
B           BB           3333
B           BB           4444

into this :

[
  {
    Ministry: 'A' ,
    Department: 'AA' ,
    EmployeeCount: 2 ,
    Accounts:[
               {
                 '1111'  
               },
               {
                 '2222'  
               },
             ]
  },
  {
    Ministry: 'B' ,
    Department: 'BB' ,
    EmployeeCount: 2 ,
    Accounts:[
               {
                 '3333'  
               },
               {
                 '4444'  
               },
             ]
  }
]

thanks in advance.

Update : this question wants the same as i want, i have used the same answer for it, but i still get the same error, so i believe that the reason is the migration of asp.net core to 3.o
questionLink

Answer 1

While this might be useful, I ran into the same situation for myself. I ended up converting the query into a View and then using

https://learn.microsoft.com/en-us/ef/core/modeling/keyless-entity-types?tabs=data-annotations

this method of creating the projection I needed, as for this being EFCore version is dependent on how you create the Keyless type in your datacontext.

What I really gained from this was a ridiculous amount of speed in return, the query normally took 8-18 seconds now it returns the exact data in a fraction of that. Moving the query away from EFC doing the work to the server doing the work, only bottle neck would be the wire at that point.