SQL Server Compact 4.0 BulkCopy throws an 'Expression evaluation caused an overflow' exception (single Insert is working correctly)

[alexey-leonovich]

Describe your issue

I have an entity with some Guid, int, string, decimal and bool fields.
If I insert 2 sample entites one by one it is working without any problem.
If I insert same entites with BulkCopy - System.Data.SqlServerCe.SqlCeException is thrown.

Exception message:
System.Data.SqlServerCe.SqlCeException: 'Expression evaluation caused an overflow. [ Name of function (if known) =  ]'
Stack trace:
   System.Data.SqlServerCe.SqlCeCommand.ProcessResults(Int32 hr)
   System.Data.SqlServerCe.SqlCeCommand.ExecuteCommandText(IntPtr& pCursor, Boolean& isBaseTableCursor)
   System.Data.SqlServerCe.SqlCeCommand.ExecuteCommand(CommandBehavior behavior, String method, ResultSetOptions options)
   System.Data.SqlServerCe.SqlCeCommand.ExecuteNonQuery()
   LinqToDB.Data.DataConnection.ExecuteNonQuery(DbCommand command)
   LinqToDB.Data.DataConnection.ExecuteNonQuery()

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 - entities.zip
5. Run application - get an exception

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

namespace ConsoleApp1
{
    [Table(Name = "ProductsContainers")]
    internal sealed class ProductsContainer
    {
        public ProductsContainer(Guid productId, int containerNumber, string name, decimal weight, decimal amount,
            decimal gross, bool isDefault)
        {
            ProductId = productId;
            ContainerNumber = containerNumber;
            Name = name;
            Weight = weight;
            Amount = amount;
            Gross = gross;
            IsDefault = isDefault;
        }

        [Column(CanBeNull = false)] public Guid ProductId { get; set; }

        [Column(CanBeNull = false)] public int ContainerNumber { get; set; }

        [Column(CanBeNull = false)] public string Name { get; set; }

        [Column(CanBeNull = false)] public decimal Weight { get; set; }

        [Column(CanBeNull = false)] public decimal Amount { get; set; }

        [Column(CanBeNull = false)] public decimal Gross { get; set; }

        [Column(CanBeNull = false)] public bool IsDefault { get; set; }
    }

    public class MyDb : DataConnection
    {
        public MyDb(DataOptions<MyDb> options) : base(options.Options)
        {
        }
    }

    internal class Program
    {
        static void Main(string[] args)
        {
            var options = new DataOptions<MyDb>(new DataOptions().UseSqlCe(@"Data Source=entities.sdf"));
            using (var db = new MyDb(options))
            {
                var items = new List<ProductsContainer>() {
                    new ProductsContainer(Guid.NewGuid(), 1, "Pack", 0, 20, 0, true),
                    new ProductsContainer(Guid.NewGuid(), 1, "Bottle, 0,5", 0.08m, 0.5m, 0.54m, true)  };
                //single insert is working correctly
                //foreach (var item in items)
                //{
                //    db.Insert(item);
                //}
                //bulk insert throws an exception
                db.GetTable<ProductsContainer>().BulkCopy(items);
            }
        }
    }
}

Generated SQL:

INSERT INTO [ProductsContainers]
(
	[ProductId],
	[ContainerNumber],
	[Name],
	[Weight],
	[Amount],
	[Gross],
	[IsDefault]
)
SELECT '49db0e61-ea2c-438e-9246-4e407783b363',1,'Pack',0,20,0,1 UNION ALL
SELECT '16775ac7-4754-41c0-8817-038c45848b57',1,'Bottle, 0,5',0.08,0.5,0.54,1

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

[alexey-leonovich]

@MaceWindu Were you able to reproduce this bug? Do you need any additional information from me?

[MaceWindu]

Another case of bad type inference by SqlCe, it cannot narrow column type to decimal from integer + decimal value properly.
As workaround you can set decimal part to decimal value:

new ProductsContainer(Guid.NewGuid(), 1, "Pack", 0.0m, 20.0m, 0.0m, true),

or for variables

value = value * 1.0m;

INSERT INTO [ProductsContainers]
(
	[ProductId],
	[ContainerNumber],
	[Name],
	[Weight],
	[Amount],
	[Gross],
	[IsDefault]
)
SELECT '1efb52e4-0711-4481-a69c-f32e6d6f43ef',1,'Pack',0,20,0,1 UNION ALL
SELECT '530fcd42-f561-4ab4-9deb-eaede34524f5',1,'Bottle, 0,5',0.08,0.5,0.54,1