Try .NET /elinq/
Powered by Try .NET

Efficient Data Modification

The rule of dumb tells that for optimal performance we need to make as few database requests as possible. This is especially relevant for insert and update scenarios, where we sometimes need to work with thousands of objects. Sending those objects to the database one by one is usually significantly slower than in a batch.

ELINQ lets write optimized INSERT queries using C#:

In case of data update there are 3 important scenarios:

And the last scenario - UPSERT. INSERT the new rows and UPDATE existing. Most vendors support it, but with different syntax and capabilities:

// There is a store which might already exist in the database.
// Should we add it or update? (PK is not always the only UNIQUE KEY)
newOrExisting.LastUpdate = DateTime.Now;

var rows = DbContext.Database.Query((Store store) =>
{
    var view = store.@using((store.StoreId, store.AddressId, store.ManagerStaffId, store.LastUpdate));
    INSERT().INTO(view);
    VALUES(view.RowFrom(newOrExisting));
    ON_DUPLICATE_KEY_UPDATE(() => store.LastUpdate = INSERTED_VALUES(store.LastUpdate));
});

Console.WriteLine($"{rows} rows affected");

The queries can be freely combined as demonstrated in the MERGE above.

< BACK | HOME