Csharp 如何使用 EF Core 和 LINQ 从相关表中按日期排序

我有一个包含这些表的MS SQL数据库

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

和

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

其中Review.ItemId是外键，将每个 comments 链接到各自的项目.

For now, with EF Core 6 和 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 和 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;
}

我之所以这样做，是因为我不想获取整个数据库，然后排序，而是排序然后提取(我不知道它是否清楚).希望我做得对.

在使用它之后，我发现按最后Review.Date个排序会更好，但同样，我希望排序然后提取，并且我仍然希望显示没有 comments 的项目(如使用LEFT JOIN或OUTER APPLY).

First I tried to do it in classic SQL 和 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 和 LINQ.

I tried first to just get the Items 和 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 和 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 和 best practice but I didn't want to duplicate a field.

有没有人可以帮我解决这个问题，同时保持对数据进行排序/排序，然后获取数据的 idea .