Read some json values, Common Language Runtime detected an invalid program.

[sd-mobile]

Describe your issue

Всем привет!

Пытаюсь прочитать некоторые колонки из строкового поля, содержащего данные сериализованного Dictionary<string, object?>, получаю ошибку

Сгенерированный SQL

-- SqlServer.2012
SELECT
[t1].[Id],
JSON_VALUE(JsonData, '$."0"') AS '0',
JSON_VALUE(JsonData, '$."1"') AS '1',
JSON_VALUE(JsonData, '$."2"') AS '2',
JSON_VALUE(JsonData, '$."3"') AS '3',
JSON_VALUE(JsonData, '$."4"') AS '4'
FROM
[TestJsonWrite] [t1]

Exception message:
Common Language Runtime detected an invalid program.

Stack trace:
Exception: System.InvalidProgramException
Common Language Runtime detected an invalid program.
   at System.Reflection.Emit.DynamicMethod.CreateDelegate(Type delegateType, Object target)
   at System.Linq.Expressions.Compiler.LambdaCompiler.CreateDelegate()
   at System.Linq.Expressions.Compiler.LambdaCompiler.Compile(LambdaExpression lambda)
   at System.Linq.Expressions.Expression`1.Compile()
   at LinqToDB.Linq.QueryRunner.Mapper`1.GetMapperInfo(IDataContext context, IQueryRunner queryRunner, DbDataReader dataReader)
   at LinqToDB.Linq.QueryRunner.ExecuteQuery[T](Query query, IDataContext dataContext, Mapper`1 mapper, Expression expression, Object[] ps, Object[] preambles, Int32 queryNumber)+MoveNext()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)

Steps to reproduce

                using var db = ....;

                var testData = Enumerable.Range(0, 100)
                    .Select
                    (
                        f =>
                        {
                            var map = Enumerable.Range(0, 1000)
                                .Select(p => KeyValuePair.Create(p.ToString(), Random.Shared.Next(1000)))
                                .ToDictionary(f => f.Key, f => f.Value);
                            return new TestJsonWrite
                            {
                                Id = Guid.NewGuid(),
                                JsonData = JsonSerializer.Serialize(map)
                            };
                        }
                    )
                    .ToDictionary(f => f.Id);
                var testTbl = db.CreateTable<TestJsonWrite>(nameof(TestJsonWrite));
                try
                {
                    testTbl.BulkCopy(testData.Values);

                    var ms = new MappingSchema(_dbEngine.DefaultMappingSchema);
                    var fm = new FluentMappingBuilder(ms);

                    const int readColCount = 5;
                    foreach (var col in Enumerable.Range(0, readColCount))
                    {
                        var colExpr = $"JSON_VALUE({nameof(TestJsonWrite.JsonData)}, '$.\"{col}\"') AS '{col}'";
                        fm.Entity<TestJsonRead>()
                            .Property(x => Sql.Property<float?>(x, col.ToString()))
                            .IsExpression(row => Sql.Expr<float?>(colExpr), isColumn: true)
                            ;
                    }
                    fm.Build();
                    db.AddMappingSchema(ms);

                    var testRows = db.GetTable<TestJsonRead>().ToArray();
                    foreach (var testRow in testRows)
                    {
                        Debug.Assert(testData.ContainsKey(testRow.Id));
                        Debug.Assert(testRow.Values.Count == readColCount);
                    }
                }
                finally
                {
                    db.DropTable<TestJsonWrite>(nameof(TestJsonWrite));
                }

        [Table(Name = nameof(TestJsonWrite))]
        class TestJsonWrite
        {
            [PrimaryKey]
            public Guid Id { get; set; }

            [Column, DataType(DataType.NVarChar, "NVARCHAR(MAX)")]
            public string JsonData { get; set; } = string.Empty;
        }

        [Table(Name = nameof(TestJsonWrite))]
        class TestJsonRead
        {
            [PrimaryKey]
            public Guid Id { get; set; }

            [DynamicColumnsStore]
            public Dictionary<string, object?> Values { get; set; } = new();
        }

Environment details

Linq To DB version: 5.4.1

Database (with version): SQL Server 2019 (e.g. SQL Server 2019)

ADO.NET Provider (with version): Microsoft.Data.SqlClient 5.2.0

Operating system: Windows 10

.NET Version: 6.0

[sdanyliv]

Can you try to use this extension? #1230 (comment)
There is no need to define new mapping schema.

[viceroypenguin]

SqlFn.JsonValue already exists inside of linq2db. No need to add a new function.

[sd-mobile]

Can you try to use this extension? #1230 (comment) There is no need to define new mapping schema.

Суть в том, что мне нужно динамически, из 1000 сохраненных в колонке с json значений прочитать только 5 параметров, заранее не известных.

Именно поэтому я пытаюсь использовать этот код

const int readColCount = 5;

                foreach (var col in Enumerable.Range(0, readColCount))
                {
                    var colExpr = $"JSON_VALUE({nameof(TestJsonWrite.JsonData)}, '$.\"{col}\"') AS '{col}'";
                    fm.Entity<TestJsonRead>()
                        .Property(x => Sql.Property<float?>(x, col.ToString()))
                        .IsExpression(row => Sql.Expr<float?>(colExpr), isColumn: true)
                        ;
                }

[sd-mobile]

SqlFn.JsonValue already exists inside of linq2db. No need to add a new function.

I know, but this is just an example, in fact, my solution should work with various databases, including Sql Server, Postgres, SQLite.

If linq2db has uniform support for this, it will be great.

[sd-mobile]

Addition
If i replace the float? to string?, then the error text changes to
"Dynamic column setter is not to be called."

`

foreach (var col in Enumerable.Range(0, readColCount))
{
var colExpr = $"JSON_VALUE({nameof(TestJsonWrite.JsonData)}, '$."{col}"') AS '{col}'";
fm.Entity()
.Property(x => Sql.Property<string?>(x, col.ToString()))
.IsExpression(row => Sql.Expr<string?>(colExpr), isColumn: true)
;
}
`
Sorry, but code editor is messing up the text.