In LinQ-to-Entities, adding to the Where clause of an Update

entity-framework linq linq-to-entities sql

Question

Say I have a table named Product that has the following three columns: Id, CustomerId, and Name. The main key is id. We now have a need to always give CustomerId as a parameter for all queries since the schema is outside of my group's control (selects, updates, deletes). It's a lengthy narrative that I'd prefer not to discuss since it contains triggers:-P

So, my concern is how to preserve modifications while I have an associated entity in LinqToEntities (let's assume I'm changing the name in this example). How can I get the SQL to be generated?

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

Where the where clause also contains the customerId argument in addition to the main key.

Thanks:-)

1
5
12/23/2008 2:29:16 PM

Accepted Answer

Does CustomerId contribute to the row's unique identification after @Id? I didn't really follow the "triggers" bit, since the predicate used for the update is not known by the trigger. Or you don't want to constantly update the CustomerId (as can be seen fromUPDATE(...) inside the trigger)

The simplest method is to execute it as an object update:

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 are aware that just one record is anticipated, you may use:

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

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

Although you could potentially write it in Entity-SQL, I'm not sure whether I would bother. (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)

7
12/23/2008 3:04:14 PM

Popular Answer

To update using a stored procedure. would be one approach. You have total control over the SQL as a result of this.

An other method is to include the CustomerId in the entity key.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow