sql - Entity Framework ，更高效地更新多个字段

Question

使用 Entity Framework ，我大约每 30 秒更新大约 300 行和 9 列。以下是我目前正在做的事情。我的问题是，如何使代码更加高效？

每隔一段时间，我都会觉得我的数据库受到影响，我只想让它尽可能高效。

// FOREACH OF MY 300 ROWS
var original = db.MarketDatas.FirstOrDefault(x => x.BBSymbol == targetBBsymbol);

if (original != null)
{
    //if (original.BBSymbol.ToUpper() == "NOH7 INDEX")
    //{
    //    var x1 = 1;
    //}

    original.last_price = marketDataItem.last_price;
    original.bid = marketDataItem.bid;
    original.ask = marketDataItem.ask;

    if (marketDataItem.px_settle_last_dt_rt != null)
    {
        original.px_settle_last_dt_rt = marketDataItem.px_settle_last_dt_rt;
    }

    if (marketDataItem.px_settle_actual_rt != 0)
    {
        original.px_settle_actual_rt = marketDataItem.px_settle_actual_rt;
    }

    original.chg_on_day = marketDataItem.chg_on_day;

    if (marketDataItem.prev_close_value_realtime != 0)
    {
        original.prev_close_value_realtime = marketDataItem.prev_close_value_realtime;
    }

    if (marketDataItem.px_settle_last_dt_rt != null)
    {
        DateTime d2 = (DateTime)marketDataItem.px_settle_last_dt_rt;

        if (d1.Day == d2.Day)
        {
            //market has settled
            original.settled = "yes";
        }
        else
        {
            //market has NOT settled
            original.settled = "no";
        }
    }

    if (marketDataItem.updateTime.Year != 1)
    {
        original.updateTime = marketDataItem.updateTime;
    }

    db.SaveChanges();
}

观察调试器中正在发生的事情...

SELECT TOP (1) 
[Extent1].[MarketDataID] AS [MarketDataID], 
[Extent1].[BBSymbol] AS [BBSymbol], 
[Extent1].[Name] AS [Name], 
[Extent1].[fut_Val_Pt] AS [fut_Val_Pt], 
[Extent1].[crncy] AS [crncy], 
[Extent1].[fut_tick_size] AS [fut_tick_size], 
[Extent1].[fut_tick_val] AS [fut_tick_val], 
[Extent1].[fut_init_spec_ml] AS [fut_init_spec_ml], 
[Extent1].[last_price] AS [last_price], 
[Extent1].[bid] AS [bid], 
[Extent1].[ask] AS [ask], 
[Extent1].[px_settle_last_dt_rt] AS [px_settle_last_dt_rt], 
[Extent1].[px_settle_actual_rt] AS [px_settle_actual_rt], 
[Extent1].[settled] AS [settled], 
[Extent1].[chg_on_day] AS [chg_on_day], 
[Extent1].[prev_close_value_realtime] AS [prev_close_value_realtime], 
[Extent1].[last_tradeable_dt] AS [last_tradeable_dt], 
[Extent1].[fut_notice_first] AS [fut_notice_first], 
[Extent1].[updateTime] AS [updateTime]
FROM [dbo].[MarketDatas] AS [Extent1]
WHERE ([Extent1].[BBSymbol] = @p__linq__0) OR (([Extent1].[BBSymbol] IS NULL) AND (@p__linq__0 IS NULL))

如果我理解正确的话，它似乎多次更新相同的内容。

UPDATE [dbo].[MarketDatas]
SET [last_price] = @0, [chg_on_day] = @1, [updateTime] = @2
WHERE ([MarketDataID] = @3)

UPDATE [dbo].[MarketDatas]
SET [last_price] = @0, [chg_on_day] = @1, [updateTime] = @2
WHERE ([MarketDataID] = @3)

Answer 1

您可以将往返次数减少到 2 次。

1. 不要在循环内部调用SaveChanges()。处理完所有内容后将其移到外面并调用它。
2. 以这样一种方式编写选择，即一次性检索所有原始数据并将它们推送到内存集合，然后从中检索要更新/插入的每个项目。

代码

// use this as your source
// to retrieve an item later use TryGetValue
var originals = db.MarketDatas
  .Where(x => arrayOftargetBBsymbol.Contains(x.BBSymbol));
  .ToDictionary(x => x.BBSymbol, y => y);

// iterate over changes you want to make
foreach(var change in changes){
    MarketData original = null;

    // is there an existing entity
    if(originals.TryGetValue(change.targetBBsymbol, out original)){
        // update your original
    }
}

// save changes all at once
db.SaveChanges();