Problem
Entity Framework Core (EF Core) is a lightweight and extensible version of Microsoft’s popular ORM Entity Framework. EF Core runs on top of the .NET Core runtime and is an open source, lightweight, cross platform Object Relational Mapping (ORM) that can be used to model your entities much the same way you do with Entity Framework.
Entity Framework provides support for executing raw SQL queries against the database. This feature is available in Entity Framework Core as well. Such raw SQL queries can return entity types or query types (from EF Core 2.1). This article presents a discussion on how we can work with raw SQL queries in Entity Framework Core.
Solution
In this article we’ll explore how we can execute raw SQL queries in EF Core.
Getting Started with Entity Framework Core and SQL Server
In this example, we’ll use Visual Studio 2019. If you don’t have a copy of Visual Studio 2019 installed in your system, you can download a copy.
If Visual Studio 2019 has been successfully installed in your system, follow the steps outlined below to create a new console application project in Visual Studio.
- Open Visual Studio 2019
- On the File menu, click on New > Project
- In the "Create a new project" dialog, select "Console App (.NET Core)"
- Click Next
- Now specify the name and location of the project
- Click Create
This would create a new console application project in Visual Studio 2019. We'll use this project in the subsequent sections of this article.
In this example we’ll use the AdventureWorks database for the sake of simplicity and avoid having to create a new database. If you don’t have it in your system, you can download a copy of the AdventureWorks database.
Follow the steps outlined below to restore the AdventureWorks database in your system:
- Open Microsoft SQL Server Management Studio
- Select Databases > Restore database…
- Select the database to be restored
- Specify the path and the name of the destination database
- Click OK
This would restore the AdventureWorks database in your system and you are now all set to use it.
Installing the Necessary Packages for Entity Framework Core
To be able to use Entity Framework Core, you should install the necessary packages. You can install the packages from the NuGet Package Manager or from the Package Manager Console using the following commands.
Install-Package Microsoft.EntityFrameworkCore Install-Package Microsoft.EntityFrameworkCore.Tools Install-Package Microsoft.EntityFrameworkCore.SqlServer
So far so good. We’ve set up the environment for working with EF Core. In the sections that follow, we’ll learn how we can execute raw SQL queries in EF Core.
Create a Model from the AdventureWorks Database
So far so good. To create the Entity Data Model, we'll take advantage of the Scaffold-DbContext command. Here's how the syntax of this command looks:
Scaffold-DbContext [-Connection] [-Provider] [-OutputDir] [-Context] [-Schemas>] [-Tables>]
[-DataAnnotations] [-Force] [-Project] [-StartupProject] [<CommonParameters>]
To create the Entity Data Model for our AdventureWorks database, specify the following command at the Package Manager Console.
Scaffold-DbContext "Server=JOYDIP;Database=AdventureWorks2017;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models
While the Server attribute is used to specify the name of the database server, the Database attribute is used to specify the database name. In our example, the name of the database is AdventureWorks2017. Once the command has run successfully, you’ll see that the entity classes and the DbContext class has been generated.
Executing Raw SQL Queries in EF Core
You can execute raw SQL queries in EF Core in several ways. These include the following:
- Using the DbSet.FromSql method
- Using parameterized queries
- Using stored procedures
- Using the Database.ExecuteSqlCommand property
Using the DbSet.FromSql method with Entity Framework Core and SQL Server
You can take advantage of the DbSet.FromSql method to execute raw SQL against the database and get back the results as entity objects. The following code snippet illustrates how this method can be used to execute a SQL statement.
var result = dbContext.Employee.FromSql
("SELECT * FROM [HumanResources].[Employee] Where SalariedFlag = 1").ToList();
The above statement when executed will return all the Employee records who are salaried.
The following code listing shows the complete program with the necessary namespaces included.
using EFCoreExecuteRawSQLDemo.Models;
using Microsoft.EntityFrameworkCore;
using System;
using System.Linq;
namespace EFCoreExecuteRawSQLDemo
{
class Program
{
static void Main(string[] args)
{
using (var dbContext = new AdventureWorks2017Context())
{
var result = dbContext.Employee.FromSql
("SELECT * FROM [HumanResources].[Employee] Where SalariedFlag = 1").ToList();
}
Console.Read();
}
}
}
You can also specify the columns that you would want to be retrieved. The following code snippet illustrates how this can be achieved.
using (var dbContext = new AdventureWorks2017Context())
{
var result = dbContext.Department.FromSql
("SELECT DepartmentId, Name, GroupName From [HumanResources].[Department]");
}
Using Parameterized Queries with Entity Framework Core and SQL Server
The following code snippet illustrates how we can take advantage of parameterized queries to execute raw SQL queries.
int deptId = 1;
using (var dbContext = new AdventureWorks2017Context())
{
var result = dbContext.Department.FromSql
("SELECT * From [HumanResources].[Department] Where DepartmentId = {0}",
deptId).FirstOrDefault();
}
Using Stored Procedures with Entity Framework Core and SQL Server
You can execute raw SQL queries using stored procedures as well. The following code snippet illustrates how this can be achieved.
using (var dbContext = new AdventureWorks2017Context())
{
var result = dbContext.Department.FromSql
("Exec GetAllDepartments").ToList();
}
You can also pass parameters to the stored procedures using the FromSql method as shown in the code snippet below.
using (var dbContext = new AdventureWorks2017Context())
{
var deptId = new SqlParameter("@DepartmentId", 1);
var result = dbContext.Department.FromSql("Exec GetDepartment @DepartmentId", deptId);
}
Using the Database.ExecuteSqlCommand property with Entity Framework Core and SQL Server
The ExecuteSqlCommand method of the Database class can also be used to execute raw SQL against the database. Incidentally, the DbContext exposes a property that is of type Database. Note that you can perform insert, update or delete operations using the ExecuteSqlCommand method. This method cannot be used to retrieve data from the database. The following code snippet shows how the ExecuteSqlCommand method can be used.
using (var dbContext = new AdventureWorks2017Context())
{
var commandText = "INSERT [HumanResources].[Department] (Name, GroupName,ModifiedDate) " +
"VALUES(@Name, @GroupName, @ModifiedDate)";
var name = new SqlParameter("@Name", "Test");
var groupName = new SqlParameter("@GroupName", "Test Group");
var modifiedDate = new SqlParameter("@ModifiedDate", DateTime.Now);
dbContext.Database.ExecuteSqlCommand(commandText, name);
}
Summary Using Entity Framework Core and SQL Server
The ability to execute raw SQL queries against the database is a nice feature in Entity Framework Core. You can take advantage of this feature particularly when you would want to execute a query that cannot be expressed in LINQ. There are a few limitations that you should be aware of when working with raw SQL queries in EF Core. These include the following:
- The entity types returned on execution of a query should be a part of the model
- The raw SQL query must return data for all properties of the entity type
- The column names of the result set and the names of the properties they map to should be identical
Next Steps
Check out these related tips:
