This section adds the ViewModel to the context, sets it as NotMapped, and the updates the ShoppingCartRepo to populate the ViewModel direction from the database.
- [Instructor] The final change to the data access layer is using a new feature in EF Core to populate models. So from SQL allows you to populate a DbSet from a direct SQL call. This could be inline SQL, a stored proc, or a user define function. There are some restrictions. The model must be contained by a DbContext. The select list names must match the mapped property names of the model.
And all fields on the model must be returned. But you can also mixed LINQ statements with from SQL. So it becomes a very powerful mechanism for getting data from the database, especially for view models. So let's open up the stored context. And remember we have to put the view model as a DbSet into the context. So public DbSet and that was cart.
With product info. And we'll just call it view models, not a very creative name. And one change that resharper did for me automatically that you'll have to do yourself, go up to the top. And you want to add a using statement for a SpyStoreModels dot view models. Now if I left it like this, and any frame work thinks this is a new table in the database.
But it's not. It's a view model. So we add not mapped. And this also requires the namespace system component model data annotations dot Schema. So now we have a DbSet that we can call from SQL on, but it doesn't affect our database. Which is a view model. So let's save that.
Open up our shopping cart repo. And remember those methods that we just deleted a code? We're going to come back to them now. So what we want to do is on the stored context, which is the local verbal underscore Db, your Db's set name from SQL we will put a SQL string in here. But then we want first or default.
We actually want to put a return in there. For the list we're going to return underscore Db dot view models dot from SQL dot to list. Now all we have to do is add in the SQL. And instead of watching me type, I'm going to once again jump into the finished product that you have in the download code and copy out the SQL statements.
Okay for the search to get the individual shopping cart record, the long withy product info and category name. We're going to add in just a fairly standard SQL statement with some joins. And then we want to pull in the customer ID and product ID into the wear clause. And then run it and this will give us that particular record if it exists. For production code, I certainly would not write inline SQL like this.
I will turn it into a store procedure or user defined function. Which you would just call by putting the name under store procedure from SQL excepts parameters. It's a very, very powerful construct. Now we're going to go down to the get shopping cart records. And we're going to paste the same thing in. But then we're going to come in here and get rid of the product ID because we want to get the lists of all the records for a particular customer.
- Creating the .NET Core project
- Adding Entity Framework
- Migrating the data access layer (DAL)
- Configuring services and the HTTP pipeline
- Adding remaining services to the dependency injection container
- Migrating controllers and actions
- Testing the services