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

Question

我有一个MS SQL数据库与这些表

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

和

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

，Review.ItemId是将每个Review链接到各自的Item的外键。

现在，使用EF Core 6和LINQ(在。net 6.0上)，我用一些可选的搜索参数获取N行(用于分页)的项目。它们是由ItemId订购的。对于每个项目，我还添加了我从表review中获得的最后一次审查日期，并将其设置为item类的[NotMapped]属性。

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;
}

我这样做是因为我不想先取整个DB然后排序而是先排序然后取(我不知道是否清楚)希望我做对了。

在使用它之后，我发现按最后的Review.Date排序会更好，但是，再次，我想排序然后获取，我仍然想显示没有评论的项目(如左JOIN或OUTER APPLY)。

首先，我尝试在经典SQL中这样做，当我测试它时它工作了(顺便说一句，如果有更好的方法在SQL中执行以下操作，我也会采用它)

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

但是我被EF和LINQ难住了。

我试着先得到项目和审查日期，但我显然做错了。我知道这是不完整的，我想做的一切，但我首先想得到的只是项目和LastReviewDate。

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

现在我在这里，因为我不想只添加一个DateLastReview列到Item表;如果我找不到任何解决方案，或者如果你认为它对性能和最佳实践更好，但我不想复制字段，我会的。

谁能帮助我与此同时保持排序/排序然后获取数据的想法。

Answer 1

不要这样做:

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

这将导致从数据库中获取所有内容到内存中(而没有实际获取所需的信息来按相关实体进行排序)。我没有你的设置，但需要的排序可以很简单地使用& static"查询，例如使用以下实体:

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();

导致以下SQL (Postgres)为我的设置:

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

对于动态查询生成-你-删除AsEnumerable调用和反射的东西(var porpertyInfo = typeof(Item).GetProperty(queryParameters.OrderBy);…)，你将需要硬编码它通过使用一些开关情况或生成表达式树或使用一些第三方库，如System.Linq.Dynamic.Core或LINQKit。

另见答案