JSONB Query Support

Discussion of open issues, suggestions and bugs regarding LinqConnect – Devart's LINQ to SQL compatible ORM
Post Reply
Bugaboo
Posts: 3
Joined: Fri 27 Apr 2018 18:28

JSONB Query Support

Post by Bugaboo » Fri 27 Apr 2018 18:31

Hi,

Are there any plans to support Queries on JSONB fields using LinqConnect?

Thanks

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: JSONB Query Support

Post by Shalex » Tue 01 May 2018 11:21

JSONB fields in LinqConnect are supported: https://www.devart.com/linqconnect/docs ... pping.html.

Could you please specify a query on JSONB which doesn't work? Tell us the expected and actual results.

Bugaboo
Posts: 3
Joined: Fri 27 Apr 2018 18:28

Re: JSONB Query Support

Post by Bugaboo » Tue 01 May 2018 12:03

Hi, thanks for the response...

My issue is that I cannot find any way to query a JSONB field using LinqConnect nor can I find any examples of this. And other posts have suggested that this can only be done in DotConnect.

So how would I for example query "name" == "John" against a JSONB field using LinqConnect?

Thanks

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: JSONB Query Support

Post by Shalex » Thu 03 May 2018 13:57

Please create a test table:

Code: Select all

CREATE TABLE jsonb_test (
  id serial primary key,
  data jsonb
);
INSERT INTO json_test (data) VALUES 
  ('{}'),
  ('{"a": 1}'),
  ('{"a": 2, "b": ["c", "d"]}'),
  ('{"a": 1, "b": {"c": "d", "e": true}}'),
  ('{"b": 2}');
Generate the LinqConnect model and try this code (it returns one record):

Code: Select all

   using (var context = new MyDataContext())
    {
        var result = context.JsonbTests.Where(a => a.Data == "{\"a\": 1}").ToList();
    }
This is the only way to work with JSONB in LinqConnect and EF Core.

In case you want ORM to generate json-specific SQL like "SELECT * FROM jsonb_test WHERE data ->> 'a' = '1'", it is supported in EF6: https://blog.devart.com/json-support-in ... resql.html.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: JSONB Query Support

Post by Shalex » Mon 21 Sep 2020 17:29

The PgSqlFunctions class, which allows using PostgreSQL-specific functions in LINQ to Entities, is supported in EF Core: viewtopic.php?f=3&t=42248.

Post Reply