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