只用linq更新1列

I'm using linq to retrieve a row from a table in the database. Now I only update 1 column. Then I update it back into the database.

我正在使用linq从数据库中的表中检索一行。现在我只更新1列。然后我将其更新回数据库。

this goes well, except for the times any of the other fields is changed by another process/thread/user In that case I get an exception (optimistic concurrency), telling me to look out, values have been changed since I last got the object with linq.

这很顺利,除了任何其他字段由另一个进程/线程/用户更改的时间在这种情况下我得到一个异常(乐观并发),告诉我注意,自从我上次获得对象后值已经更改与linq。

Since I'm only interested in this 1 column, can I tell linq to just update this column and ignore the others? (and warn me this one column has indeed been changed)

由于我只对这一列感兴趣,我可以告诉linq只更新这个列并忽略其他列吗? (并警告我这一栏确实已经改变了)

9 个解决方案

#1

You can detect and resolve your concurrency issues by catching a ChangeConflictException:

您可以通过捕获ChangeConflictException来检测并解决并发问题:

using (var db = new MyDataContext())
{
    var row = db.MyTable.Single(x => x.Id == tableId);  // Getting the row

    row.Column = columnNewValue;  // Assign the new value

    try
    {
        db.SubmitChanges();
    }
    catch (ChangeConflictException)
    {
        db.ChangeConflicts.ResolveAll(RefreshMode.KeepChanges);
        db.SubmitChanges();
    }
}

With RefreshMode.KeepChanges all the changes made to your client objects will persist, and the changes from other users/processes/threads, on other columns will be merged.

使用RefreshMode.KeepChanges,对客户端对象所做的所有更改都将保留,并且将合并其他列上其他用户/进程/线程的更改。

In your case, only your column will be changed.

在您的情况下,只会更改您的列。

#2

I don't know this too well, but looks like you could use the UpdateCheck.Never enum value on the ColumnAttribute to avoid the concurrency check completely.

我不太清楚这一点,但看起来你可以使用ColumnAttribute上的UpdateCheck.Never枚举值来完全避免并发检查。

MSDN

#3

There is a good article in MSDN:

MSDN上有一篇很好的文章:

Simultaneous Changes

The following example (a little further in the article) may be of special interest:

以下示例(文章中的稍后一点)可能特别有趣:

public partial class Northwind : DataContext
{
   ...

   public void UpdateProduct(Product original, Product current) {
      // Execute the stored procedure for UnitsInStock update
      if (original.UnitsInStock != current.UnitsInStock) {
         int rowCount = this.ExecuteCommand(
            "exec UpdateProductStock " +
            "@id={0}, @originalUnits={1}, @decrement={2}",
            original.ProductID,
            original.UnitsInStock,
            (original.UnitsInStock - current.UnitsInStock)
         );
         if (rowCount < 1)
            throw new Exception("Error updating");
      }
      ...
   }
}

with your stored procedure being:

您的存储过程是:

create proc UpdateProductStock
   @id               int,
   @originalUnits    int,
   @decrement         int
as
UPDATE Product 
SET originalUnits=@originalUnits,
    decrement=@decrement
WHERE id=@id

In my experience, the best way to go is making your own stored procedure. Linq doesn't offer an easy way to do simultaneous changes, unless you set "UpdateCheck" properties from (almost) all your columns to "Never".

根据我的经验,最好的方法是制作自己的存储过程。 Linq不提供一种简单的方法来进行同步更改,除非您将(几乎)所有列的“UpdateCheck”属性设置为“Never”。

#4

It sounds like you may be using the DataContext in a singleton pattern, or at least sharing it across multiple threads.

听起来您可能正在以单例模式使用DataContext,或者至少在多个线程*享它。

When you call SubmitChanges on your DataContext is will do all the database changes which the DataContext knows about.

当您在DataContext上调用SubmitChanges时,将执行DataContext知道的所有数据库更改。

The DataContext is meant to be used only as a unit-of-work object and disposed of as soon as you've completed that piece of work.

DataContext仅用作工作单元对象,并在您完成该工作后立即处理。

#5

I found this. Is that what you looked for?

我找到了这个。那是你找的?

If that doesn't solve the problem, you can always build an sql update statement and execute it.

如果这不能解决问题,您始终可以构建一个sql update语句并执行它。

#6

You can do this, but it requires that you structure your code in a certain way.

您可以这样做,但它要求您以某种方式构建代码。

For example you have a description and an amount that needs to be updated individually. You would then create 2 methodes:

例如,您有一个描述和需要单独更新的金额。然后,您将创建2个方法:

updateAmount which takes key and amount.

获取密钥和金额的updateAmount。

udateDesription which takes key and description.

获取密钥和描述的udateDesription。

Then within for example the updated amount methode:

然后在例如更新量的方法:

create a serializable transaction

创建一个可序列化的事务

read the row from the database

从数据库中读取行

updated the amount in memory

更新了内存中的金额

save the record to the database

将记录保存到数据库中

commit the transaction

提交交易

When you do it like this, you only change what you intended to change.

当你这样做时,你只会改变你想要改变的东西。

The times when we have used this pattern has been due to auditing requirements. You are able to say that this user changed the amount and this user changed the description. Not that each user changed the record and you need to look at the data to see what they did.

我们使用这种模式的时间是由于审计要求。您可以说该用户更改了金额,此用户更改了说明。并非每个用户都更改了记录,您需要查看数据以查看他们所做的事情。

#7

I read an article talking about the possibility of changing the UpdateCheck attribute at runtime. (Filed it away under the 'I might need that some day' category, but so far I haven't needed it.)

我读了一篇文章,讨论在运行时更改UpdateCheck属性的可能性。 (根据'我可能需要某一天'类别提交它,但到目前为止我还没需要它。)

See

http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/12/20/10038.aspx

#8

If you have access to change the DB schema, add a timestamp column to the data table in question, then ensure that no other column is set for concurrency updating (it should automatically pick up on the TimeStamp column, but may need to regenerate the table in your dbml if not). When you have a TimeStamp column, LINQ to SQL will only use that field when doing concurrency checks, thus allowing for a more discrete update.

如果您有权更改数据库模式,请将时间戳列添加到相关数据表中,然后确保没有为并发更新设置其他列(它应自动获取TimeStamp列,但可能需要重新生成表在你的dbml中,如果没有)。当你有一个TimeStamp列时,LINQ to SQL只会在进行并发检查时使用该字段,从而允许更加离散的更新。

#9

using the PLINQO framework at http://plinqo.com, you can update one column if you like using the batch operations that are a part of PLINQO.

使用http://plinqo.com上的PLINQO框架,如果您喜欢使用属于PLINQO的批处理操作,则可以更新一列。

context.Task.Update(t => t.Id == 1, t2 => new Task {StatusId = 2});

context.Task.Update(t => t.Id == 1,t2 => new Task {StatusId = 2});

This will perform a Update Task Set StatusId = 2 Where Id = 1

这将执行更新任务集StatusId = 2其中Id = 1

#1