CODEWITHSUNDEEP
sql-serverentity-framework
Elham Azadfar
Elham Azadfar

Reputation: 739

How to update table in SQL Server with Entity Framework?

My table name is StcDocItems:

id      partRef   DocTypeRef  itemRef
--------------------------------------
3850    366          12        NULL
3851    367          63        3850

I'd like to update partRef to (in this case 366) partRef of the record that its itemRef is equal to its Id and its DocTypeRef is 63.

Upvotes: 1

Views: 1037

Answers (2)

Mohammad Nikravesh
Mohammad Nikravesh

Reputation: 975

Update StcDocItems set partRef =366 where itemRef=id and DocTypeRef =63

Upvotes: 1

marc_s
marc_s

Reputation: 754220

Not entirely sure what you're trying to do.... assuming you want to update the whole table, you could try something like this:

  • load the whole table into memory as a List<StcDocItems>
  • loop over each item and see if it has a matching entry - if so, update it

So this would be the code to do this:

public class StcDocItems
{
    public int Id { get; set; }
    public int PartRef { get; set; }
    public int DocRefType { get; set; }
    public int? ItemRef { get; set; }
}

using(YourDbContext ctx = new YourDbContext())
{
    // get all rows from table
    List<StcDocItems> allItems = ctx.StcDocItems.ToList();

    // iterate over items
    foreach(StcDocItems item in allItems)
    {
        // do we find a row which has "ItemRef = Id" and "DocTypeRef = 63" ?
        StcDocItems updateToItem = allItems.FirstOrDefault(i => i.ItemRef = item.Id && i.DocTypeRef = 63);

        // if we found an item
        if(updateToItem != null)
        {
            // set the partRef to new item's partRef
            item.PartRef = i.PartRef;
        }
    }

    // save any changes back to database table
    ctx.SaveChanges();
}

Upvotes: 2

Related Questions