How to order by date from related table with EF Core and LINQ

Question

I have a MS SQL database with these tables

Item { ItemId | SerialNumber | Type | Height | ... }

and

Review { ReviewId | Date | ... | ItemId }

with Review.ItemId being a foreign key to link each Review to their respective Item.

For now, with EF Core 6 and using LINQ (on .NET 6.0) I am fetching N rows (for pagination) of items with some optional search parameters. They are ordred by ItemId. For each item, I also add the last review date that I was getting from the table Review and set as a [NotMapped] property of the Item class.

public static List<Item> GetInstruments(Dictionary<string, int> searchParameters, QueryParameters queryParameters)
{
    List<Item> items = new();

    try
    {
        using (var dbContext = new DbContext())
        {
            var dataQuery = dbContext.Items.AsQueryable().AsEnumerable();

            if (searchParameters != null)
            {
                foreach (var searchParameter in searchParameters)
                {
                    if (searchParameter.Key == "SerialNumber")
                    {
                        dataQuery = dataQuery.Where(i => i.SerialNumber == searchParameter.Value);
                    }
                    if (searchParameter.Key == "Type")
                    {
                        dataQuery = dataQuery.Where(i => i.Type == searchParameter.Value);
                    }
                    if (searchParameter.Key == "Height")
                    {
                        dataQuery = dataQuery.Where(i => i.Height == searchParameter.Value);
                    }
                }
            }

            var offset = (queryParameters.PageNumber - 1) * queryParameters.RowsPerPage;
            if (queryParameters.OrderBy == null)
            {
                dataQuery = dataQuery.OrderByDescending(i => i.ItemId);
            }
            else
            {
                var porpertyInfo = typeof(Item).GetProperty(queryParameters.OrderBy);
                if (queryParameters.Ascending == true)
                {
                    dataQuery = dataQuery.AsEnumerable().OrderBy(i => porpertyInfo.GetValue(i, null));
                }
                else
                {
                    dataQuery = dataQuery.AsEnumerable().OrderByDescending(i => porpertyInfo.GetValue(i, null));
                }
            }
            dataQuery = dataQuery
                    .Skip(offset)
                    .Take(queryParameters.RowsPerPage);

            items = dataQuery.ToList();
            foreach (var instrument in items)
            {
                instrument.DateLastReview = dbContext.Reviews
                    .Where(c => c.ItemId == instrument.ItemId)
                    .Max(c => c.Date);
            }
        }
    }
    catch (Exception e)
    {
        ...
    }

    return items;
}

I'm doing it in this specific way because I don't want to fetch the whole DB then sort but sort then fetch (I don't know if it's clear). Hopefully I did it correctly.

After using it I found it would be better to sort by last Review.Date BUT, again, I want to sort then fetch AND I still want to display items that have no reviews (as with an LEFT JOIN or a OUTER APPLY).

First I tried to do it in classic SQL and it worked when I tested it (btw if there is a better way to do the following in SQL I'll also take it)

SELECT i.ItemId, i.SerialNumber, i.Type, i.Height, r.Date
FROM Item i
OUTER APPLY
(
    SELECT Max(r.Date) as Date
    FROM Review r
    WHERE r.ItemId = i.ItemId
) r
ORDER BY r.Date DESC

But I'm stumped with EF and LINQ.

I tried first to just get the Items and the Review Date but I obviously doing it wrong. I know it's incomplete to do everything I want but I first wanted to just get the items and the LastReviewDate.

var dataQuery = from il in dbContext.Items
                select new
                {
                    il,
                    DateLastReview = dbContext.Reviews
                                     .Where(r => il.ItemId == r.ItemId)
                                     .Max(c => r.Date)
                };

So now I'm here because I don't want to just add a DateLastReview column to the Item table; I will if I don't find any solution or if you think it's better for performance and best practice but I didn't want to duplicate a field.

Could anyone help me with this while keeping the idea of sorting/ordering then fetching data.

Answer 1

Don't do this:

dbContext.Items.AsQueryable().AsEnumerable();

This will lead to fetching everything into memory from the database (without actually fetching the needed information to sort by related entity). I don't have your setup but the needed ordering can be done quite simply with "static" query, for example using the following entities:

public class Blog
{
    public int Id { get; private set; }
    // ...
    public List<Post> Posts { get; } = new(); // do not forget the relationship setup
}

public class Post
{
    public int Id { get; private set; }
    // ...
    public DateTime PublishedOn { get; set; }
    public Blog Blog { get; set; } = null!; // do not forget the relationship
}

var blogs = ctx.Blogs
    .OrderByDescending(blog => blog.Posts.Max(post => post.PublishedOn))
    .ToList();

Which results in following SQL (Postgres) for my setup:

SELECT b."Id", b."Name"
FROM "Blogs" AS b
ORDER BY (
   SELECT max(p."PublishedOn")
   FROM "Posts" AS p
   WHERE b."Id" = p."BlogId") DESC

As for dynamic query generation - you - remove the AsEnumerable call and for the reflection stuff (var porpertyInfo = typeof(Item).GetProperty(queryParameters.OrderBy); ...) you will need either to hardcode it by using some switch cases or generate expression tree or use some 3rd party library like System.Linq.Dynamic.Core or LINQKit.

Also see this answer.