locked
Dynamic Data - Need to filter by child object through a many to many relationship RRS feed

  • Question

  • User-43769838 posted

    Hi There 

    I am using Dynamic Data in a client project, and having a lot of success with it.  I have come across a situation where I need to access an object via a many to many relationship.  Here is what I have

     
    Product -> CategoryProduct <- Category

    So I have products, categories, and a Category to Product link table inbetween.  This all works great.

    In my dynamic data website I can view the CategoryProducts collection, and it shows me the various product/Category links, as well as allowing me to filter by Category (I have turned off the Product filter as I don't want it loading all of the products into the drop down)

    On the Category table there is a Foreign Key to a Store Table.  I now want to be able to filter my CategoryProduct collection by Store.

    I have done the following in a Partial Class, which gives me the ability to display a Store filter, and also displayes the Store name in the CategoryProduct List view.

            private EntityRef<Store> _Store;

            [Association(Storage="_Store")]
            public Store Store
            {
                get
                {
                    return this.StoreCategory.Store;
                }
            }

    However.  When I change the Store drop down filter, I get no results back.  Obviously this is because the relationship between Product, Category and Store has not been defined.  How can I mark up my public Store Store property so that LINQ to SQL knows that STORE relates to the StoreID field on the Category table?

    Thanks

    Tatsky
     

    Friday, July 18, 2008 6:41 AM

All replies

  • User-330204900 posted

    Hi Tatsky, have a look at these two threads

    Many to many with dynamic data

    One-to-many and many-to-many mappings

    May be these will help [:D]

    Friday, July 18, 2008 7:22 AM
  • User-43769838 posted

    Hi

    Thanks for the reply.  Those 2 links show how to deal with one to many and many to many relationships.  However I already have my many to many relationship working.  ie Products to Categories via a link table CategoryProducts.  This all works fine, and I can add, remove, filter etc.

    However, what I need to do is from the Product object, get the Store object in which the Category sits.  So basically A Store contains categories.  The category contains products.  I need within my CategoryProduct relationship to allow the Product to see the Store object on its linked Category.  So then I can filter on All products in a particular Store.

    If I wanted to do it on a one by one basis in code it would be

    Store s = Product.ProductCategories[0].Store;

    But I need to define that relationship, so that I can use filtering etc in my Dynamic Data site.  ie the query generated by the LINQ to SQL should be something like

    Select * from products P
    inner join CategoryProducts CP on CP.ProductID=P.ProductID
    Inner join Category C on CP.Category=C.CategoryID and C.StoreID=1

    Does that make sense?

    Thanks

    Tatsky 

    Friday, July 18, 2008 8:08 AM
  • User-330204900 posted

    So what you want is to

    var stores = Products.Stores;

    and this to return att the stores for that product?

    Friday, July 18, 2008 8:29 AM
  • User-418270074 posted

    I'm hoping to do something similar myself. I have:

    Merchants -> MerchantCategories <- Categories

    which is hopefully fairly self-explanitory. This is a table of Merchants, and a table of Categories, and a linking many-to-many table as a Merchant can be listed under many categories and a category will have many merchants.

    I have been playing around with the all the excellent futures bits and pieces and Josh Heyse's excellent Dynamic Filter stuff (http://blogs.catalystss.com/blogs/josh_heyse/archive/tags/Dynamic+Data/default.aspx) but I am really struggling to even know where to start with this.

    What I basically want is to filter the Merchants table by the Category they are in. I can create a dropdown filter containing all the possible Categories quite easily, but I need to be able to then only return the Merchants which have a matching Merchant and Category entry in the MerchantCategories linking table.

    The main difficulty I have is I don't have a field I can filter by on my Merchants table eg "where CategoryID = 4". If I were writing the SQL I would do a JOIN or EXISTS or an IN I guess (may be thinking in SQL is the problem?).

    I have tried doing a join operation in the GridDataSource.Selecting event which works, but I would like it to work with the other filters on my page too. I've looked at the ComplexWhereParameters example but I can't see how I could fit a JOIN/EXISTS type command in with the WhereParameters.

    I hope this makes some sense but I'm struggling here a bit!

    Thanks

    Rob

    Thursday, July 24, 2008 11:13 AM
  • User-330204900 posted

    Merchants -> MerchantCategories <- Categories

    Hi Rob I'm not saying this is the answer but it may be a step along the way: 

    [MetadataType(typeof(MerchantMD))]
    public partial class Merchant : INotifyPropertyChanging, INotifyPropertyChanged
    {
    	[ScaffoldColumn(true)]
    	public IEnumerable<CATEGORY> Categories
    	{
    		get
    		{
    			var DC = new ManyDataContext();
    			IEnumerable<CATEGORY> categories = from mc in DC.MerchantCategories
    					      where mc.MerchantId == this.Id
    					      select mc.Category;
    			return categories;
    		}
    	}
    }

    This additional property returns a collection (IEnumerable) of Categories which allow you to see a link to all the categories for this merchant[:S]

    Hope this is somthing along the way to sorting this, because I think it may be a common requirement. [:D]

    Thursday, July 24, 2008 3:18 PM
  • User-418270074 posted

    Hi,

    Thanks for your reply, I've managed to do something similar already by borrowing some of the ideas from http://forums.asp.net/t/1221110.aspx, but your solution is probably a little more elegant than mine at present :)

    One thing I wasn't entirely happy with in my solution is that it is not very flexible, that is if I have multiple many-to-many relationships I need to write a seperate control/property for each one explicitly using the Entity collection, for example MerchantCategories. I'll try and come up with a more generic solution soon.

    There was mention of a many-to-many solution being worked on for the futures project, is this still on going? I know many-to-many is a limitation from linq2sql but this seems to be a question that crops up again and again so it would be useful if a solution/example were forthcoming.

    Many thanks

    Rob 

    Friday, July 25, 2008 5:43 AM
  • User-330204900 posted

    There was mention of a many-to-many solution being worked on for the futures project, is this still on going? I know many-to-many is a limitation from linq2sql but this seems to be a question that crops up again and again so it would be useful if a solution/example were forthcoming.

    I agree but I think that we won't here about anything new untill after the relase of DD in SP1, but may be one of the team will have mroe to say [:P]

    Friday, July 25, 2008 5:53 AM
  • User1024101778 posted

    Rob,

    Thanks for the feedback.  I have added some documentation to the Dynamic Filter as well as a reference implementation and uploaded the code to CodePlex.

    http://forums.asp.net/t/1308475.aspx

    http://www.codeplex.com/DynamicDataFiltering

    Please let me know if you have any other questions.


    Josh
     

    Thursday, August 21, 2008 9:53 AM