How to update fields by single query without key value using Entity

Question

class Amodel
{
    [Key]
    public virtual int ID { get; set; }
    public virtual string str1 { get; set; }
    public virtual string str2 { get; set; }
    public virtual string str3 { get; set; }
}

Is it possible to update A table using Entity by single sql (force entity to produce it) like this: UPDATE A SET str2 = 'com1', str3 = 'com2' WHERE str1 = 'val99'? ID is unknown.

EDIT: I don't want to use plain SQL, I neeed to force entity generate such (or similar) sql. I tried something like this, but here entity uses ID to identify records

var obj = new A{str1="com1", str2 = "com2", str3 = "val99"}
db.A.Attach(obj);
db.Entry(obj).Property(x => x.str1).IsModified = true;
db.Entry(obj).Property(x => x.str2).IsModified = true;  
db.SaveChanges();

If you are updating you will only modify the values that are defined in your SET. However this will modify all the records that have `str1 = 'val99'` — maccettura, Feb 16 '18 at 15:29
Possible duplicate of [how to update the multiple rows at a time using linq to sql?](https://stackoverflow.com/questions/10314552/how-to-update-the-multiple-rows-at-a-time-using-linq-to-sql) — Alex, Feb 16 '18 at 15:31
If the update is to be done on a non-key value, then you're really looking at creating a stored procedure that can take the `WHERE` clause as a parameter. — DiskJunky, Feb 16 '18 at 15:48

DiskJunky · Answer 1 · 2018-02-24T20:06:25.937

If you want to be very specific, you can execute a raw command directly against the context like so;

using (var context = new BloggingContext()) 
{ 
    context.Amodel.SqlQuery("UPDATE Amodels SET str2='com1' WHERE str1='val999'"); 
}

If it's required that you need to update on a non-key value, then you'd be looking to create a stored procedure that takes as parameters the values you want to update and what you want to update by. E.g. if you want to call on the context like so;

context.UpdateAModelByStr1(amodel.Str2, amodel.Str1);

Create a Stored Procedure in your DB such that;

CREATE PROCEDURE UpdateAModelByStr1(@Str2Value nvarchar(50),
                                    @Str1UpdateBy nvarchar(50))
BEGIN
    UPDATE AModels
        SET Str2 = @Str2Value
        WHERE Str1 = @Str1UpdateBy
END

To add the stored procedure to your EF context (make sure you've actually created the SP in the DB first), go to your entity data model (if you don't have one, right-click the project, Add -> New Item and select ADO.NET Entity Data Model. For a more detailed how-to, see here), right-click anywhere in the model and select Update Model From Database, tick the Stored Procedures and Functions box in the Add tab and select UpdateAModelByStr1. When you click Finish, a new method called UpdateAModelByStr1 is now added to your context as in the example above.

While using a stored procedure might seem like more than what you want to do, EF is not really designed to have the full feature set and flexibility of SQL itself, its purpose is to make the standard CRUD stuff much easier to get set up with with a few bells and whistles for common scenarios.

How to update fields by single query without key value using Entity

1 Answers1