在子节点上搜索核心，在父节点上排序

Question

我有两个模型Parent和Child，我想检索所有具有unitid = 5并按家庭排序的Distinct Parent

public class Parent
{
public int Id { get; set; }
public string Family { get; set; }
public List<Child> Children { get; set; }
}
public class Child
{
public int Id { get; set; }
public int ParentId { get; set; }
public int UnitId { get; set; }
public Parent parent { get; set; }
}

父表有500,000条记录，子表有2000000条记录。我使用SelectMany和Distinct，但它需要8s来检索记录

var parents= Context.Set<Parent>()
.SelectMany(x => x.Children.DefaultIfEmpty(), 
(u, r) => new { 
Parent = u, 
Child = r })
.Where(x=>x.Child.UnitId == 5)
.Select(m => new Parent{ 
Id = m.Parent.Id, 
Family= m.Parent.Family
})
.Distinct()
.OrderBy(x=>x.Family)
.Take(30).Skip(0).ToList();

翻译成:

SELECT [t].[Id], [t].[Family] FROM (
SELECT  distinct [a].[Id],  [a].[Family]
FROM [Parent]  AS [a]
LEFT JOIN [Children] AS [r] ON [a].[Id] = [r].[ParentId]
WHERE  ([r].[unitid] = 5) AS [t] ORDER BY [t].[family] OFFSET 0 ROWS FETCH next 30 ROWS ONLY

或

var parents = Context.Set<Children>()
.Include(x=>x.Parent)
.Where(x=>x.UnitId ==5)
.Select(m => new Parent{ 
Id = m.ParentId , 
Family = m.Parent.Family})
.Distinct()
.OrderBy(x=>x.Family)
.Take(30).Skip(0).ToList();

翻译成:

SELECT [t0].[Id],[t0].[Family] FROM ( SELECT DISTINCT [t].[Id], [t].[Family]
FROM [Children] AS [r]
INNER JOIN (
SELECT [a].[Id] , [a].[Family]
FROM [Parent] AS [a]
) AS [t] ON [r].[ParentId] = [t].[Id]
WHERE ([r].[UnitId] = 5)  AS [t0] ORDER BY [t0].[Family] OFFSET 0 ROWS FETCH next 30 ROWS ONLY

1。当使用order by id时，它非常快

2。使用时按家庭顺序取，跳过是慢的

3。

4。Ef Core 3.1

哪个解决方案是正确的或更好的?

Answer 1

检查以下两个查询的性能:

var parents = 
from p in Context.Set<Parent>()
where p.Children.Any(c => c.UnitId == 5)
orderby p.Family
select p;
parents = parents
.Take(30)
.Skip(0)
.ToList();

和另一个:

var parentIds = 
from c in Context.Set<Child>()
where c.UnitId == 5
select c.ParentId
var parents = 
from p in Context.Set<Parent>()
join id in parentIds.Distinct() on p.Id equals id
orderby p.Family
select p;
parents = parents
.Take(30)
.Skip(0)
.ToList();