Entity Framework 架构师高级 HTML 中级开发 SQL Server .NET C#

Entity Framework：将复杂属性存储为数据库中的 JSON 文本

Jovan Popovic(MSFT)

5.00/5 (37投票s)

2017年1月17日

CPOL

11分钟阅读

175186

1064

将 EF 模型中的属性映射到包含 JSON 的数据库列

下载源代码 - 791.9 KB

背景

Entity Framework 使您能够将 C# 模型类映射到 SQL 关系表。通常，模型类和关系表之间存在一对一的关系。如果模型类中的属性是简单/值类型（例如 `int`、`double`、`string`、`boolean`），则每个值类型都可以轻松地映射到底层表的列。

但是，对于模型类中的复杂属性，例如数组和对象，我们可以怎么做？

想象一下，您的模型中有一个 `Blog` 类，它有几个值类型的属性，例如 `BlogId`（`int`）、博客的 URL（`string`）以及与 `Post` 类的关系。这两个类都将作为 Blogs 和 Posts 表持久化到数据库中。

现在设想您想在 `Blog` 类中添加一些复杂属性，例如标签（字符串数组）或有关博客所有者的信息（单独的类）。

通常，您需要将它们映射到单独的表，因为您无法将字符串数组存储在表列中。但是，假设您不希望为每个非平凡属性创建一个单独的表，因此您不想为这些属性创建额外的 `BlogsTags`、`Tags` 和 `Person` 表。

SQL Server 2016 和 Azure SQL Database（在以下部分中，我将它们称为 SQL Database）使您能够存储混合数据，包括关系表和列，但您也可以将某些属性存储为格式化为 JSON 文本的复杂结构。

您可以创建一个表，该表可以同时具有标准列和与其他表的关联，还可以包含格式化为 JSON 的半结构化信息，存储在标准的 `NVARCHAR` 列中。

这可能是处理一些轻量级对象的好方法，例如简单的数组或键值字典，您希望将它们保留在主要实体中。

现在，主要问题是如何在 Entity Framework 中使用这些类型的字段来序列化数组和较小对象等结构？

在本文中，我将向您展示如何将 SQL 表中的 JSON 字段映射到 EF 模型类。

引言

此示例基于 EF 博客中使用的标准 Blog 示例。我们将从 EF 模型中简单的 Blog/Post 类开始。

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; }

}

`Blog` 和 `Post` 模型类映射到 SQL 数据库中的 `Blogs` 和 `Posts` 表。

在此示例中，在 `Blog` 模型类中添加了以下更改：

在 EF 模型中添加了 `string[]` 类型的 `Tags` 属性，表示与博客关联的标签。标签在数据库中存储为 JSON 字符串数组。
添加了 `Owner` 属性，类型为 `Person`，包含有关所有者姓名、姓氏和电子邮件地址的信息。此对象在数据库中存储为带有 `Name`、`Surname` 和 `Email` 键的 JSON 对象。

在下一节中，您可以看到如何使用这些属性。

数据库设置

首先，让我们看看 `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 文本。由于我们不打算在 C# 应用程序中使用原始 JSON 文本，因此这些属性被标记为内部。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");
}

我们将创建两个包装属性来反序列化 JSON 文本为 `string[]` 和 `Person` 对象，并在有人设置值时存储序列化的 JSON 表示。这些将是 `public` 属性，返回将在我们的应用程序中使用的实际类型。

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 `string` 将被存储到 JSON 列中，反之亦然。由于外部代码只使用表示 JSON 对象 C# 等效项的 `public` 属性，因此没有人会知道这些属性是如何序列化的。

使用 JSON 字段的 ASP.NET MVC 应用程序

在本文的这一部分，我将描述一个简单的 ASP.NET MVC 应用程序，该应用程序使您能够：

显示所有博客，包括存储在标准表列中的值类型属性，以及格式化为 JSON 的复杂属性。
通过输入值类型属性和复杂属性来创建新的博客。
按存储在 `Person` 类中并格式化为 JSON 键的所有者姓名搜索博客。

显示 JSON 字段

一旦我们将模型属性映射到 JSON 列，我们就可以显示来自 JSON 列的信息。

我们需要一个视图来显示 `Tags` 和 `Owner` 属性。在此示例中，我将在 `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>

`BlogId` 和 `Url` 属性使用标准的 `DisplayFor` 方法显示。请注意，即使 JSON 字段 `Owner.Name`、`Owner.Surname` 和 `Owner.Email` 也可以使用此方法显示，因为它们已加载到标准的 C# 类中。视图无法知道这些字段是从 JSON 文本还是单独的 `Person` 表加载的。为了避免为 `string[]` 使用自定义模板，我只显示了用逗号分隔的标签。

Entity Framework 将从数据库读取 `Blog` 对象，并填充内部 `_Tags` 和 `_Owner` 字段。`Tags` 和 `Owner` 属性将反序列化从数据库获取的 JSON 文本，并将其转换为 `blog` 类中的 `string[]` 和 `Person` 属性。因此，您可以在视图中像显示其他字段一样显示它们。

插入 JSON 字段

我们可以创建一个新的博客，并输入将插入到标准列和 JSON 字段中的字段，如下图所示。

`Url` 是 `Blog` 类中标准 `Url` 属性的文本输入。标签使用多选列表选择，而 `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>

在此示例中，我使用多选列表来添加标签数组，并使用标准输入字段来处理其他属性。有两件重要的事情您应该注意到：

多选列表的名称为“`Tags[]`”。这使 ASP.NET 能够将每个选定的值作为 `Tags` 属性中 `string` 数组的单独元素获取。
我需要在用于输入 `Owner` 属性数据的字段中输入“`Owner.Name`”、“`Owner.Surname`”和“`Owner.Email`”的名称。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 Database 还允许您使用 `CHECK` 约束对将存储在 `NVARCHAR` 单元格中的 JSON 文本添加一些验证，例如：

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

但是，最好尽早验证字段（例如，在视图中）。

按 JSON 字段搜索

最后，我们能够使用存储在复杂属性中的数据来搜索博客。在此示例中，我将向您展示如何按存储在 `Owner` 属性中的所有者姓名过滤博客。我们有两种方法：

.NET 过滤，您可以创建 LINQ 查询来按 `Owner` 属性过滤 `Blog` 模型对象。
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 查询，该查询将使用 `JSON_VALUE` T-SQL 函数直接在数据库中搜索博客。`JSON_VALUE` 是一个解析数据库中的 JSON 文本并返回指定 JavaScript 风格路径的值的函数。

然后，您可以使用 `ForSQL` 方法执行此 T-SQL 查询。

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` 方法将直接在 SQL Database 上执行 T-SQL 命令，并使用 `JSON_VALUE` 函数从存储在 `Owner` 列中的 JSON 中获取键 `$.Name` 的值。然后，它将与提供的所有者姓名进行比较，并返回 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 Database 找到一个以过滤博客的 `JSON_VALUE(Owner, '$.Name')` 表达式为谓词的查询时，它将使用此索引来加速查询。

注意：无法保证索引在每个查询中都会实际使用，因为 SQL Database 可能会选择不同的计划。在此示例中，如果 `Blogs` 表只有 3 行，则索引将不会被使用。

可以使此功能更进一步的事情（愿望清单）

尽管我们有办法将 EF 模型和 SQL JSON 绑定在一起，但 Entity Framework 模型仍有改进的空间。我将提及我列表中的两件事。在 EF Core 中，GithHub 上有一个请求，要求在 EF Core 中添加更好的支持 https://github.com/aspnet/EntityFramework/issues/4021，您可以在那里添加评论。以下是我最看重的两点。

声明式映射到 JSON 列

如果能够以声明式方式定义某个属性应序列化为 JSON，那将是非常棒的。虽然创建两个单独的属性并配置它们并不难，但拥有像 `[Serialization(JSON)]` 这样的属性来映射属性到 JSON 列，而无需处理 `JsonConvert` 类的转换，这将是一种优雅的方式。

在底层，它可能会做同样的事情，因为它必须将数据库中的 `string` 转换为 C# 对象。但是，只添加一个属性会更优雅。

JSON 过滤器下推与 LINQ

最大的缺失功能仍然是 LINQ 查询未映射到 T-SQL 查询，因此 EF 无法利用 SQL Database 可以在数据库层过滤或排序 JSON 属性的事实。

SQL Database 提供了新的 `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 `string` 数组，并将它们转换为 SQL 表格式，这样您就可以将它们与父行连接，并过滤掉不满足条件的行。这样，大多数行将直接在数据库层过滤掉，而根本不会到达 .NET 层。
按 `Owner` 属性查询博客 - 我们可以尝试使用某些所有者状态查找 10 个博客，按 `Email` 对它们进行排序，并返回 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` 函数将获取 `WITH` 子句中指定的 JSON 属性，并将它们转换为指定类型，以便您可以使用这些值进行过滤或排序。

所以，目前我们有两个选择：

使用 LINQ 查询，它将获取所有内容并在 .NET 层处理对象。
编写 T-SQL 查询，它将在数据库中过滤和排序，并且只获取所需的条目。

希望这两个方法将来能够合并，使我们能够编写 LINQ 查询，这些查询可以下推到数据库层（如果可能）。

结论

本文的目的是展示如何轻松地在 SQL Database 中组合关系数据和 JSON 数据，并在 Entity Framework 模型中使用它们。现在，您可以选择最适合您的数据库和 EF 模型建模方式，并定义哪些属性应映射到表列，哪些属性应格式化为 JSON 单元格。

关系和 NoSQL 概念在数据库中的集成为您提供了更大的 Entity Framework 模型自由度。

历史

2017 年 1 月 17 日：初始版本

Entity Framework： 将复杂属性存储为数据库中的 JSON 文本

背景

引言