Bug: RepoDb.SqlServer v1.1.3: Failing to map ENUM to INT

[Freysev]

Bug Description

Hello. I'm encountering an issue when im trying to insert or merge a class with an enum in it. I've tried adding a [TypeMap(DbType.Int32)] attribute, but it doesnt change anything.

Exception Message:

Compiler.Entity/Object.Property: Failed to automatically convert the value expression 
for property 'Resolution (XXX.YYY.PeriodResolution)'. 
ClassProperty :: Name = Resolution (XXX.YYY.PeriodResolution), DeclaringType = 
XXX.YYY.Models.Entities.MarketPrice

InnerException: Expression of type 'XXX.YYY.PeriodResolution' 
cannot be used for parameter of type 'System.Int32' of method 'Int32 ToInt32(Int32)' (Parameter 'arg0')

InnerException.StackTrace: 
   at System.Dynamic.Utils.ExpressionUtils.ValidateOneArgument(MethodBase method, ExpressionType nodeKind, Expression arguments, ParameterInfo pi, String methodParamName, String argumentParamName, Int32 index)
   at System.Linq.Expressions.Expression.Call(MethodInfo method, Expression arg0)
   at RepoDb.Reflection.Compiler.ConvertExpressionToSystemConvertExpression(Expression expression, Type toType)
   at RepoDb.Reflection.Compiler.ConvertExpressionWithAutomaticConversion(Expression expression, Type toType)
   at RepoDb.Reflection.Compiler.GetEntityInstancePropertyValueExpression(Expression entityInstanceExpression, ClassProperty classProperty, DbField dbField)

Schema and Model:

CREATE TABLE [dbo].[MarketPrices]
(
    [Product] VARCHAR(50) NOT NULL, 
    [Resolution] INT NOT NULL, 
    [Period] INT NOT NULL, 
    [Value ] DECIMAL(18, 3) NOT NULL, 
    Primary key ([Product], [Resolution], [Period])
)

    [Map("[dbo].[MarketPrices]")]
    public class MarketPrice
    {
        public string Product { get; set; }
        [TypeMap(DbType.Int32)]
        public PeriodResolution Resolution { get; set; }
        public int Period{ get; set; }
        public decimal Value { get; set; }
    }

    public enum PeriodResolution
    {
        [Description("Uke")]
        Week,
        [Description("Måned")]
        Month,
        [Description("Kvartal")]
        Quarter,
        [Description("År")]
        Year,
        [Description("Egendefinert")]
        Custom
    }

Library Version:

RepoDb.SqlServer v1.1.3
.NET Core 3.1

[mikependon]

RepoDB should automatically map your PeriodResolution enum type on property Resolution to an INT type without you even defining such TypeMap attribute only if use the model-based operations (i.e.: Insert, Merge, Update etc).

Did you use the raw SQL approach when inserting and/or merging the data?

[mikependon]

Only if use the raw-SQL operations (i.e: ExecuteNonQuery and other Execute<Methods>), then this section from the documentation is relevant to you.

[Freysev]

I'm using MergeAllAsync, also tested with InsertAsync, same issue there

[mikependon]

Interesting as this is well covered here. Let me play around with it.

[mikependon]

A bit weird how is the integration test is passing, but I can replicate this problem. I will revisit all of them. When do you need the fix?

Project: RepoDbEnumProject.zip

[Freysev]

Of cource it would be nice to have it ASAP, but it is not to difficult to work around the issue for now

[mikependon]

I found your problem after playing around with it further. It is because you are using the composite keys in which RepoDB is not working well.

Would it be possible for you to create a dedicated Id for your table like below?

CREATE TABLE [dbo].[MarketPrices]
(
	[Id] INT IDENTITY PRIMARY KEY,
	[Product] VARCHAR(50) NOT NULL, 
	[Resolution] INT NOT NULL, 
	[Period] INT NOT NULL, 
	[Value] DECIMAL(18, 3) NOT NULL
)

[mikependon]

I also place this note on our disclaimer in relation to the limitations.

Your scenario is one of them.

[Freysev]

Ok, I'll see if i can avoid the comp key. Thank you for the quick responses :)

Just FYI, as it might be helpful when you are working on comp key issues in the future;
While trying to work around the issue i found some interesting things, i'm guessing, also related to composite key.

I tried adding this little get/set hack:

    [Map("[dbo].[MarketPrices]")]
    public class MarketPrice
    {
        public string Product { get; set; }
        public int Resolution { get; set; }
        public PeriodResolution PeriodResolution  //Code updated to ref this prop instead of 'Resolution'
        {
            get { return (PeriodResolution)Resolution; }
            set { Resolution = (int)value; }
        }
        public int Period{ get; set; }
        public decimal Value { get; set; }
    }

But the following method results in only a single row insertion

        public async Task UpsertTest()
        {
            using var connection = new SqlConnection(options.Value.ConnectionString);

            await connection.MergeAllAsync(new List<MarketPrice> {
                    new MarketPrice
                    {
                        Product = "A",
                        PeriodResolution = PeriodResolution.Month,
                        Period = 1,
                        Value = 1
                    },
                    new MarketPrice
                    {
                        Product = "A",
                        PeriodResolution = PeriodResolution.Quarter,
                        Period = 1,
                        Value = 1
                    }
            });
        }

Product | Resolution | Period | Value
A | 2 | 1 | 1.000

If i use InsertAsync individually for the two objects it works, i get two rows inserted, but if i try to use UpdateAsync on the inserted rows i get an insert (?) error
Violation of PRIMARY KEY constraint 'PK__MarketPr__6988E071DC4E9FB6'. Cannot insert duplicate key in object 'dbo.MarketPrices'. The duplicate key value is (A, 1, 1). The statement has been terminated.

[mikependon]

Yes, that is because RepoDB will use either 1 of the column from your composite key as the qualifier. Of course, unless otherwise you explicitly specify the qualifier fields in the qualifiers argument, this kind of issue is always prevalent.

This is known issue from our limitation page. In short, this is expected if you are using a composite key as the key as also reported by others.

[mikependon]

UpdateAsync? Throwing an Insert exception? As I know, SQL Server is deleting and reinserting a row if you update, that is visible if you are using the CDC, but this seems to me a problem in messaging.

Can you confirm that the insert exception is triggered by update?

[Freysev]

Hi, sorry for late answer. Yes, the error message from my last post came after a call to UpdateAsync.