Привет, %username%! Меня зовут Антон Жеронкин, я Data Scientist в Сбере, участник профессионального сообщества NTA. Сегодня поговорим о том, как можно сделать лучше жизнь разработчиков, которые часто сталкиваются с базами данных. Дело в том, что, когда разработчики вручную пишут функциональные модули, ответственные за связь с БД, они проделывают следующую работу:

• описывают таблицы в виде классов;

• описывают отдельные атрибуты таблиц в виде атрибутов классов. При этом требуется следить за тем, чтобы типы и форматы данных совпадали;

• на CRUD-операции пишут много SQL-кода, который зашивается в методы языка программирования и помогает остальным модулям при необходимости использовать связь с БД.

Примерно такую же работу приходится проделывать, если сущности, атрибуты и отношения изначально заданы в приложении, а после этого данную модель требуется реализовать в БД. Главный её недостаток — рутина. О том, как её автоматизировать, поговорим под катом.

В чём вообще проблема?

Когда в БД таблиц, их связей и атрибутов немного либо когда прорабатываешь структуру большой модели данных в первый раз, это вполне можно пережить. Работать со всем этим даже интересно. Но при ручной разработке Data Access Layer (DAL) на обширной базе через 4 часа хочется всё бросить, а прописывать те же самые сущности во второй раз в коде приложения уже нет желания — становится скучно.

Знатоки, конечно же, скажут, что SQL-скрипты можно «распотрошить» регулярными выражениями и из этого частично сгенерировать нужный код. Но, во-первых, какую-то часть работы всё равно придётся делать руками, а во-вторых, DDL-скрипты не всегда можно достать.

ORM-фреймворки (англ. Object-Relational Mapping), предназначенные для автоматизации рутины, уже существуют. ORM — это та самая прослойка между приложением и БД, с помощью которой можно, управляя объектами в приложении, синхронизировать их с объектами в БД, а также избавиться от необходимости вручную реализовывать DAL. То есть не прописывать, как должен выглядеть SQL-запрос на CRUD-операцию, не раскладывать переменные объекта по местам в запросе, не задавать приведение к типам/размерность и т. д. При этом ORM стоит с осторожностью использовать там, где для работы с данными требуются сложные SQL-запросы, так как на них ORM часто работает неоптимально либо же вообще не работает.

Для большинства языков разработана масса ORM-фреймворков — например, SQLAlchemy для Python, Entity Framework для .NET, Hibernate для Java. Опираясь на свои предпочтения, разберу типовые фишки ORM с помощью .NET Core/EF Core/MS SQL.

Для начала создадим пустой консольный проект на .NET Core, а после этого установим 3 пакета из NuGet:

• Microsoft.EntityFrameworkCore;

• Microsoft.EntityFrameworkCore.Tools;

• Microsoft.EntityFrameworkCore.<DATABASE> — провайдер БД под конкретную СУБД.

Список доступных провайдеров БД в Entity Framework Core можно найти здесь.

Нюансы работы с Entity Framework

К работе с Entity Framework применимы 3 подхода, два из которых мы сейчас и рассмотрим.

1. Database First

Этот подход применяется тогда, когда есть уже готовая БД, а в приложении требуется реализовать интерфейс взаимодействия с ней. Для примера создадим на MS SQL вот такую базу:

USE DatabaseFirstDB
GO

CREATE TABLE [dbo].[company](
id INTEGER IDENTITY(1,1) PRIMARY KEY,
company_name VARCHAR(300) NOT NULL
)

CREATE TABLE [dbo].[department](
id INTEGER IDENTITY(1,1) PRIMARY KEY,
dep_name VARCHAR(300) NOT NULL,
add_info VARCHAR(3999) NULL,
company_id INTEGER NOT NULL FOREIGN KEY REFERENCES company(id)
)

CREATE TABLE [dbo].[employee](
id INTEGER IDENTITY(1,1) PRIMARY KEY,
fullname VARCHAR(300) NOT NULL,
birth_date DATE NOT NULL,
department_id INTEGER NOT NULL FOREIGN KEY REFERENCES department(id)
)

Таким образом, у нас есть сотрудники, которые прикреплены к какому-то подразделению. В свою очередь, подразделения входят в состав компании. Схематически это выглядит так:

Давайте создадим пустое консольное приложение на .NET Core с помощью шаблона.

После этого обозначенные ранее библиотеки установлены, в Visual Studio нужно открыть консоль пакетного менеджера и выполнить всего одну команду:

Scaffold-DbContext -Provider Microsoft.EntityFrameworkCore.SqlServer -Connection "Data Source=(localdb)\MSSQLLOCALDB; Initial Catalog=DatabaseFirstDB"

В параметре Provider указываем полное имя библиотеки-провайдера БД, которую установили ранее. В параметре Connection — полную строку подключения к БД. Команда Scaffold-DbContext на основе подключённой базы автоматически реконструирует её структуру в виде моделей данных под каждую таблицу + класса DbContext.

Как мы видим, в проекте появились 4 новых файла:

• [Company/Employee/Department].cs — модели данных;

• DatabaseFirstDBContext.cs — контекст данных.

Сгенерированный код можно с лёгкостью кастомизировать, разложить по папкам/пространствам имён. Модели данных устроены просто — посмотрим на примере таблиц Employee и Department:

    public partial class Employee
    {
        public int Id { get; set; }
        public string Fullname { get; set; }
        public DateTime BirthDate { get; set; }
        public int DepartmentId { get; set; }

        public virtual Department Department { get; set; }
    }


    public partial class Department
    {
        public Department()
        {
            Employee = new HashSet<Employee>();
        }

        public int Id { get; set; }
        public string DepName { get; set; }
        public string AddInfo { get; set; }
        public int CompanyId { get; set; }

        public virtual Company Company { get; set; }
        public virtual ICollection<Employee> Employee { get; set; }
    }

Это самая что ни на есть обычная модель данных, знакомая тем, кто часто использует паттерны MVVM, MVC, и т.  д. в своих приложениях. Каждый столбец таблицы реализован с помощью стандартных автосвойств. Связь с другими таблицами реализуется в две стороны. В «нижестоящей» модели (Employee) присутствует экземпляр «вышестоящей» модели (Department). В «вышестоящей» модели, в свою очередь, присутствует коллекция экземпляров тех моделей, которые ссылаются на неё. Это позволяет без лишних хлопот ходить по их связям в обе стороны и не изобретать для этого «велосипед».

Теперь рассмотрим класс DatabaseFirstDBContext по частям:

public DatabaseFirstDBContext()
        {
        }

        public DatabaseFirstDBContext(DbContextOptions<DatabaseFirstDBContext> options)
            : base(options)
        {
        }

        public virtual DbSet<Company> Company { get; set; }
        public virtual DbSet<Department> Department { get; set; }
        public virtual DbSet<Employee> Employee { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer("Data Source=(localdb)\\MSSQLLOCALDB; Initial Catalog=DatabaseFirstDB");
            }
        }
    //=====остальной контент….=====
}

Здесь всё достаточно просто: 3 коллекции DbSet по числу моделей, куда EntityFramework перекладывает данные, которые потом будут доступны в приложении. Есть обработчик OnConfiguring, который в момент создания контекста задаёт опции по умолчанию. Соответственно, опции и их значения можно задавать как извне, подавая их потом конструктору, так и изнутри.

Также в контексте данных есть ещё один интересный обработчик OnModelCreating, через который настраиваются связи и параметры моделей:

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Company>(entity =>
            {
                entity.ToTable("company");

                entity.Property(e => e.Id).HasColumnName("id");

                entity.Property(e => e.CompanyName)
                    .IsRequired()
                    .HasColumnName("company_name")
                    .HasMaxLength(300)
                    .IsUnicode(false);
            });

            modelBuilder.Entity<Department>(entity =>
            {
                entity.ToTable("department");

                entity.Property(e => e.Id).HasColumnName("id");

                entity.Property(e => e.AddInfo)
                    .HasColumnName("add_info")
                    .HasMaxLength(3999)
                    .IsUnicode(false);

                entity.Property(e => e.CompanyId).HasColumnName("company_id");

                entity.Property(e => e.DepName)
                    .IsRequired()
                    .HasColumnName("dep_name")
                    .HasMaxLength(300)
                    .IsUnicode(false);

                entity.HasOne(d => d.Company)
                    .WithMany(p => p.Department)
                    .HasForeignKey(d => d.CompanyId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK__departmen__compa__25869641");
            });

            modelBuilder.Entity<Employee>(entity =>
            {
                entity.ToTable("employee");

                entity.Property(e => e.Id).HasColumnName("id");

                entity.Property(e => e.BirthDate)
                    .HasColumnName("birth_date")
                    .HasColumnType("date");

                entity.Property(e => e.DepartmentId).HasColumnName("department_id");

                entity.Property(e => e.Fullname)
                    .IsRequired()
                    .HasColumnName("fullname")
                    .HasMaxLength(300)
                    .IsUnicode(false);

                entity.HasOne(d => d.Department)
                    .WithMany(p => p.Employee)
                    .HasForeignKey(d => d.DepartmentId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK__employee__depart__2C3393D0");
            });

            OnModelCreatingPartial(modelBuilder);
        }

Если посмотреть на него внимательней, можно увидеть, что в данном примере настройка моделей сводится к настройке свойств и синхронизации видов 3 сущностей (по числу моделей):

• Entity<Employee>;

• Entity<Department>;

• Entity<Company>.

В свою очередь, настройки конкретной сущности в примере можно разбить на 3 группы:

А. Настройка названия таблицы в БД.

Методом entity.ToTable("employee") «подсказываем» Entity Framework, с какой таблицей синхронизировать коллекцию сущностей, если их название не совпадает с названием таблицы в БД.

Б. Настройка атрибутов сущности.

• HasColumnName — действует так же, как метод ToTable, только для столбца;

• IsRequired — аналог признака NOT NULL из SQL. Если оставить этот параметр пустым, будет вызвано исключение;

• HasColumnType — задаёт тип данных поля в БД, если в .NET такие типы данных отсутствуют;

• HasMaxLength — задаёт максимальную длину поля для типов данных VARCHAR, VARBINARY и т. д.;

• IsUnicode — задаёт, закодирован ли столбец в Юникоде.

В. Настройка связи с другими сущностями.

• HasOne — сущность, на которую мы ссылаемся (связь 1->M);

• WithMany — сущности, которые ссылаются на настраиваемую сущность (связь M<-1);

• HasForeignKey — продолжение метода WithMany — указывается внешний ключ, который связывает сущность из WithMany с текущей;

• OnDelete/OnUpdate — указывается метод обеспечения ссылочной целостности при удалении/изменении текущей сущности.

2. Code First

Теперь обсудим второй подход. Приём с автоматическим созданием модели данных можно проделать и в обратном направлении, если у вас в приложении она уже есть и теперь её надо переложить в БД со всеми связями и типами данных. Это и есть Code First. В случае его применения всё делается в обратном порядке: сначала вы пишете в приложении модели и контекст, затем маппируете эту структуру на базу. Для примера в уже готовом приложении сменим БД на пустую и добавим в конструктор контекста следующий код:

public DatabaseFirstDBContext()
        {
            Database.EnsureCreated();
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                optionsBuilder.UseSqlServer("Data Source=(localdb)\\MSSQLLOCALDB; Initial Catalog=CodeFirstDB");
            }
        }

С помощью метода Database.EnsureCreated Entity Framework при каждом запуске приложения будет проверять структуру БД на соответствие модели данных приложения и, если необходимо, переносить модель в БД.

Практическое применение

После настройки контекста данных проверим его работоспособность на простом запросе, в котором соединим сотрудников и департамент:

DatabaseFirstDBContext context = new DatabaseFirstDBContext();
var employees = context.Employee
                       .Include(x=>x.Department)
                       .ToList()

Я создал экземпляр контекста данных, через который я запросил список сотрудников. Чтобы привязанный к ним департамент был также доступен для взаимодействия, я использовал метод Include, который выполнит присоединение департамента. Без него в записях значение переменной Department будет равно null.

Теперь попробуем в список сотрудников добавить нового сотрудника.

var department = context.Department.Find(3);
var employee = new Employee 
{ 
     Fullname = "new test user",
     BirthDate = DateTime.Parse("2022-10-21"),
     DepartmentId = department.Id
};
context.Employee.Add(employee);
context.SaveChanges();

С этим тоже всё просто — мы сделали запись о новом сотруднике, задали её параметры, после чего поместили её в список сотрудников и зафиксировали изменения. Проверим, отображается ли новый сотрудник в БД и в приложении.

Таким же образом работает обновление и удаление записей (их конкретную реализацию я здесь не описываю).

Что в итоге?

При помощи Entity Framework можно автоматически создавать низкоуровневые интерфейсы взаимодействия приложения с БД либо саму БД. В случае если работа с данными сводится к CRUD и простым запросам, ORM-фреймворки помогают избавиться от рутины и сэкономить время. Если у вас есть свои предпочтения, методы работы, подходы и технологии, поделитесь ими в комментариях, пожалуйста.