robbersrow
asked on
How to perform Insert/Update/Delete via LINQ to Entities (Entity Framework) across Many-to-Many or 1-to-Many relation (using ADO.NET Data Service)?
I have a Silverlight application which uses ADO.NET Data Services and the Entity Framework to interact with a SQL Server Database. In the SQL Server database I have: User, Role, and User_Role (join) tables. I successfully use the ADO.NET Entity Framework Wizard to generate the Model from the database in my Silverlight application server-side code (.NET), and the resulting EDMX diagram properly shows a many-to-many relationship between the User and Role table. So this part is working ok.
Then my Silverlight/C# client-side code is set up to where I have a Service Reference (Proxy) to the server-side ADO.NET Data Service which accesses the Entity Framework EDMX definitions (also on the server-side, along with the database).
I am able to successfully create a User or a Role to the database. The problem comes when I try to ADD ROLES TO A USER, and THEN save that user to the database.
First, here is an example of what DOES work in my Silverlight C# code:
Proxy.Entities proxy = new Proxy.Entities(new Uri("DataService.svc", UriKind.Relative));
Proxy.User user = new Proxy.User();
user.Username = UsernameTB.Text;
user.FirstName = FirstNameTB.Text;
user.LastName = LastNameTB.Text;
user.CreatedDate = DateTime.Now;
user.UpdatedDate = DateTime.Now;
proxy.AddObject("User", user);
proxy.BeginSaveChanges(
SaveChangesOptions.None,
(asyncResult) => { proxy.EndSaveChanges(async
null
);
Now, here is an example of what DOES NOT work (note the lines where I say "selectedUser.Role.Add( )", and also note that "selectedUser" is a user that is already in the database and has been successfully queried/retrieved in another part of the client-side code):
public UpdateUserRoles(Proxy.User
{
Proxy.Entities proxy = new Proxy.Entities(new Uri("DataService.svc", UriKind.Relative));
proxy.AttachTo("User", selectedUser);
selectedUser.Role.Add(role
selectedUser.Role.Add(role
selectedUser.Role.Add(role
proxy.UpdateObject(selecte
proxy.BeginSaveChanges(
SaveChangesOptions.Batch,
(asyncResult) => { proxy.EndSaveChanges(async
null
);
}
In this second example, I would expect the "User_Role" join table in the database to be automatically updated with the new User/Role entries (corresponding to adding specific Roles to a User). In other words, there should be a new ROW in the User_Role join database table for every new Role I add to a User. But for some reason, the User_Role join table does not get updated accordingly. It remains blank, without any new ROWs added. Furthermore, the same issue applies to tables which are related via a 1-to-many relationship. Please let me know if you see something that I may be missing here. Any help would be greatly appreciated. Thank you.
Then my Silverlight/C# client-side code is set up to where I have a Service Reference (Proxy) to the server-side ADO.NET Data Service which accesses the Entity Framework EDMX definitions (also on the server-side, along with the database).
I am able to successfully create a User or a Role to the database. The problem comes when I try to ADD ROLES TO A USER, and THEN save that user to the database.
First, here is an example of what DOES work in my Silverlight C# code:
Proxy.Entities proxy = new Proxy.Entities(new Uri("DataService.svc", UriKind.Relative));
Proxy.User user = new Proxy.User();
user.Username = UsernameTB.Text;
user.FirstName = FirstNameTB.Text;
user.LastName = LastNameTB.Text;
user.CreatedDate = DateTime.Now;
user.UpdatedDate = DateTime.Now;
proxy.AddObject("User", user);
proxy.BeginSaveChanges(
SaveChangesOptions.None,
(asyncResult) => { proxy.EndSaveChanges(async
null
);
Now, here is an example of what DOES NOT work (note the lines where I say "selectedUser.Role.Add( )", and also note that "selectedUser" is a user that is already in the database and has been successfully queried/retrieved in another part of the client-side code):
public UpdateUserRoles(Proxy.User
{
Proxy.Entities proxy = new Proxy.Entities(new Uri("DataService.svc", UriKind.Relative));
proxy.AttachTo("User", selectedUser);
selectedUser.Role.Add(role
selectedUser.Role.Add(role
selectedUser.Role.Add(role
proxy.UpdateObject(selecte
proxy.BeginSaveChanges(
SaveChangesOptions.Batch,
(asyncResult) => { proxy.EndSaveChanges(async
null
);
}
In this second example, I would expect the "User_Role" join table in the database to be automatically updated with the new User/Role entries (corresponding to adding specific Roles to a User). In other words, there should be a new ROW in the User_Role join database table for every new Role I add to a User. But for some reason, the User_Role join table does not get updated accordingly. It remains blank, without any new ROWs added. Furthermore, the same issue applies to tables which are related via a 1-to-many relationship. Please let me know if you see something that I may be missing here. Any help would be greatly appreciated. Thank you.
Salim Fayad
You have to use the same proxy and not creating another proxy
ASKER
I am not sure that I understand what you mean? Let me clarify the code in my question above. The first code piece is to "Create a New User", and is completely independent of the second code piece, which is to "Update the Roles for an existing User". These pieces of code are executed in different (and independent) pages/files of the Silverlight application.
In the second code example (which is not working), I use the proxy to retrieve the "selectedUser" and the "roles" List, and then pass those parameters in to the "UpdateUserRoles" method. This all occurs within the same file, and using the same proxy variable created in that file. The proxy variable is not actually created in the "UpdateUserRoles" method, but I included it there in my code example above to clarify what I am all working with. The proxy variable is scoped and used by the entire class file, including the querying of the "selectedUser" and "roles", as well as in the "UpdateUserRoles" method to try and update the "selectedUser".
In the second code example (which is not working), I use the proxy to retrieve the "selectedUser" and the "roles" List, and then pass those parameters in to the "UpdateUserRoles" method. This all occurs within the same file, and using the same proxy variable created in that file. The proxy variable is not actually created in the "UpdateUserRoles" method, but I included it there in my code example above to clarify what I am all working with. The proxy variable is scoped and used by the entire class file, including the querying of the "selectedUser" and "roles", as well as in the "UpdateUserRoles" method to try and update the "selectedUser".
So there is only one Proxy.Entities variable that gets the user and updates the roles? (Because this is what it must be, the same Proxy.Entities variable gets the user and updates the roles).
You can check the following link for more info:
http://msdn.microsoft.com/en-us/library/cc668184.aspx
You can check the following link for more info:
http://msdn.microsoft.com/en-us/library/cc668184.aspx
ASKER
Yes, only one Proxy.Entities variable is used. I looked at the link you sent. I am familiar with all the material in that link. That just seems to be for setting up the ADO.NET Data Service in Visual Studio. I already have it set up and working. The problem is that I can't figure out how to get the many-to-many relationships working on Entities for update/insert/delete.
Proxy.User user = new Proxy.User();
user.Roles.Add(role1);
user.Roles.Add(role2);
etc...
Now I would like to save the user object, and have the association to all the roles we added to it be reflected in the database join table.
Proxy.User user = new Proxy.User();
user.Roles.Add(role1);
user.Roles.Add(role2);
etc...
Now I would like to save the user object, and have the association to all the roles we added to it be reflected in the database join table.
You have to set it and save it:
user.Roles = role1;
and then update user
user.Roles = role1;
and then update user
ASKER
This works for a 1-to-many relationship. For example, where a session has only 1 user (ie. session.User). By using the "SetLink" method I am able to connect the user to the session before saving the session, as in this example:
COSProxy.Session s = new COSProxy.Session();
proxy.AddObject("Session",
proxy.SetLink(s, "User", user);
s.User = user;
s.StartTime = DateTime.Now;
proxy.BeginSaveChanges(Sav
However, the "SetLink" method does not seem to work for a many-to-many relationship, as it doesn't accept a Collection as the second input parameter. For example, lets say that a user has many Roles, and I want to add a particular role to a users Collection of Roles.
// the following statement does not work, because user.Roles is a collection of entities,
// not just a singe entity (like in the above example where a session only has a single user).
// So I can't assign a single object to it.
proxy.SetLink(user, "Roles", role1);
user.Roles = role1;
proxy.UpdateObject("User",
proxy.BeginSaveChanges(Sav
COSProxy.Session s = new COSProxy.Session();
proxy.AddObject("Session",
proxy.SetLink(s, "User", user);
s.User = user;
s.StartTime = DateTime.Now;
proxy.BeginSaveChanges(Sav
However, the "SetLink" method does not seem to work for a many-to-many relationship, as it doesn't accept a Collection as the second input parameter. For example, lets say that a user has many Roles, and I want to add a particular role to a users Collection of Roles.
// the following statement does not work, because user.Roles is a collection of entities,
// not just a singe entity (like in the above example where a session only has a single user).
// So I can't assign a single object to it.
proxy.SetLink(user, "Roles", role1);
user.Roles = role1;
proxy.UpdateObject("User",
proxy.BeginSaveChanges(Sav
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Figured out solution.