Missing Column on entity On Table - column has underscores and space

[JamesSkemp]

We have a SQL Server table that uses underscores in the table and field names, and are attempting to using BulkInsert to add a number of records.

A combination of the following works fine for all columns except one:

BulkOperationManager.BulkOperationBuilder = operation => operation.MatchNamesWithUnderscores = true;
DapperPlusManager.Entity<TableName>().Table("dbo.TABLE_NAME");

The column that is not working includes a space in the column name, in addition to underscores. For example FAILING_COLUMN_ NAME.

If I add [System.ComponentModel.DataAnnotations.Schema.Column("FAILING_COLUMN_ NAME")] to the entity property, there's no errors, but the field values (for this column) also aren't saved to the database. All other fields in the table properly save.

If I update the DapperPlusManager above to the following (based upon https://dapper-plus.net/mapping#auto-mapping):

DapperPlusManager.Entity<TableName>().Table("dbo.TABLE_NAME")
    .Map(x => x.FailingColumnName, "FAILING_COLUMN_ NAME")
    .AutoMap()
                ;

Then I get the following error:

Missing Column : FAILING_COLUMN_ NAME
On entity : TableName
On Table : [dbo].[TABLE_NAME]

Partial stacktrace:

   at Z.BulkOperations.BulkOperation.()
   at Z.BulkOperations.BulkOperation.Execute()
   at Z.BulkOperations.BulkOperation.BulkInsert()
   at Z.Dapper.Plus.DapperPlusAction.Execute()
   at Z.Dapper.Plus.DapperPlusAction..ctor(BaseDapperPlusActionSet action, String key, DapperPlusActionKind kind, Object dataSource)
   at Z.Dapper.Plus.DapperPlusActionSet`1.AddAction(String mapperKey, DapperPlusActionKind actionKind, TEntity item)
   at Z.Dapper.Plus.DapperPlusActionSet`1.DapperPlusActionSetBuilder(DapperPlusContext context, IDbConnection connection, IDbTransaction transaction, String mapperKey, DapperPlusActionKind actionKind, TEntity item, Func`2[] selectors)
   at Z.Dapper.Plus.DapperPlusActionSet`1..ctor(DapperPlusContext context, IDbConnection connection, String mapperKey, DapperPlusActionKind actionKind, TEntity item, Func`2[] selectors)
   at Z.Dapper.Plus.DapperPlusExtensions.BulkInsert[T](IDbConnection connection, String mapperKey, T item, Func`2[] selectors)
   at Z.Dapper.Plus.DapperPlusExtensions.BulkInsert[T](IDbConnection connection, T item, Func`2[] selectors)
   [... calling custom code]

Throwing []s around the map - .Map(x => x.FailingColumnName, "[FAILING_COLUMN_ NAME]") and switching the order of .Map() and .AutoMap() (just in case) doesn't resolve the issue.

I'm able to query the table with that column name - select top 10 [FAILING_COLUMN_ NAME] from dbo.TABLE_NAME - so I know the column exists.

https://github.com/zzzprojects/Dapper-Plus/issues/82#issuecomment-765474631 does mention that all columns need to mapped, but given the date, and the link further above, it seems this is outdated. (The table also has 241 columns, so hoping I don't need to setup manual mapping for all of them.)

I also tried running BulkOperationManager.ClearInformationTableCache();, based upon https://github.com/zzzprojects/EntityFramework-Extensions/issues/313#issuecomment-571210415 mentioning cached table information, but that changed nothing.

What am I missing to help Dapper map the column for the insert?

Relevant versions:

• .NET 8
• Dapper 2.1.66
• Z.Dapper.Plus 7.5.11

[JonathanMagnan]

Hello @JamesSkemp,

Thank you for reporting this.

Looking at your issue, it seems you have a combination of underscore and space.

Could you try the following option?

BulkOperationManager.BulkOperationBuilder = operation => operation.UseSmartMatchNames = true;

This option is designed to skip dot, underscore, and spacing.

We will also investigate why the mapping is not working. However, it’s possible that MatchNamesWithUnderscores = true was still set when you tested, which could explain the behavior you encountered.

Best Regards,

Jon

[JamesSkemp]

Thanks for the head's up on UseSmartMatchNames - used some older code for setting this and didn't see that's a new option.

We will also investigate why the mapping is not working. However, it’s possible that MatchNamesWithUnderscores = true was still set when you tested, which could explain the behavior you encountered.

I did still have that set because I didn't put explicit column names (via [Column()]) on all properties on the class. (There's 241 total, so trying to leverage auto mapping as much as possible.) Sorry, I should have included that line in the second code snippet to be clearer that I only changed the DapperPlusManager line.

I tried the following:

1. No [Column("FAILING_COLUMN_ NAME")] on the class. Changed BulkOperationManager.BulkOperationBuilder = operation => operation.UseSmartMatchNames = true;. Removed DefaultTypeMap.MatchNamesWithUnderscores = true; (being set as a higher level in the code, just in case it's causing a conflict).
2. Added [Column("FAILING_COLUMN_ NAME")] on the class. Changed BulkOperationManager.BulkOperationBuilder = operation => operation.UseSmartMatchNames = true;. Removed DefaultTypeMap.MatchNamesWithUnderscores = true; (being set as a higher level in the code, just in case it's causing a conflict).

In both cases this field was still not mapping/updating, but the other 240 fields appeared to be fine.

A little more context, if it's helpful:

// constructor setup
{
    //DefaultTypeMap.MatchNamesWithUnderscores = true;
    SqlMapper.Settings.CommandTimeout = 0;
}

public void InsertData(List<TableName> claims)
{
    BulkOperationManager.BulkOperationBuilder = operation => operation.UseSmartMatchNames = true;
    DapperPlusManager.Entity<TableName>().Table("dbo.TABLE_NAME");

    using var connection = new SqlConnection(_connectionString);
    connection.BulkInsert(claims);
}

[JonathanMagnan]

Hello @JamesSkemp,

Thank you for the additional information. My developer will take a look, and if there’s something to fix, we’ll do our best to address it.

Best regards,

Jon

[JamesSkemp]

For what it's worth, adding an explicit [Column("...")] on every property and switching to the following allowed inserts.

BulkOperationManager.BulkOperationBuilder = operation => operation.UseSmartMatchNames = false;
DapperPlusManager.Entity<TableName>().Table("dbo.TABLE_NAME");

using var connection = new SqlConnection(_connectionString);
connection.BulkInsert(claims);

Not very pretty, but no issues with the FAILING_COLUMN_ NAME after switching it over.

Have a branch, so will be easy enough to test the previous code if your dev finds anything.

[JonathanMagnan]

Hello @JamesSkemp,

We currently have a branch under review that introduces a new option: MatchNameFactory. This will allow you to define exactly the logic you want to apply (e.g., removing dots, underscores, spaces, or any other characters).

Unfortunately, we won’t be able to deploy this fix tomorrow, as some changes are still required.

Best regards,

Jon

[JamesSkemp]

No worries. I'll keep an eye out for it and be prepared to give it some testing when it hits.

Thanks for the update!

[JonathanMagnan]

Hello @JamesSkemp ,

Sorry for the long delay, we forgot to release the fix last month for the MatchNameFactory I was talking.

The v7.6.0 is now available: https://github.com/zzzprojects/Dapper-Plus/releases/tag/v7.6.0

You can literally change anything you wish in your column name to match your property name using the MatchNameFactory. You can see in live action here: Online Example.

Best Regards,

Jon