最好调用context.SaveChanges只有一次?(Would it be best to call context.SaveChanges only once?)

496 IT屋

I'm semi new to EF and I'm converting a Windows Service App over to using EF. It will read a text file with 11k employees in it and look to see if a record exists or not and either do an insert or update.

Currently I'm looping through each line, create a new entity object with the values, pass that to a data class and in there it will look up the entity to see if it will insert or update. Thing is I'm calling the context.SaveChanges() each record.

Now I'm wondering if I should create a list of entities for all employees. Then I would send that list into the data class, loop through the list, set the modified state or attach and then do the SaveChanges after I've gone through the whole list. Would that work and would it only call an insert/update one time or would it still a query 11k times for all the records?

Just looking for a fast way to do this.

foreach (string line in lines)
{
    if (line.Trim().Length > 0)
    {
        SAP_DATES dateRecord = new SAP_DATES();
        dateRecord.EMP = line.Substring(0, 8);
        dateRecord.DATE_TYPE = line.Substring(10, 20);
        dateRecord.DATE_VALUE = Convert.ToDateTime(line.Substring(30, 10));
        dateRecord.DATE_MODIFIED = Convert.ToDateTime(line.Substring(40, 10)); 

        Data.DataManager.ProcessDateRecord(dateRecord);     
    }
}

This is in my Data Class

public static void ProcessDateRecord(SAP_DATES dateRecord)
{
    using (DataContext db = new DataContext())
    {
        var original = db.SAP_DATES.Find(dateRecord.EMP, dateRecord.DATE_TYPE);

        if (original != null)
        {
            db.Entry(original).CurrentValues.SetValues(dateRecord);                    
        }
        else
        {
            db.SAP_DATES.Add(dateRecord);
        }
        db.SaveChanges();
    }     
}
解决方案

Both technique will be very slow but using SaveChanges every X records may improve the performance by a little bit.

Why is slow?

  • The Find method perform a database round trip for every record
  • The “Add” or “Update” perform a database round trip for every record

So if you have 11k records, both of your solution will make 22k database round trip which is insane.

Disclaimer: I'm the owner of the project Entity Framework Extensions

If you are really looking for the faster way to Upsert records, this library will improve dramatically your performance by 2000% and more!

The merge statements will update existing records and insert new records.

using (DataContext db = new DataContext())
{
    // Using EF Key
    db.BulkMerge(list);

    // Using a Custom Key
    db.BulkMerge(list, ctx.BulkMerge(list, 
          operation => operation.ColumnPrimaryKeyExpression = date => 
                       new { date.EMP, date.DATE_TYPE });
}  
我是EF的半新人,我正在将Windows服务应用程序转换为使用EF。它会读取一个11k个员工的文本文件,并查看是否存在一条记录,并且进行插入或更新。



目前,我正在循环遍历每行,使用值创建一个新的实体对象,并将其传递给数据类,并在其中查找实体以查看如果它将插入或更新。我正在调用context.SaveChanges()每个记录。



现在我想知道我是否应该为所有员工创建一个实体列表。然后我将该列表发送到数据类中,循环遍历列表,设置修改后的状态或附加,然后在完成整个列表后执行SaveChanges。这会工作,只会调用一次插入/更新,还是对所有记录仍然查询11k次?



只需寻找一个快速的方法来实现。



  foreach (行中的行)
{
if(line.Trim()。Length> 0)
{
SAP_DATES dateRecord = new SAP_DATES();
dateRecord.EMP = line.Substring(0,8);
dateRecord.DATE_TYPE = line.Substring(10,20);
dateRecord.DATE_VALUE = Convert.ToDateTime(line.Substring(30,10));
dateRecord.DATE_MODIFIED = Convert.ToDateTime(line.Substring(40,10));

Data.DataManager.ProcessDateRecord(dateRecord);
}
}


这是在我的数据类



  public static void ProcessDateRecord(SAP_DATES dateRecord)
{
using(DataContext db = new DataContext())
{
var original = db.SAP_DATES.Find(dateRecord.EMP,dateRecord.DATE_TYPE);

if(original!= null)
{
db.Entry(original).CurrentValues.SetValues(dateRecord);
}
else
{
db.SAP_DATES.Add(dateRecord);
}
db.SaveChanges();
}
}

解决方案

技术将非常慢,但使用SaveChanges每X记录可能会提高一点点的性能。



为什么慢?




  • Find方法为每条记录执行数据库往返

  • "添加"或"更新"执行数据库往返每个记录



所以如果你有11k的记录,你的两个解决方案将使22k数据库往返这是疯狂的。



免责声明:我是项目的所有者实体框架扩展



如果您真的在寻找Upsert记录的更快的方式,该库将大大提高2000%的性能,更多!



合并语句将更新现有记录并插入新记录。



  using(DataContext db = new DataContext())
{
//使用EF键
db.BulkMerge(list) ;

//使用自定义键
db.BulkMerge(list,ctx.BulkMerge(list,
operation => operation.ColumnPrimaryKeyExpression = date =>
新的{date.EMP,date.DATE_TYPE});
}

本文地址:IT屋 » 最好调用context.SaveChanges只有一次?