How to update multiple tables using one query in Entity Framework?

Question

How to update multiple tables using one query in Entity Framework?

B M-A 361

Hello,

I want to update three table with one Query in Entity Framework:

Customer_Identification                   Region                        Customer Account  
| Id                                         |Region_Id                   |Customer Id  
| Name                                       |Region_Name                 |Bank_Name  
| Address                                                                 |Bank Account  
| Region_Id

I created a class Customer with all fields required and I used a join query to select what information I want to
update in database.
I try to update changes in this way :

dataContext.Entry( Customer).State = System.Data.Entity.EntityState.Modified;  
dataContext.SaveChanges();

I receive the following exception :
The entity type DbQuery`1 is not part of the model for the current context.
How is possible to update database without using multiple queries?

Best regards,

Karen Payne MVP 35,586 Reputation points Volunteer Moderator

2022-07-15T21:40:32.407+00:00

Are you using the same DbContext that data was read from or not?
B M-A 361 Reputation points

2022-07-16T03:38:33.227+00:00

Yes, it is the same DbContext.
Jack J Jun 25,296 Reputation points

2022-07-18T05:31:47.35+00:00

@B M-A , Welcome to Microsoft Q&A, Could you provide the related code about how do you update the three tables? It will be better for me to analyze your problem. Also, you could also use Database.ExecuteSqlCommand Method to update the tables.

B M-A 361

Hello,

I use WPF and WCF.

 public void UpdateCustomerById(int id, string name,string adress, string region, string bank_account)  
        {  
            var customer = from sel1 in DbContext.Customer_Identifications  
                         join sel2 in DbContext.Customer_Accounts  
                         on sel1.Id equals sel2.Customer_Id  
                         join sel3 in DbContext.Regions  
                         on sel1.Region_Id equals sel3.Region_Id  
                         where sel1.Id == id  
                         select new Client()  
                         {  
                             Name = sel1.Name,  
                             Id = sel1.Id,  
                             Address = sel1.Address,  
                             Region_Name = sel3.Region_Name,  
                             Bank_Account = sel2.Bank_Account,  
                               
                         };  
            DbContext.Entry(customer).State = System.Data.Entity.EntityState.Modified;  
            DbContext.SaveChanges();  
 }

And here is the " Customer " class

     public class Customer  
       {    [DataMember]  
         public string Name { get; set; }  
        [DataMember]  
         public int Id { get; set; }  
        [DataMember]  
        public string Address { get; set; }  
        [DataMember]  
        public string Region_Name { get; set; }    
        [DataMember]  
        public string Bank_Account { get; set; }  
                 }

Jack J Jun 25,296 Reputation points

2022-07-19T05:49:13.613+00:00

@B M-A , thanks for the feedback, the code you provided is only to select the data from the database. What do you want to update in the three tables?
B M-A 361 Reputation points

2022-07-19T15:35:02.877+00:00

I want to update all selected data at once.

Jack J Jun 25,296

@B M-A , based on my test, it is hard to update the three tables use One query. We need to split into three querys.

Please refer to the following query:

Update c1 set c1.Name='Example1',c1.Address='address1',c1.Id=1001  from Customer_Identification c1 JOIN Customer_Account c2 on c1.Id=c2.[Customer Id]  join Region c3 on c1.Region_Id=c3.Region_Id  where c1.Id=1001  
  
Update c2 set c2.Bank_Account='newaccount' from Customer_Identification c1 JOIN Customer_Account c2 on c1.Id=c2.[Customer Id]  join Region c3 on c1.Region_Id=c3.Region_Id  where c1.Id=1001  
  
Update c3 set c3.[Region_Name ]='newregion'  from Customer_Identification c1 JOIN Customer_Account c2 on c1.Id=c2.[Customer Id]  join Region c3 on c1.Region_Id=c3.Region_Id  where c1.Id=1001

You could use Database.ExecuteSqlCommand to execute the sql query directly.

1 answer

Your answer

Karen Payne MVP 35,586 Reputation points Volunteer Moderator

2022-07-15T21:40:32.407+00:00

Are you using the same DbContext that data was read from or not?
B M-A 361 Reputation points

2022-07-16T03:38:33.227+00:00

Yes, it is the same DbContext.
Jack J Jun 25,296 Reputation points

2022-07-18T05:31:47.35+00:00

@B M-A , Welcome to Microsoft Q&A, Could you provide the related code about how do you update the three tables? It will be better for me to analyze your problem. Also, you could also use Database.ExecuteSqlCommand Method to update the tables.
B M-A 361 Reputation points

2022-07-18T16:50:04.88+00:00

Hello,

I use WPF and WCF.

public void UpdateCustomerById(int id, string name,string adress, string region, string bank_account) { var customer = from sel1 in DbContext.Customer_Identifications join sel2 in DbContext.Customer_Accounts on sel1.Id equals sel2.Customer_Id join sel3 in DbContext.Regions on sel1.Region_Id equals sel3.Region_Id where sel1.Id == id select new Client() { Name = sel1.Name, Id = sel1.Id, Address = sel1.Address, Region_Name = sel3.Region_Name, Bank_Account = sel2.Bank_Account, }; DbContext.Entry(customer).State = System.Data.Entity.EntityState.Modified; DbContext.SaveChanges(); }

And here is the " Customer " class

public class Customer { [DataMember] public string Name { get; set; } [DataMember] public int Id { get; set; } [DataMember] public string Address { get; set; } [DataMember] public string Region_Name { get; set; } [DataMember] public string Bank_Account { get; set; } }
Jack J Jun 25,296 Reputation points

2022-07-19T05:49:13.613+00:00

@B M-A , thanks for the feedback, the code you provided is only to select the data from the database. What do you want to update in the three tables?
B M-A 361 Reputation points

2022-07-19T15:35:02.877+00:00

I want to update all selected data at once.
Jack J Jun 25,296 Reputation points

2022-07-20T09:30:57.407+00:00

@B M-A , based on my test, it is hard to update the three tables use One query. We need to split into three querys.

Please refer to the following query:

Update c1 set c1.Name='Example1',c1.Address='address1',c1.Id=1001 from Customer_Identification c1 JOIN Customer_Account c2 on c1.Id=c2.[Customer Id] join Region c3 on c1.Region_Id=c3.Region_Id where c1.Id=1001 Update c2 set c2.Bank_Account='newaccount' from Customer_Identification c1 JOIN Customer_Account c2 on c1.Id=c2.[Customer Id] join Region c3 on c1.Region_Id=c3.Region_Id where c1.Id=1001 Update c3 set c3.[Region_Name ]='newregion' from Customer_Identification c1 JOIN Customer_Account c2 on c1.Id=c2.[Customer Id] join Region c3 on c1.Region_Id=c3.Region_Id where c1.Id=1001

You could use Database.ExecuteSqlCommand to execute the sql query directly.

Answer 1

Bruce (SqlWork.com) 77,686 Volunteer Moderator

projections (objects mapped from a query) are not updatable.

only DbSets defined in the DbContext that are mapped to a table (or updatable view) support updates.

note: sql databases do not support updating multiple tables in a single statement.

Share via

How to update multiple tables using one query in Entity Framework?

1 answer

Your answer