0

Sorry for my bad English

Note: Npgsql does not supporting json query with EF Core Mapping directly

As yo know PostgreSQL is supporting json and hybrid data. And if you want to query, you can use syntax like this

select * 
from archive.ArchiveObject 
where FileInfo->>'FileName' = 'name.ext';

For my question here is sample table and sample class

Table:

CREATE TABLE archive."ArchiveObject"
(
    "Id" bigint NOT NULL DEFAULT nextval('archive."ArchiveObject_Id_seq"'::regclass),
    "Uid" uuid NOT NULL,
    ...
    ...
    ...
    "FileInfo" json,
    ...
    ...

)

and here is C# classes for this table

[Table("ArchiveObject", Schema = CX.Schema)]
public partial class ArchiveObject
{
        [Key]
        public long Id { get; set; }
        [Required]
        public Guid Uid { get; set; }
        [Column("FileInfo", TypeName = "json")]
        public string _FileInfo { get; set; }

        [NotMapped]
        public ObjectFileInfo FileInfo
        {
            //get;set;
            get
            {
                return string.IsNullOrEmpty(_FileInfo) ? null : JsonConvert.DeserializeObject<ObjectFileInfo>(_FileInfo);
            }
            set
            {
                _FileInfo = value != null ? JsonConvert.SerializeObject(value) : null;
            }
        }
...
...
}

OK. When I used linq or lambda like this no problem

var query = from x in db.ArchiveObject 
            where x.Id < 65535
            select x;

generates

"SELECT x."Id", x."CreatedAt", x."KellePaca", x."Tags", x."Uid", x."UpdateHistory", x."VirtualPathId", x."FileInfo"
 FROM archive."ArchiveObject" AS x
 WHERE (x."Id" < 65535)

but I can't query json area named FileInfo field like this.

select * 
from archive.ArchiveObject 
where FileInfo->>'FileName' = 'name.ext'    ;

because of EF doesn't have any Provider for convert to sql "file_info->>'FileName'"

I'm searched and found this terms

ExpressionVisitor, QueryProvider, BinaryExpression,
ParameterExpression, ObjectQuery.ToTraceString, Expression

and also found this documents and answers

How to convert an expression tree to a partial SQL query?

https://www.codeproject.com/Articles/22819/How-To-LINQ-To-SQL-Part-III

I'm believe can be generate [ file_info->>'FileName' = 'name.ext' ; ] query using this documents. But I can't get it.

public static IQueryable<ArchiveObject> FileNameEquals(this IQueryable<ArchiveObject> source, string s)
        {
           ....
        }
    or what?

Please can you show me simple example.

3
  • 1
    what are you using to access postgresql from C#? npgsql? Commented Jun 18, 2018 at 13:34
  • @JuanCarlosOropeza Thanks for comment. I'm alerady using npgsql But unfortunately npgsql does not supporting json query with EF Mapping directly. Commented Jun 19, 2018 at 6:53
  • Then you have your answer? You can't do that using EF. Create a sp in postgresql and call it from your app. Commented Jun 19, 2018 at 12:49

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.