support for OPENJSON sql server syntax with linq to entities through EFCore
- Your project use EF Core (2.1.1).
- You need some JSON features of SQL Server (
JSON_VALUE
,JSON_QUERY
,OPENJSON
). - You don't want to use FromSql
You can use this extension library with a new extension method
ValueFromOpenJson
.
You have to declare some fake DbSet
(due to actual limitation of EFCore) :
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<JsonResult<string>>();
modelBuilder.Entity<JsonResult<int>>();
modelBuilder.Entity<JsonResult<bool>>();
}
You can use SQLServer or InMemory provider :
optionsBuilder.UseExtensions(extensions =>
{
//extensions.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=testdbapp2;Trusted_Connection=True;");
extensions.UseInMemoryDatabase("inmemory");
});
If you want to EF Core generate valid SQL with right usage of OPENJSON
, you have to use the method ValueFromOpenJson
on a fake DbSet of JsonResult
. You have to declare this DbSet as a variable :
var json = appContext.Set<JsonResult<string>>();
Next you can query a real DbSet (mapped to an existing table) :
var query = appContext.Persons
.Where(p => json.ValueFromOpenJson(p.Kinds, "$").Select(jr => jr.Value).Contains("kind2"));
The variable query is a classic IQueryable
, you trigger the SQL Command with ToList
or ToListAsync
from EFCore :
var result = await query.ToListAsync();
The SQL generated :
SELECT [p].[Id], [p].[Kinds], [p].[Name]
FROM [Persons] AS [p]
WHERE N'kind2' IN (
SELECT [jr].[Value]
FROM (
SELECT [Key], [Value], [Type] FROM OPENJSON([p].[Kinds], N'$')
) AS [jr]
)
- declare JsonResult DbSet automaticaly
- SQLite support