Stop Duplicating Your EF Core Queries
57sOpens with a common coding pain point—duplicating business logic in memory and SQL—that immediately hooks developers.
▶ Play ClipAI Summary
This video explains how the specification pattern can encapsulate EF Core queries and business logic checks in a single reusable definition. The author walks through building a base Specification<T> class, creating concrete specifications, and composing them with And/Or/Not helpers. He also compares the generated SQL, discusses trade-offs, and presents a simpler pragmatic alternative for most applications.
Chapters
[00:01]
The core problem
Business rules are often duplicated between service-layer if statements and EF Core LINQ queries, causing maintenance issues.
[00:42]
Starting example
A promotion service chains several if statements to check customer eligibility. If any check fails, the service returns false.
[01:11]
Base Specification class
Create an abstract generic Specification<T> class with an abstract method returning Expression<Func<T, bool>>. This expression can run in memory or be translated to SQL.
[02:08]
Helper methods
Add ToExpression() to return the expression, and IsSatisfiedBy(candidate) to compile and evaluate it in memory.
[02:38]
Concrete specifications
Split monolithic checks into small semantically named classes (e.g., ActiveCustomerSpecification) that each implement ToExpression().
[03:45]
Parameterized specifications
Pass parameters like a DateTime cutoff into the constructor, removing hardcoded values and enabling runtime flexibility.
[04:53]
Composing specifications
And/Or/Not helpers combine specifications using AndAlso, OrElse, Expression.Not, and a visitor to unify parameters.
[07:37]
Generated SQL comparison
The original LINQ query hardcodes values. With the specification pattern, the SQL is parameterized, which is safer and enables query plan reuse.
[08:48]
Implicit conversion
Define an implicit operator from Specification<T> to Expression<Func<T, bool>> so you can skip explicit ToExpression() calls.
[09:45]
Honest verdict
The specification pattern is only worth its overhead when business rules must be composed dynamically at runtime. For fixed rules, a static extension method is simpler.
The specification pattern is a powerful but heavy solution that shines when rules need runtime composition and query reuse. For most apps with fixed business rules, a simple static extension method on IQueryable<T> delivers the same benefits with far less code.
Mentioned in this Video
Tutorial Checklist
Study Flashcards (8)
What is the specification pattern used for in the context of EF Core?
easy
Click to reveal answer
What is the specification pattern used for in the context of EF Core?
easy
Click to reveal answer
It encapsulates business rules so the same logic can be reused in both in-memory checks and database queries.
00:01
What is the return type of the abstract method in the base Specification<T> class?
easy
Click to reveal answer
What is the return type of the abstract method in the base Specification<T> class?
easy
Click to reveal answer
Expression<Func<T, bool>>.
01:26
How does IsSatisfiedBy evaluate a specification?
medium
Click to reveal answer
How does IsSatisfiedBy evaluate a specification?
medium
Click to reveal answer
It calls ToExpression().Compile() to get a predicate and invokes it with the candidate object.
02:08
What happens when you pass a parameter into a specification constructor?
medium
Click to reveal answer
What happens when you pass a parameter into a specification constructor?
medium
Click to reveal answer
The specification creates a closure around the parameter at creation time.
04:11
Why is parameterization beneficial in EF Core queries?
easy
Click to reveal answer
Why is parameterization beneficial in EF Core queries?
easy
Click to reveal answer
It is safer and lets the database reuse query plans.
08:20
Which library does the author recommend for the specification pattern?
medium
Click to reveal answer
Which library does the author recommend for the specification pattern?
medium
Click to reveal answer
Ardalis.Specification by Steve Smith.
09:59
What is the author's pragmatic alternative to the full specification pattern?
medium
Click to reveal answer
What is the author's pragmatic alternative to the full specification pattern?
medium
Click to reveal answer
A static extension method on IQueryable<T> that encapsulates the business rule and can also return an expression.
10:12
When is the specification pattern worth the extra code?
hard
Click to reveal answer
When is the specification pattern worth the extra code?
hard
Click to reveal answer
When business rules need to be composed dynamically at runtime.
09:45
💡 Key Takeaways
💡
Duplicated rules are the root problem
Identifies the specific pain point the pattern solves: business logic scattered across services and queries.
00:58
📊
Specifications capture parameters via closures
Important caveat for reusing specification instances across queries; parameters are frozen at creation time.
04:11
🔧
Specification queries are automatically parameterized
Demonstrates an unexpected benefit beyond code reuse: safer, optimizable SQL.
08:20
⚖️
Pattern pays off mainly for dynamic compositions
Honest trade-off analysis that helps developers avoid over-engineering.
09:45
🔧
Simpler alternative: static extension methods
Offers a lighter solution for most fixed-rule scenarios, making the pattern optional rather than mandatory.
10:12Full Transcript
[00:01] how you can implement the specification pattern to encapsulate your EF Core queries as well as your business logic checks. We're going to talk about the pros and cons of this pattern, which there are many, and I'm going to show
[00:14] you a more pragmatic solution at the end. So, let's dive in. So, this is going to be our starting point, and I used a very simple example on purpose because I want to focus on the essence of the specification pattern, and this
[00:28] is what I came up with. So, I've got this promotion service that encapsulates the customer is eligible for a promotion. Now, this just consists of a couple of if statements that we chain
[00:42] approach where if any of the if statements gives us a negative result, pass, we return true. So, this is a simple case of some business rule check inside of a typical application. Now, what happens is you often end up
[00:58] duplicating the same rule inside of your database queries. So, let's say we want database, we'd write the same rule differently inside of our link query. And one of the popular ways in
[01:11] can solve this is using the specification pattern. So, let me show you how we could introduce this. I'll add a new class inside of our solution, and I'm going to call this the specification. As this is our base class
[01:26] concrete specifications, I'm going to make it abstract and generic. And inside of this, I want one abstract method, which is going to return an expression of type T, and this is actually going to
[01:41] represent our business rule. And here, we are going to return an expression. Now, this is a type that can encapsulate a lambda statement, and we can use it to execute a function in memory as well as pass it to EF Core. We're kind of trying
[01:53] to solve both problems with the same implementation. Now, we're going to return a function that takes in an object of type T and returns a boolean implement a predicate and we're going to call this to expression. Another helper
[02:08] method that I will add here is going to return a boolean right away and we're going to call this is satisfied by and this will just allow us to evaluate if the specification is satisfied for a given candidate object. So, what we want
[02:23] to do is derive our predicate. And how you get to one from an expression is by calling to expression and then compile and this gives us back the raw predicate and then we can call our predicate with the candidate object and get back our
[02:38] specification class. Now, what's the point of it? Well, I'm going to add a new type that's just going to be a wrapper for all of my concrete specifications and inside of here we can implement our first specification and
[02:51] implement our first specification and what we want to do is encapsulate either all of these conditions together or encapsulate them one by one. So, if we encapsulate them one by one, we gain the option of composing them as needed at
[03:04] runtime. So, we can check for active customers where the registered date is less than 40 days in the past or active customers who have spent less than 500 gives us a bit more flexibility. So, let's say we want to take that route and
[03:18] we're going to create a type here that I will call the active customer specification. So, we implement our base specification type and we use the customer as our generic argument and we've got this method we need to
[03:31] implement called to expression. So, what we can do here is just write a lambda expression that checks if the customer is active. So, now I'm going to repeat specifications and here I've got a registered before specification where we
[03:45] can now provide our datetime cutoff as an argument and use that inside of the expression. So, we no longer have to hardcode the 30 days check. Instead, we can pass in the datetime object of our choice, and that decides when the cutoff
[03:58] is. Our next two checks are the total spend and the eligible countries. So, I'm going to add a minimum spend specification, and then the allowed country specification. So, all of these follow the same idea where we've got our
[04:11] specification object encapsulating the lambda expression that we would run for this check, and we gain the option to parameterize this as needed when we create the specification instance. Now, it's important to note that all of our
[04:25] specification instances create a closure around our parameters at the time of creation. So, keep this in mind if you want to reuse the specification instance for multiple queries. Okay, so we've got our base specification type. We've got
[04:39] our concrete specifications. So, how do we actually use them to simplify our promotion service as well as our EF Core Query. I mentioned that we actually want to combine our individual specifications. So, we need a way to be
[04:53] able to compose them. And to save us a lot of time and headaches of writing this from scratch, I'm going to drop in the code that's going to allow us to combine our specifications. And as you might imagine, this is just another
[05:05] implementation of our base specification type. But the difference here is that we can pass in two specification instances, a left and a right instance, and we want to apply the logical and operator between them. Now, expression types give
[05:20] us an and also method that we can use to achieve this. Of course, we have to do use the same parameter between these. And in the same spirit, we can implement an or specification that uses or else to
[05:33] implement the logical or operator. We can also implement negation of our specification by using expression not. And then here's the visitor that ties everything together. So, with these three glue pieces in place, we can add a
[05:46] set of helper methods on our base specification type that's going to allow us to combine these. So, let's say I have a method called and that takes in the other specification that I want to combine it with. And what I do is create
[06:00] a new and specification instance, pass in the current object and the other object I want to combine it with. I can do the same thing for the or operator. or and we're going to create a new or specification. And then for the
[06:16] negation, I can say not. We're going to create a new not specification. And because this is a unary operator, we're just going to pass in the current instance. So, we wrote all of this code, but where's the payoff? Well, let's go
[06:28] to our program file and we're going to write our specification here. So, let me add a comment to denote our new section. And let's create an instance of our promotion specification. We're going to start by creating our first
[06:43] specification instance, which is the active customer specification. And then we want to keep chaining the conditions that we want to be part of the same specification. So, we chain our next check, which is the registered before
[06:55] specification. And this has a cutoff date. So, let's add this as datetime UTC. Now, we're going to say add days minus 30 and we're going to pass this to our cutoff instance. Then let's chain our next specification, which is the
[07:09] minimum spend specification. And in this case, we're going to pass in 500 as the value. And lastly, we're going to pass in a new allowed country specification. And we can pass in the codes for the allowed countries. So, before showing
[07:22] you how we can use this, let me just first run the original example. I'll place a breakpoint here and I just want us to look at at the generated SQL that EF Core sends to the database. So, if I zoom in here from the Aspire dashboard,
[07:37] here is the statement sent generated by EF Core that contains our is active check, checking if the date registered has the correct cutoff, if the total spend is greater than a certain value, and if the countries are in the list of
[07:51] to be our baseline, and we want our specification to also give us the same value. So, going back to our example, I want to replace the promotion service with a call of our specification, and we've got this is satisfied by method is
[08:06] going to implement the same logic. What about our EF Core query? Well, this lambda here is effectively an expression, and we can just take all of this, take our specification, and call to expression, and this should give us
[08:20] the same behavior. Let's actually make sure that this is the case by looking at the SQL that's produced, and here's the same statement as before. However, it's not really the same, and in fact, it's improved because our new version that's
[08:34] using the specification pattern is now parameterized. Notice that the check for when the customer was registered uses a parameter, as well as the total spend, this is first of all safer, and secondly, databases can reuse query
[08:48] plans produced with proper parameterization, which could give us a quality of life improvement that you can make here is to define an implicit operator on our specification. So, we're going to say public static implicit
[09:03] operator, we want to return an expression of function, effectively a predicate, and how we can define this is by taking in our base specification, and we just call spec to expression. So, the benefit here is you
[09:17] can omit this to expression call, and just pass in the raw specification to on what we actually got here, and if all of this was worth it. So, what we had was a service to run the check on objects in memory, and a LINQ query to
[09:32] run the same check with EF Core. We replaced this with a specification type, a bunch more types to be able to combine these, and then individual specification for the checks that we want to enforce. And overall, the payoff isn't all that
[09:45] great if you've got very fixed business rules like what we had here. If you need to produce dynamic business rules at runtime from your application, then the specification pattern is actually worth it, but I don't recommend implementing
[09:59] it yourself. Instead, there's a great library from Steve Smith or Ardalis called specification. You can get it on NuGet by installing the Ardalis to cover most of what I showed you in this video with a lot better
[10:12] implementation and support for many more advanced features. And then, I promised you a pragmatic approach that also solves this. So, what you can do is create a simple class, make it static, and let this encapsulate your actual
[10:26] business rules. So, what we do is make this an extension of IQueryable of our type that we want to check, and then I can say DB.Customers and call eligible for promotion, pass in the cut off date, and I effectively get the same thing
[10:40] additional classes and maintain so much extra code. And if I also want to reuse this for my objects, I can make this return an expression and be able to use it with objects in memory like I showed you here. If you want to grab the source
[10:53] available from the pinned comment right below. Also, let me know what you actually think about the specification pattern and if you think it's worth all the extra code that you have to write. If you enjoyed this video, consider
[11:06] smashing the like button. Thanks a lot for watching, and until next time, stay for watching, and until next time, stay awesome.
