PostgreSQL pgsql jsonb 类型 POCO 访问扩展现实方案

[2881099]

public class SomeEntity
{
    [Column(IsIdentity = true)]
    public int Id { get; set; }
    [Column(MapType = typeof(JToken))]
    public Customer Customer { get; set; }
}

public class Customer    // Mapped to a JSON column in the table
{
    public string Name { get; set; }
    public int Age { get; set; }
    public Order[] Orders { get; set; }
}

public class Order       // Part of the JSON column
{
    public decimal Price { get; set; }
    public string ShippingAddress { get; set; }
}

var joes = fsql.Select<SomeEntity>()
    .Where(a => a.Customer.Name == "Joe")
    .ToList();

执行的 SQL 如下：

SELECT a.""Id"", a.""Customer""
FROM ""SomeEntity"" a
WHERE a.""Customer""->>'Name' = 'Joe'

实现 POCO 访问扩展的源码如下：

//注删 POCO 类型
FreeSql.Internal.Utils.TypeHandlers.TryAdd(typeof(Customer), new String_Customer());
class String_Customer: TypeHandler<Customer>
{
    public override object Serialize(Customer value) => JsonConvert.SerializeObject(value);
    public override Customer Deserialize(object value) => JsonConvert.DeserializeObject<Customer>((string)value);
}

fsql.Aop.ParseExpression += (_, e) =>
{
    if (e.Expression.IsParameter() == false) return;
    //解析 POCO Jsonb   a.Customer.Name
    if (e.Expression is MemberExpression memExp)
    {
        var parentMemExps = new Stack<MemberExpression>();
        parentMemExps.Push(memExp);
        while (true)
        {
            switch (memExp.Expression.NodeType)
            {
                case ExpressionType.MemberAccess:
                    memExp = memExp.Expression as MemberExpression;
                    if (memExp == null) return;
                    parentMemExps.Push(memExp);
                    break;
                case ExpressionType.Parameter:
                    var tb = fsql.CodeFirst.GetTableByEntity(memExp.Expression.Type);
                    if (tb == null) return;
                    if (tb.ColumnsByCs.TryGetValue(parentMemExps.Pop().Member.Name, out var trycol) == false) return;
                    if (new[] { typeof(JToken), typeof(JObject), typeof(JArray) }.Contains(trycol.Attribute.MapType.NullableTypeOrThis()) == false) return;
                    var tmpcol = tb.ColumnsByPosition.OrderBy(a => a.Attribute.Name.Length).First();
                    var result = e.FreeParse(Expression.MakeMemberAccess(memExp.Expression, tb.Properties[tmpcol.CsName]));
                    result = result.Replace(tmpcol.Attribute.Name, trycol.Attribute.Name);
                    while (parentMemExps.Any())
                    {
                        memExp = parentMemExps.Pop();
                        result = $"{result}->>'{memExp.Member.Name}'";
                    }
                    e.Result = result;
                    return;
            }
        }
    }
};