How to use bulk insert about entityframework-extensions HOT 13 CLOSED

You almost got it. You don't have to specify the DbSet.

List<DictWord> dw = new List<DictWord>();
DictWord w = new DictWord { WordID = WordID_counter, Word = "word1", WordStat = 20 };
dw.Add(w);

context.BulkInsert(dw);

Best Regards,

Jonathan

from entityframework-extensions.

Thank you, but why don't I have to specify the DbSet? It kinda relies on <DictWord> type or something(he-he))?
Anyway I tried it with MSSQL LocalDB and it worked! (And it worked much faster. Strange, I measured the time it takes to context.SaveChanges() and context.BulkSaveChanges() the first one took 0 seconds, the second one 0,027 seconds, funny))
I actually need to use other DBMS, when I switch to PostgreSQL it says "The Provider could not be resolved. You must explicity set the Provider.". Is there any chance to fix it?

from entityframework-extensions.

DbSet
We don’t have to specify the DbSet since by using the entity type we can easily find out which DbSet the entity belongs. However, we added this idea to our suggestions list to allow also to make bulk operations directly from DbSet.

Benchmarking
Most people make the same error when performing benchmarking and forget about the JIT compilation. The Entity Framework has been already JIT compiled from a previous statement, so you are currently comparing the time of SaveChanges vs BulkSaveChanges + JIT Compilation, which is unfair. You must always call both methods once before performing your benchmark.

Provider
The PostgreSQL is currently under development and almost completed. The provider will be supported tomorrow or next week, so it is very close.

from entityframework-extensions.

COOL!!! You're good))

from entityframework-extensions.

Is it still not supported in new 3.9.26 version?

from entityframework-extensions.

It partially supported in the version 3.9.26, we almost completed all the test required. Only a few tests with some column type are remaining. The provider took a little bit more time than expected because I was sick in the past few days.

We really hope to release the version this Friday.

from entityframework-extensions.

Hi,
When I use the follow code to insert about 3 million record to one table, it throw a OutOfMemoryException.
The array is Array object.

this.db.BulkInsert<TB_Statistics>(array, (batchOption)=> batchOption.BatchSize = 1000);

So I use the followed code, but why the upper code throw a exception?

int insertCount = 0, batchSize = 1000;
while (insertCount < array.Length)
{
this.db.BulkInsert<TB_Statistics>(array.Skip(insertCount).Take(batchSize));
insertCount += batchSize;
}

from entityframework-extensions.

Hello Hesi,

This error happens because BulkInsert transfers all values into a DataTable for performing the insertion. After some millions of entities, the DataTable become too large and throw this error.

The code will be modified this week-end and a new version should be available within the few first day of the next week fixing this issue. We will remove the DataTable code to allow streaming via a DataReader instead.

from entityframework-extensions.

And I found another bug.
I need update 139000 records , so I use the follows code to update.

var updateIndex = 0;
List updateTaskList = new List();
while (updateIndex < changedComplex.Count)
{
int[] updateSubArray = changedComplex.SubArray(ref updateIndex, batchSize);
var task = this.db.TB_Statistics
.Where((a) => updateSubArray.Contains(a.Id) && a.LastStatisticsTradeDate.HasValue)
.UpdateFromQueryAsync((a) => new TB_Statistics { LastStatisticsTradeDate = null });
updateTaskList.Add(task);
}
Task.WaitAll(updateTaskList.ToArray());

Then,

System.AggregateException
HResult=-2146233088
Message=发生一个或多个错误。(some errors happened bala bala....)
Source=mscorlib
StackTrace:
在 System.Threading.Tasks.Task.WaitAll(Task[] tasks, Int32 millisecondsTimeout, CancellationToken cancellationToken)
在 System.Threading.Tasks.Task.WaitAll(Task[] tasks, Int32 millisecondsTimeout)
在 System.Threading.Tasks.Task.WaitAll(Task[] tasks)
在 Web.Controllers.ConditionController.<>c__DisplayClass5_4.b__8() 在 E:\TotalDevelop\StockAnalyze\Web\Controllers\ConditionController.cs 中: 第 271 行
在 System.Threading.Tasks.Task.InnerInvoke()
在 System.Threading.Tasks.Task.Execute()
InnerException:
HResult=-2147024809
Message=已添加了具有相同键的项。(Have add the same key bala bala...)
Source=mscorlib
StackTrace:
在 System.ThrowHelper.ThrowArgumentException(ExceptionResource resource)
在 System.Collections.Generic.Dictionary2.Insert(TKey key, TValue value, Boolean add) 在 . . ( , IEnumerable1 )
在 . . (IEnumerable1 ) 在 . . ( ) 在 . . ( ) 在 . . ( ) 在 . . ( ) 在 . . ( ) 在 . . ( ) 在 . . ( ) 在 . . ( ) 在 . . ( ) 在 . . ( ) 在 . . ( ) 在 . . (Type , ) 在 . . (String , ParameterExpression[] , Object[] , Type ) 在 . . (Type , Type , String , Object[] ) 在 . . (IQueryable , String , Object[] ) 在 DbContextExtensions.UpdateFromQuery[TEntity](IQueryable1 query, Expression1 updateExpression, Action1 bulkOperationFactory)
在 DbContextExtensions.UpdateFromQuery[TEntity](IQueryable1 query, Expression1 updateExpression)

from entityframework-extensions.

Thank you Hesi,

We will check this one after fixing the streaming issue.

from entityframework-extensions.

@Plavozont

We just released the v3.9.29 which support all common type for PostgreSQL,

Can you give it a try and let us know if you get some error?

We did over 3500 unit tests for this provider, but we are aware we still have to support some uncommon database type.

from entityframework-extensions.

@hesi726

We just released the v3.9.30 which supports DataTable Batch Size. We did not success to support streaming because to much code was involved and we don't want to change to much our library (we never know about side effect!). We will for sure code in the next major version using streaming instead of DataTable.

The fix is very similar to what you did

using (var ctx = new CodeFirstEntities())
{
    // Examples
    ctx.BulkInsert(list, operation =>
    {
        operation.DataTableBatchSize = 1000000;
        operation.BatchSize = 1000;

    });
}

Every 1,000,000 records, a new data table will be created and a bulk insert will be performed. The BulkInsert will insert record in the destination table in batch of 1000.

We will check the issue about UpdateFromQuery tomorrow.

from entityframework-extensions.

@hesi726

We just released the v3.9.32 fixing the UpdateFromQueryAsync issues.

Make sure to use a different context instance for every task else our code will fail. Unfortunately, the library doesn't support yet shared DbContext instance in a concurrent scenario.

We will close this issue since every request should be completed. Feel free to re-open it or create a new one.

from entityframework-extensions.