This extention aim is define json columns and querying them.
PM> Install-Package EntityFrameworkCore.SqlServer.JsonExtention -Version 2.0.0
PM> Install-Package EntityFrameworkCore.SqlServer.JsonExtention -Version 1.0.0
Inside dbcontext OnConfiguring method, call UseJsonExtention()
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
base.OnConfiguring(optionsBuilder);
optionsBuilder.UseJsonFunctions();
}Using Fluent api in model builder and just call HasJsonConversion() method.
modelBuilder.Entity<Country>().Property(p => p.CountryDetail).HasJsonConversion();The library supports List<>, Dictionary<string,object> and custom entity types.
Example entities:
public class Country
{
public int Id { get; set; }
public string Name { get; set; }
public CountryDetail CountryDetail { get; set; }
public Dictionary<string, object> ExtraInformation { get; set; }
public List<string> OfficialLanguages { get; set; }
public List<int> UtcTimeZones { get; set; }
}
public class CountryDetail
{
public string Code { get; set; }
public DateTime? Founded { get; set; }
public List<City> Cities { get; set; }
}
public class City
{
public string Name { get; set; }
public DateTime? Founded { get; set; }
public int? Population { get; set; }
}And usage in dbcontext:
public class TestContext : DbContext {
public DbSet<Country> Countries { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
base.OnConfiguring(optionsBuilder);
optionsBuilder.UseSqlServer("Data Source=.;Initial Catalog=JsonExtentionTest;Integrated Security=True",
providerOptions => providerOptions.CommandTimeout(60));
optionsBuilder.UseJsonFunctions();
}
protected override void OnModelCreating(ModelBuilder modelBuilder) {
modelBuilder.Entity<Country>().Property(p => p.CountryDetail).HasJsonConversion();
modelBuilder.Entity<Country>().Property(p => p.ExtraInformation).HasJsonConversion();
modelBuilder.Entity<Country>().Property(p => p.UtcTimeZones).HasJsonConversion();
modelBuilder.Entity<Country>().Property(p => p.OfficialLanguages).HasJsonConversion();
}
}Supported Json functions are:
ISJSON : Tests whether a string contains valid JSON.
JSON_VALUE: Extracts a scalar value from a JSON string.
JSON_QUERY: Extracts an object or an array from a JSON string.var isJsons = _context.Countries.Select(s => EF.Functions.IsJson(s.CountryDetail)).ToList();Generated SQL query
SELECT ISJSON([c].[CountryDetail])
FROM [Countries] AS [c]var phones = _context.Countries
.Where(w => EF.Functions.JsonValue(w.ExtraInformation, "InternetTLD") != null)
.OrderByDescending(o => EF.Functions.JsonValue(o.ExtraInformation, "InternetTLD"))
.Select(s => EF.Functions.JsonValue(s.ExtraInformation, "InternetTLD"))
.ToList();Generated SQL query
SELECT JSON_VALUE([c].[ExtraInformation], '$.InternetTLD')
FROM [Countries] AS [c]
WHERE JSON_VALUE([c].[ExtraInformation], '$.InternetTLD') IS NOT NULL
ORDER BY JSON_VALUE([c].[ExtraInformation], '$.InternetTLD') DESCvar entityId = 1;
var result = _context.Countries.Where(w => w.Id == entityId).Select(s => EF.Functions.JsonQuery(s.CountryDetail, "Cities")).FirstOrDefault();
var cities = JsonSerializer.Deserialize<List<City>>(result);Generated SQL query
SELECT TOP(1) JSON_QUERY([c].[CountryDetail], '$.Cities')
FROM [Countries] AS [c]
WHERE [c].[Id] = 1
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent