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, Jan 14 '18 at 15:14
@IvanStoev i am unable to understand that, could you pls provide a code for my scenario ( for user table ) ... — Admin, Jan 14 '18 at 15:27

Nitesh Kumar · Answer 1 · 2018-01-14T16:05:39.917

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 '18 at 16:05

answered Jan 14 '18 at 15:43

Nitesh Kumar

1,774
4
19
26

its not pure, like : using .Property or SetModifiedProperty ? – Admin Jan 14 '18 at 16:04

Alexei - check Codidact · Answer 2 · 2018-01-14T17:46:12.427

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 '18 at 17:46

answered Jan 14 '18 at 16:39

Alexei - check Codidact

22,016
16
145
164

i am using mvc4,,, its showing error'Update' is not a member of 'System.Linq.IQueryable(Of users)' – Admin Jan 22 '18 at 14:49
@Admin - did you include a reference to Entity Framework Plus? These batch statements (`UPDATE`, `DELETE`) are not included within EntityFramework. – Alexei - check Codidact Jan 22 '18 at 15:01
I can not add dll or reference other than Microsoft Own DLLS... is there any microsoft update for Entity Framework Plus ? – Admin Jan 22 '18 at 15:10
@Admin - I am not aware of such Microsoft assemblies. However, you can easily use the direct query approach (`Nitesh`' answer) – Alexei - check Codidact Jan 22 '18 at 15:11

score 0 · Answer 3 · answered Jan 14 '18 at 15:06

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 '18 at 15:06

Ken Tucker

4,126
1
18
24

Stored Procedure not a solution, since i can use SQLCE 4.0 in my mvc application and that DOES NOT SUPPORT stored Procedures... – Admin Jan 14 '18 at 15:21

Update multiple columns in MVC Entity Framework

3 Answers3