 On today's Visual Studio Toolbox, Phil's going to show us the power of global query filters in EF Core. Hi, welcome to Visual Studio Toolbox. I'm your host, Robert Green, and joining me today for another in our EF Core in-depth series is Filchipixie. Hey, Phil. Hey, Robert. How are you? I'm doing great. Great. We're going to talk about query filters today. Yeah, we're going to talk about global query filters. One of our viewers had sent a message to us asking about multi-tenancy and how that works. When we talked about configuring the DB context, we talked about how you could plug in different DB context options to get that to change. Something that came in with EF Core, and I'm blanking on the version, it doesn't matter, is the concept of global query filters, where you can set up a query filter on one of your entities that will be used every time that entity is pulled back using EF Core. So whether it's an attached property through a navigation property or whether it's the main target of your query, these filters will always apply. One of the common scenarios that I use in almost every project is most of my customers are in finance or legal or healthcare, all these things where you can't actually delete data, you just hide it away. So we'll have an is-deleted column as part of our base class, like when we showed you in the last episode about building up to base class that everything inherits from. So we've got the ID, we've got a timestamp, but we also have is-deleted, and it's just a Boolean, and so we'll put a query filter on there to say, don't ever show me the ones that are deleted. But for a multi-tenancy perspective, you could say we're going with our Autolot example. So you've got a BMW dealer, you only want to show the inventory that has the make of ID1, which is BMW. But then you can use the same code and you say, okay, but now show me all the Volkswagen's. That can all be done with these global query filters, and they're super simple to set up. So in other words, instead of always having to say where deleted is not true in your individual queries, you can just set that at a global level and then it always applies. Correct. Cool. Yeah. So this is one of my employees, strong SQL Server background. We did a demo for a customer and the customer said, well, what about this? Something they totally forgot to tell us about, which never happens in real life. Right. And I said, well, we'll have it done for tomorrow and we're driving back. And Rick is like, you said tomorrow we're going to be up all night doing this. We have to change all the queries to take into account that we need to hide this inventory. It was for a machine shop. And I said, no, we don't. We got back to the office. I updated all the models to use this global query filter and reran the app and it worked perfectly. We ended up having to change three or four methods because you can also, so the opt-in part of this is, I can say ignore the query filters and bring me everything back. Right. Cool. Let's see that. So let's see how it works. And we're going away from car lot back to the blog samples. Robert, can you see my screen? I can. Okay. So let's look at our simple blog model here. My hat is deleted and then some other random stuff. Right. And here's an example where blog ID will become the primary key. I didn't explicitly tell it. It's an int. So it becomes a sequence. And we talked about that in the last episode. So the way we configure global query filters is quite simply one line of code. So here we're saying our entity blog has a query filter that shows not deleted. So if it is deleted, then it won't show in the queries. So let's look at some code that exercises this. So here I have a real simple setup, creating a blogging contest, context, drop and recreate the database, add a couple of records in, but I'm setting Lee's brand, Lee Brand's blog to delete it. It's not a reflection on Lee at all. It's just, you know, I gotta come up with some sample data somehow. Sorry, Lee, you didn't make the cut. All right, so let's do a blogs.count, which is gonna show how many active blogs we have. Even when we do from SQL, raw or interpolated, it honors the query filter. And then we can say ignore query filters. And what we're gonna see in the output is a one, a one and a two. All right. So we have one active, we have one active, we have two total. Perfect. Now, the caveat, the watch out, if you will, is when you call ignore query filters, it's gonna ignore every query filter on every object in that query. Right. So if I, and this is simple, I'm only querying one thing, but let's say, Well, that is a global query filter you said, right? Well, it's a global query filter on that object. Okay. So it's used globally on that object because we're setting it up in a DB context. If I had four different objects and I wanted to see all the authors, I wanna see all of the posts, I wanna see all of the whatever, but only those blogs that are not deleted. And I say, or I wanna see all the blog, whatever, right? Some combination of, I wanna mod it off. Then I have to do the old school thing of go in and say, ignore query filters and then add my where clause for each of those tables to add the query filters back in. Okay. So for example, if you are doing multi-tenancy and you have a dealer ID and is deleted, and I wanna show all the BMWs that are deleted and I say ignore query filters and I'll have to add back in the where dealer ID equals one or whatever the BMW dealer ID is. Okay. But this is something that we use, like I said, we pretty much use is deleted on every project, add in the query filters and it works like champ. The only time that it has bit me in the rear is when I'm looking in SQL Server Management Studio and I'm doing a query of the data, right? And I see 37 records and I'm debugging Visual Studio and I see 12 records and I'm like, what did I do wrong? And I'm like, oh, I have a query filter turned on. Got it. So it's a shorty but goody. This is a super powerful construct that they add in the EF core and it definitely needs to be part of your toolbox. All right. Very cool. So I hope you enjoyed that one nice and short, a rarity for us and we will see you next time on Visual Studio Toolbox. See you soon.