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,386 Reputation points

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 24,496 Reputation points Microsoft Vendor

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 24,496 Reputation points Microsoft Vendor

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 24,496 Microsoft Vendor

@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

Bruce (SqlWork.com) 61,731 Reputation points

2022-07-18T18:11:43.62+00:00

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.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Share via

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

1 answer