Question 221563 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to parse text json in linq with Entity Framework

I have a table SliceStatements (DB: PostgreSQL) that has a column Payload. The column’s datatype is String but the string is in json format. The json has field rows which is an array of objects. It is known that each objects has field locationM.

I need to write a Linq query to get first 1000 rows of JSON objects (not SliceStatements), ordered by locationM.

I know that I can do it easily by loading data first using AsEnumerable or something like that, but there is so much data that application crashes with OutOfMemoryException.

So how do I write the linq query?

Here’s my code:

await _context.SliceStatements.AsNoTracking()
            .SelectMany(s => (JArray)JObject.Parse(s.Payload)["rows"], (s, row) => new { row })
            .OrderBy(r => r["locationM"])
            .Take(1000)
            .ToListAsync();

Sadly, I get

The LINQ expression ‘s => (JArray)JObject.Parse(s.Payload).get_Item("rows")’ could not be translated.

I know I can parse text into JSON using SQL query by fieldName::json, but I don’t know how to do it using linq.

And I would like to not write whole query as SQL, cause my code is 10% of the whole query, that consists of multiple Join and Where.

Tags:

2

Answers


  1. 0

    The reason you are encountering the "LINQ expression could not be translated" error is that Entity Framework Core (EF Core) is not able to translate the JObject.Parse method into SQL. As a result, the query cannot be executed on the database side, and EF Core tries to execute it in memory, leading to the OutOfMemoryException issue.

    To overcome this problem, you need to find an alternative approach that allows EF Core to translate the query into SQL. One way to achieve this is by using the EF.Functions.JsonbArrayElements method available in EF Core for PostgreSQL.

    Assuming you are using EF Core 5.0 or later, you can rewrite your LINQ query like this:

    using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Query;

// ...

var query = _context.SliceStatements.AsNoTracking()
    .SelectMany(s => EF.Functions.JsonbArrayElements(s.Payload)["rows"], (s, row) => new { row })
    .OrderBy(r => r.row["locationM"])
    .Take(1000);

var result = await query.ToListAsync();

    In the above code, we use the EF.Functions.JsonbArrayElements method to extract the elements from the "rows" array in the JSON, which is equivalent to JArray in your original code. This method can be translated to SQL, allowing EF Core to handle the query on the database side and avoid the memory issue.

    Please make sure to include the necessary using directives, and also ensure that you are using EF Core 5.0 or later with the Npgsql.EntityFrameworkCore.PostgreSQL package installed for PostgreSQL support.

    Login or Signup to reply.
  2. 0

    Please see this doc describing how to work with Postgres’s Json coulmns using EF Core. Looks like you need to add the [Column(TypeName = "jsonb")] attribute to your Payload column containing JSON. After that, it is easy to query using Linq in a normal way.

    There should be something like this (it is just a sort of pseudocode, I did not run it):

    _context
.SliceStatements
.AsNoTracking()
.SelectMany(x => x.Payload.rows)
.OrderBy(x => x.locationM);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search