 On today's Visual Studio Toolbox, Phil shows us some additional options in NAD Framework Core for querying data. Hi, welcome to Visual Studio Toolbox. I'm your host Robert Green and joining me today is Phil Jepixi. Hey, Phil. Hey, Robert. This is part two of our second series on NAD Framework Core. In the first part of this, we focused on performance and saw that NAD Framework Core is dramatically faster than NAD Framework Classic. Today, what are we going to talk about? Today, we're going to talk about some viewer questions, but I really want to talk about how you can use different techniques to get data out of the database that isn't traditional TableRow column. Cool. One of the questions that I get all the time is about store procedure support. It is different than it was in EF6. EF6, you could scaffold a set of store procedures for all of the CRUD operations. EF Core really is just supporting read operations with this thing called a query type, and it also supports inline SQL, which I know typically that's a bad word, but there are cases with EF Core where it makes sense. It is still interpolated. It is still parameterized, but I wanted to go through those couple of things too. Correlation to this is the whole no-tracking part, which I think we've covered pretty sufficiently. I just want to let you know that when we use a query type, it won't be tracking. Let me start with a query type and what is a query type. We talked about DB sets and how they represent a table. In EF Core up until 2.2, we had also something called a DB query, which is different than a DB set in that it was not representing anything that would ever get updated through EF Core. A view, user defined function, raw SQL things like that. There was a shift with the 3.x versions, where instead of calling it a DB query, it's back to being a DB set, but it's configured as not having a key. This has also been built into scaffolding. When you scaffold AdventureWorks, you scaffold any of your systems that have views, they'll come back in as DB sets, but they don't have a key. The has no key is important because what that tells entity framework core is that, hey, there's no primary key, we're not going to go into the change tracker, never going to make any changes. We don't have to call ASNO tracking. From the last episode, I think you saw the performance improvements just between the ASNO tracking and the query type. Setting them up, I have three in here. The model for testing this is used for the performance, but let's look at the product view model, and what I have created, and this is a typical use case for this. I've got a Sprock or some SQL where I don't want all of the fields for a table, or I want to join multiple tables into a view model. Like maybe we're combining product and category, and other metadata about those products. If we want to go to traditional ORM route, what we need to do is we need to say, get me all this, include these other tables as we showed in the last series with those getting, which is show on getting related data, and then we can do a projection to just come up with the data we need. Well, why get all that data if we're not going to use it? What we can do is we can skip that whole EF Core projection and get data straight from the database into our view model without having to go through that intermediate step. Easy for me to say. Here I have a product view model, and it is just a regular C-Sharp class. In this example, it is the product table, but without all of the related field. If we look at the product table or the product entity, what we see is all these navigation properties as we discussed in the last show, and we don't want those. We just want the product data, and we also don't want to ever update this. To get set up, we just create a class that will hold the data. Again, if we wanted to have the category information and things like that, and then we have to tell in the DB context, our derived DB context, that the entity has no key. What we're saying here is that this is, to be honest here, I don't agree with the decision to take away the DB query type. I really liked that. In EF Core prior to 3.0, I could do this, and everybody looking at the code knew it was a DB query. You can technically still do that, although it comes up with a warning that it's deprecated. Now it's a two-step process. Here's our DB set. We then go and configure it as not having a key. We can also, if we know we're using a view, we can actually configure it right here to always use that view. The fact that I am not configuring a server-side object here means when I want to fill this data, I have to use a from SQL call. Let's talk about that. A couple of questions before we move on. Sure. This is a way of returning something other than the complete table. If you wanted to get information from multiple tables, or if you wanted on your product screen rather than bring back all of the data, you just want to show the product name and manufacturer to display in a list, for example. You could use one of these view models, is that right? Correct. Then when you say view model here, is this the same type of view model that I know from MVVM, or is the view here equivalent to a SQL server view and not related to UI? How are you using view here? Yes. So what I mean by that is my definition of a view model is something that is not precisely persisted. Which is the same as MVVM, right? So in MVVM, you're squishing together a bunch of models as a transport mechanism to get to the view. Typically, it's on a WPF. A view in SQL server is the same thing, in a sense that it's taking data from different models, they call them tables of course, and then squishing them together and filtering to then produce some other database on those other things. Right. So they really are interchangeable. Okay. In what I'm using here as a view model, as a convention, anything that is not an entity that ties into a database table, I always add a view model to the end so people understand it's not a database table. Okay. I've seen that term used other places, so this isn't a term that you invented. Oh, no, no, no. Just fill speak. Well, I think it's pretty university. I don't know that it's completely fill speak in the sense that I've created a different use case for it. It's a pretty common term, but it's good to clarify, right? It's an overloaded term. Okay. Cool. All right. So I have this view model, and again, this is just a subset of the product table, but then when we want to get data from it, we can do a couple of different things. There is from SQL, which now in EF Core 3.x and above, is either from SQL raw or from SQL interpolated. So let's say that we wanted to use SQL Server interpolation, comment this out, so we sharper isn't angry at me, and we wanted to pass in a parameter. This is a terrible example, we're going to go ahead and do it anyway, just so I can show you the syntax. Food doesn't exist, but if I use C-sharp interpolation in my from SQL interpolated, it will actually turn anything that is interpolated in the C-sharp syntax, as a parameter. So a better example, that's going to make some people cringe, and it still does make me cringe a little bit, is update foo, where set name equals Bob, where id equals, so this statement, that's actually better, let's delete that. So this will then change foo into an official parameter. It will be the same as if we built up a parameter collection, a parameter object, had a command object, assigned the connection to it, assigned the parameter to the command as an input parameter, and then call it that way. So it provides you not complete security, but better isolation from SQL injection attacks, by actually coming in as a parameter. So your user can't type in little Bobby tables, and drop your database, and that's an XKCD reference. If you don't have anything that's interpolated, you can call from SQL raw. Now, you could also use from SQL raw with interpolation, but it's really not recommended, and Rawson will give you a bunch of warnings. So I'm showing two different ways to do this. So this is calling a stored procedure. So I can say return to list, we want it to the same rules apply. If I don't call to list on this, even though it's a from SQL call, and not using the DB context directly in a link statement, it still won't execute until we iterate over it. So the query rules still apply even though it's from SQL. So this is one way to attach to views, store procedures, functions, things like that. Okay. Cool. I don't do this a ton. What I do much more often is a variation of this. Now, the advantage of using inline SQL is two-fold. First and foremost, there's going to be times when you're trying to do some sort of join and filter. So one of the things that's pretty hard to do in actually pertinent or possible in EF Core up to and including 3.1 is filtering on your included tables, your joins. Really easy to do in T-SQL, hard to do in a link as it is written today. The good news is in EF Core 5, or whatever the final name will be, they've added the feature on those join tables. So until you get to 5, you're going to be writing something like p.all from production product, inner join order. I don't remember the table name, I will clean it up before I check it in. Sales.orderDetails.OD on, let me just make this easier to read. So on p.id equals od.productid, where od.orderDate is greater than, let's see, less than datetime.now. We're going to interpolate this. So now the orders that were ordered prior to today, we're getting all the products that were in orders that are prior to today. Very simple query. So again, the difference between SQL raw and SQL interpolated because you're interpolating and you're raw. Good. Call out. Oh, that's the difference. The difference is I'm not supposed to use SQL raw if I have some sort of interpolation. But this is going to fail miserably because this isn't the right query. That's fine. Now the advantage of doing this instead of a store procedure, because you could certainly write a store procedure because it takes parameters. If you have a non-terminating SQL statement in your from-SQL call, you can continue to build on it with link. So in this particular example, I could say dot include x lambda x dot, whatever navigation property I wanted to add, and it bundles all that up server side as if we were using the DB context or the DB set directly. So you can start with that complex query, put it into SQL, and then still building your includes and everything else. So it's a really handy tool to use. Now, terminating queries, and I have to check this in 3.1. I know this is a problem all along. If I put a semicolon in here, it's considered terminating, and then you can't build on it. So make sure you don't end with your semicolon. But something that can't be built on, for example, store procedure, user defined function, anything with a common table expression as CTE, you can't build on it. What SQL server, what any framework core is doing, and this might be changing in five, I haven't gone down that deep yet, is taking your query and wrapping it. So if I did include or I can also do aware here dot where blah, blah, blah, blah. So it takes your initial query inside that from SQL interpolated, wraps it as an inner select, and then builds on the link around it. At least that's the SQL server implementation. So if it's a terminated query, you can't have a terminating query be the target of a sub-select. That's the reason behind it. Okay. On from SQL or query types, or how to use them, or why to use them. Nope. All right. So last comment on this before we wrap, those little bit of a shorter episode, I wanted to get this out there. The link translation engine for SQL server at least is really good. The people who are working on that are wicked smart people and almost always the queries they're generating are spot on. Sometimes however, and this is why performance profiling is so important, you're going to get a situation whether it's your design, whether it's an edge case they didn't think about, whether it's your data, where the performance of the generated SQL from your link statement just isn't cutting it from a performance standpoint. That's a common thing I hear is, oh my gosh, it's too slow if I do X. Well, the answer to that is what we're talking about in today's episode. From SQL interpolated, from SQL raw, you can write that store procedure. Excuse me. You can write that query statement however you want to pull the data back. There is a limitation that in your SQL statement itself, I cannot bring back related tables. So I cannot do select star from product and order details, which would get me back, all of them, unless I'm going into a view model. But it's not going to fill up the product table and the order details table if I do this. I would have to do select P, get those orders, and then do an include to bring in the order details table. The other limitation I want to call out is that whatever fields you're bringing back in this from SQL call, whether it's from SQL interpolated or from SQL raw, have to exist in your view model. It's an all or nothing proposition. So if my product view model has seven fields, and I only bring back five fields in my from SQL interpolated call, then I'm going to get a runtime error that says you're missing some fields. So you have to match them up. But other than that, it's a powerful tool that we use quite often to build complex view models without having to go through pulling the data back from seven different tables and then creating a projection. Right. So that's what I got on from SQL and query types. Cool, very useful. So there's a great look at some of the more advanced query options we have. I love the idea of the view model where you can bring back just some of the records from a table. So for example, you want to display them in a list box, somebody chooses a product and then goes see the full information on the product. You just bring back a subset of the data. And we also looked at the from SQL. What are we going to look at in the next episode, Phil? So next we're going to back up a little bit and dive into how you configure your DB context. All right, excellent. So I hope you guys are enjoying this and we will see you next time on Visual Studio Toolbox. Thanks, see you then.