1

My model is:

public class WhereClause
{
    [Key]
    public int Id { get; set; }

    public int? ColumnId { get; set; }

    [StringLength(100)]
    public string Logic { get; set; }

    [StringLength(250)]
    public string Value { get; set; }

    public List<WhereClause?> WhereClauses { get; set; }

    public short HierarchyCount { get; set; }
}

As you see, model contain self-list of object and i want to include all inner list in any level at one fetch. Based on Entity Framework - Include Multiple Levels of Properties I know that can use Include and ThenInclude but i don't know how many level of inner list in model. currently i store HierarchyCount property at model for this purpose and create string of include like .Include("WhereClauses,WhereClauses.WhereClauses,WhereClauses.WhereClauses.WhereClauses") . but are there any solution better of that?

Update:
WhereClause is part of Report Table and can't load whole WhereClause for every report query:

public class Report:ModelAbstract
{
    [Key]
    public int Id { get; set; }

    [Required]
    [StringLength(100)]
    public string Name { get; set; }

    [Required]
    public Table Table { get; set; }

    public List<SelectClause> SelectClauses { get; set; }

    [ForeignKey("WhereClause")]
    public int? WhereClauseId { get; set; }

    public WhereClause WhereClause { get; set; }
}
Micle
  • 159
  • 10
  • 1
    Maybe it would be possible to get all Navigationproperies via reflection. But i do not think thats a good approach. Does it a to be a single query? Based on the depth of the model structure the performance could be horrible. Maybe this is points you in a direction ? https://habr.com/en/post/516596/ – jeb Feb 20 '23 at 08:28
  • 1
    If you need whole table it is easy. `context.WhereClause.ToList()` - EF Core will correct all navigation properties. If you need it from the specific root - Recursive CTE is needed. – Svyatoslav Danyliv Feb 20 '23 at 08:42
  • Could you provide full table schema? From this point of view it looks almost like it was easier to query for whole WhereClauses table and manually build the data structure. – Rusmirnator Feb 20 '23 at 08:49
  • Thank you @jeb. I think `TreeExtensions` need more time to load all level. – Micle Feb 20 '23 at 08:54
  • Thank you @SvyatoslavDanyliv and @Rusmirnator. I retrieve `WhereClause` from parent node(`Report`). something like: `dbcontext.Reports.Include("WhereClause,WhereClause.WhereClauses").FirstOrDefault(x => x.Id== id) ` – Micle Feb 20 '23 at 08:58

1 Answers1

2

You can use Recursive CTE for such task. CTE will help to return all children objects then we can filter out root. Under hood, EF Core will correct children collections automatically.

var sql = @"
WITH Clauses AS 
(
    SELECT
        Id
    FROM WhereClause
    WHERE Id = {0}
    UNION ALL
    SELECT
        w.Id
    FROM Clauses c
        JOIN WhereClause w ON w.WhereClauseId = c.Id
)
SELECT wc.*
FROM WhereClause wc
JOIN Clauses c ON wc.Id = c.Id";

var whereId = 7;
var whereClause = ctx.WhereClause
    .FromSqlRaw(sql, whereId)
    .ToList()
    .First(w => w.Id == whereId);
Svyatoslav Danyliv
  • 21,911
  • 3
  • 16
  • 32
  • Thank you for answer... I think is is possible with your answer, but I don't want to use raw SQL – Micle Feb 20 '23 at 13:17
  • 1 ) then you can create User-Defined function which returns the same recordset and map to C# function. 2) You can include very long path `dbcontext.WhereClauses.Include("WhereClause.WhereClause.WhereClauses...").` 2 ) you can use [linq2db.EntityFrameworkCore](https://github.com/linq2db/linq2db.EntityFrameworkCore) which supports recursive CTE. As I know there no more options. – Svyatoslav Danyliv Feb 20 '23 at 13:23