LINQ version of sql query with multiple left joins, group by, and datetime conversion

Question

I have the following SQL query which is returning one row of data exactly as expected:

select count(c.ID) as NoteCount, count(s.ClaimStatusHistoryID) as ActionCount, p.DayGoal
from Collector_Profile p
left join ClaimStatusHistory s on s.AppUserID = p.AppUserID and CONVERT(varchar(10), s.StatusDateTZ, 101) = convert(varchar(10), GETDATE(), 101)
left join Claim_Notes c on c.CollectorID = p.ID and CONVERT(varchar(10),c.PostDateTZ,101) = convert(varchar(10), GETDATE(), 101)
where p.ID = 1338
group by p.DayGoal

I am trying to convert to LINQ. When I attempt to include the DbFunctions.TruncateTime, I get an error that TruncateTime is not defined. So I have commented them out in this example, but I need to get that working as well. This is what I have so far, which compiles but throws an error:

var utcNow = DateTimeOffset.UtcNow.Date;
var query = from p in _context.Collector_Profile
  join s in _context.ClaimStatusHistory on p.AppUserID 
    equals s.AppUserID into gs 
    // && DbFunctions.TruncateTime(s.StatusDateTZ) equals utcNow into gs
join c in _context.Claim_Notes on p.ID 
    equals c.CollectorID into gc 
    //&& DbFunctions.TruncateTime(c.PostDateTZ) equals utcNow into gc
from s in gs.DefaultIfEmpty()
from c in gc.DefaultIfEmpty()
where p.ID == CollectorID
group new { gs, gc } by p.DayGoal into grouped
select new UserStatistics { DayGoal = grouped.Key, 
   NoteCount = grouped.Count(x => x.gc.Any()), 
   ActionCount = grouped.Count(x => x.gs.Any()) };

return query.FirstOrDefault();

I get the following error when I run it:

InvalidOperationException: Processing of the LINQ expression 'DbSet<Collector_Profile>
 .GroupJoin(
 outer: DbSet<ClaimStatusHistory>, 
 inner: p => p.AppUserID, 
 outerKeySelector: s => s.AppUserID, 
 innerKeySelector: (p, gs) => new { 
 p = p, 
 gs = gs
 })' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.

Can someone help me to get this linq query working? Thanks for any assistance and examples. I've looked at lots of questions and none are doing exactly what I'm doing that I've found yet.

Perhaps my [SQL to LINQ Recipe[(https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) might help you. What LINQ are you using: LINQ to SQL / EF 6.x / EF Core 2.0 / EF Core 2.1 / EF Core 3.x? — NetMage, Jul 30 '20 at 17:57
`DBFunctions.TruncateTime` isn't needed in EF Core 3.x because `DateTime.Date` is translated properly. — NetMage, Jul 30 '20 at 18:28
It's a DateTimeOffset column. Date is not contained in DateTimeOffset, what should I use instead? — nonesuch, Jul 30 '20 at 19:51
I've read through your guide, and I feel like I did everything as specified in it, but I'm still confused. Can you please help? — nonesuch, Jul 30 '20 at 20:22
EF Core 3 has a bug with `DateTimeOffset.Date` and timezones (see [this issue](https://github.com/dotnet/efcore/issues/19052)) but `Date` is definitely contained in `DateTimeOffset` why do you think it isn't? — NetMage, Jul 30 '20 at 20:36
You have a reference to `CollectorID` in your code - is that an outside variable? — NetMage, Jul 30 '20 at 20:36
yes CollectorID is an outside variable which is present available for use. — nonesuch, Jul 30 '20 at 21:10

NetMage · Accepted Answer · 2020-07-31T20:48:02.627

1

I believe this should work, barring issues with DateTimeOffset.Date and timezones.

Since EF Core 3 only has extremely limited support for translating GroupJoin (basically just to LEFT JOIN), you must split the query into two parts, a SQL query with LEFT JOIN and then a client side GroupBy to create the effect of GroupJoin.

var utcNowDate = DateTimeOffset.UtcNow.Date;

var dbQuery = from p in _context.Collector_Profile
              where p.ID == CollectorID
              join s in _context.ClaimStatusHistory.Where(s => s.StatusDateTZ.Value.Date == utcNowDate) on p.AppUserID equals s.AppUserID into gs
              from s in gs.DefaultIfEmpty()
              join c in _context.Claim_Notes.Where(c => c.PostDateTZ.Value.Date == utcNowDate) on p.ID equals c.CollectorID into gc
              from c in gc.DefaultIfEmpty()
              select new { p.DayGoal, s = s.ClaimStatusHistoryID, c = c.ID };

var query = from psc in dbQuery.AsEnumerable()
            group new { psc.s, psc.c } by psc.DayGoal into grouped
            select new UserStatistics {
                DayGoal = grouped.Key,
                NoteCount = grouped.Count(sc => sc.c != null),
                ActionCount = grouped.Count(sc => sc.s != null)
            };

return query.FirstOrDefault();

edited Jul 31 '20 at 20:48

answered Jul 30 '20 at 20:42

NetMage

26,163
3
34
55

Thanks for the efforts, it's really appreciated. I get the following error: InvalidOperationException: Processing of the LINQ expression 'DbSet .Where(p => p.ID == __CollectorID_0) .GroupJoin( outer: DbSet, inner: p => p.AppUserID, outerKeySelector: s => s.AppUserID, innerKeySelector: (p, gs) => new { p = p, gs = gs })' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information. – nonesuch Jul 30 '20 at 21:04
It also doesn't truncate the DateTimeOffset columns before the comparison. I've tried to do s.StatusDateTZ.Date == utcNowDate and I get an undefined on .Date within DateTimeOffset? – nonesuch Jul 30 '20 at 21:08
@GeorgeHarris Can you provide the actual C# declaration for the `StatusDateTz` member? – NetMage Jul 30 '20 at 22:02
public DateTimeOffset? StatusDateTZ {get; set; } – nonesuch Jul 30 '20 at 22:24
@GeorgeHarris Ah - then `StatusDateTZ` might be null - you need to say `s.StatusDateTz.Value.Date`. `Nullable` (`DateTimeOffset?`) doesn't have a member or extension method `Date`; `DateTimeOffset` does. – NetMage Jul 30 '20 at 23:08
Okay, great, that worked -- but I'm still getting the error stated above when I run it. InvalidOperationException: Processing of the LINQ expression 'DbSet .Where(p => p.ID == __CollectorID_0) .GroupJoin( outer: DbSet, inner: p => p.AppUserID, outerKeySelector: s => s.AppUserID, innerKeySelector: (p, gs) => new { p = p, gs = gs })' by 'NavigationExpandingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information. – nonesuch Jul 31 '20 at 12:58
@GeorgeHarris Unfortunately EF Core 3 doesn't translate `GroupJoin` at all and is unlikely to do so, when re-discussed they decided the limited times it would work wasn't worth it (!). Looking at your query more closely, is it possible for more than one `p` to match `CollectorID` or will that always be a single record? – NetMage Jul 31 '20 at 18:09
@GeorgeHarris I replaced my answer with something I hope EF Core 3 will support. – NetMage Jul 31 '20 at 20:48
Thanks so much for your assistance on this! – nonesuch Aug 04 '20 at 18:59

LINQ version of sql query with multiple left joins, group by, and datetime conversion

1 Answers1