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
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.
User contributions licensed under CC BY-SA 3.0