Not handling JSON-conversion of IList<string> into database

Question

I'm using Linq2DB and the EF Core plugin for it. However, I noticed when trying to implement bulk-insert that some columns that otherwise work in EF Core fail with the following exception:

The given value 'System.Collections.Generic.List1[System.String]' of type List1 from the data source cannot be converted to type nvarchar for Column 14 [OrderNumbers].

The class which contains the property in question is set up like this:

public IList<string> OrderNumbers { get; set; } = new List<string>();

While the column in the SQL Server database is defined as nvarchar(max).

The class which configures the table for this class is setup like this:

builder.Property(x => x.OrderNumbers)
       .HasColumnOrder(15);

This is using a fairly-recent feature implemented in EF Core 7 or 8 which allows for automatically converting a list of primitives to a JSON column in the database.

I've tried calling this in my unit test which is testing the bulk-insert functionality but it's a no-go so far:

MappingSchema.Default.SetConverter<List<string>, object>(list => JsonSerializer.Serialize(list));
MappingSchema.Default.SetConverter<List<string>, SqlString>(list => JsonSerializer.Serialize(list));
MappingSchema.Default.SetConverter<List<string>, string>(list => JsonSerializer.Serialize(list));
MappingSchema.Default.SetConverter<IList<string>, object>(list => JsonSerializer.Serialize(list));
MappingSchema.Default.SetConverter<IList<string>, SqlString>(list => JsonSerializer.Serialize(list));
MappingSchema.Default.SetConverter<IList<string>, string>(list => JsonSerializer.Serialize(list));

Is MappingSchema.Default.SetConverter an appropriate method to convert lists of strings to a nvarchar? Has anyone done something similar?

Answer 1

I was able to get my issue resolved by registering the different initializations and mappings in my service registration class:

public static IServiceCollection AddSqlServerServices(
          this IServiceCollection services,
          IConfiguration configuration)
      {
          LinqToDBForEFTools.Initialize();

          services.AddDbContextFactory<ExampleDbContext>(
              opt =>
              {
                  opt.UseSqlServer(
                      configuration.GetConnectionString("ExampleAppDb"));
                  opt.UseLinqToDB(builder =>
                  {
                      var mappingSchema = new MappingSchema();
                      var mapBuilder = new FluentMappingBuilder(mappingSchema);                                            mapBuilder.Entity<Bill>()
                          .Property(b => b.CustomerOrderNumbers)
                          .HasConversion(
                              list => JsonSerializer.Serialize(list, JsonSerializerOptions.Default),
                              s => JsonSerializer.Deserialize<IList<string>>(s, JsonSerializerOptions.Default) ?? new List<string>())
                          .Build();
                      builder.AddMappingSchema(mappingSchema);
                  });
              },
              ServiceLifetime.Transient);

          // ... snip ...

          return services;
      }

1. LinqToDBForEFTools.Initialize();: This sets things up so I can use DbContext normally
2. UseLinqToDB: This allows me to customize the schema to include the custom property I needed it to treat differently.

Note: Since my unit tests do not run service registration classes, I would probably abstract this Linq2Db stuff to its own class and call it from both the unit test class and in this service registration class. That way, if mappings needed to change, I would only need to do in one place.