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

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
 

Answer 1

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]

Answer 2

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 

Answer 3

User-330204900 posted

So what you want is to

var stores = Products.Stores;

and this to return att the stores for that product?

Answer 4

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

Answer 5

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]

Answer 6

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 

Answer 7

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]

Answer 8

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