Share via

Working with associations in ADO.NET Data Services , Part 2

  • Article

In part 2 ,we will talk about how to use the client library to deal with :

1..N Associations

This is the case of entities associated with each other 1..N . An example from Northwind is the relation between Categories and Products Entities.

An instance of the Categories entity type should have associated Products entities, this is another way of saying “ A row in the Products table should have links to a row in the Categories table”.
In case of 1..N relations , the left( child ) ends of the association are expressed as Collection properties on the right ( parent ) end of the relation.
This is shown in the metadata of the service as :

 <EntityType Name="Categories">
<!-- Primary Key-->
<Key>
  <PropertyRef Name="CategoryID" /> 
</Key>
 <!--Primitive Properties-->
  <Property Name="CategoryID" Type="Edm.Int32" Nullable="false" /> 
  <Property Name="CategoryName" Type="Edm.String" Nullable="false" /> 
  <Property Name="Description" Type="Edm.String" Nullable="true" /> 
  <Property Name="Picture" Type="Edm.Binary" Nullable="true" /> 
 <!-- Child Entity Set-->
  <NavigationProperty 
        Name="Products" 
        Relationship="northwind.FK_Products_Categories" 
        FromRole="Categories" 
        ToRole="Products" /> 
</EntityType>

This leads the client utility( DataSvcUtil.exe ) to generate a type Individual which has instances of types Customer and Contact as Properties.

The generated class on the client looks like this :

 public class Categories {
  public global::System.Collections.ObjectModel.Collection<Products> Products {
            get {
                return this._Products;
            }
            set {
                if ((value != null))
                {
                    this._Products = value;
                }
            }
        }
}

Now that we have set the stage, lets look at how one would use the client library to interact with the related types.

You have new instances of Individual , Contact and Customer and you want to persist the new entities and their relation to the store.

 DataServiceContext dsc = new DataServiceContext(new Uri("https://ServiceEndpoint"));
Categories freeProductsCategory = new Categories()
{
    CategoryName = "Free Products",
    Description = "These products are free",
};
Products freeHeadPhones = Products.CreateProducts(0, "Free Headphones", false);
Products freeZune = Products.CreateProducts(1, "Free Zune", false);

dsc.AddObject("Categories", freeProductsCategory);
dsc.AddObject("Products", freeHeadPhones);
dsc.AddObject("Products", freeZune);

Now what ?

Since the entities are created and added to the store can’t I just set the Customer and Contact Property to the Individual object and that will save the relation ?

 freeProductsCategory.Products.Add(freeHeadPhones);
freeProductsCategory.Products.Add(freeZune);
dsc.SaveChanges();

Shouldn’t this be enough ?

Nope , that’s not enough to save the relation to the store.

Why ?

Remember that the client context only gives you POCO access to entities in the store and any new entities that you create.

The Client context does not track any relations unless you explicitly ask it to do so !

Neat , now how do I ask it to do that ?

You use the AddLink method defined on the context.

 dsc.AddLink(freeProductsCategory, "Products", freeHeadPhones);
dsc.AddLink(freeProductsCategory, "Products", freeZune);

The signature and the intent of the SetLink method is lucid , it binds 2 entities into a relation , its kinda like a priest at a wedding,

This is what it looks like ..

AddLink ( Parentinstance ,”ChildPropertyName”,ChildInstance)

Deleting the parent entity

Deleting the parent entity means that the relations with the child entities are also removed.

Think of this as the argument of “How do I delete an entity that has 1..1 links with other   entities?”

What happens if I delete the parent entity without deleting the links ?

It depends on your store . If your database is configured to do a cascade delete on deletion of the parent entity ,you might get away with deleting the entity without removing the links

The right way to delete the entity is to remove all the links it has with the child entities and then delete the entity itself.

The code would look like this..

 DataServiceContext dsc = new DataServiceContext(new Uri("https://ServiceEndpoint"));
//Load an existing parent
Categories freeProductsCategory = dsc
                                  .CreateQuery<Categories>("Categories")
                                  .Where(cat => cat.CategoryName == "Free Products")
                                  .First();
//Load all the related child Entities
dsc.LoadProperty(freeProductsCategory, "Products");

//Delete the links with each of the child entities
foreach (Products freeProduct in freeProductsCategory.Products) {
    dsc.DeleteLink(freeProductsCategory, "Products", freeProduct);
}
//Delete the parent entity
dsc.DeleteObject(freeProductsCategory);
dsc.SaveChanges();

Wait !! what’s with the LoadProperty there ?
Well, if you need to delete the relation , the way to identify the relation or the link is to have both the right and the left end of the relations to be materialized. without the expand , the Contact and the Customer property are null , and we don’t know which relation to delete.In case of 1..1 relations it might be easy to predict based on the relation name ,

but wouldn’t be easy in case of 1..n relations.

Deleting the child entity

 //Load an existing parent
Categories freeProductsCategory = dsc
                                   .CreateQuery<Categories>("Categories")
                                   .Where(cat => cat.CategoryName == "Free Products")
                                   .First();
//Load all the related child Entities
dsc.LoadProperty(freeProductsCategory, "Products");

//Delete the links with each of the child entities
foreach (Products freeProduct in freeProductsCategory.Products) {
    dsc.DeleteLink(freeProductsCategory, "Products", freeProduct);
    //Delete the child entity
    dsc.DeleteObject(freeProduct);
}
dsc.SaveChanges();
Special Consideration for Navigation properties that are named differently than the Entity Type

This is a special case because of a bug in the client library at the time of writing this post.

If you have a navigation property  which is named differently than the EntityType and try to delete the link between the source

and the navigation property , we produce an invalid URI for the DELETE of the link.

ex:

Entity Type : Customers , EntitySet Name : Customers

Navigation Properties :
                      EntityType : Orders , Navigation Property Name : CashOrders

                      EntityType : Orders , Navigation Property Name : CreditCardOrders

Upon calling ,

dataContext.DeleteLink(customerInstance,”CashOrders”,orderInstance);

We generate a DELETE to the URI

/Customers(Key)/$links/Orders(Key)

This is wrong , the DELETE should go to :

/Customers(Key)/$links/CashOrders(Key)

We will address this issue in a future release .In the meantime ,  use the workaround below ,

 public partial class YourDataContextClass {

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

            //Get the Segment of the URI with the Key 
            string strChildAssociationURI = UriOfChild.Segments.Last();
            strChildAssociationURI = strChildAssociationURI.Substring(strChildAssociationURI.IndexOf('('));

            //ServiceEndPoint/ParentEntitySet/$links/NavigationPropertyName(ChildEntitySetKey)
            string requestUriForLinkDelete = System.String.Format("{0}/{1}/$links/{2}{3}",
                                             this.BaseUri.OriginalString,
                                             UriOfParent.Segments.Last(),
                                             associationName,
                                             strChildAssociationURI);

            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"));
            }
            //Detach this link as the link no longer exists and keep the context consistent
            this.DetachLink(source, associationName, target);
      }
}

and then , in your code ,

 YourDataContext dataContext = new YourDataContext(<Uri>);
dataContext.DeleteLinkManual(customerInstance,”CashOrders”,orderInstance);

This will produce the right URI :

 ServiceEndPoint/Customers(Key)/$links/CashOrders(Key)

Note that this doesnt work in Silverlight , I will create a separate sample for Silverlight later .

If you have any questions  , leave a comment . If you have any issues with code and need help , please post your query on the astoria forums as I can’t promise that I will be able to reply to emails sent to me directly .The whole team is very active on the forums and the more eyes on a problem the better.

Comments

  • Anonymous
    October 24, 2008
    PingBack from http://www.alvinashcraft.com/2008/10/24/dew-drop-october-24-2008/

  • Anonymous
    November 21, 2008
    Thank you for that, when in a vague term for time when would one be expecting a hotfix to come for the client library on deletion of links?

  • Anonymous
    December 08, 2008
    This really eats at me.  Entity Framework is one of the only OR/Ms that doesn't manage relationships or state.  This really just gets at me.  I've seriously considered going back to NHibernate, LLBLGen, .netTiers, or some other more expansive, mature, and useful OR/M.  Microsoft with its vast resources should have really turned out a better product. ...so far, rating == unhappy.

  • Anonymous
    December 08, 2008
    Hi Adron, This post is about the client side context that we ship with Astoria . The Entity Framework sits on the Server-Side of an ADO.NET Data Service. That being said , we welcome feedback on how to make this easier for you in the next version . What kind of APIs would you like added to the client library to make your programming experience better ? Change the existing APIs to abstract away dealing with associations/links/Relations ? Add data binding support to entities materialized through the context ?

  • Anonymous
    December 31, 2008
    Hi Daniel , The fix for deletion of links is published here : http://support.microsoft.com/default.aspx/kb/958481/

  • Anonymous
    February 05, 2009
    See my related question here: http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataservices/thread/33ba7ae3-eca7-44e3-a7fd-9dbc63fd8881 Basically we need better support for exposing the actual keys of realted entities for highly normalized table scenarios.  In would be nice to be able to include the foreign key ids directly in the main entity so that lookups can be cached on the client side and all relationships do not need to be expanded.

  • Anonymous
    April 25, 2009
    How do you edit related records and then save them? Here is the code that I tried but did not work: AdventureWorksEntities svc = new AdventureWorksEntities(new Uri("http://localhost:54377/AWADONetService.svc"));            ProductCategory  category = svc.ProductCategory.Expand("ProductSubcategory").Where(r => r.Name.StartsWith("Category 1")).First ();            category.ProductSubcategory[0].Name = category.ProductSubcategory[0].Name + " changed";            svc.SaveChanges(System.Data.Services.Client.SaveChangesOptions.Batch);

  • Anonymous
    April 25, 2009
    Hi Philip, You are missing a call to UpdateObject before calling SaveChanges svc.UpdateObject( category.ProductSubcategory[0] ); svc.SaveChanges(System.Data.Services.Client.SaveChangesOptions.Batch); This should work

  • Anonymous
    April 26, 2009
    Thanks Phani Raj.  This was very helpful.  Adding the svc.UpdateObject call solved the problem.  But I faced a couple of related problems. 1- If I consume this service in silverlight application and call this query for a ProductCategory where there are no ProductSubCategories I get an error “Value cannot be null” at the step of calling the EndExecute within the AsyncCallback function.  I use this syntax for defining the query: DataServiceQuery<ProductCategory> qry = Context.ProductCategory.Expand("ProductSubCategory"); 2- When I added the service refrence to a Silverlight application using VS2008. I clicked on the Advanced button and indicated ObservableCollection where it says “Collection Type”. Yet the generated reference.cs class still shows the expended ProductSubCategories as System.Collections.ObjectModel.Collection.  I want to be able to bind the expended records to a datagrid that reflects any changes to the collection.  How to do this correctly?

  • Anonymous
    May 02, 2009
    I have been trying at the expand query but am still getting 2 errors: "Value Cannot be null" and "An Item with the same key has already been added".  I put here  a complete compressed project that reproduces the problems: http://webswapp.com/categories/Silverlight/ADONET/ExpandQuery.aspx

  • Anonymous
    June 18, 2014
    Thank you!! After about 8 hours of trial and error and reading various web pages, this solved my problem!  I was trying to set a lookup value on a custom collection added to a SharePoint calendar. Toward the end I was getting errors like "setlink method only works when the source property is not a collection". Then I saw your description of adding relations and the AddLink example, and that worked.