Clean Up Your EF Core Queries With Specification Pattern

In today's video, I'm going to show you In today's video, I'm going to show you how you can implement the specification how you can implement the specification how you can implement the specification pattern to encapsulate your EF Core pattern to encapsulate your EF Core pattern to encapsulate your EF Core queries as well as your business logic queries as well as your business logic queries as well as your business logic checks. We're going to talk about the checks. We're going to talk about the checks. We're going to talk about the pros and cons of this pattern, which pros and cons of this pattern, which pros and cons of this pattern, which there are many, and I'm going to show there are many, and I'm going to show there are many, and I'm going to show you a more pragmatic solution at the you a more pragmatic solution at the you a more pragmatic solution at the end. So, let's dive in. So, this is end. So, let's dive in. So, this is end. So, let's dive in. So, this is going to be our starting point, and I going to be our starting point, and I going to be our starting point, and I used a very simple example on purpose used a very simple example on purpose used a very simple example on purpose because I want to focus on the essence because I want to focus on the essence because I want to focus on the essence of the specification pattern, and this of the specification pattern, and this of the specification pattern, and this is what I came up with. So, I've got is what I came up with. So, I've got is what I came up with. So, I've got this promotion service that encapsulates this promotion service that encapsulates this promotion service that encapsulates very simple business rule. It checks if very simple business rule. It checks if very simple business rule. It checks if the customer is eligible for a the customer is eligible for a the customer is eligible for a promotion. Now, this just consists of a promotion. Now, this just consists of a promotion. Now, this just consists of a couple of if statements that we chain couple of if statements that we chain couple of if statements that we chain together. We're using a fail-fast together. We're using a fail-fast together. We're using a fail-fast approach where if any of the if approach where if any of the if approach where if any of the if statements gives us a negative result, statements gives us a negative result, statements gives us a negative result, we return false, and if all of them we return false, and if all of them we return false, and if all of them pass, we return true. So, this is a pass, we return true. So, this is a pass, we return true. So, this is a simple case of some business rule check simple case of some business rule check simple case of some business rule check inside of a typical application. Now, inside of a typical application. Now, inside of a typical application. Now, what happens is you often end up what happens is you often end up what happens is you often end up duplicating the same rule inside of your duplicating the same rule inside of your duplicating the same rule inside of your database queries. So, let's say we want database queries. So, let's say we want database queries. So, let's say we want to fetch eligible customers from the to fetch eligible customers from the to fetch eligible customers from the database, we'd write the same rule database, we'd write the same rule database, we'd write the same rule differently inside of our link query.

differently inside of our link query. differently inside of our link query. And one of the popular ways in And one of the popular ways in And one of the popular ways in object-oriented programming for how we object-oriented programming for how we object-oriented programming for how we can solve this is using the can solve this is using the can solve this is using the specification pattern. So, let me show specification pattern. So, let me show specification pattern. So, let me show you how we could introduce this. I'll you how we could introduce this. I'll you how we could introduce this. I'll add a new class inside of our solution, add a new class inside of our solution, add a new class inside of our solution, and I'm going to call this the and I'm going to call this the and I'm going to call this the specification. As this is our base class specification. As this is our base class specification. As this is our base class that's going to be used to implement that's going to be used to implement that's going to be used to implement concrete specifications, I'm going to concrete specifications, I'm going to concrete specifications, I'm going to make it abstract and generic. And inside make it abstract and generic. And inside make it abstract and generic. And inside of this, I want one abstract method, of this, I want one abstract method, of this, I want one abstract method, which is going to return an expression which is going to return an expression which is going to return an expression of type T, and this is actually going to of type T, and this is actually going to of type T, and this is actually going to represent our business rule. And here, represent our business rule. And here, represent our business rule. And here, we are going to return an expression. we are going to return an expression. we are going to return an expression. Now, this is a type that can encapsulate Now, this is a type that can encapsulate Now, this is a type that can encapsulate a lambda statement, and we can use it to a lambda statement, and we can use it to a lambda statement, and we can use it to execute a function in memory as well as execute a function in memory as well as execute a function in memory as well as pass it to EF Core. We're kind of trying pass it to EF Core. We're kind of trying pass it to EF Core. We're kind of trying to solve both problems with the same to solve both problems with the same to solve both problems with the same implementation. Now, we're going to implementation. Now, we're going to implementation. Now, we're going to return a function that takes in an return a function that takes in an return a function that takes in an object of type T and returns a boolean object of type T and returns a boolean object of type T and returns a boolean value. So, we're just using it to value. So, we're just using it to value. So, we're just using it to implement a predicate and we're going to implement a predicate and we're going to implement a predicate and we're going to call this to expression. Another helper call this to expression. Another helper call this to expression. Another helper method that I will add here is going to method that I will add here is going to method that I will add here is going to return a boolean right away and we're return a boolean right away and we're return a boolean right away and we're going to call this is satisfied by and going to call this is satisfied by and going to call this is satisfied by and this will just allow us to evaluate if this will just allow us to evaluate if this will just allow us to evaluate if the specification is satisfied for a the specification is satisfied for a the specification is satisfied for a given candidate object. So, what we want given candidate object. So, what we want given candidate object. So, what we want to do is derive our predicate. And how to do is derive our predicate. And how to do is derive our predicate. And how you get to one from an expression is by you get to one from an expression is by you get to one from an expression is by calling to expression and then compile calling to expression and then compile calling to expression and then compile and this gives us back the raw predicate and this gives us back the raw predicate and this gives us back the raw predicate and then we can call our predicate with and then we can call our predicate with and then we can call our predicate with the candidate object and get back our the candidate object and get back our the candidate object and get back our boolean value. So, we've got this

boolean value. So, we've got this boolean value. So, we've got this specification class. Now, what's the specification class. Now, what's the specification class. Now, what's the point of it? Well, I'm going to add a point of it? Well, I'm going to add a point of it? Well, I'm going to add a new type that's just going to be a new type that's just going to be a new type that's just going to be a wrapper for all of my concrete wrapper for all of my concrete wrapper for all of my concrete specifications and inside of here we can specifications and inside of here we can specifications and inside of here we can implement our first specification and implement our first specification and implement our first specification and what we want to do is encapsulate either what we want to do is encapsulate either what we want to do is encapsulate either all of these conditions together or all of these conditions together or all of these conditions together or encapsulate them one by one. So, if we encapsulate them one by one. So, if we encapsulate them one by one. So, if we encapsulate them one by one, we gain the encapsulate them one by one, we gain the encapsulate them one by one, we gain the option of composing them as needed at option of composing them as needed at option of composing them as needed at runtime. So, we can check for active runtime. So, we can check for active runtime. So, we can check for active customers where the registered date is customers where the registered date is customers where the registered date is less than 40 days in the past or active less than 40 days in the past or active less than 40 days in the past or active customers who have spent less than 500 customers who have spent less than 500 customers who have spent less than 500 for their orders. So, this approach for their orders. So, this approach for their orders. So, this approach gives us a bit more flexibility. So, gives us a bit more flexibility. So, gives us a bit more flexibility. So, let's say we want to take that route and let's say we want to take that route and let's say we want to take that route and we're going to create a type here that I we're going to create a type here that I we're going to create a type here that I will call the active customer will call the active customer will call the active customer specification. So, we implement our base specification. So, we implement our base specification. So, we implement our base specification type and we use the specification type and we use the specification type and we use the customer as our generic argument and customer as our generic argument and customer as our generic argument and we've got this method we need to we've got this method we need to we've got this method we need to implement called to expression. So, what implement called to expression. So, what implement called to expression. So, what we can do here is just write a lambda we can do here is just write a lambda we can do here is just write a lambda expression that checks if the customer expression that checks if the customer expression that checks if the customer is active. So, now I'm going to repeat is active. So, now I'm going to repeat is active. So, now I'm going to repeat the process for the remaining the process for the remaining the process for the remaining specifications and here I've got a specifications and here I've got a specifications and here I've got a registered before specification where we registered before specification where we registered before specification where we can now provide our datetime cutoff as can now provide our datetime cutoff as can now provide our datetime cutoff as an argument and use that inside of the an argument and use that inside of the an argument and use that inside of the expression. So, we no longer have to expression. So, we no longer have to expression. So, we no longer have to hardcode the 30 days check. Instead, we hardcode the 30 days check. Instead, we hardcode the 30 days check. Instead, we can pass in the datetime object of our can pass in the datetime object of our can pass in the datetime object of our choice, and that decides when the cutoff choice, and that decides when the cutoff choice, and that decides when the cutoff is. Our next two checks are the total is. Our next two checks are the total is. Our next two checks are the total spend and the eligible countries. So, spend and the eligible countries. So, spend and the eligible countries. So, I'm going to add a minimum spend I'm going to add a minimum spend I'm going to add a minimum spend specification, and then the allowed specification, and then the allowed specification, and then the allowed country specification. So, all of these country specification. So, all of these country specification. So, all of these follow the same idea where we've got our

follow the same idea where we've got our follow the same idea where we've got our specification object encapsulating the specification object encapsulating the specification object encapsulating the lambda expression that we would run for lambda expression that we would run for lambda expression that we would run for this check, and we gain the option to this check, and we gain the option to this check, and we gain the option to parameterize this as needed when we parameterize this as needed when we parameterize this as needed when we create the specification instance. Now, create the specification instance. Now, create the specification instance. Now, it's important to note that all of our it's important to note that all of our it's important to note that all of our specification instances create a closure specification instances create a closure specification instances create a closure around our parameters at the time of around our parameters at the time of around our parameters at the time of creation. So, keep this in mind if you creation. So, keep this in mind if you creation. So, keep this in mind if you want to reuse the specification instance want to reuse the specification instance want to reuse the specification instance for multiple queries. Okay, so we've got for multiple queries. Okay, so we've got for multiple queries. Okay, so we've got our base specification type. We've got our base specification type. We've got our base specification type. We've got our concrete specifications. So, how do our concrete specifications. So, how do our concrete specifications. So, how do we actually use them to simplify our we actually use them to simplify our we actually use them to simplify our promotion service as well as our EF Core promotion service as well as our EF Core promotion service as well as our EF Core Query. I mentioned that we actually want Query. I mentioned that we actually want Query. I mentioned that we actually want to combine our individual to combine our individual to combine our individual specifications. So, we need a way to be specifications. So, we need a way to be specifications. So, we need a way to be able to compose them. And to save us a able to compose them. And to save us a able to compose them. And to save us a lot of time and headaches of writing lot of time and headaches of writing lot of time and headaches of writing this from scratch, I'm going to drop in this from scratch, I'm going to drop in this from scratch, I'm going to drop in the code that's going to allow us to the code that's going to allow us to the code that's going to allow us to combine our specifications. And as you combine our specifications. And as you combine our specifications. And as you might imagine, this is just another might imagine, this is just another might imagine, this is just another implementation of our base specification implementation of our base specification implementation of our base specification type. But the difference here is that we type. But the difference here is that we type. But the difference here is that we can pass in two specification instances, can pass in two specification instances, can pass in two specification instances, a left and a right instance, and we want a left and a right instance, and we want a left and a right instance, and we want to apply the logical and operator to apply the logical and operator to apply the logical and operator between them. Now, expression types give between them. Now, expression types give between them. Now, expression types give us an and also method that we can use to us an and also method that we can use to us an and also method that we can use to achieve this. Of course, we have to do achieve this. Of course, we have to do achieve this. Of course, we have to do some visitor magic to make sure that we some visitor magic to make sure that we some visitor magic to make sure that we use the same parameter between these.

use the same parameter between these. use the same parameter between these. And in the same spirit, we can implement And in the same spirit, we can implement And in the same spirit, we can implement an or specification that uses or else to an or specification that uses or else to an or specification that uses or else to implement the logical or operator. We implement the logical or operator. We implement the logical or operator. We can also implement negation of our can also implement negation of our can also implement negation of our specification by using expression not. specification by using expression not. specification by using expression not. And then here's the visitor that ties And then here's the visitor that ties And then here's the visitor that ties everything together. So, with these everything together. So, with these everything together. So, with these three glue pieces in place, we can add a three glue pieces in place, we can add a three glue pieces in place, we can add a set of helper methods on our base set of helper methods on our base set of helper methods on our base specification type that's going to allow specification type that's going to allow specification type that's going to allow us to combine these. So, let's say I us to combine these. So, let's say I us to combine these. So, let's say I have a method called and that takes in have a method called and that takes in have a method called and that takes in the other specification that I want to the other specification that I want to the other specification that I want to combine it with. And what I do is create combine it with. And what I do is create combine it with. And what I do is create a new and specification instance, pass a new and specification instance, pass a new and specification instance, pass in the current object and the other in the current object and the other in the current object and the other object I want to combine it with. I can object I want to combine it with. I can object I want to combine it with. I can do the same thing for the or operator. do the same thing for the or operator. do the same thing for the or operator. So, I'm just going to call this method So, I'm just going to call this method So, I'm just going to call this method or and we're going to create a new or or and we're going to create a new or or and we're going to create a new or specification. And then for the specification. And then for the specification. And then for the negation, I can say not. We're going to negation, I can say not. We're going to negation, I can say not. We're going to create a new not specification. And create a new not specification. And create a new not specification. And because this is a unary operator, we're because this is a unary operator, we're because this is a unary operator, we're just going to pass in the current just going to pass in the current just going to pass in the current instance. So, we wrote all of this code, instance. So, we wrote all of this code, instance. So, we wrote all of this code, but where's the payoff? Well, let's go but where's the payoff? Well, let's go but where's the payoff? Well, let's go to our program file and we're going to to our program file and we're going to to our program file and we're going to write our specification here. So, let me write our specification here. So, let me write our specification here. So, let me add a comment to denote our new section.

add a comment to denote our new section. add a comment to denote our new section. And let's create an instance of our And let's create an instance of our And let's create an instance of our promotion specification. We're going to promotion specification. We're going to promotion specification. We're going to start by creating our first start by creating our first start by creating our first specification instance, which is the specification instance, which is the specification instance, which is the active customer specification. And then active customer specification. And then active customer specification. And then we want to keep chaining the conditions we want to keep chaining the conditions we want to keep chaining the conditions that we want to be part of the same that we want to be part of the same that we want to be part of the same specification. So, we chain our next specification. So, we chain our next specification. So, we chain our next check, which is the registered before check, which is the registered before check, which is the registered before specification. And this has a cutoff specification. And this has a cutoff specification. And this has a cutoff date. So, let's add this as datetime date. So, let's add this as datetime date. So, let's add this as datetime UTC. Now, we're going to say add days UTC. Now, we're going to say add days UTC. Now, we're going to say add days minus 30 and we're going to pass this to minus 30 and we're going to pass this to minus 30 and we're going to pass this to our cutoff instance. Then let's chain our cutoff instance. Then let's chain our cutoff instance. Then let's chain our next specification, which is the our next specification, which is the our next specification, which is the minimum spend specification. And in this minimum spend specification. And in this minimum spend specification. And in this case, we're going to pass in 500 as the case, we're going to pass in 500 as the case, we're going to pass in 500 as the value. And lastly, we're going to pass value. And lastly, we're going to pass value. And lastly, we're going to pass in a new allowed country specification. in a new allowed country specification. in a new allowed country specification. And we can pass in the codes for the And we can pass in the codes for the And we can pass in the codes for the allowed countries. So, before showing allowed countries. So, before showing allowed countries. So, before showing you how we can use this, let me just you how we can use this, let me just you how we can use this, let me just first run the original example. I'll first run the original example. I'll first run the original example. I'll place a breakpoint here and I just want place a breakpoint here and I just want place a breakpoint here and I just want us to look at at the generated SQL that us to look at at the generated SQL that us to look at at the generated SQL that EF Core sends to the database. So, if I EF Core sends to the database. So, if I EF Core sends to the database. So, if I zoom in here from the Aspire dashboard, zoom in here from the Aspire dashboard, zoom in here from the Aspire dashboard, here is the statement sent generated by here is the statement sent generated by here is the statement sent generated by EF Core that contains our is active EF Core that contains our is active EF Core that contains our is active check, checking if the date registered check, checking if the date registered check, checking if the date registered has the correct cutoff, if the total has the correct cutoff, if the total has the correct cutoff, if the total spend is greater than a certain value, spend is greater than a certain value, spend is greater than a certain value, and if the countries are in the list of and if the countries are in the list of and if the countries are in the list of the allowed countries. So, this is going the allowed countries. So, this is going the allowed countries. So, this is going to be our baseline, and we want our to be our baseline, and we want our to be our baseline, and we want our specification to also give us the same specification to also give us the same specification to also give us the same value. So, going back to our example, I value. So, going back to our example, I value. So, going back to our example, I want to replace the promotion service want to replace the promotion service want to replace the promotion service with a call of our specification, and with a call of our specification, and with a call of our specification, and we've got this is satisfied by method is we've got this is satisfied by method is we've got this is satisfied by method is going to implement the same logic. What

going to implement the same logic. What going to implement the same logic. What about our EF Core query? Well, this about our EF Core query? Well, this about our EF Core query? Well, this lambda here is effectively an lambda here is effectively an lambda here is effectively an expression, and we can just take all of expression, and we can just take all of expression, and we can just take all of this, take our specification, and call this, take our specification, and call this, take our specification, and call to expression, and this should give us to expression, and this should give us to expression, and this should give us the same behavior. Let's actually make the same behavior. Let's actually make the same behavior. Let's actually make sure that this is the case by looking at sure that this is the case by looking at sure that this is the case by looking at the SQL that's produced, and here's the the SQL that's produced, and here's the the SQL that's produced, and here's the same statement as before. However, it's same statement as before. However, it's same statement as before. However, it's not really the same, and in fact, it's not really the same, and in fact, it's not really the same, and in fact, it's improved because our new version that's improved because our new version that's improved because our new version that's using the specification pattern is now using the specification pattern is now using the specification pattern is now parameterized. Notice that the check for parameterized. Notice that the check for parameterized. Notice that the check for when the customer was registered uses a when the customer was registered uses a when the customer was registered uses a parameter, as well as the total spend, parameter, as well as the total spend, parameter, as well as the total spend, as well as the allowed countries, and as well as the allowed countries, and as well as the allowed countries, and this is first of all safer, and this is first of all safer, and this is first of all safer, and secondly, databases can reuse query secondly, databases can reuse query secondly, databases can reuse query plans produced with proper plans produced with proper plans produced with proper parameterization, which could give us a parameterization, which could give us a parameterization, which could give us a performance improvement. One more performance improvement. One more performance improvement. One more quality of life improvement that you can quality of life improvement that you can quality of life improvement that you can make here is to define an implicit make here is to define an implicit make here is to define an implicit operator on our specification. So, we're operator on our specification. So, we're operator on our specification. So, we're going to say public static implicit going to say public static implicit going to say public static implicit operator, we want to return an operator, we want to return an operator, we want to return an expression of function, expression of function, expression of function, effectively a predicate, and how we can effectively a predicate, and how we can effectively a predicate, and how we can define this is by taking in our base define this is by taking in our base define this is by taking in our base specification, and we just call spec to specification, and we just call spec to specification, and we just call spec to expression. So, the benefit here is you expression. So, the benefit here is you expression. So, the benefit here is you can omit this to expression call, and can omit this to expression call, and can omit this to expression call, and just pass in the raw specification to just pass in the raw specification to just pass in the raw specification to your where segment. Now, let's comment your where segment. Now, let's comment your where segment. Now, let's comment on what we actually got here, and if all on what we actually got here, and if all on what we actually got here, and if all of this was worth it. So, what we had of this was worth it. So, what we had of this was worth it. So, what we had was a service to run the check on was a service to run the check on was a service to run the check on objects in memory, and a LINQ query to objects in memory, and a LINQ query to objects in memory, and a LINQ query to run the same check with EF Core. We run the same check with EF Core. We run the same check with EF Core. We replaced this with a specification type, replaced this with a specification type, replaced this with a specification type, a bunch more types to be able to combine a bunch more types to be able to combine a bunch more types to be able to combine these, and then individual specification

these, and then individual specification these, and then individual specification for the checks that we want to enforce. for the checks that we want to enforce. for the checks that we want to enforce. And overall, the payoff isn't all that And overall, the payoff isn't all that And overall, the payoff isn't all that great if you've got very fixed business great if you've got very fixed business great if you've got very fixed business rules like what we had here. If you need rules like what we had here. If you need rules like what we had here. If you need to produce dynamic business rules at to produce dynamic business rules at to produce dynamic business rules at runtime from your application, then the runtime from your application, then the runtime from your application, then the specification pattern is actually worth specification pattern is actually worth specification pattern is actually worth it, but I don't recommend implementing it, but I don't recommend implementing it, but I don't recommend implementing it yourself. Instead, there's a great it yourself. Instead, there's a great it yourself. Instead, there's a great library from Steve Smith or Ardalis library from Steve Smith or Ardalis library from Steve Smith or Ardalis called specification. You can get it on called specification. You can get it on called specification. You can get it on NuGet by installing the Ardalis NuGet by installing the Ardalis NuGet by installing the Ardalis specification library, and this is going specification library, and this is going specification library, and this is going to cover most of what I showed you in to cover most of what I showed you in to cover most of what I showed you in this video with a lot better this video with a lot better this video with a lot better implementation and support for many more implementation and support for many more implementation and support for many more advanced features. And then, I promised advanced features. And then, I promised advanced features. And then, I promised you a pragmatic approach that also you a pragmatic approach that also you a pragmatic approach that also solves this. So, what you can do is solves this. So, what you can do is solves this. So, what you can do is create a simple class, make it static, create a simple class, make it static, create a simple class, make it static, and let this encapsulate your actual and let this encapsulate your actual and let this encapsulate your actual business rules. So, what we do is make business rules. So, what we do is make business rules. So, what we do is make this an extension of IQueryable of our this an extension of IQueryable of our this an extension of IQueryable of our type that we want to check, and then I type that we want to check, and then I type that we want to check, and then I can say DB.Customers and call eligible can say DB.Customers and call eligible can say DB.Customers and call eligible for promotion, pass in the cut off date, for promotion, pass in the cut off date, for promotion, pass in the cut off date, and I effectively get the same thing and I effectively get the same thing and I effectively get the same thing without having to write a lot of without having to write a lot of without having to write a lot of additional classes and maintain so much additional classes and maintain so much additional classes and maintain so much extra code. And if I also want to reuse extra code. And if I also want to reuse extra code. And if I also want to reuse this for my objects, I can make this this for my objects, I can make this this for my objects, I can make this return an expression and be able to use return an expression and be able to use return an expression and be able to use it with objects in memory like I showed it with objects in memory like I showed it with objects in memory like I showed you here. If you want to grab the source you here. If you want to grab the source you here. If you want to grab the source code for this video, it's going to be code for this video, it's going to be code for this video, it's going to be available from the pinned comment right available from the pinned comment right available from the pinned comment right below. Also, let me know what you below. Also, let me know what you below. Also, let me know what you actually think about the specification actually think about the specification actually think about the specification pattern and if you think it's worth all pattern and if you think it's worth all pattern and if you think it's worth all the extra code that you have to write.

the extra code that you have to write. the extra code that you have to write. If you enjoyed this video, consider If you enjoyed this video, consider If you enjoyed this video, consider smashing the like button. Thanks a lot smashing the like button. Thanks a lot smashing the like button. Thanks a lot for watching, and until next time, stay for watching, and until next time, stay for watching, and until next time, stay awesome.