>> On today's Visual Studio Toolbox,
Phil's going to show us the power of
Global Query Filters in EF Core.
[MUSIC]
>> Hi, welcome to Visual Studio Toolbox.
I'm your host, Robert Green.
Joining me today for another in
our EF Core in-depth series is Phil Japikse. Hey, Phil.
>> Hey, Robert.
>> How are you?
>> I'm doing great.
>> Great. We're going to talk about query filters today.
>> 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 DbContext,
we talked about how you could plug in
different DbContext 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.
So one of the common scenarios
that I use in almost every project is,
most of my customers are in finance,
or legal, or health care.
All of these things where you can't actually delete data,
you just hide it away.
So we'll have an IsDeleted column as part of our base class,
like when we showed you on the last episode about building
up the base class, that everything it inherits from.
So we've got the ID, we've got the timestamp,
but we also have IsDeleted.
It's just a boolean, and so we'll
put a query filter on there that say,
"Don't ever show me the ones that are deleted."
But for a multi-tenancy perspective, you could say,
where going with our auto lot example.
So you've got a BMW dealer.
You only want to show the inventory that has
the make of ID one, which is BMW.
But then you can use the same code and you say, "Okay.
But now show me all the Volkswagens."
That can all be done with [inaudible] 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.
>> So this is one of my employee's,
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.
I said, "We'll have it done for tomorrow."
We were driving back and Rick is like, "You said, tomorrow.
We're going to be up all night doing this.
We have to change all of the queries that take into account,
that we need to hide this inventory."
It was for a machine shop."
I said, "No, we don't." We got back to the office.
I updated all the models to use
this Global Query Filter and
re-ran the app, and it worked perfectly.
We [inaudible] having to change three or four methods.
So the opt-in part of this is,
I can say, "Ignore
the query filters and bring me everything back."
>> Cool. Let's see that.
>> So let's see how it works.
We're going away from car lot,
back to the blog samples.
Robert, can you see my screen?
>> I can.
>> So let's look at our simple blog model here.
I have IsDeleted and then some other random stuff.
Here's an example where BlogId will become the primary key.
I didn't explicitly tell it.
It's an int so it becomes a sequence.
We talked about that in the last episode.
So the way we configure Global Query Filters
is quite simply one line in 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 codes of exercises of this.
So here I have a real simple setup,
creating a blogging context,
drop and recreate the database,
add a couple of records in.
But I'm setting Leebrandt's blog to delete it.
It's not a reflection on Lee at all.
It's just I got to come up with some sample data set.
>> Sorry, Lee. You didn't make the cut.
>> So let's do a Blogs.Count,
which is going to show how many active blogs we have.
Even when we do from SQL raw or interpolated,
it honors the query filter.
Then we can say, IgnoreQueryFilters,
and what we're going to see in the output is a one,
a one, and a two.
So we've one active,
we've one active, we have two total.
>> Perfect.
>> Now, the caveat,
the watch out, if you will,
is when you call IgnoreQueryFilters,
it's going to ignore every query filter
on every object in that query.
This is simple, I'm only querying one thing.
>> Well, it is a Global Query Filter you said, right?
>> Well, it's a Global Query Filter on that object.
So it's used globally on that object
because we're setting it up in a DbContext.
If I had four different objects
and I wanted to see all the authors,
I wanted to see all of the posts,
I want to see all of the whatever,
but only those blogs that are not deleted and I say,
I want to see all the blog, whatever.
Some combination of I want them on and 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.
So for example, if you are doing
multi-tenancy and you have a dealer ID
and IsDeleted and I want to show all the BMWs that are deleted,
and I say, IgnoreQueryFilters,
and I'll have to add back in the where dealer ID
equals one or whatever the BMW dealer ID is.
But this is something that we use, like I said,
we pretty much use IsDeleted 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,
and I see 37 records and I'm
debugging Visual Studio and I see 12 records.
I'm like, "What did I do wrong?"
Then I'm like, "I have a query filter turned on."
>> Got it.
>> So it's a shorty, but goody.
This is a super powerful construct that they added in the EF Core,
and definitely needs to be part of your toolbox.
>> Very cool. So hope you enjoyed that one,
nice and short, a rarity for us.
We'll see you next time on Visual Studio Toolbox.
>> See you soon.
[MUSIC]