使用DbContext時，不獲取實體更新數據的注意點

其實真的是個很無聊的問題…但一不小心就會中套，留條記錄備忘好了。

我就是想單純的增加某個實體的點擊量，也不在意並發，如果還要獲取實體再update真是毫無意義。

於是：

public IShoppingService IncreaseGoodsHits(Guid id)

{

    long hits = _ctx.Commodities.Where(c => c.Id == id && !c.IsOffShelves).Select(c => c.Hits).SingleOrDefault();

    var goods = new Commodity { Id = id, Hits = ++hits };

    try

    {

        _ctx.Commodities.Attach(goods);

        _ctx.Entry(goods).Property(c => c.Hits).IsModified = true;

        _entities.SaveChanges();

    }

    catch

    {

    }

    

    return this;

}

好吧我悲劇了。原因很簡單，這個表有一個RowVersion字段，而且他的Concurrency Mode是Fixed（廢話），所以想要更新就必須獲取RowVersion字段並且將EF的Validation關閉。這是因為有timestamp的情況下EF將使用pk和timestamp作為update的where子句，如果不獲取那麼會拋出DbConcurrencyException異常，而不關閉Validation則會在Save時自動驗證實體，我只寫了一個Hits，必然會拋出DbEntityValidationException異常…這樣的話是無論如何也沒法update了。

於是修改如下： 

public IShoppingService IncreaseGoodsHits(Guid id)
{
    try
    {
        var hits = _ctx.Commodities.Where(c => c.Id == id && !c.IsOffShelves).Select(c => new { c.Hits, c.RowVersion }).Single();
        var goods = new Commodity { Id = id, Hits = hits.Hits + 1, RowVersion = hits.RowVersion };
        _ctx.Configuration.ValidateOnSaveEnabled = false;
        _ctx.Commodities.Attach(goods);
        _ctx.Entry(goods).Property(c => c.Hits).IsModified = true;
        _entities.SaveChanges();
    }
    catch
    {
    }
    finally
    {
        _ctx.Configuration.ValidateOnSaveEnabled = true;
    }
    
    return this;
}

好了，看看生成的SQL： 

exec sp_executesql N'SELECT TOP (2) 
1 AS [C1], 
[Extent1].[Hits] AS [Hits], 
[Extent1].[RowVersion] AS [RowVersion]
FROM [dbo].[Commodities] AS [Extent1]
WHERE ([Extent1].[Id] = @p__linq__0) AND ([Extent1].[IsOffShelves] <> cast(1 as bit))',N'@p__linq__0 uniqueidentifier',@p__linq__0='B07D0508-1142-4A89-821A-49DFA189C88A'

exec sp_executesql N'update [dbo].[Commodities]
set [Hits] = @0
where (([Id] = @1) and ([RowVersion] = @2))
select [RowVersion]
from [dbo].[Commodities]
where @@ROWCOUNT > 0 and [Id] = @1',N'@0 bigint,@1 uniqueidentifier,@2 binary(8)',@0=34,@1='B07D0508-1142-4A89-821A-49DFA189C88A',@2=0x000000000001733D

似乎沒什麼問題了，就先這樣吧。