0

I'm trying to build dependency graph by grabbing dependencies from EntityFramework. I would like my query to be as efficient as possible, so I would prefer not to pull all of the data.

I have a structure which self references multiple times like so:

public class PTOPlan
{
    public int Id { get; set; }    
    public string Name { get; set; }
    public decimal Allowed { get; set; }
    public decimal Taken { get; set; }
    public PTOPlan DependentPTO { get; set; }
    public PTOPlan RolloverPlan { get; set; }
    public int RolloverPlanId { get; set; }
    public int DependentPTO { get; set; }
    public List<PTOPlanRule> PTOPlanRules { get; set; }

}

Later on we introduced a more granular way of creating rules, but for legacy purposes we left the old structure in place

public class PTOPlanRule
{
    public int Id { get; set; }
    public int DepartmentId { get; set; }
    public string Name { get; set; }
    public decimal Allowed { get; set; }
    public decimal Taken { get; set; }
    public PTOPlan ParentPTOPlan{ get; set; }
    public PTOPlan DependentPTO { get; set; }
    public PTOPlan RolloverPlan { get; set; }
    public int ParentPTOPlanId { get; set; }
    public int RolloverPlanId { get; set; }
    public int DependentPTO { get; set; }
}

I need to build a graph of the dependencies. But Some places have a lot of plans and I would prefer to not have to pull the dataset into memory.

I would like the includes to be applied recursively to the query, so they pull all the dependencies. e.g I would like to Include a dependency while the Id exists

public Dictionary<int, List<int>> GetPTOPlanDependencyGraph(List<int> ptoPlanIds)
{
    var rawPlans = context.PTOPlans.Where(x => ptoPlanIds.Contains(x.Id))
        .IncludeWhile(x => x.DependentPTOPlan, x.DependentPTOPlan != null)
        .IncludeWhile(x => x.RolloverPlan, x.RolloverPlan != null)
        .IncludeWhile(x => x.PTOPlanRules.SelectMany(x => x.DependentPTOPlan), x => x.PTOPlanRules.Any())
    
     return this.BuildDependencyGraph(rawPlans);
}

Is there a way to recursively include data, or a way to pull my data without having to grab extra plans?

johnny 5
  • 19,893
  • 50
  • 121
  • 195
  • 2
    You'll probably have to write actual SQL for this, to take advantage of SQL Server's ability to do recursive CTEs. https://learn.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-ver15#guidelines-for-defining-and-using-recursive-common-table-expressions I've yet to see any ORM capable of doing that without going down to raw SQL. – Joel Coehoorn Dec 09 '20 at 14:51
  • 2
    (continued) This is why I'm not a fan of full-featured ORMs. You need to learn the intricacies of the ORM, but ultimately it doesn't save you from needing SQL, too, and at those times you need to _really know_ SQL. Once you really know SQL well like this, it's now the easier way to handle a lot of the ORM's advanced features. Result: "light" ORMs like Dapper that only do simple mapping and connection plumbing end up as the more optimal tool in most cases. – Joel Coehoorn Dec 09 '20 at 14:54
  • 1
    `Include` is an addition that determines the generated SQL query, so it's a one-time operation. It can't be added conditionally based on content, i.e. while the query is running. This requires recursive code and/or lazy loading. There are many questions in this area and the answer is never really satisfactory. – Gert Arnold Dec 09 '20 at 14:58
  • Thanks, so just to clarify, as of right now I can't recursively pull. Without perhaps writing a custom visitor to fix the expression-tree manually? – johnny 5 Dec 09 '20 at 15:03

0 Answers0