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