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,982 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

863 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

22 Experts available now in Live!

Get 1:1 Help Now