Run Update Statement Using Entity Framework Linked Table

Question

Using EF Core Console App I need to give an admin user the ability to select a checkbox, and on button press, basically run this pseudocode statment

update EF Table set active = 'Yes' where userID = userID of checkbox

I have no problem running Select with EF but how to do an update?

EDIT
I currently have this syntax

foreach ((int Id, DateTime? submissionDate, string status) tuple in apiData)
{
    if (ctx.Test.Any(x => x.OrderId == tuple.Id))
    {
        Test ts = new Test();
        ts.Status = tuple.status;
        ctx.Test.Add(ts);
        ctx.SaveChanges();
    }
    else
    {
        Test ts = new Test();
        ts.OrderId = tuple.Id;
        ts.PdfDownloaded = tuple.submissionDate;
        ts.Status = tuple.status;
        ctx.Test.Add(ts);
        ctx.SaveChanges();
    }
}

but when i run the code, I get this error on an update (the if).

System.InvalidOperationException: 'The instance of entity type 'Test' cannot be tracked because another instance with the same key value for {'OrderId'} is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached. Consider using 'DbContextOptionsBuilder.EnableSensitiveDataLogging' to see the conflicting key values.'

EDIT 2
I have also tried this approach

var records = ctx.Test.Where(x => x.OrderId == tuple.Id);
records.ForEach(x => x.Status = tuple.status);
ctx.SaveChanges();

but I get this compile error on the foreach

'IQueryable' does not contain a definition for 'ForEach' and no accessible extension method 'ForEach' accepting a first argument of type 'IQueryable' could be found (are you missing a using directive or an assembly reference?)

`SaveChanges` saves *all* modifications. It shouldn't be called in a loop. As for the second error, `ForEach` is a `List` method. You need to load the data into a list first, although you could just use `foreach` and modify each object without putting it into a list — Panagiotis Kanavos, Jun 25 '20 at 14:08

Rik Maton · Accepted Answer · 2020-06-25T14:11:50.510

1

You should first get the record(s) that you want to update

var records = context.EFTable.Where(x => x.userID == UserId).ToList();

Then you should set active to yes, on all those objects

records.ForEach(x => x.active = "yes");

Finally, save all the changes

context.saveChanges();

edited Jun 25 '20 at 14:11

answered Jun 25 '20 at 13:44

Rik Maton

135
1
8

In my edit, aren't I grabbing the record I want to update? – jamesMandatory Jun 25 '20 at 13:45
No, in your edit you are not grabbing the record, like i do in the first line of code of my answer. Can you change `Test ts = new Test();` to `Test ts = ctx.FirstOrDefault(x => x.OrderId == tuple.Id);` – Rik Maton Jun 25 '20 at 13:48
That gives me an error of ```'DataBankContext' does not contain a definition for 'FirstOrDefault' and no accessible extension method 'FirstOrDefault' accepting a first argument of type 'DataBankContext' could be found (are you missing a using directive or an assembly reference?) ``` – jamesMandatory Jun 25 '20 at 13:50
To fix that error, you should add the `System.Linq` namespace. For more information about that, take a look at this [link](https://stackoverflow.com/a/32855426/10346096) – Rik Maton Jun 25 '20 at 13:52
I fig that part out. I needed to do ```ctx.Test.FirstOrDefault()``` – jamesMandatory Jun 25 '20 at 13:57
Now I get this error ```Microsoft.EntityFrameworkCore.DbUpdateException: 'An error occurred while updating the entries. See the inner exception for details.' SqlException: Cannot insert explicit value for identity column in table 'Test' when IDENTITY_INSERT is set to OFF. ``` – jamesMandatory Jun 25 '20 at 13:58
I have no specific knowledge on this part, but it seems like you will have to turn `IDENTITY_INSERT` on, everytime you want to insert something into the table, using: `ctx.Database.ExecuteSqlCommand("SET IDENTITY_INSERT dbo.Test ON");` I think this error occurs in your else-statement, where you are creating a new record. For more information about this, you can take a look at this [link](https://entityframeworkcore.com/knowledge-base/51821241/ef-core--cannot-insert-explicit-value-for-identity-column--although-identiy-insert-is-set-to-on) – Rik Maton Jun 25 '20 at 14:05
it's throwing that error when i am trying to update not insert. That's what is baffling me – jamesMandatory Jun 25 '20 at 14:09
To fix the error in your EDIT2, change `var records = ctx.Test.Where(x => x.OrderId == tuple.Id);` to `var records = ctx.Test.Where(x => x.OrderId == tuple.Id).ToList();` I will also update my answer. – Rik Maton Jun 25 '20 at 14:11
So, when its giving you the error on the update, it seems to me that your code is still not using the record that we've retrieved from the database. Could you update your post with the complete code that you are using right now? – Rik Maton Jun 25 '20 at 14:14

score 1 · Answer 2 · answered Jun 25 '20 at 13:52

1

Also Put SaveChanges out of loop

answered Jun 25 '20 at 13:52

herosuper

164
1
9

Paulo · Answer 3 · 2020-06-25T14:27:27.537

0

var entity = context.table.WHERE(userId == checkBox).FirstOrDefault();

entity.property = "value"
context.saveChanges();

edited Jun 25 '20 at 14:27

answered Jun 25 '20 at 13:34

Paulo

577
3
8
23

how does the ```where``` come into play so i am exclusively only updating the one? – jamesMandatory Jun 25 '20 at 13:34
you dont need to use "WHERE". The framework will generate automatically. (you cannot control like in fact will be generate the SQL statement. This is why the structure of the entity is slower than normal queries) – Paulo Jun 25 '20 at 13:35
I still don't understand. I'm wanting to use a ```if else``` statement, and I'm checking if the ```userID``` already exists in the table then we will need to update, if the userID does not exist then we need to insert. I've got the insert syntax down, but i can't wrap my mind around the ```update```. – jamesMandatory Jun 25 '20 at 13:38
please, post yout completly entity class Test. – Paulo Jun 25 '20 at 13:54
This does not provide an answer to the question. To critique or request clarification from an author, leave a comment below their post. - [From Review](/review/low-quality-posts/26511480) – Cristian Szpisjak Jun 25 '20 at 19:16
@CristianSzpisjak the question was change completely. Thats why dont answare the question – Paulo Jun 29 '20 at 20:22

Run Update Statement Using Entity Framework Linked Table

3 Answers3