SQL в Entity Framework Core

Выполнение SQL-запросов

Данное руководство устарело. Актуальное руководство: Руководство по Entity Framework Core 7

LINQ to Entities позволяет создавать запросы к источнику данных. Однако в случае, если запрос очень сложный по своей структуре или нам его легче описать на языке SQL, то в Entity Framework Core выполнить напрямую sql-запрос.

FromSqlRaw

Для получения данных из БД у объектов DbSet определен метод FromSqlRaw(), который принимает в качестве параметра sql-выражение и набор параметров. В качестве результата FromSqlRaw возвращает набор полученных из бд объектов.

При этом надо учитывать, что передаваемое в метод FromSqlRaw SQL-выражение не должно извлекать связанные данные, а полученные значения должны соответствовать определению какого-либо класса.

Для примера возьмем следующие модели и контекст данных:

public class Company
{
	public int Id { get; set; }
	public string Name { get; set; }

	public List<User> Users { get; set; } = new List<User>();
}

public class User
{
	public int Id { get; set; }
	public string Name { get; set; }
	public int Age { get; set; }

	public int CompanyId { get; set; }
	public Company Company { get; set; }
}
public class ApplicationContext : DbContext
{
	public DbSet<Company> Companies { get; set; }
	public DbSet<User> Users { get; set; }
        
	protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
	{
		optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=helloappdb;Trusted_Connection=True;");
	}
}

Пусть ранее у нас были добавлены следующие данные:

using (ApplicationContext db = new ApplicationContext())
{
	// пересоздаем базу данных
	db.Database.EnsureDeleted();
	db.Database.EnsureCreated();

	Company microsoft = new Company { Name = "Microsoft"};
	Company google = new Company { Name = "Google"};
	db.Companies.AddRange(microsoft, google);

	User tom = new User { Name = "Tom", Age = 36, Company = microsoft };
	User bob = new User { Name = "Bob", Age = 39, Company = google };
	User alice = new User { Name = "Alice", Age = 28, Company = microsoft };
	User kate = new User { Name = "Kate", Age = 25, Company = google };
	User tomas = new User { Name = "Tomas", Age = 22, Company = microsoft };
	User tomek = new User { Name = "Tomek", Age = 42, Company = google };

	db.Users.AddRange(tom, bob, alice, kate, tomas, tomek);
	db.SaveChanges();
}

Например, получим все объекты из таблицы Companies:

using (ApplicationContext db = new ApplicationContext())
{
	var comps = db.Companies.FromSqlRaw("SELECT * FROM Companies").ToList();
	foreach (var company in comps)
		Console.WriteLine(company.Name);
}

Выражение SELECT извлекает данные из таблицы. Так как эта таблица сопоставляется с моделью Company и хранит объекты этой модели, то в результате мы получим набор объектов класса Company.

При этом мы можем добавлять к методу другие методы LINQ, которые все вместе будут конкатенироваться в одно общее SQL-выражение:

var comps = db.Companies.FromSqlRaw("SELECT * FROM Companies").OrderBy(x=>x.Name).ToList();
foreach (var company in comps)
    Console.WriteLine(company.Name);

В итоге будет выполняться следующее SQL-выражение:

SELECT [x].[Id], [x].[Name]
FROM (
    SELECT * FROM Companies
) AS [x]
ORDER BY [x].[Name]

Также можно использовать метод Include для подгрузки связанных данных:

var users = db.Users.FromSqlRaw("SELECT * FROM Users")
	.Include(c=>c.Company).ToList();
foreach (var user in users)
	Console.WriteLine($"{user.Name} - {user.Company.Name}");

Параметры

Другая версия метода FromSqlRaw() позволяет использовать параметры. Например, выберем из бд все модели, в названии которых есть подстрока "Tom":

using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Data.SqlClient;

namespace HelloApp
{
    public class Program
    {
        public static void Main(string[] args)
        {
            using (ApplicationContext db = new ApplicationContext())
            {
                SqlParameter param = new SqlParameter("@name", "%Tom%");
                var users = db.Users.FromSqlRaw("SELECT * FROM Users WHERE Name LIKE @name", param).ToList();
                foreach (var user in users)
                    Console.WriteLine(user.Name);
            }
            Console.Read();
        }
    }
}

Класс SqlParameter из пространства имен System.Data.SqlClient позволяет задать параметр, который затем передается в запрос sql.

Также мы можем определять параметры как простые переменные:

using (ApplicationContext db = new ApplicationContext())
{
	var name = "%Tom%";
	var users = db.Users.FromSqlRaw("SELECT * FROM Users WHERE Name LIKE {0}", name).ToList();
	foreach (var user in users)
		Console.WriteLine(user.Name);

	var age = 30;
    users = db.Users.FromSqlRaw("SELECT * FROM Users WHERE Age > {0}", age).ToList();
    foreach (var user in users)
		Console.WriteLine(user.Name);
}

ExecuteSqlRaw

Метод FromSqlRaw() осуществляет выборку из БД, но кроме выборки нам, возможно, придется удалять, обновлять уже существующие или вставлять новые записи. Для этой цели применяется метод ExecuteSqlRaw(), который возвращает количество затронутых командой строк. Для его вызова у контекста данных используется свойство Database:

// вставка
string newComp = "Apple";
int numberOfRowInserted = db.Database.ExecuteSqlRaw("INSERT INTO Companies (Name) VALUES ({0})", newComp);

// обновление
string appleInc = "Apple Inc.";
string apple = "Apple";
int numberOfRowUpdated = db.Database.ExecuteSqlRaw("UPDATE Companies SET Name={0} WHERE Name={1}", appleInc, apple);

// удаление
int numberOfRowDeleted = db.Database.ExecuteSqlRaw("DELETE FROM Companies WHERE Name={0}", appleInc);

Интерполяция строк

Для методов FromSqlRaw и ExecuteSqlRaw в EF Core определены их двойники - методы FromSqlInterpolated() и ExecuteSqlInterpolated() соответственно, которые позволяют использовать интерполяцию строк для передачи параметров. Пример с FromSqlInterpolated:

using (ApplicationContext db = new ApplicationContext())
{
	var name = "%Tom%";
    var age = 30;
    var users = db.Users.FromSqlInterpolated($"SELECT * FROM Users WHERE Name LIKE {name} AND Age > {age}").ToList();
    foreach (var user in users)
		Console.WriteLine(user.Name);
}

В этом случае на стороне сервера также будут создаваться параметры для передачи значений name и age наподобие:

@p0='%Tom%'
@p1=30

SELECT *
FROM Users
WHERE Name LIKE @p0 AND Age > @p1

Использование ExecuteSqlInterpolated:

using (ApplicationContext db = new ApplicationContext())
{
	string samsung = "Samsung";
	db.Database.ExecuteSqlInterpolated($"INSERT INTO Companies (Name) VALUES ({samsung})");
                
	foreach (var comp in db.Companies.ToList())
		Console.WriteLine(comp.Name);
}

Асинхронные версии

Методы Database.ExecuteSqlInterpolated() и Database.ExecuteSqlRaw имеют асинхронные версии:

using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using System.Threading.Tasks;

namespace HelloApp
{
    public class Program
    {
        public static async Task Main(string[] args)
        {
            using (ApplicationContext db = new ApplicationContext())
            {
                string ibm = "IBM";
                await db.Database.ExecuteSqlRawAsync("INSERT INTO Companies (Name) VALUES ({0})", ibm);
                
				string huawei = "Huawei";
                await db.Database.ExecuteSqlInterpolatedAsync($"INSERT INTO Companies (Name) VALUES ({huawei})");
                
                var companies = db.Companies.FromSqlRaw("SELECT * FROM Companies").ToList();
                foreach (var comp in companies)
                    Console.WriteLine(comp.Name);
            }
        }
    }
}