Group By and Count query fails to translate from Linq to SQL-CodePudding

I have created the linq query shown below, in LINQPad. This fails when using EF Core 3.0. I know the issues around Group By and the reason EF Core fails when the translation cannot be made rather than pulling back all the data and doing it client side. My problem is that I have so far not worked out if it is possible to do what I want to do to get my query to run.

My table has a CorrelationId which is the same value only for related records. I need to create a query which just returns the records where the CorrelationId only exists once. If the CorrelationId appears more than once then that indicates there are related records and none of these should be returned. The query must run on the server so pulling back all the data is not an option.

Here is my Linq query that fails with the "...could not be translated. Either rewrite the query in a form that can be translated..." error.

from x in History
where
    (
        from d in History
        group d by new {d.CorrelationId} into g
        where g.Count() == 1
        select new {Id = g.Key.CorrelationId}
    ).Contains(new { Id = x.CorrelationId })
select new {Id = x.Id, FileName = x.FileName}

CodePudding user response：

Try using Any instead of Contains

.Any(y => y.Id == x.CorrelationId)