Solved

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)?

Posted on 2008-10-16
9
27,970 Views
Last Modified: 2013-11-12
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(asyncResult); },

    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 selectedUser, List<Proxy.Role> roles)
{

    Proxy.Entities proxy = new Proxy.Entities(new Uri("DataService.svc", UriKind.Relative));

    proxy.AttachTo("User", selectedUser);

    selectedUser.Role.Add(roles[0]);

    selectedUser.Role.Add(roles[1]);

    selectedUser.Role.Add(roles[2]);

    proxy.UpdateObject(selectedUser);
    proxy.BeginSaveChanges(

        SaveChangesOptions.Batch,

        (asyncResult) => { proxy.EndSaveChanges(asyncResult); },

        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.
0
Comment
Question by:robbersrow
  • 5
  • 4
9 Comments
 
LVL 11

Expert Comment

by:Salim Fayad
ID: 22729126
You have to use the same proxy and not creating another proxy
0
 

Author Comment

by:robbersrow
ID: 22734031
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".
0
 
LVL 11

Expert Comment

by:Salim Fayad
ID: 22743373
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

0
 

Author Comment

by:robbersrow
ID: 22743903
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 11

Expert Comment

by:Salim Fayad
ID: 22744121
You have to set it and save it:

user.Roles = role1;

and then update user
0
 

Author Comment

by:robbersrow
ID: 22746459
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", s);
proxy.SetLink(s, "User", user);
s.User = user;
s.StartTime = DateTime.Now;
proxy.BeginSaveChanges(SaveChangesOptions.Batch);

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", user);
proxy.BeginSaveChanges(SaveChangesOptions.Batch);
0
 
LVL 11

Assisted Solution

by:Salim Fayad
Salim Fayad earned 100 total points
ID: 22753266
Here is how I did it:

using (TransactionScope sc = new TransactionScope(TransactionScopeOption.Required))

            {

                var hub = new HubController().GetHubByID(HubID);

                var city = GetCityByID(CityID);

                city.Hubs.Add(hub);

                db.SaveChanges();

                sc.Complete();

            }

Open in new window

0
 

Accepted Solution

by:
robbersrow earned 0 total points
ID: 22753630
Thanks for looking into this.  Actually, I ended up figuring out the solution through trial and error.

//
// The most important thing to note:  query for the user and use the Expand option to
// include the Roles to be returned with the user.  If this is not done in the same class file, using the
// same proxy connection as the Adding and Removing of Roles from the User, then it does not
// properly recognize the association between User and Roles.
//
var query = from u in proxy.User.Expand("Role")
                    where u.UserID == user.UserID
                    select u;

// Then execute the query and get the "selectedUser" for below

//
// To Add a Role to a User
//
if (!proxy.Links.Any(l => l.Source == (object)selectedUser && l.Target == (object)currentRole))
    proxy.AddLink(selectedUser, "Role", currentRole);
if (!selectedUser.Role.Contains(currentRole))
    selectedUser.Role.Add(currentRole);
proxy.UpdateObject(selectedUser);

//
// To Remove a Role from a User
//
if (proxy.Links.Any(l => l.Source == (object)selectedUser && l.Target == (object)currentRole))
    proxy.DeleteLink(selectedUser, "Role", currentRole);
if (selectedUser.Role.Contains(currentRole))
    selectedUser.Role.Remove(currentRole);
proxy.UpdateObject(selectedUser);
0
 

Author Comment

by:robbersrow
ID: 22753631
Figured out solution.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now