Support ColumnMappings in MySqlBulkCopy

[themagicis]

I'm using MySqlBulkCopy to load data from a data reader into a MariaDb 10.4 database.
I'm using EF core and Pomelo. Code first way to create the database.
It doesn't work if the column order of the data reader doesn't match the database column order.
The EF migration creates the db tables with some ordering of the columns.
The issue occurs when I try to use MySqlBulkCopy and I pass a data reader with different order of the columns. I checked the sql query - the LOAD DATA statement orders the columns as they are in the database, but when the data is sent to the server, the column are written as they are in the data reader. So I got an error that string value cannot be inserted into a numerical column.
The data reader is a FastMember ObjectReader. If I change the columns order of the object reader to match the database order, MySqlBulkCopy is working as exepcted.

[bgrainger]

As you've discovered, MySqlBulkCopy currently requires that the bulk copy source contain data for all database columns (i.e., you can't rely on DEFAULT values) and that they be in the same order as the destination table.

This restriction will probably be relaxed by implementing a ColumnMappings property similar to SqlBulkCopy.ColumnMappings.

I haven't decided what the API shape of that should be yet, and if it should support expressions (like MySqlBulkLoader).

If I change the columns order of the object reader to match the database order, MySqlBulkCopy is working as exepcted.

This is the current workaround you'll need to use.

[bgrainger]

Added this known issue to the documentation: 46783e3

[themagicis]

Thanks, having option like SqlBulkCopy.ColumnMappings would be very useful. Especially when using ORMs and cases where the data source has different column names compared to the database.

[MickaelBillet]

Thanks, having option like SqlBulkCopy.ColumnMappings would be very useful. Especially when using ORMs and cases where the data source has different column names compared to the database.

[mikeTWC1984]

+1. This is also useful if destination table has autoincremental column.

[bgrainger]

The initial API I'm thinking of going with is:

class MySqlBulkCopyColumnMapping
{
    public int SourceOrdinal { get; set; }
    public string DestinationColumn { get; set; }
    public string Expression { get; set; }
}

SourceColumn would require enumerating the input source (of which there can be many different types) to map column names to ordinals. DestinationOrdinal would require getting the columns of the destination table to make positions to column names. These would both be less efficient (and more complicated) than simply mapping source ordinals to destination column names so I would omit them from the initial implementation unless there's user feedback that they'd be necessary.

Expression would be an optional expression (accepted by LOAD DATA) that transforms an input column to a destination column.

[bgrainger]

Added in 0.65.0; would like to hear your feedback.

Documentation at https://mysqlconnector.net/api/mysql-bulk-copy/.

[mikeTWC1984]

Good work. I think in most cases mapping would come from some dictionary though (key as source, value as destination). Also ordinal look a bit more tricky than column name. MSSQL uses mapping collection which is a property on bulkcopy object, and you can add new mapping just like that: bcp.ColumnMappings.Add("srcName", "dstName").

[bgrainger]

MSSQL uses mapping collection which is a property on bulkcopy object, and you can add new mapping just like that: bcp.ColumnMappings.Add("srcName", "dstName").

Its custom collection type (probably?) predates generics, and certainly predates usability enhancements in the C# language, e.g., collection initialisers. I think it's overkill to implement a custom collection type right now, and it will become even easier to use in C# 9 with target-typed new:

var bulkCopy = new MySqlBulkCopy
{
    DestinationTableName = "table",
    ColumnMappings =
    {
        new(0, "colA"),
        new() { SourceOrdinal = 1, DestinationColumn = "colB" },
    }
};

bulkCopy.ColumnMappings.Add(new(2, "colC"));

Also ordinal look a bit more tricky than column name.

Adding SourceColumn and automatically mapping it to the corresponding SourceOrdinal is a reasonable future extension (for input sources that can support such a lookup).

[bgrainger]

it will become even easier to use in C# 9 with target-typed new

Example:

MySqlConnector/tests/SideBySide/BulkLoaderSync.cs

Lines 773 to 782 in 2cbc4dd

	var bulkCopy = new MySqlBulkCopy(connection)
	{
	DestinationTableName = "bulk_copy_column_mapping",
	ColumnMappings =
	{
	new(1, "@val", "intvalue = @val + 1"),
	new(3, "text"),
	new(4, "data"),
	},
	};