locked
Deleting Relationships (1-to-many) - PLEASE HELP RRS feed

  • Question

  • Hello,

     

    I posted this in the "Problem deleting many-to-many relationship" thread but I've had no responses and am stuck in my code...

     

    Let's say I have an Address entity that has a 1 to many relationship with Contact entities. I want to write a method for deleting an address in the DB. This methods sits in my "gateway" DLL loaded into my ASP.NET app. Would this not suffice:

    public void DeleteAddress(int addressID)
    {

    Address address = (from a in _context.AddressSet
    where a.AddressID == addressID
    select a).First();

    // There would exist a collection of Contact entities
    _context.LoadProperty(address, "Contacts");

    for (int i = 0; i < address.Contacts.Count; i++)
    {

    _context.DeleteLink(address, "Contacts", address.Contact[ i ]);
    }

    _context.DeleteObject(address);
    _context.SaveChanges();

    }

     

    I've discovered that I cannot use this call in the for loop:
    _context.DeleteLink(address, "Contacts", address.Contact[ i ]);

    I get this error:
    <message xml:lang="en-CA">Resource not found for the segment "ContactsSet".</message>

    I tried the DeleteLink in the opposite direction (Contact [ i ], "Address", address), but I get this error:
    "AddLink and DeleteLink methods only work when the sourceProperty is a collection."

    I tried DETACHLINK in both directions as well. One direction gave me the same error as "Resource not found...", and the other gave me an error about how the DELETE statement was violating the Foreign Key constraint!

    What exactly do I need to do to remedy this?

    Friday, October 3, 2008 2:57 PM

All replies

  •  

    Hi,

     

    From the post , the Address entity has a 1 to many relation with Contact entities .

    I understand that there are links between Address and Contact entities.

     

    Are there back links from Contact entities to Address entities ?

     

    "I  tried the DeleteLink in the opposite direction (Contact [ i ], "Address", address), but I get this error:
    "AddLink and DeleteLink methods only work when the sourceProperty is a collection.""

     

    This tels me that the link from a Contact entity to an address entity is 1 to 1.

     

    1) Grab a fiddler trace with the code that tries to delete the object.

     

         What is the entity Type , is it ContactsSet?

         If the name of the entitySet is different from the entityType ,  and you can change the  schema , then change the schema so that both match.

    or follow the instructions here :
    http://forums.microsoft.com/Forums/ShowPost.aspx?PostID=3859081&SiteID=1

     

    2) Second case of deleting the relation from Contact->Address ,

         If you do this :

     

    Code Snippet

    Address address = (from a in _context.AddressSet
    where a.AddressID == addressID
    select a).First();

    // There would exist a collection of Contact entities
    _context.LoadProperty(address, "Contacts");

     

     

    only links which are address-> Contact are materialized.

    the links Contact ->Address are not materialized.

     

    to get the links in the opposite direction , you would need to add this ;


    Code Snippet
    for (int i = 0; i < address.Contacts.Count; i++)
    {

       _context.LoadProperty(address.Contact[ i ],"Address");
    }

     

    and then you can write this to delete the links in the other way

     

    Code Snippet

    for (int i = 0; i < address.Contacts.Count; i++)
    {

       _context.SetLink(address.Contact[ i ],"Address",null);
    }

     

    Friday, October 3, 2008 4:19 PM
    Moderator
  • Hi Phani,

     

    The code that I included was a simplified version of my code. I'd like to clarify a few things (without the Fiddler trace) and ask a couple of questions.

     

    1. ONE Address entity can be linked to MANY Contact entities. There is a no back link from an Address to a Contact (i.e., in the DB, there exists a Contact Table with a column called "AddressID", but in the Address table, there is no "ContactID" column).

     

    2. In my EDMX, My "Entity Set Name" is ContactSet and the "Name" is Contact (is this what you meant by "Type")? Same goes for AddressSet and Address. Why do these need to be the same title? There's a benefit to there being a difference here. When you use the objects, you refer to a contact as a "Contact", but when you want to save to that entity set, you can use the plural or group name "ContactSet".

     

    3. A simpler version of my question is this: I'd simply like to know how to Delete an Address and with it, clean up all the links between the MANY contacts that might have been related to that Address. Is the answer to this question the same as the 2 loops you had in your code snippets from your last reply?

     

    Thanks for the help.

    Monday, October 6, 2008 3:46 PM
  • Phani,

     

    I have tried your previously suggested way of deleting the "Address" as well. Here is the stripped down version of the code:

     

    Code Snippet

    public void DeleteAddress(int addressID)

    {

    Address address = (from a in _context.Address

    where a.AddressID == addressID

    select a).First();

     

    _context.LoadProperty(address, "Contact");

     

    for (int i = 0; i < address.Contact.Count; i++)

    {

    _context.LoadProperty(address.Contact[i], "Address");

    _context.SetLink(address.Contact[i], "Address", null);

    }

     

    _context.DeleteObject(address);

     

    _context.SaveChanges(SaveChangesOptions.Batch);

    }

     

    This seems like a strange way to simply delete an entity and all of its links to other entities. Still, it does not work, and I get the following Fiddler trace when I make the SaveChanges call:

     

    Outbound:

    POST /CRMDataService.svc/$batch HTTP/1.1

     

    --batch_19c3b8fe-a445-4d9f-aea2-69bb7f47b537

    Content-Type: multipart/mixed; boundary=changeset_127d3396-e810-4adb-955c-db8b20a16496

    --changeset_127d3396-e810-4adb-955c-db8b20a16496

    Content-Type: application/http

    Content-Transfer-Encoding: binary

    DELETE http://127.0.0.1.:50000/DataService.svc/Contact(4)/$links/Address HTTP/1.1

    DataServiceVersion: 1.0;NetFx

    Content-ID: 5

    --changeset_127d3396-e810-4adb-955c-db8b20a16496

    Content-Type: application/http

    Content-Transfer-Encoding: binary

    DELETE http://127.0.0.1.:50000/DataService.svc/Address(47)/$links/Contact(4) HTTP/1.1

    DataServiceVersion: 1.0;NetFx

    Content-ID: 6

    --changeset_127d3396-e810-4adb-955c-db8b20a16496

    Content-Type: application/http

    Content-Transfer-Encoding: binary

    DELETE http://127.0.0.1.:50000/DataService.svc/Address(47) HTTP/1.1

    Content-ID: 7

    --changeset_127d3396-e810-4adb-955c-db8b20a16496--

    --batch_19c3b8fe-a445-4d9f-aea2-69bb7f47b537--

     

    Inbound:

    HTTP/1.1 202 Accepted

     

    --batchresponse_08415fd9-19b1-4f4f-8c5e-73cd62b5a0ac

    Content-Type: multipart/mixed; boundary=changesetresponse_6b83485e-0387-47b6-b90d-2c47135707c5

    --changesetresponse_6b83485e-0387-47b6-b90d-2c47135707c5

    Content-Type: application/http

    Content-Transfer-Encoding: binary

    HTTP/1.1 404 Not Found

    Content-ID: 6

    Content-Type: application/xml

    DataServiceVersion: 1.0;

    <?xml version="1.0" encoding="utf-8" standalone="yes"?>

    <error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">

    <code></code>

    <message xml:lang="en-US">Resource not found for the segment 'Contact'.</message>

    </error>

    --changesetresponse_6b83485e-0387-47b6-b90d-2c47135707c5--

    --batchresponse_08415fd9-19b1-4f4f-8c5e-73cd62b5a0ac--

     

     

    I don't quite understand what is going on. The Contact and Address data is getting correctly pulled and populated with each LoadProperty call. I've even tried JUST deleting the address object without deleting any links. I get the same error. As I've said before, I've tried deleting the relationship in the opposite direction (Address to Contact instead of Contact to Address) to no avail. I figured that the link (branch) direction shouldn't matter when deleting... does it?

     

    I simply don't know what to do at this point to simply delete entities via ADO.NET DS! My create and modify code in my gateway DLL work just fine!

    Tuesday, October 7, 2008 4:59 PM
  • The problem is this :

     

    DELETE http://127.0.0.1.:50000/DataService.svc/Address(47)/$links/Contact(4) HTTP/1.1

     

    your entity set is named ContactSet , whereas the client is sending a DELETE to the URI with the entity Type endpoint "Contact".

     

    Deleting the Link between Address and its navigation property "Contact" should send a DELETE to the Address (47)/$links/ContactSet(4) endpoint , instead a DELETE is being sent to the  Address (47)/$links/Contact(4) endpoint.
    Since the uri is invalid , you get the 404 error from the service.


    Unfortunately , this scenario is broken .

    This is a bug in the client library and we are aware of this.
    I cannot confirm the dates when a hotfix will be available for this release.

     

    This was illustrated in the Forum thread I linked to in my first reply :

    http://forums.microsoft.com/Forums/ShowPost.aspx?PostID=3859081&SiteID=1

     

    In the meantime , you can use the workaround as suggested by user "Lord" and me on that thread.

    Tuesday, October 7, 2008 5:04 PM
    Moderator
  • Thanks Phani,

     

    Unfortunately, I'm not sure if that is my problem. After your previous post, I changed all of my Entity Set Names to match my Entity Names (so ContactSet is called Contact, AddressSet is now Address).

     

    I updated the EDMX, ran the Data Service again, created a new Entity.cs using DataSvcUtil.exe, placed this new .cs in my gateway DLL, updated my gateway code to stop making LINQ calls to the "ContactSet", "AddressSet", etc. entity sets, and instead they now call Contact, Address, etc.

     

    Have I missed a step in changing my entity set names to match my entity names? Is there something that is still causing the client library some grief and giving me these delete errors?

    Tuesday, October 7, 2008 5:52 PM
  • Hi ,

     After changing the names of the entityset do you still see the same failure ?

     can you capture a fiddler trace with this new failure ?

     

    Tuesday, October 7, 2008 5:54 PM
    Moderator
  • That failure I posted above was with the set names changed.  Before the change, my failure would say "Resource could not be found for Segment ContactSet".

     

    In case more information would help, here are the details to the ACTUAL entities (as seen in the EDMX) in my application:

     

    Consultant

    Entity Set Name: Consultant

    Name: Consultant

    Entity Key: ConsultantID

    --- a bunch of scalar properties ---

    --- some navigation properties ---

    Pertinent Navigation Property 1: Address1

      Multiplicity: 1 (One)

      Name: Address1

      Return Type: Instance of Address
      Association: namespace.FK_Consultant_Address1ID_Address_AddressID

      From Role: Consultant

      To Role: Address

    Pertinent Navigation Property 2: Address2

      Multiplicity: 1 (One)

      Name: Address2

      Return Type: Instance of Address
      Association: namespace.FK_Consultant_Address2ID_Address_AddressID

      From Role: Consultant

      To Role: Address

     

    Address

    Entity Set Name: Address

    Name: Address

    Entity Key: AddressID

    --- a bunch of scalar properties ---

    --- some navigation properties ---

    Pertinent Navigation Property 1: Address1Consultant

      Multiplicity: * (Many)

      Name: Address1Consultant

      Return Type: Collection of Consultant
      Association: namespace.FK_Consultant_Address1ID_Address_AddressID

      From Role: Address

      To Role: Consultant

    Pertinent Navigation Property 2: Address2Consultant

      Multiplicity: * (Many)

      Name: Address2Consultant

      Return Type: Collection of Consultant
      Association: namespace.FK_Consultant_Address2ID_Address_AddressID

      From Role: Address

      To Role: Consultant

    Pertinent Navigation Properties 3 & 4: Address1Client & Address2Client

      Basically the same as above but linking to Client entities (see below)

     

    Clients

    Basically the same as Consultant, it too has two Address properties.

     

    The code you saw before was simplified using just 1 address and the notion of a "Contact" rather than Consultants and Clients, etc.

    Tuesday, October 7, 2008 6:06 PM
  • Hi,

     

    I would suggest something like this:

     

    Code Snippet

    public void DeleteAddress(int addressID)
    {

    Address address = (from a in _context.AddressSet
    where a.AddressID == addressID
    select a).First();

    _context.LoadProperty(address, "Contacts");

    // Remove all associations to this address (forward and backward)
    for (int i = 0; i < address.Contacts.Count; i++)
    {

    // Detach back link from contact to address

    Contact contact = address.Contacts[ i ];

    _context.DetachLink(contact, "Address", address);

    _context.UpdateObject(contact);

     

    // Delete forward link from address to contact

    _context.DeleteLink(address, "Contacts", contact); 

    }

    _context.DeleteObject(address);
    _context.SaveChanges();

    }

     

    I had a similar scenario only I was simply removing one of the entities on the "many" side of the 1-to-many. If I'm not mistaken, you are trying to remove the entity on the "1" side of the relationship. I found that, in my case, the back link (from each object on the "many" side) needed to be detached while the forward link (from the object on the "1" side)  needed to be deleted. Don't ask me why. It was just trial and error. I went through several permutations before I found the one that worked for me.

     

    I hope it works for you.

     

    Rob

    Wednesday, October 8, 2008 1:34 PM
  • Hi Fazeli,

     

    Can you post the actual error message you recieve with this new model ?

     From what I understand ,
     

    Clients have 1 to * relation with Address entities.

    Contacts have 1 to * relation with Address entities.

     

    When you want to delete an Address entity , you need to remove the links from the
    Address entities to any related Clients and Contacts entities.

    Code Snippet

    Address address = (from a in _context.AddressSet
    where a.AddressID == addressID
    select a).First();

    //load the related navigation properties

    context.LoadProperty(address ,"Address1Consultant");

    context.LoadProperty(address ,"Address2Consultant");

    context.LoadProperty(address ,"Address1Client");

    context.LoadProperty(address ,"Address2Client");

     

    //Delete the links to these navigation properties by calling Delete Link
    address.Address1Consultant.ToList().Foreach(

     c1 => context.DeleteLink(address,"Address1Consultant",c1);

    );

    address.Address2Consultant.ToList().Foreach(

     c2 => context.DeleteLink(address,"Address2Consultant",c2);

    );

    address.Address1Client.ToList().Foreach(

     c3 => context.DeleteLink(address,"Address1Client",c3);

    );

    address.Address2Client.ToList().Foreach(

     c4 => context.DeleteLink(address,"Address2Client",c4);

    );

     

    //Delete the top level entity now

    context.DeleteObject(address);

     

    //Commit the changes

    context.SaveChanges();

     

     

     

    If this fails , then capture a fiddler trace with the failure , and paste it in a response.

    Wednesday, October 8, 2008 4:44 PM
    Moderator
  • Hi Rob (and Phani),

     

    I tried your code exactly with 1 exception: I call _context.SaveChanges(SaveChangeOptions.Batch).

     

    It did not work. I still get the same error as before "Resource Not Found For Segment 'Contact'" (you can substitute the entity "Consultant" or "Client" in the place of "Contact" for my actual EDM design and error messages).

     

    Here is the code (the entity models for Consultant and Address are described in a previous post):

    Code Snippet

    for (int i = 0; i < address.Address2Consultant.Count; i++)

    {

      // Detach back link from Consultant to Address

      Consultant consultant = address.Address2Consultant[i];

      _context.DetachLink(consultant, "Address2", address);

      _context.UpdateObject(consultant);

      // Delete forward link from Address to Consultant

      _context.DeleteLink(address, "Address2Consultant", consultant);

    }

    _context.DeleteObject(address);

    _context.SaveChanges();

     

     

    I believe the problem is in the DELETE /DataService.svc/Address(47)/$links/Consultant(4) call. If I try to manually navigate to this URL, it doesn't exist. The proper URL is /DataService.svc/Address(47)/$links/Address2Consultant. At that URL, I see:

     

    <?xml version="1.0" encoding="utf-8" standalone="yes" ?>

    - <links xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices">
      <uri>http://127.0.0.1.:50000/DataService.svc/Consultant(4)</uri>
    </links>

     

    Although my entity set name is "Consultant" and the entity name is Consultant, the navigation properties in the Address entity are named "Address1Consultant" and "Address2Consultant". Is this causing the issue?

     

    I can post the fiddler trace again, but it looks very similar to before (now there is an extra MERGE call in there following the "UpdateObject" call).

     

    I'm very confused here!

    Wednesday, October 8, 2008 5:09 PM
  • Hi Phani,

     

    I was typing my last post as you were typing yours. I have yet to try your latest suggestion.

     

    Just to clarify:

    1 Address can be linked to Many Consultants (not the other way around)

    1 Address can be linked to Many Clients (not the other way around)

     

    1 Consultant can only be linked to 2 Addresses (via 2 separate AddressIDs called Address1ID and Address2ID in the DB table).

    -- the same goes for Clients...

     

    Please ignore the shortcomings or bizarreness of this design, but I thought I would clarify.

     

    Wednesday, October 8, 2008 5:13 PM
  • while you try that , we will still generate the wrong URI for deleting the link .

     

    a) Add this method to the Data ServiceContext class you are using :

     

    Code Snippet

    public void DeleteLinkManual(object Source,string associationName,string Target) {

     Uri UriOfParent;
     Uri UriOfChild;
     this.TryGetUri(Source,out UriOfParent);
     this.TryGetUri(Target,out UriOfChild);

     string requestUriForLinkDelete = String.Format( "{0}/$links/{1}",
     UriOfParent.Segments.Last() ,
     UriOfChild.Segments.Last());

     WebRequest request = WebRequest.Create(requestUriForLinkDelete);
     request.Method = "DELETE";
     HttpWebResponse reponse = request.GetResponse() as HttpWebResponse;

     if( response.StatusCode != HttpStatusCode.NoContent){
       throw ( new DataServiceClientException("Delete Failed"));
     }
    }

     

     

    b) and change the code to be :

    Code Snippet

    Address address = (from a in _context.AddressSet
    where a.AddressID == addressID
    select a).First();

    //load the related navigation properties

    context.LoadProperty(address ,"Address1Consultant");

    context.LoadProperty(address ,"Address2Consultant");

    context.LoadProperty(address ,"Address1Client");

    context.LoadProperty(address ,"Address2Client");

     

    //Delete the links to these navigation properties by calling Delete Link
    address.Address1Consultant.ToList().Foreach(

     c1 => context.DeleteLinkManual(address,"Address1Consultant",c1);

    );

    address.Address2Consultant.ToList().Foreach(

     c2 => context.DeleteLinkManual(address,"Address2Consultant",c2);

    );

    address.Address1Client.ToList().Foreach(

     c3 => context.DeleteLinkManual(address,"Address1Client",c3);

    );

    address.Address2Client.ToList().Foreach(

     c4 => context.DeleteLinkManual(address,"Address2Client",c4);

    );

     

    //Delete the top level entity now

    context.DeleteObject(address);

     

    //Commit the changes

    context.SaveChanges();

     

     

    Wednesday, October 8, 2008 5:25 PM
    Moderator
  • Hi Phani,

     

    I'm sorry for what might be a stupid question, but when I place your DeleteLinkManual in my Client side autogenerated DataServiceContext class in Entities.cs (in my ASP.NET app), The Uri.Segments.Last() call does not compile.

     

    I use absolute referencing for all the classes so:

     

    Code Snippet

    public partial class Entities : global::System.Data.Services.Client.DataServiceContext

    {

    ...

    public void DeleteLinkManual(object source, string associationName, object target)

    {

    System.Uri UriOfParent;

    System.Uri UriOfChild;

    this.TryGetUri(source, out UriOfParent);

    this.TryGetUri(target, out UriOfChild);

     

    string requestUriForLinkDelete = System.String.Format("{0}/$links/{1}",

    UriOfParent.Segments.Last(),

    UriOfChild.Segments.Last());

     

    System.Net.WebRequest request = System.Net.WebRequest.Create(requestUriForLinkDelete);

    request.Method = "DELETE";

    System.Net.HttpWebResponse response = request.GetResponse() as System.Net.HttpWebResponse;

     

    if (response.StatusCode != System.Net.HttpStatusCode.NoContent)

    {

    throw (new System.Data.Services.Client.DataServiceClientException("Delete Failed"));

    }

    }

    }

     

     

    I see what it is you're trying to do here. But no matter how I check it or look it over, I don't have access to the "Last" method (or any other indexing method) on the Segments property of the System.Uri objects!!!

     

    If I can resolve this, I can run the test and see if it works.

    Wednesday, October 8, 2008 6:48 PM
  •  

    Hi,

     the Last Method is an extension method and its present in the System.Collections.Generic and System.Linq namespace.

    you will need to include both of these namespaces for the code to compile

    Wednesday, October 8, 2008 9:36 PM
    Moderator
  • Hi again Phani,

     

    When I use the code that you provided, here is what happens during a debug in the DeleteLinkManual method:

     

    this.TryGetUri(source, out UriOfParent) --> returns http://127.0.0.1.:50000/DataService.svc/Address(47)

    this.TryGetUri(target, out UriOfChild) --> returns http://127.0.0.1.:50000/DataService.svc/Consultant(4) (this seems like a problem)

     

    the line:

    Code Snippet

    string requestUriForLinkDelete = System.String.Format("{0}/$links/{1}", UriOfParent.Segments.Last(), UriOfChild.Segments.Last());

     

    does not generate a URL that is liked by the next line:

    Code Snippet

    System.Net.WebRequest request = System.Net.WebRequest.Create(requestUriForLinkDelete)

     

    so I hard coded the entire URL (System.String.Format(http://127.0.0.1.:50000/DataService.svc/{0}/$links/{1}, ...) just to get the code to run correctly.

     

    What happens then is that the following Request is sent out:

    Header

    DELETE /CRMDataService.svc/Address(47)/$links/Consultant(4) HTTP/1.1

     

    And as expected, the response says 404 Not Found --> Resource not found for the segment 'Consultant'

     

    Manual navigation of the DataService in IE7 does not resolve http://127.0.0.1.:50000/DataService.svc/Address(47)/$links/Consultant(4), What's expected is /Address(47)/$links/Address2Consultant.

     

    The TryGetUri is not returning the right link URI. What now?

    Thursday, October 9, 2008 4:25 PM
  • Hi Phani,

     

    Any other suggestions? I'm rather stuck with this problem. Do I have to change my EDM or the way I use EF / ADO.NET DS? I don't quite understand why this problem exists or how others haven't encountered it.

     

    Tomorrow I'm going to reconstruct the EDM and create the DS from scratch just in case something funny has happened along the way. Still, I don't believe that will fix my problem.

     

    I'd appreciate the help to resolve this issue. TIA.

     

    Tuesday, October 14, 2008 12:45 AM
  • Hi Phani, Pablo, Mike, or anyone else on the Astoria team who can help:

     

    1. I have recreated my EDM, made sure that all the Entity Set Names are identical to the Entity Names (i.e., the entity isn't called Consultant but the set called ConsultantSet).

     

    2. I have renamed all of my navigation properties as needed. For example, a Consultant entity has 2 Address entities (seen as Address1ID and Address2ID in the Consultant table in the DB), and because the EDM generator automatically calls these "Address", and "Address1", I have renamed them appropriate to say "Address1" and "Address2".

     

    3. I have specifically renamed my back-navigation properties from Address entities to Consultant & Client entities so that instead of "Consultant" and "Consultant1" they read "ConsultantAddress1" and "ConsultantAddress2" (same for Clients).

     

    4. I created a new Data Service that links to the new EDM.

     

    5. I used DataSvcUtil to generate my client side classes.

     

    6. All of my gateway code (dll) to access the Data Service from my ASP.NET app is updated and compiles.

     

    7. I run a page that deletes a Consultant's Address2 and this executes Phani's DeleteLinkManual code as shown earlier in this thread.

     

    I get the exact same error as before, so I am totally out of ideas.

     

    There is only a single link to be removed in my "test data" and a single Address object to be deleted. The link that is generated and sent out is:

    DELETE /DataService.svc/Address(47)/$links/Consultant(4) HTTP/1.1

    And the reply:

    HTTP/1.1 404 Not Found

    <error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">

    <code></code>

    <message xml:lang="en-US">Resource not found for the segment 'Consultant'.</message>

    </error>

     

    You cannot navigate manually (in IE7) to /DataService.svc/Address(47)/$links/Consultant(4) - it does not exist.

    You have to navigate to /DataService.svc/Address(47)/$links/ConsultantAddress2

    (note if you're following the forum posts, I used to call it Address2Consultant)

    Then what you see is:

    <?xml version="1.0" encoding="utf-8" standalone="yes" ?>

    - <links xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices">
      <uri>http://127.0.0.1.:50000/DataService.svc/Consultant(4)</uri>
    </links>
     
    How do I fix this problem? I'm totally blocked in this respect and clearly not knowledgeable enough to figure this out on my own!

     

    Is anyone on the Astoria team available to discuss this offline and go into further details?
    Tuesday, October 14, 2008 3:56 PM
  • HI Fazeli ,

    " Is anyone on the Astoria team available to discuss this offline and go into further details? "

    Please mail me at "PhaniRaj at Microsoft dot com"

     

    -Phani

    Thursday, October 16, 2008 6:26 PM
    Moderator
  • Hello,

    I am experiencing the exact same issue Deleting Relationships between (1-to-many) and (many-to-many).  Instead of starting another thread, would you be able to notify me via email of any correspondence/advancements on this issue?

    Thanks,
    Robert
    Saturday, October 18, 2008 6:28 AM
  • Hey Robert,

     

    Phani and I are currently discussing solutions to the problem. Phani has come up with one potential solution and I'll be trying it out tomorrow.

     

    When we have a working solution, I'll post it here.

     

    - Fazeli

    Sunday, October 19, 2008 6:37 PM
  • Hey Fazeli,

    Thanks for getting back to me.  I have been doing some trial and error for this issue, and believe I have come to figure out a solution.  It works for many-to-many relationship, as well as 1-to-many relationship.  I hope maybe this can help you too.  Let me know if you have any questions.

    I have the following tables defined in SQL Server, exactly with the following names:
    --Role
    --User
    --User_Role (this is a join table, with only 2 columns - UserID and RoleID, used as a composite primary key.  And I setup foreign keys to the User an Role tables)

    Using the Entity Framework Wizard to generate the Model from the DB, it automatically sets up a (* to *) relationship between User and Role tables, bypassing the User_Role join table altogether in the EF Model.

    //
    // The most important thing to note...
    // You must 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 context database/dataservice connection as is subsequently used for
    // Adding and Removing of Roles from the User,
    // then it does not properly recognize the association between User and Roles when deleting.
    // Before making this modification, I was experiencing problems where it was able to delete from the DB, but not
    // the client. 
    //
    // Note: MyProxy is simply a Service Reference from the client side "Silverlight" code to the server side
    // ADO.NET Data Services, which is itself connected to the Entity Framework.
    //
    private MyProxy.MyEntities proxy = new MyProxy.MyEntities(new Uri("MyDataService.svc", UriKind.Relative));
    private MyProxy.User selectedUser;
    private MyProxy.Role currentRole;


    //
    // The User is passed in to this class from another page in the App.  But I found that I needed to re-query for the
    // User directly in this class, and use the Expand option on its Roles.  Otherwise, it didn't seem to work, even if
    // I used the Expand option on querying for the User before passing it into this class.
    //
    private void Init(MyProxy.User user, MyProxy.Role role)
    {
        // Note that it is not necessary to re-query for the Role that will be added to the User.  So we can just assign it
        // from the Role parameter that was passed into this class.
        currentRole = role;
        ExpandQueryUserRoles(user);
    }


    //
    // Expand the User in relation to its Roles
    //
    private void ExpandQueryUserRoles(MyProxy.User user)
    {
        var query = from u in proxy.User.Expand("Role")
                        where u.UserID == user.UserID
                        select u;

        DataServiceQuery<COSProxy.User> userQuery = (DataServiceQuery<COSProxy.User>)query;

        userQuery.BeginExecute((asyncResult) =>
        {
            List<COSProxy.User> users = userQuery.EndExecute(asyncResult).ToList();
            selectedUser = users[0];
        }, userQuery);
    }


    //
    // To Add a Role to a User
    //
    private void AddRoleToUser()
    {
        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
    //
    private void RemoveRoleFromUser()
    {
    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);
    }


    //
    // Now save/update the Roles for the User, whether they were added or removed to/from the User.
    // This will automatically add or delete the proper row in the User_Role join table in the DB, without having
    // to make reference to it from the code.
    //
    private void UpdateUserRoles()
    {
    proxy.BeginSaveChanges(SaveChangesOptions.Batch, (asyncResult) =>
    {
    proxy.EndSaveChanges(asyncResult);
    }, null);
    }

    Monday, October 20, 2008 1:07 AM