C# 使用 linq 生成无需选择的直接更新

Use linq to generate direct update without select

提问by Spence

G'day everyone.

大家好。

I'm still learning LINQ so forgive me if this is naive. When you're dealing with SQL directly, you can generate update commands with conditionals, without running a select statement.

我还在学习 LINQ，所以如果这很幼稚，请原谅我。当您直接处理 SQL 时，您可以生成带有条件的更新命令，而无需运行 select 语句。

When I work with linq I seem to follow the pattern of:

当我使用 linq 时，我似乎遵循以下模式：

1. Select entities
2. Modify entities
3. Submit changes

1. 选择实体
2. 修改实体
3. 提交更改

What I want to do is a direct update using linq and deferred execution. Is it possible that the actual execution occurs directly at the SQL without any data being transmitted up to the client?

我想要做的是使用 linq 和延迟执行的直接更新。实际执行是否有可能直接在 SQL 上发生而没有任何数据传输到客户端？

DataContext dc = new DataContext

var q = from product in dc.Products
        where product.Type = 1
        set product.Count = 0

dc.SubmitChanges

So in essence LINQ has all the information it needs WITHOUT using a select to generate an update command. It would run the SQL:

因此，本质上 LINQ 拥有它需要的所有信息，而无需使用选择来生成更新命令。它将运行 SQL：

Update Products Set Count = 0 Where Type = 1

Does a keyword like "set" exist in LINQ?

LINQ 中是否存在像“set”这样的关键字？

采纳答案by Randolpho

No, neither LINQ nor LINQ to SQL has set-based update capabilities.

不，LINQ 和 LINQ to SQL 都没有基于集合的更新功能。

In LINQ to SQL, you must query for the object you wish to update, update the fields/properties as necessary, then call SubmitChanges(). For example:

在 LINQ to SQL 中，您必须查询要更新的对象，根据需要更新字段/属性，然后调用 SubmitChanges()。例如：

var qry = from product in dc.Products where Product.Name=='Foobar' select product;
var item = qry.Single();
item.Count = 0;
dc.SubmitChanges();

If you wish to do batching:

如果您想进行批处理：

var qry = from product in dc.Products where Product.Type==1 select product;
foreach(var item in qry)
{
  item.Count = 0;
}
dc.SubmitChanges();

Alternatively, you could write the query yourself:

或者，您可以自己编写查询：

dc.ExecuteCommand("update Product set Count=0 where Type=1", null);

回答by Pop Catalin

Linq 2 SQL doesn't have direct insert/update/delete equivalents of SQL. In V1 the only updates you can do using linq is thought SubmmitChanges on the context or if you fallback to sql.

Linq 2 SQL 没有 SQL 的直接插入/更新/删除等效项。在 V1 中，您可以使用 linq 进行的唯一更新被认为是上下文中的 SubmmitChanges，或者如果您回退到 sql。

However some people have tried to overcome this limitation of linq using custom implementations.

然而，有些人尝试使用自定义实现来克服 linq 的这种限制。

Linq batch update.

Linq 批量更新。

回答by Shannon Davidson

The PLINQO (http://plinqo.com) framework is using the LINQ batch update to perform updates

PLINQO ( http://plinqo.com) 框架使用 LINQ 批量更新来执行更新

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

这将执行一个 Update Task Set StatusId = 2 Where Id = 1

回答by laktak

You can actually let LINQ-to-SQL generate update statements:

您实际上可以让 LINQ-to-SQL 生成更新语句：

Foo foo=new Foo { FooId=fooId }; // create obj and set keys
context.Foos.Attach(foo);
foo.Name="test";
context.SubmitChanges();

In your Dbml set UpdateCheck="Never" for all properties.

在您的 Dbml 中，为所有属性设置 UpdateCheck="Never"。

This will generate a single update statementwithout having to do a select first.

这将生成一个更新语句，而无需先进行选择。

One caveat: if you want to be able to set Name to null you would have to initialize your foo object to a different value so Linq can detect the change:

一个警告：如果您希望能够将 Name 设置为 null，则必须将 foo 对象初始化为不同的值，以便 Linq 可以检测到更改：

Foo foo=new Foo { FooId=fooId, Name="###" };
...
foo.Name=null;

If you want to check for a timestamp while updating you can do this as well:

如果您想在更新时检查时间戳，您也可以这样做：

Foo foo=new Foo { FooId=fooId, Modified=... }; 
// Modified needs to be set to UpdateCheck="Always" in the dbml

回答by amit singh

Try this :

尝试这个 ：

dbEntities.tblSearchItems
     .Where(t => t.SearchItemId == SearchItemId)
     .ToList()
     .ForEach(t => t.isNew = false);
dbEntities.SaveChanges();

回答by orad

Use this extension method: EntityExtensionMethods.cs

使用此扩展方法：EntityExtensionMethods.cs

public static void UpdateOnSubmit<TEntity>(this Table<TEntity> table, TEntity entity, TEntity original = null)
    where TEntity : class, new()
{
    if (original == null)
    {
        // Create original object with only primary keys set
        original = new TEntity();
        var entityType = typeof(TEntity);
        var dataMembers = table.Context.Mapping.GetMetaType(entityType).DataMembers;
        foreach (var member in dataMembers.Where(m => m.IsPrimaryKey))
        {
            var propValue = entityType.GetProperty(member.Name).GetValue(entity, null);
            entityType.InvokeMember(member.Name, BindingFlags.SetProperty, Type.DefaultBinder,
                original, new[] { propValue });
        }
    }

    // This will update all columns that are not set in 'original' object. For
    // this to work, entity has to have UpdateCheck=Never for all properties except
    // for primary keys. This will update the record without querying it first.
    table.Attach(entity, original);
}

To use it, make sure the entityobject that you pass to UpdateOnSubmitmethod has all the primary key properties set for the record you want to update. This method will then update the record with the remaining properties from the entityobject without pulling the record first.

要使用它，请确保entity传递给UpdateOnSubmit方法的对象具有为要更新的记录设置的所有主键属性。然后，此方法将使用entity对象中的其余属性更新记录，而无需先拉出记录。

After calling UpdateOnSubmit, make sure to call SubmitChanges()for changes to apply.

调用 后UpdateOnSubmit，请务必调用SubmitChanges()以应用更改。

回答by koryakinp

You can use Entity Framework Extensionslibrary, it supports batch update and batch merge, however the library is not free:

您可以使用Entity Framework Extensions库，它支持批量更新和批量合并，但该库不是免费的：

PM > Install-Package Z.EntityFramework.Extensions

PM > 安装包 Z.EntityFramework.Extensions

using Z.EntityFramework.Plus;

...

dc.Products
    .Where(q => q.Type == 1)
    .Update(q => new Product { Count = 0 });