SQL Server Compact 4.0 ntext field with null value throws an exception in BulkCopy (single Insert is working correctly)

[alexey-leonovich]

Describe your issue

I have a User entity with two string fields - Name has default mapping (nvarchar(4000)), LongString is mapped to ntext.
If I insert single user (with LongString = null) it is working without any problem.
If I insert same user with BulkCopy - System.Data.SqlServerCe.SqlCeException is thrown.

Exception message:
System.Data.SqlServerCe.SqlCeException: 'The conversion is not supported. [ Type to convert from (if known) = varbinary, Type to convert to (if known) = ntext ]'
Stack trace:
This exception was originally thrown at this call stack:
    System.Data.SqlServerCe.SqlCeCommand.ProcessResults(int)
    System.Data.SqlServerCe.SqlCeCommand.CompileQueryPlan()
    System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(System.Data.CommandBehavior, string, System.Data.SqlServerCe.ResultSetOptions)
    System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
    LinqToDB.Data.DataConnection.ExecuteNonQuery(System.Data.Common.DbCommand) in DataConnection.cs
    LinqToDB.Data.DataConnection.ExecuteNonQuery() in DataConnection.cs
    LinqToDB.Data.CommandInfo.Execute() in CommandInfo.cs
    LinqToDB.DataProvider.MultipleRowsHelper.Execute() in MultipleRowsHelper.cs
    LinqToDB.DataProvider.BasicBulkCopy.MultipleRowsCopyHelper(LinqToDB.DataProvider.MultipleRowsHelper, System.Collections.IEnumerable, string, System.Action<LinqToDB.DataProvider.MultipleRowsHelper>, System.Action<LinqToDB.DataProvider.MultipleRowsHelper, object, string>, System.Action<LinqToDB.DataProvider.MultipleRowsHelper>, int, int) in BasicBulkCopy.cs
    LinqToDB.DataProvider.BasicBulkCopy.MultipleRowsCopy2(LinqToDB.DataProvider.MultipleRowsHelper, System.Collections.IEnumerable, string) in BasicBulkCopy.cs
    ...
    [Call Stack Truncated]

Steps to reproduce

1. Create .Net Framework 4.7.2 console application
2. Reference linq2db.SqlCe nuget (I used latest version 5.4.0)
3. Copy sample code below to Program.cs
4. Copy empty database to application output directory - users_small.zip
5. Run application - get an exception

using LinqToDB.Data;
using LinqToDB.DataProvider.SqlCe;
using LinqToDB.Mapping;
using LinqToDB;
using System;
using System.Collections.Generic;

namespace ConsoleApp1
{
    [Table(Name = "Users")]
    public class User
    {
        [Column]
        public string Name { get; set; }

        [Column(CanBeNull = true, DataType = DataType.NText, DbType = "ntext")]
        public string LongString { get; set; }
    }

    public class UsersDb : DataConnection
    {
        public UsersDb(DataOptions<UsersDb> options) : base(options.Options) { }
    }
    
    internal class Program
    {
        static void Main(string[] args)
        {
            var options = new DataOptions<UsersDb>(new DataOptions().UseSqlCe(@"Data Source=users_small.sdf"));
            using (var db = new UsersDb(options))
            {
                var user = new User() { Name = "Test", LongString = null };

                //single insert is working correctly
                //db.Insert(user);
                //bulk insert throws an exception
                db.GetTable<User>().BulkCopy(new List<User>() { user });
            }
        }
    }
}

Environment details

Linq To DB version: 5.4.0

Database (with version): SQL Server Compact 4.0

ADO.NET Provider (with version): linq2db.SqlCe 5.4.0

Operating system: Windows 10 22H2

.NET Version: .Net Framework 4.7.2

[MaceWindu]

Looks like another case where database fails to type NULL value in select list. First time I see it for SQLCE

Generated SQL:

INSERT INTO [Users]
(
	[Name],
	[LongString]
)
SELECT 'Test',NULL

[alexey-leonovich]

Could you please elaborate about column description: do I need both DataType and DbType attribute? It's not clear for me when only DbType is insufficient and I should use both. Thank you.

[MaceWindu]

In most cases you don't need any of those as we can infer required information about type from C# type (except length, precision, scale parameters).

In cases when non-default mapping between C# and database types used, you need to specify DataType. E.g. C# string could map in SQL Server to char, nchar, varchar, nvarchar (plus some legacy types like ntext) and by default we use nvarchar, which could result in ineffective queries for varchar columns without type hinted.

Cases when DbType required is very limited and in most cases you need it only if you use CreateTable API and linq2db cannot generate type name for your column properly. E.g. you want to use user-defined type for column.

[alexey-leonovich]

Thank you. As I can see here when TimeSpan? property is marked with DataType.Int64 attribute Linq2Db mapping is working out of the box (without any custom convertion rules). Is there a list of such default mappings documented somewhere?

[MaceWindu]

Nope.
You can check default mappings in provider's mapping schema and default schema

https://github.com/linq2db/linq2db/blob/master/Source/LinqToDB/DataProvider/SqlCe/SqlCeMappingSchema.cs#L25

https://github.com/linq2db/linq2db/blob/master/Source/LinqToDB/Mapping/MappingSchema.cs#L1376