注意
大多数关系数据库支持包含JSON文档的列。这些列中的JSON可以通过查询钻入。例如，这允许通过文档的元素进行过滤和排序，以及将文档中元素投射到结果中。 JSON列允许关系数据库采用文档数据库的某些特征，从而在两者之间创建了有用的混合。 - 从Microsoft。
重要的
而不是使用迁移，数据库是在SSM（SQL-Server Management Studio）中创建的，然后用EF电动工具进行了反向工程，而不是JSON指向类而不是Nvarchar类型的更改属性。
。如果您对EF Core是相当陌生的，那么花时间下载示例代码，研究代码，运行代码并了解代码。
使用JSON列时，请确保它适合您的数据模型，而不是简单地使用它。
本文的目的
提供几个清晰简洁的代码示例，用于使用JSON列，因为网络上的许多代码样本都不容易尝试。
示例1
我们有一个人表可以存储一个人的模型。
public class Address
{
public string Company { get; set; }
public string Street { get; set; }
public string City { get; set; }
public override string ToString() => Company;
}
人模型
public partial class Person
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime DateOfBirth { get; set; }
public List<Address> Addresses { get; set; }
public override string ToString() => $"{FirstName} {LastName}";
}
在数据库中，地址定义为nvarchar。
要配置EF核心以识别人员模型的地址属性的JSON列，我们使用以下代码，其中OwnsMany是关键，指向地址属性。
让我们在数据库中添加新记录，然后修改其中一个地址的城市属性。
private static void AddOnePerson()
{
using var context = new Context();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
Person person = new Person()
{
Addresses = new List<Address>()
{
new()
{
Company = "Company1",
City = "Wyndmoor",
Street = "123 Apple St"
},
new()
{
Company = "Company2",
City = "Portland",
Street = "999 34th St"
},
},
FirstName = "Karen",
LastName = "Payne",
DateOfBirth = new DateTime(1956, 9, 24)
};
context.Add(person);
context.SaveChanges();
context.Person.FirstOrDefault()!
.Addresses
.FirstOrDefault()
!.City = "Ambler";
context.SaveChanges();
}
如果您已经使用EF Core一段时间了，那么最后一个代码块与不使用JSON列没有什么不同。
让我们回读数据。
private static void ReadOnePerson()
{
using var context = new Context();
var person = context.Person.FirstOrDefault();
if (person is Person)
{
AnsiConsole.MarkupLine($"[white]{person.Id,-4}{person.FirstName,-10}{person.LastName,-10}{person.DateOfBirth:d}[/]");
foreach (var address in person.Addresses)
{
AnsiConsole.MarkupLine($"\t[green]{address.Company,-10}{address.Street,-15}{address.City}[/]");
}
}
var firstPerson = context.Person.FirstOrDefault(x => x.Id == 1);
var portlandAddress = firstPerson!.Addresses.FirstOrDefault(x => x.City == "Portland");
AnsiConsole.MarkupLine($"[white]{firstPerson.LastName,-8}{portlandAddress!.Company}[/]");
}
假设在某些应用程序中，开发人员由于某种原因没有使用EF核心，他们仍然可以使用此数据，但需要更多的工作。这是一个阅读的示例。
internal class DataProviderOperations
{
public static void ReadPersonAddress(int index = 0)
{
AnsiConsole.MarkupLine($"[cyan]Read data for address {index +1}[/]");
var statement =
"SELECT Id, FirstName, LastName, DateOfBirth, " +
$"JSON_VALUE(Addresses, '$[{index}].Street') AS Street, JSON_VALUE(Addresses, '$[{index}].City') AS City, JSON_VALUE(Addresses, '$[{index}].Company') AS Company FROM dbo.Person;";
using SqlConnection cn = new(ConfigurationHelper.ConnectionString());
using SqlCommand cmd = new() { Connection = cn, CommandText = statement };
cn.Open();
DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader());
foreach (DataRow row in dt.Rows)
{
Console.WriteLine($"{string.Join(", ", row.ItemArray)}");
}
Console.WriteLine();
AnsiConsole.MarkupLine("[cyan]DataTable columns[/]");
foreach (DataColumn column in dt.Columns)
{
Console.WriteLine($"{column.ColumnName,-15}{column.DataType.Name}");
}
}
}
示例2
开发人员在JSON文件中找到了WCAG规则的副本，并希望使用以下JSON将数据存储在SQL-Server数据库表中。
{
"Section": "1-2-1",
"id": "media-equiv-av-only-alt",
"title": "Audio-only and Video-only (Prerecorded)",
"description": "For prerecorded audio-only and prerecorded video-only media, the following are true, except when the audio or video is a media alternative for text and is clearly labeled as such:",
"uri": "http://www.w3.org/TR/WCAG20/#media-equiv-av-only-alt",
"conformance_level": "A",
"wuhcag_summary": "Provide an alternative to video-only and audio-only content",
"wuhcag_detail": "\u003Cp\u003E\u003Cstrong\u003EProvide an alternative to video-only and audio-only content\u003C/strong\u003E\u003C/p\u003E\n\u003Cp\u003ESome users will find it difficult to use or understand things like podcasts and silent videos or animations.\u003C/p\u003E\n\u003Ch2\u003EWhat to do\u003C/h2\u003E\n\u003Cul\u003E\n \u003Cli\u003EWrite text transcripts for any audio-only media;\u003C/li\u003E\n \u003Cli\u003EWrite text transcripts for any video-only media; or\u003C/li\u003E\n \u003Cli\u003ERecord an audio-track for any video-only media;\u003C/li\u003E\n \u003Cli\u003EPlace the text transcript, or link to it, close to the media.\u003C/li\u003E\n\u003C/ul\u003E\n",
"wuhcag_tips": "\u003Cp\u003EAudio-only and video-only content needs to be supported by text transcripts that convey the same information as the media. Sometimes this is quite simple, other times you have to make a judgement call on what that really means. The best bet is, as always,to be honest with your customers: what does the media convey and does your transcript do the same? Could you swap one for the other?\u003C/p\u003E\n\u003Cp\u003EOne of the most common uses for text transcripts is when a podcast is published online. Embedding a podcast in a page is a great way of sharing your content but no good for your customers with hearing impairments. A text transcript should contain everything mentioned in the recording.\u003C/p\u003E\n\u003Cp\u003ELess commonly, some videos do not have sound. Your customers with visual impairments need help with this kind of content. A text transcript for a video without sound should describe what is going on in the video as clearly as possible. Try to focus on\n what the video is trying to say rather than getting bogged down with detail.\u003C/p\u003E\n\u003Cdiv class=\u0027mailmunch-forms-in-post-middle\u0027 style=\u0027display: none !important;\u0027\u003E\u003C/div\u003E\n\u003Cp\u003EAs an alternative for video-only content, you could also choose to record an audio track that narrates the video.\u003C/p\u003E\n\u003Cp\u003EFor both audio-only and video-only, create your text transcript and place it either directly beneath the content or insert a link next to the content.\u003C/p\u003E\n",
"wuhcag_what_to_do": "",
"wuhcag_exceptions": "\u003Cp\u003EIf the content is itself an alternative (you don\u2019t have to provide a transcript of the audio track you provided to explain the silent video you used).\u003C/p\u003E\n",
"wuhcag_related": [
{
"Section": "1-2-2",
"conformance_level": "A"
},
{
"Section": "1-2-3",
"conformance_level": "A"
},
{
"Section": "1-2-5",
"conformance_level": "AA"
},
{
"Section": "1-2-7",
"conformance_level": "AAA"
},
{
"Section": "1-2-8",
"conformance_level": "AAA"
}
],
"RelatedList": [
{
"Section": "\u00221-2-2\u0022",
"ConformanceLevel": "\u0022A\u0022"
},
{
"Section": "\u00221-2-3\u0022",
"ConformanceLevel": "\u0022A\u0022"
},
{
"Section": "\u00221-2-5\u0022",
"ConformanceLevel": "\u0022AA\u0022"
},
{
"Section": "\u00221-2-7\u0022",
"ConformanceLevel": "\u0022AAA\u0022"
},
{
"Section": "\u00221-2-8\u0022",
"ConformanceLevel": "\u0022AAA\u0022"
}
]
}
请注意，上面的数据起初不是那么干净，并且花了一些时间来修复它。
相关清单可以放在单独的表中，但让我们考虑数据不会更改。
相关列表的模型
public class Related
{
public string Section { get; set; }
public string ConformanceLevel { get; set; }
public override string ToString() => $"{Section, -10}{ConformanceLevel}";
}
这是带有属性的主要类/模型，因此我们具有明确定义的属性名称。
public partial class WebStandards
{
public int Identifier { get; set; }
public string Section { get; set; }
[JsonPropertyName("id")]
public string Id { get; set; }
[JsonPropertyName("title")]
public string Title { get; set; }
[JsonPropertyName("description")]
public string Description { get; set; }
[JsonPropertyName("uri")]
public string Uri { get; set; }
[JsonPropertyName("conformance_level")]
public string ConformanceLevel { get; set; }
[JsonPropertyName("wuhcag_summary")]
public string Summary { get; set; }
[JsonPropertyName("wuhcag_detail")]
public string Detail { get; set; }
[JsonPropertyName("wuhcag_tips")]
public string Tips { get; set; }
[JsonPropertyName("wuhcag_what_to_do")]
public string Remedy { get; set; }
[JsonPropertyName("wuhcag_exceptions")]
public string Exceptions { get; set; }
public List<Related> RelatedList { get; set; }
}
数据库表模型
dbContext中的配置
代码从文件中读取JSON
internal class JsonOperations
{
private static string FileName => "wcagNew.json";
public static List<WebStandards> Read()
{
var jsonString = File.ReadAllText(FileName);
return JsonSerializer.Deserialize<List<WebStandards>>(jsonString);
}
}
代码将JSON文件的内容添加到我们的表格并执行多个查询。
internal class DataOperations
{
/// <summary>
/// Populate table from reading a json file
/// </summary>
/// <param name="list">Data from json</param>
public static void AddRange(List<WebStandards> list)
{
using var context = new Context();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
context.AddRange(list);
Console.WriteLine(context.SaveChanges());
}
/// <summary>
/// * Read data from database
/// * Get all AA complaint items
/// </summary>
public static void Read()
{
using var context = new Context();
var standards = context.WebStandards.ToList();
foreach (var standard in standards)
{
Console.WriteLine($"{standard.Identifier,-5}{standard.Title}");
// not all items have related items so assert for null list
if (standard.RelatedList is not null)
{
foreach (var related in standard.RelatedList)
{
Console.WriteLine($"\t{related.Section,-10}{related.ConformanceLevel}");
}
}
}
var aaStandards = standards.Where(x => x.ConformanceLevel == "AA");
AnsiConsole.MarkupLine("[cyan]ConformanceLevel AA[/]");
Console.WriteLine(aaStandards.Count());
AnsiConsole.MarkupLine("[cyan]Keyboard traps[/]");
var keyboardTraps = standards.FirstOrDefault(x => x.Title == "No Keyboard Trap");
Console.WriteLine(keyboardTraps.Description);
Console.WriteLine(keyboardTraps.Uri);
foreach (var related in keyboardTraps.RelatedList)
{
Console.WriteLine($"\t{related.Section,-10}{related.ConformanceLevel}");
}
Console.WriteLine();
}
}
从控制台项目中调用上述内容。
internal partial class Program
{
static void Main(string[] args)
{
DataOperations.AddRange(JsonOperations.Read());
DataOperations.Read();
AnsiConsole.MarkupLine("[yellow]Done[/]");
Console.ReadLine();
}
}
示例3
在先前的示例中，我们使用了OwnMany，在此示例中，我们有一个带有两个JSON列的主要模型应用程序，一个用于邮件信息，一个用于一般设置。
public partial class Applications
{
public int ApplicationId { get; set; }
/// <summary>
/// Application identifier
/// </summary>
public string ApplicationName { get; set; }
/// <summary>
/// Contact name
/// </summary>
public string ContactName { get; set; }
/// <summary>
/// For sending email messages
/// </summary>
public MailSettings MailSettings { get; set; }
public GeneralSettings GeneralSettings { get; set; }
}
public partial class GeneralSettings
{
public required string ServicePath { get; set; }
public required string MainDatabaseConnection { get; set; }
}
public partial class MailSettings
{
public required string FromAddress { get; set; }
public required string Host { get; set; }
public required int? Port { get; set; }
public required int? TimeOut { get; set; }
public required string PickupFolder { get; set; }
}
然后在dbcontext
中
最终代码填充和读取数据。
namespace HybridTestProject
{
/// <summary>
/// Fast and dirty, not true test
/// </summary>
[TestClass]
public partial class MainTest : TestBase
{
[TestMethod]
[Ignore]
[TestTraits(Trait.EntityFrameworkCore)]
public void AddRecordsTest()
{
using var context = new Context();
Applications application1 = new()
{
ApplicationName = "ACED",
ContactName = "Kim Jenkins",
MailSettings = new MailSettings()
{
FromAddress = "FromAddressAced",
Host = "AcedHost",
PickupFolder = "C:\\MailDrop",
Port = 15,
TimeOut = 2000
},
GeneralSettings = new GeneralSettings()
{
ServicePath = "http://localhost:11111/api/",
MainDatabaseConnection = "Data Source=.\\sqlexpress;Initial Catalog=WorkingWithDate;Integrated Security=True;Encrypt=False"
}
};
Applications application2 = new()
{
ApplicationName = "SIDES",
ContactName = "Mike Adams",
MailSettings = new MailSettings()
{
FromAddress = "FromAddressSides",
Host = "SidesHost",
PickupFolder = "C:\\MailDrop",
Port = 15,
TimeOut = 2000
},
GeneralSettings = new GeneralSettings()
{
ServicePath = "http://localhost:22222/api/",
MainDatabaseConnection = "Data Source=.\\sqlexpress;Initial Catalog=WorkingWithTime;Integrated Security=True;Encrypt=False"
}
};
context.Add(application1);
context.Add(application2);
context.SaveChanges();
}
[TestMethod]
[TestTraits(Trait.EntityFrameworkCore)]
public void SimpleReadTest()
{
using var context = new Context();
var apps = context.Applications.ToList();
foreach (var app in apps)
{
Console.WriteLine($"{app.ApplicationId,-4}{app.ApplicationName,-8}{app.MailSettings.Host}");
Console.WriteLine($" {app.GeneralSettings.MainDatabaseConnection}");
}
}
[TestMethod]
[TestTraits(Trait.EntityFrameworkCore)]
public void ReadOneTest()
{
using var context = new Context();
var app = context.Applications.FirstOrDefault(x =>
x.MailSettings.FromAddress == "FromAddressSides");
Assert.IsNotNull(app);
}
}
}
概括
Microsoft EF Core团队使在SQL-Server数据库中与JSON合作变得容易。他们将在下一版本的EF Core，EF Core 8。
源代码
克隆以下GitHub repository。
项目