Linq2db: effective way to query hierarchy of objects

Question

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

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.

ORMs are a leaky abstraction and that's one of the leaks - ORMs don't work well with reporting queries, which don't deal with objects/entities anyway. Typically, problems like this are solved by using a Calendar table, ie a table with eg 20 years of dates with repopulated year, month, day, quarter, semester fields covered by indexes. To find records in a specific period, you join with the Calendar table and filter by the year or month you want. The tricky query becomes a single join and filtering on indexed columns — Panagiotis Kanavos, Jul 29 '20 at 10:03
A Calendar table is a helper table though and typically doesn't make sense to appear in an application's data or domain model. You could add it, just to make querying easier, or you could create a view/stored procedure in the database that uses the Calendar table and map the results to your data model — Panagiotis Kanavos, Jul 29 '20 at 10:06
Where is the Date? How do you know which date is for which day without the date? What you want is select many but it is not going to work without the date. — jdweng, Jul 29 '20 at 10:08
The date field is not the case. We don't need it; the provided hierarchy is just a simplified example of the original hierarchy. So, we don't need to care about the date, and that is why I can't use the Calendar as well. — Grigory Zhadko, Jul 29 '20 at 10:15
I use something like this : var results = db_context.YearlyTemplate.SelectMany(x => x.MonthlyTemplates.SelectMany(y => y.DailyTemplates.Select(z => new { yearlyID = x.Id, monthlyID = y.Id, dailyId = z.Id}))).ToList(); — jdweng, Jul 29 '20 at 10:44

Svyatoslav Danyliv · Accepted Answer · 2020-08-07T07:29:27.077

8

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

And query

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

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

edited Aug 07 '20 at 07:29

answered Aug 05 '20 at 14:24

Svyatoslav Danyliv

21,911
3
16
32

Thank you. Your answer is the comment I was looking for. It is not easy to find such a typical functionality on linq2db documentation. I don't know why. – Grigory Zhadko Aug 07 '20 at 07:07
Do you know if I can filter DailyTemplates in your example? I wanted to get not all of them from the database but just the filtered part. – Grigory Zhadko Aug 07 '20 at 07:14
2

Updated post with filters. About documentation: volunteers are needed. – Svyatoslav Danyliv Aug 07 '20 at 07:30
Is it possible to make a join for MonthlyTemplate in this case? I want to do something like that: from y in db.YearlyTemplates.LoadWith(yt => yt.MonthlyTemplates.LeftJoin(t => t.AnotherTableId == AnotherTable.Id) – Grigory Zhadko Sep 01 '20 at 06:00
Yes it is possible, but better create new question. Answer will be a reference source. – Svyatoslav Danyliv Oct 17 '20 at 11:11
Unfortunately, I can't ask a new question for the next 5 months. The StackOverflow doesn't allow me to do that because my last questions (most related to linq2db) weren't good enough. It means that nobody voted for them. So, if you could help me with that and vote for this question at least, I would ask a new one. Thanks – Grigory Zhadko Nov 16 '20 at 18:31

Linq2db: effective way to query hierarchy of objects

1 Answers1

Linked