Entity Framework : The Fastest Way to Insert Large Amount of Data - C# & .NET

Entity Framework is very useful when dealking with many data classes.  I am finding that when dealing with a large amount of records, Entity Framework is not the fastes solution.  There is BulkInsert feature in Entity Framework but I have not seen anything faster than SQL BulkCopy yet.  I will explain how to use it using an example below:
 

반응형

The example will be in two different steps.  
 
PRE-REQUISITE: Have a list of entities to insert ready
STEP 1: Convert the list to a data table easily
STEP 2: Insert the data using SQL BulkCopy function and the data table from Step 1
 
For this example, I will say we have students variable stored as IEnumerable<Student> type.

---

STEP 1: Convert the list to a data table easily
 
You can use the following static method to use to convert students to a data table type:

public static DataTable ToDataTable<T>(this IEnumerable<T> data)
{
    PropertyDescriptorCollection props = TypeDescriptor.GetProperties(typeof(T));
    var propsToImport = new List<PropertyDescriptor>();
    
    // A DATA TABLE TO RETURN
    DataTable table = new DataTable();
    
    // SET TABLE COLUMNS
    for (int i = 0; i < props.Count; i++)
    {
        PropertyDescriptor prop = props[i];
        Type type = Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType;
        propsToImport.Add(prop);
        table.Columns.Add(prop.Name, type);
    }

    object[] values = new object[propsToImport.Count];
    // INSERT DATA ROWS
    foreach (T item in data)
    {
        for (int i = 0; i < values.Length-1; i++)
        {
            values[i] = propsToImport[i].GetValue(item);
        }

        table.Rows.Add(values);
    }

    table.TableName = typeof(T).Name;
    return table;
}

Then you can use this method as below:

var dtStudents = students.ToDataTable();

Now you have a data table ready for the SQL Bulk Copy.
 

728x90

---

STEP 2: Insert the data using SQL BulkCopy function and the data table from Step 2
 
Using dtStudents from STEP 1, you can use the code block below to insert data.

// DEFINE TABLE MAPPING DICTIONARY FOR THE ENTITY
var tableColumnMappings = context.Student.GetProperties()
    .ToDictionary(p => p.PropertyInfo.Name, p => p.GetColumnName());

// connectionString IS A VARIABLE YOU DEFINE
using (var connection = new SqlConnection(connectionString))
{
    SqlTransaction transaction = null;
    connection.Open();
    try
    {
        transaction = connection.BeginTransaction();
        using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction))
        {
            // destinationTableName IS A VARIABLE YOU DEFINE
            sqlBulkCopy.DestinationTableName = detinationTableName;
            
            // MAP SQL TABLE USING tableComumnMappings
            foreach (var (field, column) in tableColumnMappings)
            {
                sqlBulkCopy.ColumnMappings.Add(field, column);
            }
            // dtStudents FROM STEP 1
            sqlBulkCopy.WriteToServer(dtStudents);
        }
        transaction.Commit();
    }
    catch (System.Exception ex)
    {
        transaction.Rollback();
    }
}

SqlBulkCopy is a part of Microsoft.Data.SqlClient namespace, so you must include it.

using Microsoft.Data.SqlClient;

That's it.  Enjoy and thank you!

---