On today's Visual Studio Toolbox,
Phil shows us how to use
computed columns in Entity Framework Core.
[MUSIC]
>> Hi, welcome to Visual Studio Toolbox.
I'm your host, Robert Green,
and joining me is Phil Japikse. What's up Phil?
>> Hey. I'm doing great.
Getting to talk about some new cool EF,
well, not new but some cool EF course stuff.
>> I know. We're continuing our series here.
What's up today?
>> Today, we are going to talk about computed columns in
SQL Server and how to use them within the EF Core world.
>> Okay. Is that difficult?
>> It's not difficult. There is
a trick to it if you want to do with your migration.
So there's two things.
First of all, in the 1.0,
1.1 versions, which is a long time ago,
they are out of support,
computed columns based on formulas,
UDFs were not supported or they didn't work very well.
But they've worked fine since 2.0,
so they still work great.
The problem is that people will not always want to use them
or they think there's
this imaginary wall between EF Core and the database.
So I'm either going to use the EF Core or I'm doing
everything in store procedures and straight ADO.
It's probably more of a training thing than
the technical problem of just getting people to say,
"Hey, use the right tool for the right job."
For example, let's say we have an e-commerce site.
We've got an orders table and the order details table.
If I want to get the order total to display in a SQL Server query,
I can certainly do that by doing this like start from orders,
and then do a sub-select on
order details where I'm doing a sum and putting it back.
But that also makes it really hard to query.
What if I want to get all orders that are greater than $50?
Now I've over-complicated my query.
The one watch out I will say is that you have to be careful with
using functions in SQL Server as computed columns.
They can become a performance problem.
So it's always the right tool for
the right job and it's always test,
test, test to make sure it works.
So let me share my screen and we'll jump into this.
So what I want to do,
the final results is I want to create a function,
a scalar function, and it's going to be a really simple function.
We'll just create a new query window.
So here I have my function
GetOrderTotal passing an OrderId RETURNS MONEY.
Then it's going to select sum from OrderDetails
where OrderId equals OrderId return Result. Pretty simple.
So here's where the,
I won't say tricky part,
but I'm going to make you a SQL Server over here,
the gymnastics comes in.
I'm building up my database using code first in migrations.
So I don't have any tables in there.
So then I want to order my OrderDetails table,
and I want to create my function to get the sum.
Well, the limitations we have in SQL Server side.
If I try and create this function before my tables are in place,
it won't work because the tables don't exist.
If I try and create the stored
or the computed column before the function exists,
well, then that also doesn't work either.
So the fields have to be in place, then tables infield,
then I create my function,
and then I assign it to the column.
So it's really a three-step process.
If you create a column and want to change it to a computer column,
you can't just change it,
you have to drop the column and re-add it.
If a function is referenced by a computed column,
you can't change it,
SQL Server prevents you.
So it's not a complex problem,
it's just a timing thing.
It's a three-step process.
So let's walk through that.
So here I have my model,
I've got my order and my order details.
So order details is fine.
I can build this up just as we've talked about all along, right?
>> Right.
>> This I can do, I'm going to show you how we can
figure that without having to drop and create,
because this is just an inline computed columns.
So let's look at this.
This is in line.
We go into our store contexts,
we go down to our OrderDetail entity,
and we just say HasComputedColumnSql quantity times UnitCosts.
So I can build this as I'm
building the table because there aren't any external dependencies.
But to create my first migration,
I actually have to comment out this part and go into
my OrderTable and comment out this and then run migration.
So when I run this migration,
it creates your order table without the OrderTotal column,
it creates your details table.
So now I've executed this,
but now I need to get my function into the database.
So what I do is I then create a new migration that's empty,
so I don't make any changes.
So I've applied this initial migration,
which creates those tables.
We talked about migrations pretty in-depth earlier,
in an earlier episode.
So now I create a migration that initially
defaults to empty because there haven't been any table changes.
A convention that I've put in place with my team is
we always have the letters SQL in the name somehow.
So we know that this is
a hand-rolled migration as opposed to an auto-generated migration.
So in the up method, I create my function.
In the down method, I drop it.
So if I want to roll back to initial and have this be applied,
then it acts like any other migration with an up and a down.
The only difference is I created it
instead of Entity Framework Core creating it.
So now I execute this migration and I have
my function in SQL Server.
So then I go back into my order or add it in either way.
A lot of times I will
put on these fields the attributes for computed,
but we don't need to because it's actually handled in here.
Here's my property OrderTotal,
HasColumnType money, HasComputedColumnSql Store.GetOrderTotal.
So I guess it's not a complex thing,
it's just a timing thing.
I have to have the tables in place so I can make the function.
I can't have the field in place,
so we'll get the function to the functions ready.
So when you're using a computed column like this,
it's a three-step process.
It's a three migration process.
>> Now let's say you're going the other direction.
You have the database and
you scaffolded the context based on the database.
So you're not using Code First in migrations,
and now you go back to the database and add that computed column.
>> So you're going to have to re-scaffold it.
No, it's fine. It'll pick up this computed,
whatever the computed definition is.
If we look at the actual column definition,
so we go here, columns.
Well, I think we have to go into design mode, we do.
So OrderTotal, it's computed and there's a computed.
So it'll pick that up.
So if I scaffold the database,
if I'm doing Database First,
in scaffold, it'll be there.
I don't have to worry about creating a migration
for the UDF because UDFs are already in the database.
>> Now if you re-scaffold entirely,
then you lose any changes you made,
such as changing collection names to plural,
which we looked at in our very first episode.
Can you just add the context code for
that computed column to the context class itself?
>> I haven't even tried. I don't
know. I don't know the answer to that.
I'm going to say yes
based on all my experience working with EF Core.
Because if you're in a scaffolding situation,
then your context in your models,
your entities have to match your database.
If the database changes,
you don't have to re-scaffold,
you can just add those changes,
as long as they match.
>> So I guess you could scaffold from scratch in
a different location and then just
compare the difference and add the missing code, right?
>> Yep, you can.
>> Presumably.
>> Okay.
>> Yeah. So there's
another nice little benefit that comes out of UDFs.
That's the fact that I can map
a C# function to a scalar function in SQL Server.
So it's got to be on the store contexts,
it's got to be static.
What's interesting is it never gets called.
I have a throw, a new exception here just to show
that it never gets called from within C#,
but we then map it to our schema and function name.
They happen to match names here.
But the benefit of doing this,
is now I can use that function in my LINQ statement,
and it happens server-side.
So the LINQ translation engine and the SQL Server provider
will actually say select all from
orders where Store.GetOrderTotal of Id is greater than the amount.
So this happens server-side.
So this looks like a C# function, but it's not.
>> Cool.
>> This is more often where we use UDFs today.
I was super excited with the computed column.
But honestly, the number of
computed columns we write are pretty small.
But this is something we're using more and
more because you'll have a series of,
especially if you're thinking, "Oh, my gosh,
how do I get this LINQ query to work?"
Well, if you've gotten some really complex LINQ query,
create a user-defined function,
put it in there, map your C# function to it,
you can use it in LINQ.
You're not doing the context translation
back and forth between client and
server like you would in 2.0 and 2.1,
because that's fixed in 3.0 and 3.1. It's still all server-side.
>> Cool, cool.
>> So that's all I got.
Maybe we can run this and it will
just do what we expect it, and we'll just run it.
>> Sure, prove that it works.
>> What was you old Timex commercial or work to rehearsal,
the elephant stepping on the watch.
>> Timex? I guess folks can Bing that, right?
>> Yeah. There's a
commercial where they had the elephants step on the watch,
and the guy says, "It's so tough even an elephant can step on it."
Then he goes, "It worked to rehearsal."
I guess it was effective advertising
because I remembered Timex from it.
>> Yeah. So order total is 118.
If we look at,
let me scroll down here so we see what the code is doing.
Do it like this.
So it just creates some orders,
and then it's going to use eager fetching to get the data in.
Then it writes out the total for the order.
There's the order total column right
there and then each line item detail.
Then the final thing that it does is it says give me all of
the orders where the order total is greater than 50.
It's like one order in a database because it's just silly,
old data, but then it pulls it up.
So that's using the UDF to refine the WHERE clause.
>> All right, very cool.
>> Another rule of hit and jump.
>> Excellent. All right, thanks.
Hope you guys enjoyed that,
and we will see you next time on Visual Studio Toolbox.
>> See you soon.
[MUSIC]