Linq2db:查询对象层次结构的有效方法 [英] Linq2db: effective way to query hierarchy of objects

问题描述

我使用的是 c# 和 linq2db，并且具有以下类/表层次结构:

I'm using a c# and linq2db and have the following class/tables hierarchy:

public class YearlyTemplate
{
    [Column]
    public int Id { get; set; }
    public List<MonthlyTemplate> MonthlyTemplates { get; set;}
}

public class MonthlyTemplate
{
    [Column]
    public int Id { get; set; }

    [Column]
    public int YearlyTemplateId { get; set; }

    public YearlyTemplate YearlyTemplate{ get; set; }

    public List<DailyTemplate> DailyTemplates { get; set;}
}

public class DailyTemplate
{
    [Column]
    public int Id { get; set; }

    [Column]
    public int MonthlyTemplateId { get; set; }
    
    public MonthlyTemplate MonthlyTemplate { get; set; }
}

public class AppDataConnect : DataConnection
{
    public ITable<YearlyTemplate> YearlyTemplates => GetTable<YearlyTemplate>();
    public ITable<WeeklyTemplate> WeeklyTemplates => GetTable<WeeklyTemplate>();
    public ITable<DailyTemplate>  DailyTemplates => GetTable<DailyTemplate>();
}

我想使用 where 语句从数据库中获取特定年份，但我想获取它的所有嵌套 MonthlyTemplates，以及每个 Monthlytemplate 的所有 DailyTemplates.如何有效地使用 linq2db 做到这一点?我想我应该使用 group by，但它仅适用于一层深度.

I want to get a specific year from the database using where statement, but I want to get all nested MonthlyTemplates for it, and all DailyTemplates for each Monthlytemplate. How can I do it using linq2db effectively? I suppose I should use group by, but it works only on one level depth.

推荐答案

这里没什么特别的.就像在 EF Core 中一样，linq2db 包含 Eager Loading 的方法.首先你必须定义关联

Nothing special here. Just like in EF Core, linq2db contains methods for Eager Loading. At first you have to define Associations

public class YearlyTemplate
{
    [Column]
    public int Id { get; set; }

    [Association(ThisKey = nameof(YearlyTemplate.Id), OtherKey = nameof(MonthlyTemplate.YearlyTemplateId))]
    public List<MonthlyTemplate> MonthlyTemplates { get; set;}
}

public class MonthlyTemplate
{
    [Column]
    public int Id { get; set; }

    [Column]
    public int YearlyTemplateId { get; set; }

    public YearlyTemplate YearlyTemplate{ get; set; }

    [Association(ThisKey = nameof(MonthlyTemplate.Id), OtherKey = nameof(DailyTemplate.MonthlyTemplateId))]
    public List<DailyTemplate> DailyTemplates { get; set;}
}

和查询

var query = 
  from y in db.YearlyTemplates
           .LoadWith(yt => yt.MonthlyTemplates)
              .ThenLoad(mt => mt.DailyTemplates)
  where y.Id == 1
  select y;

var result = query.ToArray();

或使用过滤器(两种方式如何自定义 LoadWith/ThenLoad)

Or with filters (two ways how to customize LoadWith/ThenLoad)

var query = 
  from y in db.YearlyTemplates
           .LoadWith(yt => yt.MonthlyTemplates.Where(mt => !mt.IsDeleted))
              .ThenLoad(mt => mt.DailyTemplates, q => q.Where(ti => !dt.IsDeleted))
  where y.Id == 1
  select y;

var result = query.ToArray();

或者您可以使用自定义投影，这样可以提高性能，因为您可以只选择需要的字段:

Or you can use custom projection, which can be more performant because you can choose only needed fields:

var query = 
  from y in db.YearlyTemplates
  where y.Id == 1
  select new 
  {
     Id = y.Id,
     MonthlyTemplates = y.MonthlyTemplates.Select(mt => new {
        mt.Id,
        DailyTemplates = mt.DailyTemplates.ToArray()
     }).ToArray()
  };

var result = query.ToArray();

这篇关于Linq2db:查询对象层次结构的有效方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！