Click here to Skip to main content
15,877,754 members
Please Sign up or sign in to vote.
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
Updated 29-Apr-20 6:29am
v3
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

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();
 
Share this answer
 
v2
Comments
ElenaRez 1-May-20 6:27am    
Thank you very much. Your first solution worked for me.
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()
}
//...
 
Share this answer
 
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900