How to insert using SqlBulkCopy with EntityFramework

The same existing connection can be used in the Entity Framework , but, this is not a rule that can be done one, only SQLBulkCopy requires a DataTable or IDataReader to write the batch of information in

using (MyDbContext db = new MyDbContext())
{
    SqlConnection connection = (SqlConnection)db.Database.Connection;
    connection.Open();

    SqlBulkCopy copy = new SqlBulkCopy(connection);
    copy.DestinationTableName = "PessoaNotificacaoId";
    copy.ColumnMappings.Add("PessoaNotificacaoId", "PessoaNotificacaoId");
    copy.ColumnMappings.Add("PessoaUnidadeId", "PessoaUnidadeId");
    copy.ColumnMappings.Add("NotificacaoId", "NotificacaoId");
    copy.ColumnMappings.Add("Visualizado", "Visualizado");


    DataTable dt = new DataTable();
    dt.Columns.Add("PessoaNotificacaoId", typeof(int));
    dt.Columns.Add("PessoaUnidadeId", typeof(int));
    dt.Columns.Add("NotificacaoId", typeof(int));
    dt.Columns.Add("Visualizado", typeof(bool));

    DataRow row1 = dt.NewRow();
    row1["PessoaNotificacaoId"] = 1;
    row1["PessoaUnidadeId"] = 1;
    row1["NotificacaoId"] = 1;
    row1["Visualizado"] = true;

    DataRow row2 = dt.NewRow();
    row2["PessoaNotificacaoId"] = 2;
    row2["PessoaUnidadeId"] = 2;
    row2["NotificacaoId"] = 2;
    row2["Visualizado"] = true;

    DataRow row3 = dt.NewRow();
    row3["PessoaNotificacaoId"] = 3;
    row3["PessoaUnidadeId"] = 3;
    row3["NotificacaoId"] = 3;
    row3["Visualizado"] = true;
    dt.Rows.Add(row1);
    dt.Rows.Add(row2);
    dt.Rows.Add(row3);

    copy.WriteToServer(dt);
}

In this example, you can do that question interaction and generate the Rows of that DataTable , example :

DataTable dt = new DataTable();
DataRow row = null;
foreach (PessoaUnidade pessoa in pessoas)
{
    row = dt.NewRow();
    row["PessoaNotificacaoId"] = pessoa.Id;
    row["PessoaUnidadeId"] = pessoa.Id;
    row["NotificacaoId"] = notificacao.Id;
    row["Visualizado"] = false;
    dt.Rows.Add(row);
}

If you want to convert with a code it would be very practical to use a routine like this:

public static DataTable ConvertToDataTable<T>(IList<T> data)
{
    PropertyDescriptorCollection properties =
       TypeDescriptor.GetProperties(typeof(T));
    DataTable table = new DataTable();
    foreach (PropertyDescriptor prop in properties)
        table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) 
           ?? prop.PropertyType);
    foreach (T item in data)
    {
        DataRow row = table.NewRow();
        foreach (PropertyDescriptor prop in properties)
            row[prop.Name] = prop.GetValue(item)
             ?? DBNull.Value;
        table.Rows.Add(row);
    }
    return table;

}

But, do not forget that the first one is most important for using SQLBulkCopy , including to understand the process.

• Transferring Data Using SqlBulkCopy
• SQLBulkCopy
• DataTable
• IDataReader
• A Sql Bulk Insert for Entity Framework 6.

According to Microsoft documentation If the copy (source) and destination data are in the same instance of SQL Server, it is faster and more efficient to use Transact SQL yourself, for example in a procedure.

Another point, you are using a (foreach) loop to scan all records to generate the changes and then re-insert them into the database.

You can easily convert this code to a INSERT SELECT , which will run much faster, especially with the number of records.

Examples: Simple example to copy all columns

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

Example to copy only the required columns or modify some value

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

Reference: INSERT SELECT COMMAND