Entity Framework: хранение сложных свойств в виде текста JSON в базе данных

Tags: JSON, .NET, T-SQL, SQL

Wednesday, January 2, 2019 6:32:29 PM

Сложные свойства в моделях Entity Framework, таких как массивы, словари и объекты, можно сериализовать в базе данных SQL Server в формате JSON. В этой статье мы покажем вам, как сопоставить ваши свойства в модели EF со столбцами базы данных, которые содержат JSON.

Бэкграунд

Entity Framework позволяет отображать классы модели C # в реляционных таблицах SQL. Обычно между классами моделей и реляционными таблицами существует отношение “один-к-одному”. Если у вас есть свойства в модельных классах с простыми типами или значениями (например, int, double, string, boolean), каждый тип значения может быть легко сопоставлен со столбцами базовой таблицы.

Однако что мы можем сделать со сложными свойствами в модельных классах, таких как массивы и объекты?

Представьте, что у вас есть класс Blog в вашей модели, который имеет несколько свойств типа значения, таких как BlogId (int), URL-адрес блога (string) и связь с классом Post. Оба класса сохраняются в базе данных в виде таблиц Blogs и Posts.

Теперь представьте, что вы хотите добавить в класс Blog некоторые сложные свойства, такие как теги (массив строк) или информацию о владельце блога (отдельный класс).

 

Обычно вам нужно сопоставить их с отдельными таблицами, потому что вы не можете хранить массив строк в столбце таблицы. Однако давайте предположим, что вам не нужна отдельная таблица для каждого нетривиального свойства, поэтому вы не хотите создавать дополнительные таблицы BlogsTags, Tags и Person для этих свойств.

SQL Server 2016 и база данных SQL Azure (мы будем называть их базой данных SQL в следующих разделах) позволяют хранить гибридные данные как с реляционными таблицами, так и со столбцами, но также можно сохранять некоторые свойства в виде сложных структур, отформатированных в виде текста JSON.

Вы можете создать таблицу, которая может иметь как стандартные столбцы, так и отношения с другими таблицами, а также некоторую полуструктурированную информацию, отформатированную как JSON в стандартных столбцах NVARCHAR:

 

Это может быть хорошим решением для некоторых легких объектов, таких как простые массивы или словари key: value, которые вы хотите сохранить как часть своего основного объекта.

Теперь главный вопрос: как мы можем использовать такие поля в Entity Framework для сериализации некоторых структур в виде массивов и более мелких объектов?

В этой статье мы покажем вам, как отобразить поля JSON из таблиц SQL в классы модели EF.

Вступление

Этот образец построен поверх стандартного образца блога, который используется в блоге EF. Мы начнем с простых классов Blog / Post в модели EF:

public class Blog
{
    public int BlogId { get; set; }

    public string Url { get; set; }

    public List<Post> Posts { get; set; }
}

public class Post
{

        public int PostId { get; set; }

        public string Title { get; set; }

        public string Content { get; set; }

        public int BlogId { get; set; }

        public Blog Blog { get; set; }

}

 

Классы блога и модели постов отображаются в таблицы блогов и постов в базе данных SQL.

В этом примере в класс модели Blog добавлены следующие изменения:

  1. Добавлена строка [] Теги свойство в модели EF, которая представляет теги, связанные с блогом. Теги хранятся в базе данных как строка массива json.
  2. Добавлено свойство Owner с типом Person, которое содержит информацию об имени владельца, фамилии и адресе электронной почты. Этот объект хранится в базе данных как объект JSON с ключами  Name, Surname и Email

В следующем разделе вы можете увидеть, как вы можете использовать эти свойства.

Настройка базы данных

Сначала давайте рассмотрим схему таблицы Blogs:

CREATE TABLE Blogs (
    BlogId int PRIMARY KEY IDENTITY,
    Url nvarchar(4000) NOT NULL,
    Tags nvarchar(4000),
    Owner nvarchar(4000)
);

INSERT INTO Blogs (Url, Tags, Owner) VALUES
('http://blogs.msdn.com/dotnet', '[".Net", "Core", "C#"]','{"Name":"John","Surname":"Doe","Email":"john.doe@contoso.com"}'),
('http://blogs.msdn.com/webdev', '[".Net", "Core", "ASP.NET"]','{"Name":"Jane","Surname":"Doe","Email":"jane@contoso.com"}'),
('http://blogs.msdn.com/visualstudio', '[".Net", "VS"]','{"Name":"Jack","Surname":"Doe","Email":"jack.doe@contoso.com"}');

Это тот же сценарий, который используется в примере EntityFramework. Единственным интересным моментом здесь является тот факт, что у нас есть дополнительные столбцы, которые будут содержать текст JSON для тегов и информацию о владельце.

Сопоставление столбцов JSON со свойствами EF

Чтобы отобразить столбцы Tags и Owner JSON, нам потребуются отдельные свойства для них. Мы добавим два внутренних свойства _Tags и _Owner, которые будут использоваться для сопоставления этих столбцов:

public class Blog
{
   // Other properties are not displayed
   internal string _Tags { get; set; }

   internal string _Owner { get; set; }
}

Эти два поля будут содержать текст JSON, взятый из базы данных. Поскольку мы не будем использовать оригинальный текст JSON в нашем приложении C #, эти свойства помечаются как внутренние. EF игнорирует внутренние поля в отображениях, поскольку нам нужно определить в методе OnModelCreating, что эти свойства должны использоваться, и что они должны быть сопоставлены со столбцами Tags и Owner:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Blog>()
        .Property(b => b._Tags).HasColumnName("Tags");

    modelBuilder.Entity<Blog>()
        .Property(b => b._Owner).HasColumnName("Owner");
}

Cоздадим два свойства-оболочки вокруг них, которые будут десериализовать текст JSON в строку [] и объект Person, и сохранят сериализованное представление JSON, когда кто-то установит значения. Это будут открытые свойства, которые возвращают фактические типы, которые будут использоваться в нашем приложении:

public class Blog
{
   // Other properties are not displayed
   
   [NotMapped]
   public string[] Tags
   {
       get { return _Tags == null ? null : JsonConvert.DeserializeObject<string[]>(_Tags); }
       set { _Tags = JsonConvert.SerializeObject(value); }
   }

   [NotMapped]
   public Person Owner
   {
       get { return _Owner == null ? null : JsonConvert.DeserializeObject<Person>(_Owner); }
       set { _Owner = JsonConvert.SerializeObject(value); }
   }
}

Обратите внимание, что свойства Tags и Owner не отображаются в таблице базы данных. Эти свойства рассчитываются только при заполнении вспомогательных свойств _Tags и _Owner.

Поскольку у нас есть собственный класс Person, который содержит информацию о владельце, нам нужен класс Person в модели.

public class Person {
    public string Name { get; set; }

    public string Surname { get; set; }

    public string Email { get; set; }
}

Поскольку это всего лишь служебный класс, который используется для определения свойств в свойстве Owner, он не сопоставляется с таблицей базы данных. Эта таблица будет сериализована или десериализирована с использованием класса JsonConvert в коде свойства класса Blog. При желании вы можете добавить некоторые атрибуты JSON.Net, чтобы настроить сериализацию полей в столбец JSON.

Вот и все - всякий раз, когда вы моделируете класс, читаемый из базы данных или когда контекст сохраняется, строка JSON в этих свойствах будет храниться в столбцах JSON, и наоборот. Поскольку внешний код использует только открытые свойства, которые представляют эквиваленты C # объектов JSON, никто не будет знать, как эти свойства сериализуются.

Приложение ASP.NET MVC, которое работает с полями JSON

В этой части статьи мы опишем простое приложение APS.NET MVC, которое позволит вам:

  • Отображать все блоги со свойствами типа значения, хранящимися в стандартных столбцах таблицы, и со сложными свойствами, отформатированными в формате JSON.
  • Создавать новый блог, указав свойства типа значения и сложные свойства.
  • Осуществлять поиск блогов по имени владельца, который хранится в классе Person и отформатирован как ключ JSON.

Отображение полей JSON

Как только мы сопоставим свойства модели со столбцами JSON, мы сможем отобразить информацию из столбцов JSON.

Нам нужно представление, которое показывает теги и свойства владельца. В этом примере мы покажем блоги на странице Blog / Index.cshtml в таблице. В этой таблице будут показаны свойства Id и Url из стандартных столбцов, а также теги, имя / фамилия владельца и адрес электронной почты, которые считываются из столбцов JSON:

<table class="table">
    <tr>
        <th>Id</th>
        <th>Url</th>
        <th>Tags</th>
        <th>Owner</th>
        <th>Email</th>
    </tr>
    @foreach (var item in Model)
    {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.BlogId)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Url)
            </td>
            <td>
                @(item.Tags==null?String.Empty:String.Join(", ", item.Tags))
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Owner.Name)
                @Html.DisplayFor(modelItem => item.Owner.Surname)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Owner.Email)
            </td>
        </tr>
    }
</table>

Свойства ID и Url блога отображаются стандартным методом DisplayFor. Обратите внимание, что даже поля JSON Owner.Name, Owner.Surname и Owner.Email могут быть показаны с помощью этого метода, поскольку они загружаются в стандартный класс C #. Представление не может знать, загружены ли эти поля из текста JSON или отдельной таблицы Person. Чтобы избежать пользовательского шаблона для строки [], мы только что показали теги, разделенные запятой.

Entity Framework будет читать объекты Blog из базы данных и заполнять внутренние поля _Tags и _Owner. Свойства тэгов и владельцев будут десериализовать текст JSON, взятый из базы данных, и преобразовать его в строки [] и свойства Person в классе блога. Поэтому вы можете показать их на виде как любое другое поле.

 

Вставка полей JSON

Мы можем создать новый блог и ввести поля, которые будут вставлены как в стандартные столбцы, так и в поля JSON, как показано на следующем рисунке:

 

Url - это текстовый ввод для стандартного свойства Url в классе Blog. Теги выбираются с помощью списка множественного выбора, а поля свойства Owner (которые будут храниться как объект JSON в базе данных) вводятся как три отдельных текстовых поля.

 

Представление Blogs / Create.cshtml показано в следующем коде:

<form asp-controller="Blogs" asp-action="Create" method="post" class="form-horizontal" role="form">

<div class="form-horizontal">
<div class="form-group">

<label asp-for="Url" class="col-md-2 control-label"></label>
<div class="col-md-10">
    <input asp-for="Url" class="form-control" />
</div>

<label asp-for="Tags" class="col-md-2 control-label"></label>
<div class="col-md-10">
    <select name="Tags[]" class="form-control" multiple>
        <option value="C#">C#</option>
        <option value="ASP.NET">ASP.NET</option>
        <option value=".NET Core">.NET Core</option>
        <option value="SQL Server">SQL Server</option>
        <option value="VS">Visual Studio</option>
        <option value="Azure">Azure</option>
    </select>
</div>
<label asp-for="Owner.Name" class="col-md-2 control-label"></label>
<div class="col-md-10">
    <input asp-for="Owner.Name" class="form-control" />
</div>
<label asp-for="Owner.Surname" class="col-md-2 control-label"></label>
<div class="col-md-10">
    <input asp-for="Owner.Surname" class="form-control" />
</div>
<label asp-for="Owner.Email" class="col-md-2 control-label"></label>
<div class="col-md-10">
    <input asp-for="Owner.Email" class="form-control" />
</div>
</div>
<div class="form-group">
    <div class="col-md-offset-2 col-md-10">
        <input type="submit" value="Create" class="btn btn-default" />
    </div>
</div>
</div>
</form>

В этом примере мы используем список множественного выбора для добавления массива тегов и стандартные поля ввода для других свойств. Есть две важные вещи, которые вы должны заметить:

  1. Список с множественным выбором имеет название «Tags[]». Это позволяет ASP.NET получать каждое выбранное значение как отдельный элемент массива строк в свойстве Tags.
  2. Нам нужно указать имена «Owner.Name», «Owner.Surname» и «Owner.Email» в полях, которые используются для ввода данных из свойства Owner. ASP.NET будет знать, как отобразить их, используя эти пути.

В действии контроллера нам не нужен специальный код:

[HttpPost]
[ValidateAntiForgeryToken]
public IActionResult Create(Blog blog)
{
    if (ModelState.IsValid)
    {
        _context.Blogs.Add(blog);
        _context.SaveChanges();
        return RedirectToAction("Index");
    }

    return View(blog);
}

ASP.NET MVC возьмет все поля ввода и поместит их в параметр Blog без дополнительного кода.

Проверка

Вы можете просто добавить все необходимые аннотации в классе Person, и этого достаточно для проверки:

public class Person
{

    [Required]
    public string Name { get; set; }

    public string Surname { get; set; }

    [EmailAddress]
    public string Email { get; set; }

}

В Blogs/Create.cshtml мне нужно разместить стандартные теги валидатора asp, которые ссылаются на эти поля:

<div asp-validation-summary="All" class="text-danger"></div>
<label asp-for="Url" class="col-md-2 control-label"></label>
<div class="col-md-10">
    <input asp-for="Url" class="form-control" />
    <span asp-validation-for="Url" class="text-danger"></span>
</div>

<label asp-for="Tags" class="col-md-2 control-label"></label>
<div class="col-md-10">
    <select name="Tags[]" class="form-control" multiple>
        <option value="C#">C#</option>
        <option value="ASP.NET">ASP.NET</option>
        <option value=".NET Core">.NET Core</option>
        <option value="SQL Server">SQL Server</option>
        <option value="VS">Visual Studio</option>
        <option value="Azure">Azure</option>
    </select>
    <span asp-validation-for="Tags" class="text-danger"></span>
</div>

<label asp-for="Owner.Name" class="col-md-2 control-label"></label>
<div class="col-md-10">
    <input asp-for="Owner.Name" class="form-control" />
    <span asp-validation-for="Owner.Name" class="text-danger"></span>
</div>

<label asp-for="Owner.Surname" class="col-md-2 control-label"></label>
<div class="col-md-10">
    <input asp-for="Owner.Surname" class="form-control" />
    <span asp-validation-for="Owner.Surname" class="text-danger"></span>
</div>

<label asp-for="Owner.Email" class="col-md-2 control-label"></label>
<div class="col-md-10">
    <input asp-for="Owner.Email" class="form-control" />
    <span asp-validation-for="Owner.Email" class="text-danger"></span>
</div>

ASP.NET применяет проверку сложных свойств, прежде чем они будут сериализованы в JSON. Таким образом, вы можете проверить правильность вставленного текста, прежде чем сериализовать его как JSON.

Примечание. База данных SQL также позволяет добавлять некоторые проверки текста JSON, который будет храниться в ячейке NVARCHAR с использованием ограничения CHECK, например:

ALTER TABLE Blogs
ADD CONTRAINT BlogsOwnerRules AS
CHECK( ISJSON(Owner) = 1 AND JSON_VALUE(Owner, ‘$.Name’) IS NOT NULL)

Однако лучше проверить поля как можно скорее (т.е. в представлении).

Поиск по полям JSON

Наконец, мы можем искать блоги, используя данные, хранящиеся в сложных свойствах. В этом примере я покажу вам, как фильтровать блоги по имени владельца, которое хранится в свойстве Owner. У нас есть два подхода:

  1. .Net фильтрация, где вы можете создать запрос LINQ, который будет фильтровать объекты модели блога по свойствам владельца.
  2. Фильтрация T-SQL, где вы можете создать запрос T-SQL, который будет искать блоги по свойствам JSON непосредственно в базе данных.

Фильтрация с использованием запросов LINQ

Поскольку поля JSON материализуются как объекты C # в модели, вы можете использовать стандартный LINQ для поиска блогов по имени владельца:

public IActionResult Search(string Owner)
{
    // Option 1: .Net side filter using LINQ:
    var blogs = _context.Blogs
                    .Where(b => b.Owner.Name == Owner)
                    .ToList();

    return View("Index", blogs);
}

Этот код будет принимать все блоги и сохранять те, которые удовлетворяют условию, что Owner.Name совпадает с входным параметром.

Фильтрация с использованием запросов T-SQL

Другой подход - написать запрос T-SQL, который будет искать блоги непосредственно в базе данных, используя функцию T-SQL JSON_VALUE, которая анализирует текст JSON в базе данных и возвращает значение указанного пути по подобию JavaScript.

Затем вы можете выполнить этот запрос T-SQL, используя метод ForSQL:

public IActionResult Search(string Owner)
{
    var blogs = _context.Blogs
                    .FromSql<Blog>(@"SELECT * FROM Blogs
                                    WHERE JSON_VALUE(Owner, '$.Name') = {0}", Owner)
                    .ToList();

    return View("Index", blogs);
}

Метод FromSql будет непосредственно выполнять команду T-SQL в базе данных SQL и использовать функцию JSON_VALUE, чтобы получить значение ключа $ .Name из JSON, хранящегося в столбце Owner. Затем он сравнивает его с указанным именем владельца и возвращает блоги с таким значением в столбце JSON.

Если вы используете сырой T-SQL, вы можете даже применить индексирование в своем запросе T-SQL. В этом примере вы можете добавить вычисляемый столбец, который будет представлять значение имени владельца, а затем создать индекс для этого вычисляемого столбца:

-- Add indexing on Name property in JSON column:
ALTER TABLE Blogs
       ADD OwnerName AS JSON_VALUE(Owner, '$.Name');

CREATE INDEX ix_OwnerName
       ON Blogs(OwnerName);

Всякий раз, когда база данных SQL находит запрос с предикатом, который фильтрует блоги по выражению JSON_VALUE (Owner, '$ .Name'), он может использовать этот индекс, который ускорит запросы.

Примечание: нет никаких гарантий, что индекс будет фактически использоваться в каждом запросе, поскольку база данных SQL может выбирать разные планы. В этом примере индекс не будет использоваться, если в таблице Blogs всего 3 строки.

Что может сделать это еще лучше?

Хотя у нас есть способ связать модель EF и SQL JSON, есть место для дальнейших улучшений модели Entity Framework. Упомянем две важные, по нашему мнению, вещи.

Декларативное отображение в столбцы JSON

Было бы здорово, если бы у нас был декларативный способ определить, что какое-то свойство должно быть сериализовано как JSON. Хотя нетрудно создать два отдельных свойства и настроить их, иметь что-либо в качестве атрибута [Serialization (JSON)] в поле было бы элегантным способом отображения свойств в столбцы JSON без обработки преобразований с помощью класса JsonConvert.

Внутри он, вероятно, сделал бы то же самое, потому что он должен преобразовать строку из базы данных в объекты C #. Однако было бы более элегантно просто добавить атрибут.

Фильтр JSON с нажатием кнопки LINQ

Самым большим отсутствующим свойством по-прежнему является тот факт, что запросы LINQ не отображаются на запросы T-SQL, поэтому EF не может использовать тот факт, что база данных SQL может фильтровать или сортировать свойства JSON на уровне базы данных.

База данных SQL предоставляет новую функцию OPENJSON, которая может десериализовать текст JSON из столбца и возвращать свойства из текста JSON. Эти свойства могут быть использованы в любой части запроса. Таким образом, вы можете легко написать T-SQL, который фильтрует блоги по некоторому тегу или свойству в столбце Owner.

Примером запросов LINQ и эквивалентов T-SQL являются:

  • Фильтрация блогов по имени тега - в LINQ вы можете написать что-то вроде:
_context.Blogs.Where(b => b.Tags.Contains("C#"));

В настоящее время EF будет принимать все записи в блоге и применять фильтр в слое .Net. Этот запрос может быть преобразован в следующий запрос T-SQL, который может быть выполнен на уровне базы данных:

SELECT b.*
 FROM Blogs b
      CROSS APPLY OPENJSON(Tags) WITH (value nvarchar(400)) tags
 WHERE tags.value = 'C#'

Функция OPENJSON получит массив строк JSON и преобразует их в формат таблицы SQL, чтобы вы могли объединить их с родительской строкой и отфильтровать строки, которые не удовлетворяют критерию. Таким образом, большинство строк будут отфильтрованы непосредственно на уровне базы данных и вообще не достигнут уровня .Net.

 

  • Запрос блогов по свойствам  Owner - мы можем попытаться найти 10 блогов, используя какой-то определенный статус владельца, отсортировать их по электронной почте и вернуть имя из столбца JSON.

 

_context.Blogs
         .Where(b => b.Owner.Status == 1)
         .OrderBy(b => b.Owner.Email)
         .Take(10)
         .Select(b => b.Owner.Name);

Этот запрос может быть приведен к:

SELECT TOP 10 Name
FROM Blogs b
      CROSS APPLY OPENJSON( b.Owner ) WITH (Status int, Name nvarchar(400))
WHERE State = 1
ORDER BY Email

В этом случае функция OPENJSON примет свойства JSON, указанные в предложении WITH, преобразует их в указанные типы, чтобы вы могли фильтровать или сортировать результаты, используя эти значения.

Итак, на данный момент у нас есть два варианта:

  1. Использование запросоа LINQ, которые будут принимать все и обрабатывать объекты в слое .Net
  2. Написание запросов T-SQL, которые будут фильтровать и сортировать в базе данных и получать только необходимые записи.

Надеемся, что эти два подхода будут объединены в будущем и позволят нам писать запросы LINQ, которые будут перенесены на уровень базы данных, где это возможно.

Заключение

Цель этой статьи - показать, как легко вы можете комбинировать реляционные и JSON-данные в базе данных SQL и использовать их в моделях Entity Framework. Теперь вы можете выбрать лучший способ моделирования вашей базы данных и EF-моделей, а также определить, какие свойства должны быть сопоставлены со столбцами таблицы, а какие свойства должны быть отформатированы как ячейки JSON.

Интеграция реляционных и NoSQL-концепций в базе данных дает вам больше свободы в моделях Entity Framework. Вы можете найти исходный код из статьи здесь: Download

No Comments

Add a Comment