Linq query in Entity Framework Core 3.5.1

0

I have tried every variation of this query in EF Core and I cannot figure it out.
I have simplified this as such:

Invoice Table

InvoiceId stuff
1 A
2 B

InvoiceLog Table

Id InvoiceId Date PersonId
1 1 11/12/2015 1
2 2 1/20/2018 2
3 2 3/15/2019 3

Person Table

Id Name
1 Bob
2 Steve

Here's my question

var vm = (from i in _context.Invoice                      
                   join l in _context.InvoiceLog on i.InvoiceId equals l.InvoiceId
                  **//this is returning multiple records, how do I return only the one with the Min(Date)**

                  join pl in _context.Person on l.UpdateUserId equals pl.PersonId                     
                   select new InvoiceViewModel
                   {
                       InvoiceId = i.InvoiceId,       
                       SubmitterName = pl.FirstName + " " + pl.LastName,
                   }).ToList();

ex> for Invoice 2 this should return ONE InvoiceLog record with the min date 1/20/2018 and the Person Name of Steve.

This is the error I get:

System.InvalidOperationException HResult=0x80131509 Message=Processing of the LINQ expression 'DbSet .GroupJoin( outer: DbSet, inner: i => i.InvoiceId, outerKeySelector: l => l.InvoiceId, innerKeySelector: (i, lj) => new { i = i, lj = lj })' 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. Source=Microsoft.EntityFrameworkCore

c#
linq
entity-framework-core
asked on Stack Overflow Dec 17, 2020 by jodeenm • edited Dec 18, 2020 by jodeenm

1 Answer

0

Instead of using join, you can use a cross-join by using from twice and filtering the second source down to the matching InvoiceLog rows and then taking the lowest one:

var vm = (from i in _context.Invoice
          from l in (from l2 in _context.InvoiceLog
                     where i.InvoiceId == l2.InvoiceId
                     orderby l2.Date
                     select l2).Take(1)
          join pl in _context.Person on l.UpdateUserId equals pl.Id
          select new InvoiceViewModel {
              InvoiceId = i.InvoiceId,
              SubmitterName = pl.Name
          }).ToList();

NOTE: EF Core 5 supports filtered include, with OrderBy and Take, so you could setup a proper relation between Invoice and InvoiceLog and then just Include what you need.

answered on Stack Overflow Dec 18, 2020 by NetMage

User contributions licensed under CC BY-SA 3.0