Entity Framework (EF 5.0) Core is a modern object-relational mapper (ORM) or data layer class for .NET. It supports Language Integrated Query (LINQ), change tracking, updates, schema migrations, etc. It’s an open source, lightweight, extensible, cross-platform version of the Entity Framework which is used only on Windows. With the release of EF Core, one can create a database mapper class that runs on any operating system (Windows, Linux and Mac).
Before we take a look at Entity Framework (EF) Core, one should be aware of the unification of the .NET Framework to .NET Core and then to .NET 5. Yes, you heard it right. From now on, .NET Core will get more updates because of cross-platform support. The .NET Framework, which is compatible with Windows only, will continue to exist but it is recommended to convert it to .NET 5 (.NET Core version). For more details, do read my article explaining the benefits of cross-platform applications using .NET Core, carried in the January 2019 issue of OSFY (https://bit.ly/3oA6hlO).
If you have any classic .NET Framework applications, it’s time to consider migrating to .NET 5, which offers cross-platform support and performance improvements. It’s a great time to consider .NET 5 and EF 5 Core for developing new apps as they support cloud native features out-of-the-box. This includes support for gRPC, Project Tye (which is a .NET microservices and orchestration tool), and REST APIs, as well as faster cross-platform development with Windows Subsystem for Linux (WSL) and Linux.
Why should I care about Entity Framework Core?
As discussed, EF Core can serve as an object-relational mapper (ORM), which helps .NET developers to work with a database using .NET objects. It also eliminates the need for most of the data-access code that typically needs to be coded.
1. EF Core works with many databases, including SQL (on-premises and Azure), SQLite, MySQL, PostgreSQL and Azure Cosmos DB. In simple terms, it’s a data access API that supports non-relational databases like Azure Cosmos DB too.
2. It supports 23+ databases, including prominent ones like Oracle, MySQL, MariaDB, PostgreSQL, FireBird, DB2, Informix, Progress Open Edge, etc. You can check http://aka.ms/efcoreproviders for more information on the databases it supports.
3. We can use EF Core to build various types of .NET projects, say, Blazor app, WPF, Winforms, ASP.NET Core and Xamarin, across the spectrum.
4. Another benefit is that one can use strongly typed integrated language, aka Language Integrated Query (LINQ), to fire the DB queries.
5. It also implements ‘unit of work’ out-of-the-box to help track changes and concurrency.
6. Finally, it’s data-binding friendly; this means that if you are using WPF or Winforms data binding frameworks, it is friendly with these.
Steps to create ‘Hello World’ using EF 5.0 Core
Prerequisites
1. EF Core is a .NET Standard 2.0 library. So, it requires a .NET implementation that supports .NET Standard 2.0 to run. For example, you can use EF Core to develop apps that target .NET Core. Building .NET Core apps requires the .NET Core SDK.
2. IDE: You can use any development environment like Visual Studio, Visual Studio for Mac, or Visual Studio Code. You can use EF Core to develop applications on Windows using Visual Studio. The latest version of Visual Studio is recommended.
3. EF Core can run on other .NET implementations like Xamarin and .NET Native, too. But in practice, those implementations have runtime limitations that may affect how well EF Core works on your app.
Table 1 shows various .NET implementations supported by EF Core.
| EF Core | 2.1 and 3.1 | 5 |
| .NET Standard | 2 | 2.1 |
| .NET Core | 2 | 3 |
| .NET Framework(1) | 4.7.2 | (not supported) |
| Mono | 5.4 | 6.4 |
| Xamarin.iOS(2) | 10.14 | 12.16 |
| Xamarin.Mac(2) | 3.8 | 5.16 |
| Xamarin.Android(2) | 8 | 10 |
| UWP(3) | 10.0.16299 | TBD |
| Unity(4) | 2018.1 | TBD |
Table 1: The .NET implementations supported by EF Core
How to get EF Core runtime
To add EF Core to an application, we need to install the required NuGet package of the database provider to start using it. Let’s say, if we are building an ASP.NET Core application that has SQL Server as a database, then the EF Core runtime of SQL Server is what is required, which can be installed via NuGet.
We can use the .NET Core command line interface (CLI) or the Visual Studio Package Manager Dialog, or the Visual Studio Package Manager Console to get the required packages.
Assuming you have installed .NET Core runtime, go ahead and type the command given below in your .NET Core CLI command window:
D:\EFY\myfirstefcore> dotnet add package Microsoft.EntityFrameworkCore.SqlServer
Get the .NET Core CLI tools
dotnet ef must be installed as a global or local tool. Most developers prefer installing dotnet ef as a global tool using the following command:
D:\EFY\myfirstefcore> dotnet tool install --global dotnet-ef
Let’s create a .NET Core console app that performs data access against a SQL Server database using EF Core. We can try the same using Visual Studio on Windows, or by using the .NET Core CLI on Windows, macOS, or Linux with VSCode as a free editor. I have copied the complete sample at https://github.com/Maheshk-MSFT/efcorecodefirstapproach as a reference.
In this sample, C# models created in our local project can be used as a schema to generate database tables. Assuming you have .NET Core installed along with entity framework runtime, execute the steps to get the models translated into databases.
Here’s a quick test to confirm if we have .NET Core and EF Core installed.
In this first sample, we have created four model classes – Customer, Order, Product and ProductOrder – along with their relationships.
After setting the required SQL Database Connection string, we need to start initialising the local model classes to actual database tables. Execute the commands given below in a sequential order:
a) dotnet ef migrations add InitialCreate
The above command initialises the local model classes prepared for migration.
b) dotnet ef database update
This command helps us to generate equivalent tables in the database.
c) dotnet ef migrations add AddBlogCreatedTimestamp
The sub-command Add helps to amend incremental changes synced with actual DB tables.
d) dotnet ef database update
This command helps to update the local changes.
Now, let’s see what the Data Context class is and its role. We need to configure the provider in our DbContext, either using the OnConfiguring method or the AddDbContext method, if we are using a dependency injection container. For example, the following line configures the SQL Server provider with the passed connection string:
optionsBuilder.UseSqlServer( “Server=(localdb)\mssqllocaldb;Database=MyDatabase;Trusted_Connection=True;”);
Failing to set the right connectionstring will throw an error asking to fix the connection first. Figure 5 explains the Data Context class and its various functionalities.
We can initialise the Data Context class in program.cs for making the database call. In Figure 6, you can see that we are reusing the same datacontext ‘context’ for creating, updating or deletion of the records. The Data Context class facilitates the connection between our local .NET Class and its memory state to database records.
On executing the final project using dotnet run program.cs, one can see the record creation, updation and deletion at a glance. I suggest you check out the complete code for this topic and try it.
How to work with raw SQL queries
EF Core allows us to embed and execute raw SQL queries when working with a relational database. This is quite useful where you can’t express queries using LINQ, say, nested reporting queries. We can use the FromSqlRaw extension method to begin a LINQ query based on a raw SQL query. FromSqlRaw can only be used on query roots, that is, directly on the DbSet<>.
C#
#region FromSqlRaw
using (var context = new DBContext())
{
var blogs = context.Blogs
.FromSqlRaw(“SELECT * FROM dbo.Blogs”)
.ToList();
foreach (Blog o in blogs)
Console.WriteLine(“Url: “ + o.Url + “, Blog Rating: “ + o.Rating);
}
#endregion
FromSqlInterpolated is similar to FromSqlRaw but allows us to use string interpolation syntax to avoid injection. As with the previous example, the value is converted to a DbParameter and isn’t vulnerable to SQL injection. Just like FromSqlRaw, FromSqlInterpolated can only be used on query roots.
C#
#region FromSql InterpolatedComposed
using (var context = new DBContext())
{
var Name = “https://maheshk.net”;
var blogs = context.Blogs
.FromSql Interpolated($”SELECT * From Blogs Where url = {Name}”)
//.OrderBy(b => b.Rating)
.ToList();
foreach (Blog o in blogs)
Console.WriteLine(“RESULT: Url: “ + o.Url + “, Blog Rating: “ + o.Rating);
}
#endregion
The output is shown in Figure 8.
Passing parameters
Yes, it’s recommended to use parameterization for raw SQL queries. When introducing any user-provided values into a raw SQL query, care must be taken to avoid SQL injection attacks. Make sure it does not contain invalid characters. Never pass a concatenated or interpolated string ($””) with non-validated user-provided values into FromSqlRaw or ExecuteSqlRaw.
- The FromSqlInterpolated and ExecuteSqlInterpolated methods allow using string interpolation syntax in a way that protects against SQL injection attacks.
Limitations
- The SQL query must return data for all properties of the entity type.
The column names in the result set must match the column names that properties are mapped to. - The SQL query can’t contain related data. If required, we can compose on top of the query using the Include operator to return related data.
- You can begin your journey with EF Core by logging on to https://docs.microsoft.com/en-us/ef/core/.
