How to write correlated queries in entity framework?

Question

I need to translate this query into EF Core

SELECT * FROM Release AS r1
WHERE r1.Status = 'Complete' AND
r1.UpdateDate = (
  SELECT MAX(UpdateDate)
  FROm RELEASE AS r2
  WHERE r2.Platform = r1.Platform AND
  r2.Status = 'Complete'
);

And it was great to have an idea how to write the same query using the specification pattern. There are not so many examples on the Internet about this, and nothing at all about correlated queries

You don't use `r` in your sub query. Did you mean to use `r1` in the main query? — gunr2171, Nov 30 '22 at 15:55
Check this [answer](https://stackoverflow.com/a/72787160/10646316) It has custom `DistinctBy` implementation. Do not mess with .NET 6 `DistinctBy` function. — Svyatoslav Danyliv, Nov 30 '22 at 16:45

score 0 · Answer 1 · answered Nov 30 '22 at 16:02

Results may vary, but this is how I would do it. It's not terribly efficient, but it gets the job done;

var result = await Release
.Where(r1 => r1.Status == "Complete" && r1.UpdateDate == Release
    .OrderByDescending(r2 => r2.UpdateDate)
    .First(r2 => r2.Platform == r1.Platform && r2.Status == "Complete").UpdateDate)
.ToListAsync();

How to write correlated queries in entity framework?

1 Answers1