SQL Views for Entity Framework Core - code first!
It happened several times to me that the normal way of using Entity Framework came to its limits, or did my skills — I don’t know which one. I just needed a specialized SQL query instead of conventions and generated migrations. And believe me, I’m not a SQL expert at all!
If you want to see the coded example directly, please scroll down to the first class-diagram.
Anyway, the first important rule for me always was to stay code-first, so my application handles the database automatically. Also to keep DevOps easier.
I don’t want to present you with a complex problem, you probably have enough of those already. Searching for “create a view with Entity Framework” on the internet, I usually found database first approaches, or long discussions that were helpful, but costly in time.
This is a straight forward example of how I write a SQL Query with Visual Studio Code and integrate it directly from the .sql file to my dotnet core application, which injects it into the database on startup.
At the same time, I extend the existing classes to be able to use LINQ and Include commands (join), to make the view information directly accessible to my app. For obvious reasons this view data is read-only.
The basic application, without any View, is part of my last article in this series, where I explain the whole setup of the stack with SQL Server in Docker on Mac. The demo application is just about gardens, its beds, a list of crops, and assignments of crops for a garden bed per year.
This schema allows all kinds of queries through LINQ that are translated to SQL queries. Now, for a feature of an overview, I need to have a list of gardens, together with one string that contains all of its crops for a certain year. I know that this could be solved with a LINQ query and some C# code, but I wanted to keep this example simple and there can be a lot of reasons to do all of this with a view instead. I’ll add a list of reasons at the end of the article.
So the following SQL query does this for me:
In my solution in Github, I wrote this query in a .sql file and executed it in Visual Studio Code directly against the database.
So how can I bring this query into my application as a view, to be available as a class (DbSet)? And without copying the SQL code into the cs file? Please just follow me in these steps:
- Create a class for this GardenInfo with relational properties
- DbContext: Create a DbSet for this class
- OnModelCreating: Link the class to the database view with its keys
- Application startup: inject the .sql query as a View into the database
Here is the complete abstraction of the database including the tables, the view, the relations, and the context.
Let me show you how the GardenContext looks, and what the method OnModelCreating does regarding the keys/relations of the new view. This is not defined automatically through a schema migration, but with a handwritten line that is telling EntityFramework what’s up.
Now, the whole solution would already work, if the View already existed in the database. But we need to go code first, to allow the application to bootstrap the whole thing. So on startup, the following methods read the content of the .sql file and inject it to the database through a CREATE VIEW command.
There is one small detail missing here. You have to make sure the .sql file is an embedded resource in the .csproj file. You can see that in my whole solution.
Now, everything is complete to use this new view in any LINQ query with Entity Framework Core. For example, if I want all Garden summaries for a list of years:
So here’s what this method does with a simple set of sample data:
------------------------
Getting garden plans for 2020 and 2021...
Crops in 'My first garden' (2020): Beans | Pumpkin | Salad
Crops in 'My first garden' (2021): Pumpkin | Tomatoes
------------------------So that’s it. We’ve accomplished to take a self-written SQL query directly into our .NET Core application as a View with Entity Framework. Here is my complete solution on GitHub again.
I promised you earlier in this article that I’ll tell you more reasons for doing this inside the database instead of the application. So here they are:
- To achieve the same without the View, you’d first have to write a LINQ query containing a lot of Includes, which means JOINs, which can mean much more data traffic between your application and the database
- Imagine I need this generated string (AllGardenCrops) in a table view (frontend), and the user wants to order it by this string. And there are thousands of entries in the database, for which you want to implement paging (just load page 1 of 2000). You wouldn’t be able to order the list inside the database, and then take the first page. You’d have to load all of the data to your application, order the list, and then pick the first page. So the view solves this for you (as a stored procedure could do as well). Believe me, this can save you a lot of performance, and even money!
- You or a colleague just wrote an awesome query, and you want to include it to your application without re-thinking it in terms of LINQ. Just grab this example of mine, and add a View to your app!
