Click here to Skip to main content
14,699,541 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 7: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:
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:
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();
   
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:
///...
select new
{
       lastReqName = ApiAppGp.Max(x=>x.LastReqStatusNavigation.Name),
        ReqCount = ApiAppGp.Count()
}
//...
   
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