Linq and Core simple where clause and projection issue

Question

A simple where clause is failing bringing back <>h__TransparentIdentifier...

from ts in TimeSpentQuery
              .Where(z => z.JobId.HasValue ? z.JobId.Value != 0 ?
                          z.JobId.Value==j.JobId : false : false)

The list is created here

    var TimeSpentQuery = (from js in context.TblTechnicianRecords.ToList()
    group js by js.JobId into g
    select new { JobId = g.Key, TimeSpent = g.Sum(x => x.EndTime.HasValue ? x.EndTime.Value.Ticks - x.StartTime.Ticks : 0) }).DefaultIfEmpty();

The error states

InvalidOperationException: The LINQ expression '<>h__TransparentIdentifier7 => __TimeSpentQuery_0 .Where(z => z.JobId.HasValue ? z.JobId.Value != 0 ? z.JobId.Value == - then list a pile TransparentIdentifier-<>h__TransparentIdentifier0.j.JobId : False : False)'

for a simple where clause this has caused me all sorts of grief. What am I missing?

`TimeSpentQuery.Where(z => z.JobId == j.JobId && z.JobId != 0)` — Magnus, Jun 13 '22 at 07:48
@Magnus, thank you for your response, don't think this is down to being nullable, but I admit your version is neater. — aegsomweb, Jun 13 '22 at 08:40
InvalidOperationException: The LINQ expression '<>h__TransparentIdentifier7 => __TimeSpentQuery_0 .Where(z => z.JobId == (int?)<>h__TransparentIdentifier7.<>h__TransparentIdentifier6.<>h__TransparentIdentifier5.<>h__TransparentIdentifier4.<>h__TransparentIdentifier3.<>h__TransparentIdentifier2.<>h__TransparentIdentifier1.<>h__TransparentIdentifier0.j.JobId && z.JobId != (int?)0)' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly - The error in full... — aegsomweb, Jun 13 '22 at 08:41
`g.Where(x => x.EndTime != null).Sum(x => x.EndTime.Value.Ticks - x.StartTime.Ticks) })` — Magnus, Jun 13 '22 at 09:09
@Magnus thanks again, but the TimeSpentQuery list is being filled correctly. The "from ts in TimeSpentQuery.Where(z => z.JobId != 0 && j.JobId ==z.JobId)" is part of a bigger query, but the rest is running fine. The original query was in VB and worked so this would appear to be a quirk in C# linq see https://ericlippert.com/2014/07/31/transparent-identifiers-part-one/ — aegsomweb, Jun 13 '22 at 09:23
https://stackoverflow.com/questions/20496766/can-i-join-a-table-to-a-list-using-linq Can't join list to Entity framework, that took long enough to find! — aegsomweb, Jun 13 '22 at 14:32

aegsomweb · Accepted Answer · 2022-07-12T15:33:35.217

It seems that dot net core, Entity Framework (EF) and C# has an issue with multiple LINQ from calls in building up a complex query(legacy data). I eventually created the data via multiple lists with simple joins. All previous from statements appear as <>h__TransparentIdentifier and when dealing with EF seemed to behave. but given a local list the system threw the above error, which is understandable as the SQL compiler has no Knowledge of the List, I hoped that the LINQ would compile as a group of subqueries rather than trying to form a single query.

Linq and Core simple where clause and projection issue

1 Answers1