Using Specification on related collections as subquery that's database safe

[ajc-uconn]

To give a little bit of context, I'm utilizing Entity Framework (EF) and am using this library and am having some trouble with the expressions not being properly translated into SQL when working with nested related objects and their collections and wonder if you knew how to resolve or if it's a known issue. In the scenario outlined below, EF throws: System.InvalidOperationException : Internal .NET Framework Data Provider error 1025.

Let's say I had the following entity classes:

class Student {
     public Employee Employee {get; set; }
}

class Employee {
     public string Name {get; set;}
     public ICollection<Position> Positions {get; set;}
}

class Position {
      public DateTime HireDate {get;set;}
}

And let's say I wanted to write a spec that gets passed to EF that gets students that are employees that have positions after a certain hire date. The following doesn't translate or work because it's nested:

class StudentEmployeeHiredAfter2000: ASpec<Student> {
     private readonly PositionHiredAfter2000 positionHiredAfter2000spec;

     public WasHiredAfter2000() {
          positionHiredAfter2000spec= new PositionHiredAfter2000();
     }

     public override Expression<Func<Student, bool>> Expression => s => s.Employee != null && s.Employee.Positions.Any(positionHiredAfter2000spec)
}

class PositionHiredAfter2000 : ASpec<Position> {
     private readonly DateTime dateToCheck;

     public WasHiredAfter2000() {
          dateToCheck = new DateTime(2000, 12, 31);
     }

     public override Expression<Func<Position, bool>> Expression => p => p.HireDate > dateToCheck;
}

and run dbContext.Students.Where(new StudentEmployeeHiredAfter2000()).ToList();

Is there any way to re-use the specifications nested inside of another specification on a nested related collection like above?

[nickpreston24]

You're never going to get Expressions properly translated to SQL because it's impossible for EF to consistently and predictably write good, predictable SQL. Why? Because the OOP model is completely different beast next to the tabular-ledger model that is SQL tables.

SQL is already a terrible monster to conquer and trying to force OO languages to resemble SQL AND hold oft-mutated state at the same time is a stupid idea too many coders subscribe to. Unfortunately, the industry is burning millions of :dollar: trying to force the two to make nice.

That said, your best bet for mitigating EF-hell is to not use EF. Some alternatives you should be looking into are:

1. https://github.com/jonwagner/Insight.Database - Solves a great many problems by using your server, dtos and interfaces as the SSOT, instead of forcing you to write SQL twice....(once in the db and once in the "rules", a.k.a DbSet Expression Hell).
2. Dapper.
3. Learning HTMX and figuring out how to exploit ADO.NET's DataSet XML generation so that you don't have to map :hankey:, but rather focus on delivering features, instead of "ooops, I screwed up another magic string or made another N+1! / Why is my JSON API so damn slow?".

You can use the NSpecification library freely with 1. and 2. With 3, you might not need it, because Microsoft already gives you back XML in a DataSet... feel free to explore that.

Or, just stop being a masochist/NPC altogether and use HTMX, Blazor/Razor and Neo4j as your new stack:

HTMX for .NET DEvs

Graph Databases Will Change Your Freakin' Life (Best Intro Into Graph Databases)

[miholler]

@ajc-uconn I hope this is still relevant to you. A bit of explanation is needed to fully understand the problem here. Every specification can be implicitly converted to either a predicate expression (Expression<Func<T, bool>>) or a predicate delegate (Func<T, bool>). If you use a spec directly within your EF or EF Core query, it is implicitly converted to a predicate expression, because the Queryable LINQ methods work with expressions instead of delegates. The Queryable Any method for example is defined as bool Any<T>(this IQueryable<T> source, Expression<Func<T, bool>> predicate), so any spec passed into this method will be implicitly converted to a predicate expression. The problem in your specific case lies in the definition of the 'StudentEmployeeHiredAfter2000' spec. Within its definition an instance of the 'PositionHiredAfter2000' is passed into the LINQ Any method call on the 'Positions' child collection of the 'Employee' entity: s => s.Employee != null && s.Employee.Positions.Any(positionHiredAfter2000spec). Since 'Positions' is defined as ICollection<T> on the 'Employee' entity (which is correct by the way), the Enumerable Any method bool Any<T>(this IEnumerable<T> source, Func<T, bool> predicate) is used instead of the Queryable Any method which causes your 'PositionHiredAfter2000' spec to be converted into a predicate delegate instead of a predicate expression and this results in EF not being able to translate the query. To fix this, you just need to call .AsQueryable() on your 'Positions' child collection, so that your inner spec is also converted into an expression within the expression tree of your parent spec: public override Expression<Func<Student, bool>> Expression => s => s.Employee != null && s.Employee.Positions.AsQueryable().Any(positionHiredAfter2000spec)