One LINQ Extension Method You NEED To Know For Cleaner EF Core Queries

if you're working with ef core then you're probably familiar with writing link queries and in today's video I'm going to show you a useful link extension method that you may want to add to your own project I Define a simple database context with just one entity which is the customer and the customer has an ID a name an email and an age imagine we got a request to implement a feature for fetching all of the customers in the database we can write a simple link query using EF core to fetch all of the customers available in the database to achieve that we just call context customers and we materialize all of the customers into memory by calling to list let's just quickly examine the SQL that EF core is going to send to the database in this case if you take a look at the output window you can see that you have sent a very simple select statement to just fetch all of the columns from the customer's database and this is probably what you already suspected would be the case now a new feature request comes in that requires us to apply a name filter to the customers so that we only fetch the set of customers from the database that match the specified filter so we Define a filter which would be a string and let's give it a name of name filter for example and let's give it some sample value now how we would use this inside of our link query is by appending aware statement and inside of it we want to check if the customer's name contains the name filter value if we take a look at the SQL that EF core generates in this situation and sends to the database we're going to see that it's slightly different than what we had in the previous situation what we have now is an additional wear statement that applies a filter to check if the customer's name contains the name filter parameter that EF has now generated and sent to the database you can see that EF core is also smart enough to check if the name filter is also empty and if it is then it's not going to apply any filtering and just return all of the records from the database this is because EF is applying the or logical operator in between so if the name filter is empty then we get back all of the customers and if it is not then we get back just the customers matching the filter that we specified it's very common that you're going to check if the name filter is not or empty yourself so inside of the where statement you will write something like this if the string is null or empty of name filter if this evaluates to true then we don't want to apply the name contains filter so we add a logical or operator and this is going to Short Circuit in the SQL query and not evaluate this part of the filter if the name filter value is null or empty so now another feature request comes in and apart from the name filter you also need to introduce an email filter so we add another field for the email filter we give it some random value for example email and we need to apply it inside of our query so you can write all of this inside of the where statement but it's very important to wrap your conditions in their own brackets so the TF knows how to translate this properly into SQL so you're going to add a new condition for the email filter so string is null or empty email filter and you write the same logic for filtering by the email so we're going to say customer email contains email filter so we only applied the name filter or the email filter if they are not empty and we concatenate the conditions with a logical and operator so you can imagine the SQL being generated is becoming more and more complex so if we take a look you're going to see that our where statement is now slightly more complicated than before we have the filtering by the name that we had earlier and we also have an and logical operator to concatenate the condition that we need to filter by the email as well from a SQL perspective everything looks fine but our code is becoming a little messy and you can imagine how complicated this becomes if you have a more complex data model or you need to support additional filters on top of the ones that we already have one thing that you can do to make this just a little bit more manageable is to write two where statements now if you write two consecutive where statements with separate filters inside they're going to be concatenated using an end statement which is what we had here in the first place so you would have something like this so in this case we would have the same result in our SQL query like earlier but I would argue that the code is slightly more readable in this format imagine that you had a situation where this isn't just a string filter here but you had maybe a filtering based on some integer or maybe on some other object and your check to see if you should apply your filter or not is going to become very verbose so what you can do is you can create an extension method to only apply filtering if a certain condition is met and now I'm going to show you how to write that extension method and apply it with ef core we're going to write the extension method inside of the variable extension static class and let's see how that will look like I'm going to make it public and static and it's going to return an i variable of t i queryable is an interface coming from EF core which is an abstraction allowing us to Define queries using link statements this is going to be a generic method and I'm going to call it where if it's going to need a generic argument and and because it's an extension method the first argument is going to be an i variable of t we can call it queryable and let's see what's required to implement the where if method as the name implies this is actually a where statement but you only apply it if some condition is met so we're going to need a few more arguments one is going to be the condition now the condition is going to be some Boolean value let's call it bull condition the second argument is going to be an expression that is going to allow us to actually Define the where statement so we're going to Define an expression of a function of T which returns a Boolean I know this is a little complex but expression is what EF core uses under the hood and a function that takes in an object and returns a Boolean is just called a predicate so I'm going to call it predicate and this is what we need to Define our where if method the logic inside is very very simple you're just going to say if the specified condition is met then you return the original queryable and you apply the where statement with the predicate that you just defined if the condition is not meant then you just return the variable unchanged so this is all there is to the where if method what it does in practice is it allows us to apply a predicate to one I queryable instance based on some condition so if the condition is met we apply the filter if it is not met we do not apply the filter let's go back to our query to see how we would use the where if method if you take a look here we are trying to apply a similar idea it's just slightly more complicated because we are using a logical operator to connect the two values so let's replace this with a call to where if so when do we want to apply the name filter we want to apply the name filter when the name filter value is not empty so we can define a Boolean value here and we can just negate the call to is null or empty and we know that is when we need to apply the name filter and after that we just defined the expression for applying the actual filter on the name let's do the same for our email filter value so I'm going to say where if let's copy this check if the email filter is null or empty and we're going to negate the call to this function and here we're just going to specify a predicate for filtering by the email so let's see how this query behaves at runtime I'm going to start debugging and I'm going to press continue here and notice that the name and the email filters are both empty so I'm going to hit the breakpoint inside of the where if method which I just defined and if we check the condition here you can see that it's false in this case we are not going to apply the predicate which checks that the customer name contains some name filter value and we're just going to return the original variable unchanged I'm going to press continue and we hit the where if method again this time for the predicate where we check that the email contains the actual value and again you can see that the condition is false and we skip applying the predicate so again we return the unchanged variable and the result is a slightly different query that is going to be sent to the database so if you take a look at the output window here the query that EF has generated is just a simple select statement because we have skipped applying the predicates using our where if extension method let's write the same query again only this time the name and email filters are going to have a defined value and if you take a look at the SQL that is generated you can see that we properly applied the filter by the name and the filter by the email for the customer let's also do one more example where we have an age filter defined and let's give it a value of -1 for example and this time we only want to apply the age filter if it is greater than zero so we can append another call to where if and I said that we only want to filter if the age is greater than zero so we say age greater than zero and in that case let's define a predicate that checks if the customer's age is greater than or equal to the specified age filter so you can see how easy it is to introduce additional conditions using the where if extension method if you enjoyed this video make sure to smash that like button and also subscribe to my channel so that you don't miss any of my future videos until next time stay awesome

Channel: Milan Jovanović

Views: 17,340

Keywords: ef core, ef core 7, ef core tutorial, ef core 6, ef core database first approach, ef core code first approach, ef core migrations, ef core many to many, ef core repository pattern, ef core code first, ef core linq, ef core linq query, ef core queries, ef core linq expression, ef core where statement, ef core filter, ef core performance, ef core query optimization, ef core linq performance, ef core linq extension method, ef core linq optimization, .net, c#, dotnet

Id: YYgRdMW9Omo

Length: 11min 7sec (667 seconds)

Published: Fri Feb 17 2023