Adding updates to the LinQ-to-Entities Where clause

Question

Let's say I have a table called Product with three columns: Id, CustomerId, Name. The identifier is the primary key. The scheme is beyond the control of my group, and now we have a requirement to always provide CustomerId as a parameter for all requests (selects, updates, deletes). This is a long story that I would not want to talk about ... it includes triggers: -P

So my question is when I have an attached object in LinqToEntities and I want to keep some updates (for example, I update the name in this case). How can I get it to generate SQL:

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

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

Thanks: -)

Answer 1

Does CustomerId help unambiguously identify the string passed by @Id? I really did not execute the “triggers” bit, because the predicate used for updating is unknown by the trigger. Or do you want to re-update CustomerId every time (defined with UPDATE(...) in a trigger)

The easiest option is to do this as an update to an object:

 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 expect a single entry, you can use:

 Product prod = (from product in model.Products where Id == @Id && CustomerId == @CustomerId select product).Single(); prod.Name = @Name; mode.SaveChanges(); // ditto 

You could also write it as Entity-SQL, but I'm not sure I will worry personally ... (update: I just checked, and I Don't think Entity-SQL includes DML, so no, you can't - you will have to use either the above or the regular SQL / SPROC command)

Answer 2

One way is to use the saved process for updating . This gives you full control over SQL.

Another way is to add CustomerId to the entity key.