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

[ElenaShlykova]

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]

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]

We try to find solution where models will stay clean

[JonathanMagnan]

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]

@JonathanMagnan do you have an update on this?

[JonathanMagnan]

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