添加到LinQ-to-Entities中的Update的Where子句

Let's say I have a table called Product, with three columns: Id, CustomerId, Name. Id is the primary key. The schema is outside of the control of my group, and we now have a requirement to always provide CustomerId as a parameter for all queries (selects, updates, deletes). It's a long story I'd rather not get into ... it involves triggers :-P

假设我有一个名为Product的表,有三列:Id,CustomerId,Name。 Id是主键。模式超出了我的组的控制范围,我们现在要求始终将CustomerId作为所有查询(选择,更新,删除)的参数。这是一个很长的故事,我宁愿不介入......它涉及触发器:-P

So my question is, when I have an attached entity in LinqToEntities, and I want to save some updates (say I'm updating the name in this case). How can I get it to generate the SQL:

所以我的问题是,当我在LinqToEntities中有一个附加实体时,我想保存一些更新(比如我在这种情况下更新名称)。如何让它生成SQL:

update Product set Name = @Name where Id=@Id and CustomerId=@CustomerId

Where the customerId parameter is included in the where clause in addition to the primary key.

除了主键之外,customerId参数包含在where子句中。

Thanks :-)

2 个解决方案

#1

Does the CustomerId help uniquely identify the row past @Id? I didn't really follow the "triggers" bit, since the predicate used for the update is not known by the trigger. Or you do want to re-update the CustomerId each time (detectable from UPDATE(...) in the trigger)

CustomerId是否有助于唯一标识@Id之后的行?我没有真正遵循“触发器”位,因为触发器不知道用于更新的谓词。或者您确实希望每次都重新更新CustomerId(可以从触发器中的UPDATE(...)检测到)

The easiest option is to do it as object updates:

最简单的选择是将其作为对象更新:

var qry = from product in model.Products
          where Id == @Id && CustomerId == @CustomerId
          select product;

foreach(Product p in qry) {
    p.Name = @Name;
}

model.SaveChanges(); // or whatever the method is in EF

If you know you are expecting one record, you could use:

如果您知道您希望有一条记录,则可以使用:

Product prod = (from product in model.Products
          where Id == @Id && CustomerId == @CustomerId
          select product).Single();

prod.Name = @Name;
mode.SaveChanges(); // ditto

You might also be able to write it as Entity-SQL, but I'm not sure I'd bother, personally... (update: I've just checked, and I don't think Entity-SQL includes DML, so no, you can't - you'd have to use either the above, or a regular SQL command/SPROC)

您也可以将其编写为Entity-SQL,但我不确定我会打扰,个人......(更新:我刚检查过,我不认为Entity-SQL包含DML,所以不,你不能 - 你必须使用上面的,或常规的SQL命令/ SPROC)

#2

One way would be to use a stored proc to do the update. This gives you complete control over the SQL.

一种方法是使用存储过程来进行更新。这使您可以完全控制SQL。

Another way is to add the CustomerId to the entity key.

另一种方法是将CustomerId添加到实体键。

#1