CODEWITHSUNDEEP
entity-framework-coremariadbefcore.bulkextensions
dellancelod
dellancelod

Reputation: 31

BulkInsertOrUpdate causes MariaDB exception

I try to bulk merge into database table like this:

public async Task UpdateCitiesAsync()
{
    var request = new
    {
        apiKey = _apiKey,
        modelName = "AddressGeneral",
        calledMethod = "getCities"
    };

    string result = await ApiRequest(request);

    var cityResponse = JsonConvert.DeserializeObject<NovaPoshtaResponse<City>>(result);

    await _context.BulkInsertOrUpdateAsync(cityResponse.Data);

    await _context.SaveChangesAsync();
}

but receive this exception:

MySqlConnector.MySqlException: "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '0 T.[Ref], T.[Description] INTO [CitiesTempdada41e4] FROM [Cities] AS T LEFT ...' at line 1"

Above code worked right before I decided to change AddRange to bulk extension method. BulkExtensions version is 6.0.2.

Upvotes: 0

Views: 42

Answers (1)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131641

There's no BulkInsertOrUpdateAsync in EF Core. There's no "bulk update" in the sense of the fast, minimally logged BULK INSERT in SQL Server or COPY in MySQL and MariaDB. That third party extension doesn't do what it claims.

Since you're already using MySqlConnector you can use the MySqlBulkCopy class to execute a COPY from the client side :

var dataTable = GetDataTableFromExternalSource();

// open the connection
using var connection = new MySqlConnection("...;AllowLoadLocalInfile=True");
await connection.OpenAsync();

// bulk copy the data
var bulkCopy = new MySqlBulkCopy(connection);
bulkCopy.DestinationTableName = "some_table_name";
var result = await bulkCopy.WriteToServerAsync(dataTable);

You can control what happens in case of conflict by setting the ConflictOption property, eg :

var bulkCopy = new MySqlBulkCopy(connection);
bulkCopy.DestinationTableName = "some_table_name";
bulkCopy.ConflictOption=MySqlBulkLoaderConflictOption.Replace;
var result = await bulkCopy.WriteToServerAsync(dataTable);

What the extension actually does, is a classic ETL technique. It stores the data using SqlBulkCopy into a temporary table it creates silently, then first UPDATEing the target with an INNER JOIN

UPDATE TARGET
SET
   ...
FROM tempTable INNER JOIN Target on tempTable.ID=Target.ID

Followed by an INSERT using a LEFT JOIN to only pick new rows

INSERT INTO Target (...)
SELECT ....
FROM tempTable LEFT Join Target on tempTable.ID=Target.ID
Where Target.ID is NULL

While that may seem convenient, it gives you no control over the location or indexing of the staging table, join keys, filter conditions or transformations a script would give you.

Upvotes: 0

Related Questions