复杂类型与 JSON 列支持

概述

EF Core 8+ 引入了原生 JSON 列支持,允许将复杂对象序列化为 JSON 存储在关系数据库中。这结合了关系数据库的结构化优势和 NoSQL 的灵活性。

核心特性

特性	说明
Owned Entities	拥有的实体类型,映射为 JSON 列
JSON 查询	直接查询 JSON 字段(无需客户端评估)
嵌套对象	支持多层嵌套的复杂结构
集合支持	存储 List/Array 等集合类型
数据库支持	SQL Server 2016+, PostgreSQL, MySQL 5.7+

Owned Entities 基础

定义复杂类型

csharp

// Address.cs - 值对象
public class Address
{
    public string Street { get; set; } = null!;
    public string City { get; set; } = null!;
    public string State { get; set; } = null!;
    public string ZipCode { get; set; } = null!;
    public string Country { get; set; } = null!;
}

// Customer.cs
public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    public string Email { get; set; } = null!;
    
    // 复杂类型 - 将存储为 JSON
    public Address ShippingAddress { get; set; } = null!;
    public Address? BillingAddress { get; set; }
}

配置 Owned Entity

csharp

// AppDbContext.cs
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Customer>(entity =>
    {
        entity.HasKey(c => c.Id);
        
        // 配置 Owned Entity
        entity.OwnsOne(c => c.ShippingAddress, address =>
        {
            address.Property(a => a.Street).HasColumnName("ShippingStreet");
            address.Property(a => a.City).HasColumnName("ShippingCity");
            address.Property(a => a.State).HasColumnName("ShippingState");
            address.Property(a => a.ZipCode).HasColumnName("ShippingZipCode");
            address.Property(a => a.Country).HasColumnName("ShippingCountry");
        });
        
        // 可选的地址
        entity.OwnsOne(c => c.BillingAddress, address =>
        {
            address.Property(a => a.Street).HasColumnName("BillingStreet");
            address.Property(a => a.City).HasColumnName("BillingCity");
            address.Property(a => a.State).HasColumnName("BillingState");
            address.Property(a => a.ZipCode).HasColumnName("BillingZipCode");
            address.Property(a => a.Country).HasColumnName("BillingCountry");
        });
    });
}

生成的表结构

sql

-- Customers 表
CREATE TABLE Customers (
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(100) NOT NULL,
    Email NVARCHAR(255) NOT NULL,
    
    -- ShippingAddress 展开为列
    ShippingStreet NVARCHAR(200),
    ShippingCity NVARCHAR(100),
    ShippingState NVARCHAR(50),
    ShippingZipCode NVARCHAR(20),
    ShippingCountry NVARCHAR(50),
    
    -- BillingAddress 展开为列(可空)
    BillingStreet NVARCHAR(200),
    BillingCity NVARCHAR(100),
    BillingState NVARCHAR(50),
    BillingZipCode NVARCHAR(20),
    BillingCountry NVARCHAR(50)
);

JSON 列存储

使用 JSON 列(推荐)

csharp

// EF Core 8+ 新特性
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Customer>(entity =>
    {
        entity.HasKey(c => c.Id);
        
        // ✅ 将整个 Address 对象存储为 JSON 列
        entity.OwnsOne(c => c.ShippingAddress, address =>
        {
            address.ToJson();  // ← 关键配置
        });
        
        entity.OwnsOne(c => c.BillingAddress, address =>
        {
            address.ToJson();
        });
    });
}

生成的表结构(JSON)

sql

-- Customers 表(简化)
CREATE TABLE Customers (
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(100) NOT NULL,
    Email NVARCHAR(255) NOT NULL,
    
    -- JSON 列
    ShippingAddress NVARCHAR(MAX),  -- 存储: {"Street":"...","City":"..."}
    BillingAddress NVARCHAR(MAX)
);

-- 示例数据:
-- ShippingAddress: {"Street":"123 Main St","City":"Seattle","State":"WA","ZipCode":"98101","Country":"USA"}

使用示例

csharp

// 创建客户
var customer = new Customer
{
    Name = "John Doe",
    Email = "john@example.com",
    ShippingAddress = new Address
    {
        Street = "123 Main St",
        City = "Seattle",
        State = "WA",
        ZipCode = "98101",
        Country = "USA"
    },
    BillingAddress = new Address
    {
        Street = "456 Oak Ave",
        City = "Portland",
        State = "OR",
        ZipCode = "97201",
        Country = "USA"
    }
};

context.Customers.Add(customer);
await context.SaveChangesAsync();

// 查询
var customers = await context.Customers
    .Where(c => c.ShippingAddress.City == "Seattle")  // ✅ 服务器端过滤
    .ToListAsync();

// 更新
customer.ShippingAddress.City = "Bellevue";
await context.SaveChangesAsync();

嵌套复杂类型

多层嵌套

csharp

// PhoneNumber.cs
public class PhoneNumber
{
    public string CountryCode { get; set; } = "+1";
    public string AreaCode { get; set; } = null!;
    public string Number { get; set; } = null!;
}

// ContactInfo.cs
public class ContactInfo
{
    public PhoneNumber Phone { get; set; } = null!;
    public PhoneNumber? Mobile { get; set; }
    public string Email { get; set; } = null!;
    public Address Address { get; set; } = null!;
}

// Customer.cs
public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    
    // 嵌套复杂类型
    public ContactInfo Contact { get; set; } = null!;
}

// 配置
modelBuilder.Entity<Customer>(entity =>
{
    entity.OwnsOne(c => c.Contact, contact =>
    {
        contact.ToJson();
        
        // 嵌套的 Owned Entity
        contact.OwnsOne(ci => ci.Phone, phone =>
        {
            phone.ToJson();
        });
        
        contact.OwnsOne(ci => ci.Mobile, mobile =>
        {
            mobile.ToJson();
        });
        
        contact.OwnsOne(ci => ci.Address, address =>
        {
            address.ToJson();
        });
    });
});

// 生成的 JSON:
/*
{
  "Phone": {
    "CountryCode": "+1",
    "AreaCode": "206",
    "Number": "555-1234"
  },
  "Mobile": {
    "CountryCode": "+1",
    "AreaCode": "425",
    "Number": "555-5678"
  },
  "Email": "john@example.com",
  "Address": {
    "Street": "123 Main St",
    "City": "Seattle",
    "State": "WA",
    "ZipCode": "98101",
    "Country": "USA"
  }
}
*/

集合类型

List/Array 支持

csharp

// ProductTag.cs
public class ProductTag
{
    public string Name { get; set; } = null!;
    public string Color { get; set; } = "#000000";
}

// Product.cs
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; } = null!;
    public decimal Price { get; set; }
    
    // 标签集合 - 存储为 JSON 数组
    public List<ProductTag> Tags { get; set; } = new();
    
    // 规格字典
    public Dictionary<string, string> Specifications { get; set; } = new();
}

// 配置
modelBuilder.Entity<Product>(entity =>
{
    entity.HasKey(p => p.Id);
    
    // 集合类型
    entity.OwnsMany(p => p.Tags, tag =>
    {
        tag.ToJson();
    });
    
    // 字典类型(PostgreSQL/MySQL 支持更好)
    entity.Property(p => p.Specifications)
          .HasColumnType("jsonb");  // PostgreSQL
};

// 使用
var product = new Product
{
    Name = "Laptop",
    Price = 999.99m,
    Tags = new List<ProductTag>
    {
        new() { Name = "Electronics", Color = "#FF0000" },
        new() { Name = "Computers", Color = "#00FF00" },
        new() { Name = "Portable", Color = "#0000FF" }
    },
    Specifications = new Dictionary<string, string>
    {
        ["CPU"] = "Intel i7",
        ["RAM"] = "16GB",
        ["Storage"] = "512GB SSD"
    }
};

context.Products.Add(product);
await context.SaveChangesAsync();

// 查询包含特定标签的产品
var electronics = await context.Products
    .Where(p => p.Tags.Any(t => t.Name == "Electronics"))
    .ToListAsync();

JSON 查询

基本查询

csharp

// 过滤 JSON 字段
var seattleCustomers = await context.Customers
    .Where(c => c.ShippingAddress.City == "Seattle")
    .ToListAsync();

// 生成的 SQL (SQL Server):
/*
SELECT [c].[Id], [c].[Name], [c].[Email], [c].[ShippingAddress]
FROM [Customers] AS [c]
WHERE JSON_VALUE([c].[ShippingAddress], '$.City') = N'Seattle'
*/

// 生成的 SQL (PostgreSQL):
/*
SELECT c.id, c.name, c.email, c.shipping_address
FROM customers AS c
WHERE c.shipping_address->>'City' = 'Seattle'
*/

嵌套查询

csharp

// 查询嵌套属性
var customers = await context.Customers
    .Where(c => c.Contact.Phone.AreaCode == "206")
    .ToListAsync();

// 生成的 SQL:
// WHERE JSON_VALUE([c].[Contact], '$.Phone.AreaCode') = N'206'

集合查询

csharp

// 查询集合中的元素
var products = await context.Products
    .Where(p => p.Tags.Any(t => t.Name == "Electronics"))
    .ToListAsync();

// 生成的 SQL (SQL Server):
/*
SELECT [p].[Id], [p].[Name], [p].[Price], [p].[Tags]
FROM [Products] AS [p]
WHERE EXISTS (
    SELECT 1
    FROM OPENJSON([p].[Tags]) WITH ([Name] NVARCHAR(100) '$.Name') AS [t]
    WHERE [t].[Name] = N'Electronics'
)
*/

投影查询

csharp

// 仅选择 JSON 中的部分字段
var customerDtos = await context.Customers
    .Select(c => new CustomerDto
    {
        Id = c.Id,
        Name = c.Name,
        City = c.ShippingAddress.City,  // 从 JSON 提取
        State = c.ShippingAddress.State
    })
    .ToListAsync();

数据库特定优化

SQL Server

csharp

// 启用 JSON 索引(SQL Server 2016+)
modelBuilder.Entity<Customer>(entity =>
{
    entity.HasIndex(c => c.ShippingAddress)
          .HasMethod("JSON");  // 需要自定义迁移
});

// 或使用计算列 + 索引
entity.Property(c => c.ShippingCity)
      .HasComputedColumnSql("JSON_VALUE([ShippingAddress], '$.City')")
      .IsRequired();

entity.HasIndex(c => c.ShippingCity);

PostgreSQL (JSONB)

csharp

// PostgreSQL 推荐使用 JSONB(二进制 JSON,支持索引)
modelBuilder.Entity<Customer>(entity =>
{
    entity.Property(c => c.ShippingAddress)
          .HasColumnType("jsonb");  // 二进制 JSON
    
    // GIN 索引(加速 JSON 查询)
    entity.HasIndex(c => c.ShippingAddress)
          .HasMethod("gin");
});

// 查询性能提升 10-100 倍
var customers = await context.Customers
    .Where(c => c.ShippingAddress.City == "Seattle")
    .ToListAsync();

// 生成的 SQL:
// WHERE shipping_address @> '{"City": "Seattle"}'::jsonb

MySQL

csharp

// MySQL 5.7+ 支持 JSON
modelBuilder.Entity<Customer>(entity =>
{
    entity.Property(c => c.ShippingAddress)
          .HasColumnType("json");
    
    // 虚拟列 + 索引
    entity.Property(c => c.ShippingCity)
          .HasComputedColumnSql("JSON_UNQUOTE(JSON_EXTRACT(ShippingAddress, '$.City'))")
          .Virtual();
    
    entity.HasIndex(c => c.ShippingCity);
});

迁移与版本控制

添加 JSON 列

bash

dotnet ef migrations add AddCustomerAddresses

csharp

// Migration 文件
protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AddColumn<string>(
        name: "ShippingAddress",
        table: "Customers",
        type: "nvarchar(max)",
        nullable: false,
        defaultValue: "{}");
    
    migrationBuilder.AddColumn<string>(
        name: "BillingAddress",
        table: "Customers",
        type: "nvarchar(max)",
        nullable: true);
}

数据迁移(从传统列到 JSON)

csharp

protected override void Up(MigrationBuilder migrationBuilder)
{
    // 1. 添加 JSON 列
    migrationBuilder.AddColumn<string>(
        name: "ShippingAddressJson",
        table: "Customers",
        type: "nvarchar(max)",
        nullable: true);
    
    // 2. 迁移数据
    migrationBuilder.Sql(@"
        UPDATE Customers
        SET ShippingAddressJson = JSON_MODIFY(
            JSON_MODIFY(
                JSON_MODIFY('{}',
                    '$.Street', ShippingStreet),
                '$.City', ShippingCity),
            '$.State', ShippingState)
        WHERE ShippingStreet IS NOT NULL
    ");
    
    // 3. 删除旧列
    migrationBuilder.DropColumn("ShippingStreet", "Customers");
    migrationBuilder.DropColumn("ShippingCity", "Customers");
    migrationBuilder.DropColumn("ShippingState", "Customers");
    
    // 4. 重命名
    migrationBuilder.RenameColumn("ShippingAddressJson", "Customers", "ShippingAddress");
}

最佳实践

✅ 推荐做法

1. 选择合适的存储方式

csharp

// ✅ 频繁查询的字段 → 传统列
entity.Property(c => c.Email).IsRequired();

// ✅ 灵活结构/少查询 → JSON 列
entity.OwnsOne(c => c.Preferences, p => p.ToJson());

// ✅ 混合方案
entity.OwnsOne(c => c.Address, address =>
{
    // 常用查询字段单独列
    address.Property(a => a.City).HasColumnName("City");
    
    // 其他字段存 JSON
    address.Ignore(a => a.Street);
    address.Ignore(a => a.State);
    // ...
});

2. 为 JSON 查询添加索引

csharp

// PostgreSQL: GIN 索引
entity.HasIndex(c => c.ShippingAddress)
      .HasMethod("gin");

// SQL Server: 计算列 + 索引
entity.Property(c => c.ShippingCity)
      .HasComputedColumnSql("JSON_VALUE([ShippingAddress], '$.City')");
entity.HasIndex(c => c.ShippingCity);

3. 验证 JSON 结构

csharp

public class Address
{
    [Required]
    public string Street { get; set; } = null!;
    
    [Required]
    [StringLength(100)]
    public string City { get; set; } = null!;
    
    [RegularExpression(@"^\d{5}(-\d{4})?$")]
    public string ZipCode { get; set; } = null!;
}

// SaveChanges 时验证
public override Task<int> SaveChangesAsync(CancellationToken ct = default)
{
    foreach (var entry in ChangeTracker.Entries<Customer>())
    {
        if (entry.Entity.ShippingAddress != null)
        {
            Validator.ValidateObject(entry.Entity.ShippingAddress, 
                new ValidationContext(entry.Entity.ShippingAddress), 
                validateAllProperties: true);
        }
    }
    
    return base.SaveChangesAsync(ct);
}

❌ 避免的错误

1. 不要在 JSON 中存储大量数据

csharp

// ❌ 错误: JSON 列过大影响性能
public class Order
{
    public List<OrderItem> Items { get; set; } = new();  // ⚠️ 可能有数百项
}

// ✅ 正确: 关联表
public class Order
{
    public ICollection<OrderItem> Items { get; set; } = new();
}

public class OrderItem
{
    public int OrderId { get; set; }
    public int ProductId { get; set; }
    public int Quantity { get; set; }
}

2. 不要过度嵌套

csharp

// ❌ 错误: 嵌套太深,难以查询
public class DeepNested
{
    public Level1 L1 { get; set; }  // L1 -> L2 -> L3 -> L4 -> L5
}

// ✅ 正确: 扁平化或拆分为关联表
public class FlatStructure
{
    public string Field1 { get; set; }
    public string Field2 { get; set; }
}

总结

JSON vs 传统列决策树

需要存储结构化数据?
│
├─ 频繁查询/过滤?
│  └─ ✅ 传统列 + 索引
│
├─ 结构灵活/经常变化?
│  └─ ✅ JSON 列
│
├─ 嵌套对象/集合?
│  ├─ 简单 → JSON 列
│  └─ 复杂 → 关联表
│
└─ 混合需求?
   └─ ✅ 常用字段传统列 + 扩展字段 JSON

核心要点

Owned Entities: EF Core 8+ 原生支持 JSON 列
ToJson(): 关键配置方法
JSON 查询: 服务器端执行,性能优异
索引策略: 为常用查询字段添加索引
数据库差异: PostgreSQL JSONB 性能最优

复杂类型与 JSON 列支持 ​

概述 ​

核心特性 ​

Owned Entities 基础 ​

定义复杂类型 ​

配置 Owned Entity ​

生成的表结构 ​

JSON 列存储 ​

使用 JSON 列(推荐) ​

生成的表结构(JSON) ​

使用示例 ​

嵌套复杂类型 ​

多层嵌套 ​

集合类型 ​

List/Array 支持 ​

JSON 查询 ​

基本查询 ​

嵌套查询 ​

集合查询 ​

投影查询 ​

数据库特定优化 ​

SQL Server ​

PostgreSQL (JSONB) ​

MySQL ​

迁移与版本控制 ​

添加 JSON 列 ​

数据迁移(从传统列到 JSON) ​

最佳实践 ​

✅ 推荐做法 ​

1. 选择合适的存储方式 ​

2. 为 JSON 查询添加索引 ​

3. 验证 JSON 结构 ​

❌ 避免的错误 ​

1. 不要在 JSON 中存储大量数据 ​

2. 不要过度嵌套 ​

总结 ​

JSON vs 传统列决策树 ​

核心要点 ​

复杂类型与 JSON 列支持

概述

核心特性

Owned Entities 基础

定义复杂类型

配置 Owned Entity

生成的表结构

JSON 列存储

使用 JSON 列(推荐)

生成的表结构(JSON)

使用示例

嵌套复杂类型

多层嵌套

集合类型

List/Array 支持

JSON 查询

基本查询

嵌套查询

集合查询

投影查询

数据库特定优化

SQL Server

PostgreSQL (JSONB)

MySQL

迁移与版本控制

添加 JSON 列

数据迁移(从传统列到 JSON)

最佳实践

✅ 推荐做法

1. 选择合适的存储方式

2. 为 JSON 查询添加索引

3. 验证 JSON 结构

❌ 避免的错误

1. 不要在 JSON 中存储大量数据

2. 不要过度嵌套

总结

JSON vs 传统列决策树

核心要点