Update multiple columns in MVC Entity Framework

Question

I have table with more than 25 Fields in it. It has some column with huge data (nvarchar(MAX)). It has User table like :

ID  // Primary key 
Name
Mail
Contact
RegFees
.
.
. //  about 25 Fields
Narration   // Nvarchar(Max)  may have upto 10000 chars
Tag        // Nvarchar(Max)  may have upto 15000 chars

Now i have to update only Name,Mail,Contact fields in it.

I have gone through several so posts like update 1 field, update multiple fields. But these require data to be loaded for that specific user ID and then Update it like :

 var u = dc.Users.Where(a =>a.ID.Equals(i.ID)).FirstOrDefault();
 if (u != null)
   {   
    // Note : ALL the data of 25+ columns is loaded including nvarchar(Max)
    // it will decrease performance as Whole Data is loaded first.

    u.Name = i.Name;
    u.Mail = i.Mail;
    u.Contact = i.Contact;
    }

is there any better way that does not require to load entire data ?

Actually both links show how you can do that w/o loading data from the database - first link in the accepted answer, second link in the question itself. — Ivan Stoev
– Ivan Stoev, Commented Jan 14, 2018 at 15:14
@IvanStoev i am unable to understand that, could you pls provide a code for my scenario ( for user table ) ... — Admin
– Admin, Commented Jan 14, 2018 at 15:27

Nitesh Kumar · Accepted Answer · 2018-01-14 16:05:39Z

1

You can follow the below approach for updating few fields in a table.

dc.Database.ExecuteSqlCommand("Update [User] Set Name = @Name, Mail = @Mail, Contact = @Contact Where ID = @ID", new SqlParameter("@Name", "YourName"), new SqlParameter("@Mail", "newMail"), new SqlParameter("@Contact", "newContact"), new SqlParameter("@ID", 1));

edited Jan 14, 2018 at 16:05

answered Jan 14, 2018 at 15:43

Nitesh Kumar

1,7744 gold badges19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Admin Over a year ago

its not pure, like : using .Property or SetModifiedProperty ?

Alexei - check Codidact · Accepted Answer · 2018-01-14 17:46:12Z

1

If you want a EFish approach (no SQL statement) you can treat your UPDATE as a batch statement using Entity Framework Extensions, now re-branded Entity Framework plus.

I have used the old version, but the one seems to work similarly. For your particular case:

dc.Users.Where(u => 
    u.ID = userID)
    .Update(x => new User() 
          { 
              Name = name, 
              Mail = mail, 
              Contact = contact
          });

AFAIK, this will directly issue an UPDATE against the database (it is not included in the transaction by default). However, a custom transaction (i.e. TransactionScope`) can be used when needed.

edited Jan 14, 2018 at 17:46

answered Jan 14, 2018 at 16:39

Alexei - check Codidact

23.2k22 gold badges159 silver badges179 bronze badges

4 Comments

Admin Over a year ago

i am using mvc4,,, its showing error'Update' is not a member of 'System.Linq.IQueryable(Of users)'

Alexei - check Codidact Over a year ago

@Admin - did you include a reference to Entity Framework Plus? These batch statements (UPDATE, DELETE) are not included within EntityFramework.

Admin Over a year ago

I can not add dll or reference other than Microsoft Own DLLS... is there any microsoft update for Entity Framework Plus ?

Alexei - check Codidact Over a year ago

@Admin - I am not aware of such Microsoft assemblies. However, you can easily use the direct query approach (Nitesh' answer)

Ken Tucker · Accepted Answer · 2018-01-14 15:06:22Z

0

Add a stored procedure to the database for updating the few fields in the table. You can use the entity framework to call a stored procedure which updates the fields in the database

One of these articles should help

http://www.entityframeworktutorial.net/stored-procedure-in-entity-framework.aspx

https://www.mikesdotnetting.com/article/299/entity-framework-code-first-and-stored-procedures

answered Jan 14, 2018 at 15:06

Ken Tucker

4,1561 gold badge20 silver badges24 bronze badges

1 Comment

Admin Over a year ago

Stored Procedure not a solution, since i can use SQLCE 4.0 in my mvc application and that DOES NOT SUPPORT stored Procedures...

Collectives™ on Stack Overflow

Update multiple columns in MVC Entity Framework

3 Answers 3

1 Comment

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related