EF Core translate to LINQ mix of MAX(Last) GROUP BY, Contains and WHERE

Question

I have the following table:

Id   DateTime      UserId  InvoiceId  State
1    2018-01...    1       1          5
2    2018-02...    2       1          2
3    2018-03...    2       1          1
4    2018-01...    2       2          5
5    2018-02...    1       2          8
6    2018-01...    1       3          5
7    2018-02...    2       3          8
8    2018-03...    1       3          5
9    2018-04...    2       3          10
10   2018-05...    2       3          5
11   2018-01...    1       4          1
11   2018-02...    2       4          10

I would like to get InvoiceId 2 and 4, because last state (chronological and by DateTime) is 10or 8. Invoices with id 1 is not good because last state is 1, 3 last state is 5.

Both of this queries returns correct results:

SELECT [t].[InvoiceId]
FROM [Postman].[Invoice-States] AS [t]
INNER JOIN [Postman].[Invoices] AS [t.Invoice] ON [t].[InvoiceId] = [t.Invoice].[Id]
INNER JOIN [Postman].[Recipients] AS [t.Invoice.Recipient] ON [t.Invoice].[RecipientId] = [t.Invoice.Recipient].[Id]
WHERE [t.Invoice.Recipient].[PartnerId] = 4
GROUP BY [t].[InvoiceId], [t].[DocumentState]
HAVING (MAX([t].[InsertedDateTime]) = (select top 1 InsertedDateTime from [Postman].[Invoice-States]  where t.InvoiceId = InvoiceId order by InsertedDateTime desc)) AND [t].[DocumentState] IN (CAST(8 AS tinyint), CAST(10 AS tinyint))

and

SELECT RES.[InvoiceId] FROM [Postman].[Invoice-States] AS RES
INNER JOIN (
SELECT [t].[InvoiceId], Max([t].[InsertedDateTime]) AS MaxInsertedDateTime
  FROM [OpPIS.Web.Development.Opal].[Postman].[Invoice-States] AS [t]
  INNER JOIN [OpPIS.Web.Development.Opal].[Postman].[Invoices] AS [t.Invoice] ON [t].[InvoiceId] = [t.Invoice].[Id]
  INNER JOIN [Postman].[Recipients] AS [t.Invoice.Recipient] ON [t.Invoice].[RecipientId] = [t.Invoice.Recipient].[Id]
  WHERE [t.Invoice.Recipient].[PartnerId] = 4
  GROUP BY [t].[InvoiceId]) AS MD
ON RES.InvoiceId = MD.InvoiceId AND RES.[InsertedDateTime] = MD.MaxInsertedDateTime AND (RES.DocumentState IN (8, 10))

But I don't know how to translate to LINQ.
I tried:

    //Returns ALL that have 8 or 10.
    var fDocsState = await this.Query<DbInvoiceState>(t => t.Invoice.Recipient.PartnerId == partnerId)
        .GroupBy(t => t.InvoiceId, t => new { t.DocumentState, t.InsertedDateTime })
        .Where(t => states.Contains(t.OrderByDescending(t2 => t2.InsertedDateTime).Select(t2 => t2.DocumentState).FirstOrDefault()))
        .Select(t => t.Key)
        .ToListAsync();

    //Column 'Postman.Invoice-States.DocumentState' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
    var fDocsState = await this.Query<DbInvoiceState>(t => t.Invoice.Recipient.PartnerId == partnerId)
        .GroupBy(t => t.InvoiceId, (key, t) => states.Contains(t.Last().DocumentState))
        .ToListAsync();

    //Exception in LINQ
    var fDocsState = await this.Query<DbInvoiceState>(t => t.Invoice.Recipient.PartnerId == partnerId)
        .GroupBy(t => t.InvoiceId, (key, t) => t.Last())
        .Where(t => states.Contains(t.DocumentState))
        .ToListAsync();

This one returns correct results, but it is evaluated locally (bad performance).

    var fDocsState = await this.Query<DbInvoiceState>(t => t.Invoice.Recipient.PartnerId == partnerId)
        .GroupBy(t => t.InvoiceId)
        .Select(t => new
        {
            t.Key,
            DocumentState = t.OrderByDescending(t2 => t2.InsertedDateTime).Select(t2 => t2.DocumentState).FirstOrDefault()
        })
        .ToListAsync();

Any other idea, how to get desired results with EF Core?

To simplify

I would like to get all InvoiceIds which last state is 8 or 10.

Maybe its a good idea to show what you want as result. Your post is a bit chaotic and very difficult to understand. In you sql examples you show there are joins with other tables but you dont show how these are related to the table you show... Please be more specific — Jordy van Eijk, Oct 08 '18 at 13:40
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) could help you? — NetMage, Oct 08 '18 at 20:08
@JordyvanEijk I tried to tell what I tried so far, so maybe it is a little confusing. I added new section at the bottom of my question. Hope it is a bit more understandable. — Makla, Oct 09 '18 at 07:30

score 0 · Answer 1 · answered Oct 09 '18 at 10:54

Found it, how to do inner join with contains:

    var states = new[] { DocumentState.ConfirmedReceive, DocumentState.ReceiverConfirm, DocumentState.ReceiverRefused };

    return await this.Set<DbInvoiceState>().Join(
            this.Set<DbInvoiceState>().GroupBy(t => t.InvoiceId).Select(t => new { t.Key, MaxInserted = t.Max(t2 => t2.InsertedDateTime) }),
            t => new
            {
                t.InvoiceId,
                t.InsertedDateTime,
                states = states.Contains(t.DocumentState)
            },
            t => new
            {
                InvoiceId = t.Key,
                InsertedDateTime = t.MaxInserted,
                states = true
            },
            (t, t2) => new ViewInvoiceListModel
            {
                Id = t2.Key
            })
        .ToArrayAsync();

EF Core translate to LINQ mix of MAX(Last) GROUP BY, Contains and WHERE

1 Answers1