 On today's Visual Studio Toolbox, Phil shows us how to use computed columns in Entity Framework Core. Hi, welcome to Visual Studio Toolbox. I'm your host, Robert Green, and joining me is Phil Jepixi. What's up, Phil? Hey, I'm doing great. Get ready to talk about some new cool EF. Well, not new, but some cool EF core 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 it through migration. There's two things. First of all, in the 1.0, 1.1 versions, which is a long time ago, they're out of support. Computed columns based on formulas, GDFs were not supported. They didn't work very well. 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. I'm either going to use EF core or I'm doing everything in store procedures and straight ADO. It's probably more of a training thing than a 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 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 select star 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 overcomplicated my query. The one watch out I will say is that you have to be careful with using functions in SQL Server as computer columns. They can become a performance problem. It's always the right tool for the right job and as I'll always test, test, test to make sure it works. Let me share my screen and we'll jump into this. What I want to do, the final result is I want to create a function, a scalar function, and it's going to be a really simple function. We'll just create to a new query edit window. Here I have my function, get order total, pass in an order ID, returns money, and then it's going to select sum from order details where order ID equals order ID return result. Pretty simple. Here is where the, I won't say tricky part, but let me keep SQL Server over here. The gymnastics comes in. I'm building up my database using code first and migrations. I don't have any tables in there. Then I want to build my order details table, and I want to create my function to get to sum. Well, the limitations are going to 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 computer column before the function exists, well, that also doesn't work either. The fields have to be in place. Then tables and fields, then I create my function, and then I assign it to the column. 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 computer column, you can't change it, SQL Server prevents you. It's not a complex problem, it's just a timing thing. It's a three-step process. Let's walk through that. Here I have my model, I've got my order and my order details. Order details is fine. I can build this up just as we've talked about all along. This I can do, I'm going to show you how we configure that without having to drop and create, because this is just an inline computed column. Let's look at this, this is inline. We go into our store context, we go down to our order detail entity, and we just say has computed column SQL, quantity times unit cost. 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 order table, and comment out this, and then run my migration. When I run this migration, it creates the order table without the order total column, it creates the order details table. Now I've executed this, but now I need to get my function into the database. What I do is I then create a new migration that's empty, so I don't make any changes. I've applied this initial migration, which creates those tables. We talked about migrations pretty in-depth earlier in the earlier episode. Now I created 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 letter SQL in the name somehow. We know that this is a hand-rolled migration as opposed to an auto-generated migration. In the up method, I create my function. In the down method, I drop it. 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. Now I execute this migration, and I have my function in SQL Server. 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 is my property order total, has column type money, has computed column SQL, stored I get over a total. Like I said, 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 that will get the function until the function is ready. 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. You're not using code first and migrations. Now you go back to the database and add that computed column. So you're going to have to rescapable it. It will pick up. No, it's fine. It'll pick up this computed. Whatever the computed definition is, and if we look at the actual column definition. So we go here, columns. I think we have to go into design mode, we do. So order total, it's computed and there's a computed. It'll pick that up. So if I scaffold the database, I'm doing database first, and scaffold it'll be there. I don't have to worry about creating a migration for the UDF because UDF's already in the database. Now if you want to, if you rescaffold 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 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 and your models, your entities have to match the database. If the database changes, you don't have to rescaffold. 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. Yep, you can. Presumably. So there's another nice little benefit that comes out of UDF's. That's the fact that I can map a C-sharp function to a scalar function in SQL Server. So it's got to be on the store context. It's got to be static. What's interesting is it never gets called. I have a throw new exception here just to show that it never gets called from within C-sharp. 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 link statement, and it happens server side. So the link translation engine in 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-sharp function, but it's not. I'm cool. So I can build and 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, my gosh, how do I get this link query to work? Well, if you've got some really complex link query, create a user defined function, put it in there, map your C-sharp function to it. You can use it in the link. 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. So that's what I got. I mean, we can run this and we'll just do what we expected. We'll just run it. Sure. Prove that it works. Well, it was your Timex commercial. It worked 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 elephant step on the watch, and the guy says, it's so tough, even the elephant can step on it. Then he goes, it worked to rehearsal. I guess it was effective at 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. Right. So it just creates some orders and then it's going to use eager fetching to get the data in, and 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, says give me all of the orders where the order total is greater than 50. There's only one order in a database because it's just silly little data. But then it pulls it up. So that's using the UDF to refine the where clause. Right. Very cool. All right. Another little hidden gem. Excellent. All right. Thanks. Hope you guys enjoyed that, and we will see you next time on Visual Studio Toolbox. See you soon.