How to execute huge SQL queries rapidly with EFCore?-CodePudding

Here are two simplified examples of SQL queries I recently run on my SQL database:

insert into new_entity
select top 10000 field1,
                 field2
from old_entity

update top 1000 e
            set field1 = 13
           from entity e
          where field1 is null

Their execution was so fast it's barely noticeable.

However, if I want to perform the same operation using EF, the way I know would be iterating over each object:

using(var db = new myDbContext())
{
  var new_objs = db.old_entity.Take(10000).Select(ot=> new new_entity() { ... });
  db.new_entity.AddRange(new_objs);
  db.SaveChanges();
}

using(var db = new myDbContext())
{
  var objs = db.entity.Where(e => e.field1 == null).Take(1000);
  objs.ForEach(e => e.field1 = 13);
  db.SaveChanges();
}

Which lasted for hours, which is unacceptable.

I can execute a raw SQL query from within the app, but for real-life complex objects, it's a dradgery.

Is there a way to code such operations using the EF model with a performance of a directly written query?

CodePudding user response：

EF Core 7 have introduced new method ExecuteUpdate. And your query for update can be written in the following way:

using(var db = new myDbContext())
{
    var objs = db.entity.Where(e => e.field1 == null).Take(1000);
    objs.ExecuteUpdate(b => b.SetProperty(x => x.field1, x => 13));
}

INSERT FROM is not supported by EF Core any version.

Anyway, you can install third-party extension linq2db.EntityFrameworkCore, note that i'm one of the creators.

Insert

using(var db = new myDbContext())
{
    var new_objs = db.old_entity.Take(10000).Select(ot=> new new_entity() { ... });

    new_objs.Insert(db.new_entity.ToLinqToDBTable());
}

Update

using(var db = new myDbContext())
{
    var objs = db.entity.Where(e => e.field1 == null).Take(1000);

    objs
      .Set(x => x.field1, x => 13)
      .Update();
}