CODEWITHSUNDEEP
jsonasp.net-coreentity-framework-coresql-server-2016
jen
jen

Reputation: 349

Is there an Entity Frame Library or extension that handles Json functions with linq?

I would like to complete the following code using Entity Framework in .Net Core to query a JSON object from my API where my API is linked to MS SQL Server 2016. I don't want to take in the entire JSON and query from my backend, I want SQL to handle the querying using the MS JSON Functions and return only the queried information.

Something along the lines of:

var queryList = _context.myTable.Select(r => JSON_QUERY(r.myJsonColumn, '$.info')).ToList();

and

var valueList = _context.myTable.Select(r => JSON_VALUE(r.myJsonColumn, '$.info')).ToList();

I would like to do this without using a string inside the select statement and without creating my own function.

Overall I want a library where I can use JSON_VALUE, JSON_QUERY, ISJSON and JSON_MODIFY with my entity framework and linq.

Upvotes: 2

Views: 553

Answers (1)

Max Holmark
Max Holmark

Reputation: 1

Tested on .NET EF 7.

  1. Make EF understand the JSON_VALUE function:
public static class DbFunction
{
  public static string JsonValue(string column, [NotParameterized] string path) => throw new NotSupportedException();

  public static void AddJsonValueDbFunction(this ModelBuilder builder)
  {
    builder
      .HasDbFunction(typeof(DbFunction).GetMethod(nameof(JsonValue))!)
      .HasTranslation(args => new SqlFunctionExpression(
        functionName: "JSON_VALUE",
        arguments: args,
        nullable: true,
        argumentsPropagateNullability: new bool[] { false, false },
        type: typeof(string),
        typeMapping: null
      ));
  }
}
  1. Register new 'DbFunction.JsonValue' EF Function in DbContext
public class DataContext : DbContext
{
  // ...

  protected override void OnModelCreating(ModelBuilder builder)
  {
    // Add JSON_VALUE database function support
    builder.AddJsonValueDbFunction();

    // ...
  }
}
  1. Use the 'JsonValue' in Select or Where LINQ queries
// Get db context
using var db = _dbFactory.CreateDbContext();

// Get ABCs
var abcs = await db.ABCs
  .AsNoTracking()
  .Select(a => new
  {
    a.Id,
    PlusCode = DbFunction.JsonValue(a.JsonColumn, "$.plusCode"),
    State = DbFunction.JsonValue(a.JsonColumn, "$.state")
  })
  .Where(a => DbFunction.JsonValue(a.JsonColumn, "$.state") == "NY"
  .ToListAsync();

Upvotes: 0

Related Questions