How can I convert this SQL query to linq when I have sub query, grouping and having clause?

Question

I'm really struggling to convert the following SQL to a LINQ query. Can anyone help?

select invoice, date, amount, Name 
from payments 
where invoice in (select invoice
                  from payments
                  where invoicelike 'EVENT%' 
                    and status = 3 
                    and date >= '2023-11-01 00:00'
                  group by invoice
                  having count(*) > 1)
order by invoice, date desc

Answer 1

Yes, this query could be translated into LINQ:

var date = new DateTime(2023, 11, 1);
var forFilter = context.Payments
    .Where(x => x.Invoice.StartsWith("EVENT") && x.Status == 3 && x.Date > date)
    .GroupBy(x => x.Invoice)
    .Where(x => x.Count() > 1)
    .Select(x => x.Key);

var data = await context.Payments
    .Where(x => forFilter.Contains(x.Invoice))
    .ToArrayAsync(cancellationToken);

Example: I have implemented simple example using my database:

app.MapGet("/get-special", async (ExampleWebApiContext context, CancellationToken cancellationToken) =>
{
    var forFilter = context.Movies
        .Where(x => x.ReleaseYear > 1000)
        .GroupBy(x => x.Id)
        .Where(x => x.Count() > 1)
        .Select(x => x.Key);

    var data = await context.Movies
        .Where(x => forFilter.Contains(x.Id))
        .ToArrayAsync(cancellationToken);

    return data;
});

which produced query as desired: