Invalidoperationexception: the LINQ expression for groupby

Question

0.00/5 (No votes)

See more:

I'm implementing an asp.net core project. I wrote a query like below in my code,

var RegisteredReqStatus = (from t1 in _context.Apiapplicant
    join t2 in _context.ApiApplicantHistory on t1.Id equals t2.ApiApplicantId
    join t3 in _context.EntityType on t2.LastReqStatus equals t3.Id
    where t1.IsDeleted.Equals(false) && t1.LastRequestStatus == t2.Id
    group t2 by t2.LastReqStatus into ApiAppGp
        select new
        {
            lastReqName = ApiAppGp.FirstOrDefault().LastReqStatusNavigation.Name,
            ReqCount = ApiAppGp.Count()
        }
    ).ToList();

but after running my project, it shows me an error like below:

InvalidOperationException: The LINQ expression '(GroupByShaperExpression: KeySelector: (a.lastReqStatus), ElementSelector:(EntityShaperExpression: EntityType: ApiApplicantHistory ValueBufferExpression: (ProjectionBindingExpression: EmptyProjectionMember) IsNullable: False ) ) .FirstOrDefault()' 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.

What I have tried:

var RegisteredReqStatus = (from t1 in _context.Apiapplicant
    join t2 in _context.ApiApplicantHistory on t1.Id equals t2.ApiApplicantId
    join t3 in _context.EntityType on t2.LastReqStatus equals t3.Id
    where t1.IsDeleted.Equals(false) && t1.LastRequestStatus == t2.Id
    group t2 by t2.LastReqStatus into ApiAppGp
        select new
        {
            lastReqName = ApiAppGp.FirstOrDefault().LastReqStatusNavigation.Name,
            ReqCount = ApiAppGp.Count()
        }
    ).ToList();

Posted 27-Apr-20 18:20pm

ElenaRez

Updated 29-Apr-20 6:29am

Garth J Lancaster

v3

Add a Solution

Comments

Garth J Lancaster 28-Apr-20 2:18am

My 'gut feel' is that ApiAppGp is an anonymous type, which as the error suggests, doesn't have AsEnumerable() defined on it - hence the .FirstOrDefault() fails.

As the error message suggests, you could try something line

lastReqName = ApiAppGp.AsEnumerable().FirstOrDefault().LastReqStatusNavigation.Name,

but that still looks/feels wrong. Hopefully someone else will give you a better answer, I dont have time right now to run it up and play with it.

ElenaRez 28-Apr-20 3:35am

Thanks for your suggestion, Still the error exists like the following:

.AsEnumerable()' could not be translated. Either rewrite the query in a form that can be translated

2 solutions

Solution 1

The error message is quite clear: FirstOrDefault()' could not be translated.
I'd strongly recommend to read this: EF Core: LINQ queries are no longer evaluated on the client[^]
To resolve this, try to use aggregate function available in SQL:

C#

///...
select new
{
       lastReqName = ApiAppGp.Max(x=>x.LastReqStatusNavigation.Name),
        ReqCount = ApiAppGp.Count()
}
//...

Posted 27-Apr-20 20:32pm

Maciej Los

Comments

ElenaRez 28-Apr-20 3:36am

still the error exists like the following:
.Max(x => x.LastReqStatusNavigation.Name)' 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.

Maciej Los 28-Apr-20 3:59am

You need to know that i have no access to your data and i can't see your screen. So, you have to change your query accordingly to SQL version.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2020-04-29T06:30:00

Grouping in EF Core 3.x does seem to be quite picky about what it can and cannot translate.

Try projecting the navigation property value first:

C#

var query = from t1 in _context.Apiapplicant
    join t2 in _context.ApiApplicantHistory on t1.Id equals t2.ApiApplicantId
    join t3 in _context.EntityType on t2.LastReqStatus equals t3.Id
    where t1.IsDeleted == false && t1.LastRequestStatus == t2.Id
    let tg = new 
    { 
        t2.LastReqStatus, 
        lastReqName = t2.LastReqStatusNavigation.Name 
    }
    group tg by tg.LastReqStatus into ApiAppGp
    select new
    {
        lastReqName = ApiAppGp.Max(x => x.lastReqName),
        ReqCount = ApiAppGp.Count()
    };

var RegisteredReqStatus = query.ToList();

If it still doesn't work, you might need to fall back to the extension method instead:

C#

var query = from t1 in _context.Apiapplicant
    join t2 in _context.ApiApplicantHistory on t1.Id equals t2.ApiApplicantId
    join t3 in _context.EntityType on t2.LastReqStatus equals t3.Id
    where t1.IsDeleted == false && t1.LastRequestStatus == t2.Id
    select new 
    { 
        t2.LastReqStatus, 
        lastReqName = t2.LastReqStatusNavigation.Name 
    };

var RegisteredReqStatus = query
    .GroupBy(tg => tg.LastReqStatus, (key, items) => new
    {
        lastReqName = items.Max(x => x.lastReqName),
        ReqCount = items.Count()
    })
    .ToList();