zzzprojects / Dapper-Plus

Dapper Plus - High-Efficient Bulk Actions (Insert, Update, Delete, and Merge) for .NET
https://dapper-plus.net/
384 stars 85 forks source link

Different type for Id column in the model and in the database #80

Closed ElenaShlykova closed 7 months ago

ElenaShlykova commented 3 years ago

Hello, We have a requirement to use a string value for a Id column in all models in the code and int in the database. It is needed so the customer can change the db type any time without significant changes in the code. I created such mapping:

            DapperPlusManager.Entity<TestEntity>()
                             .Key(x => x.Id, "Id")
                             .Output(x => x.Id)
                             .Table(BaseDaoTestsHelper.TestTableName);

But I receive such an error when I try to use BulkInsert: System.InvalidCastException: 'Unable to cast object of type 'System.Int32' to type 'System.String'.' It is worth mentioning that the object is saved in db. An error occurs only for identity/output columns.

Is there any workaround for such a case?

JonathanMagnan commented 3 years ago

Hello @ElenaShlykova ,

What about you create a new property on your side:

public int AnotherNameID {
    get { return Convert.ToInt32(Id); }
    set { Id = value.ToString();  }
}

I believe the best would be to handle it on your side as you will get a global solution for any other third party libraries or even your code as well.

Or perhaps that doesn't work either due to your architecture limitations?

Best Regards,

Jon

ElenaShlykova commented 3 years ago

We try to find solution where models will stay clean

JonathanMagnan commented 3 years ago

No problem,

We already have a solution for a normal column, however, it doesn't work yet with the key.

I will ask my developer to make it compatible as well with the key if possible.

karolswdev commented 7 months ago

@JonathanMagnan do you have an update on this?

JonathanMagnan commented 7 months ago

Hello @karolswdev ,

We have indeed had a solution since this time. You can now add BulkValueConverter. It's normally used for type we do not handle but you can use it with int and string as well.

Here is an example:

DapperPlusManager.Entity<EntitySimple>().Table("EntitySimples").Identity(x => x.ID).Output(x => x.ColumnString);
DapperPlusManager.AddValueConverter(typeof(string), new StringTypeHandler());

using (var connection = new SqlConnection(My.ConnectionStringMik))
{
    connection.BulkInsert(list);
}

public class StringTypeHandler : IBulkValueConverter
{
    public object ConvertFromProvider(Type destinationType, object value)
    {
        if(value is int)
        {
            // handle logic when the value is a `int` but a string is expected
            if (value == null || value == DBNull.Value)
            {
                return null;
            }

            return value.ToString();
        }

        // otherwise, return always the value
        return value;
    }

    public object ConvertToProvider(object value)
    {
        return value;
    }
}

Let me know if that could solve this kind of scenario.

Best Regards,

Jon