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.