Adding to the Where clause of an Update in LinQ-to-Entities

Question

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

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:

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.

Thanks :-)

Answer 1

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

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

Answer 2

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)

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)