A ‘Many To Many’ field template for Dynamic Data
Update: the Many to Many template is now part of the default Dynamic Data project template (when using Entity Framework), so you should not need to download this template separately unless you're on 3.5SP1
Unlike Linq To Sql, Entity Framework directly supports Many to Many relationships. I’ll first describe what this support means.
In the Northwind sample database, you have Employees, Territories and EmployeeTerritories tables. EmployeeTerritories is a ‘junction’ table which has only two columns: an EmployeeID and a TerritoryID, which creates a Many to Many relationship between Employees and Territories.
When using Linq To Sql, all three tables get mapped in your model, and you need to manually deal with the EmployeeTerritories junction table. But when using Entity Framework, the EmployeeTerritories junction table is not part of your model. Instead, your Employee entity class has a ‘Territories’ navigation property, and conversely your Territory entity class has an ‘Employees’ navigation property. What Entity Framework does under the cover to make all this work is pretty amazing!
Unfortunately, in ASP.NET Dynamic Data’s initial release (as part of Framework 3.5 SP1), we didn’t have time to add proper support for such Many to Many relationships, and in fact it behaves in a pretty broken way when it encounters them.
The good news is that it is possible to write a field template that adds great support for this, and that is exactly what this blog post is about. I should note that a couple of our users have written such field templates before (in particular, see this post). In fact, that’s what got me going to write one! :)
One difference is that I set mine out to be completely generic, in the sense that it doesn’t assume any specific database or table. e.g. it works for Northwind’s Employees/Territories (which my sample includes), but works just as well for any other database that uses Many to Many relationships.
In read-only mode, it shows you a list of links to the related entities. e.g. when looking at a Territory, you’ll see links to each of the Employees that work there.
In edit mode, it gets more interesting: it displays a list of checkboxes, one for each Employee in the database. Then, whether the Employee works in this territory is determined by whether the checkbox is checked. Pretty much what you’d expect!
I won’t go into great details about how it works here, but if you are interested, I encourage you to download the sample and look at the code, which I commented pretty well. The key things to look at are:
- The field templates ManyToMany.ascx (used for read-only) and ManyToMany_Edit.ascx (used for Edit).
- The AutoFieldGenerator, which automatically uses those field templates for Many to Many relationships.
Enjoy, and let me know if you have feedback on this.
Comments
Anonymous
October 26, 2008
Hello David, This is really helpful. I was looking for it and you can at right time. Thanks Any Idea, that this will be supported in LINQ to SQL sometime ? ShailAnonymous
October 26, 2008
Thanks for making Dynamic Data more complete David ! I think this is one of the 3 missing features that were missing in V1
- Not able to reorder columns (in detail & list view)
- No support for many to many relations
- No support for creating a lookup inline. Eg. If I create a Product and I have to specify a Category for it that doesn't exist yet I have to abort the Product creation, go and create a Category and start creating a Product again. This is a usability problem that can be adressed by creating the Category on the same page the user creates the Product. So the Category is effectivly created "inline" I know point 1 is going to get introduced in V2 and I hope you will consider point 3 as well! This would cover almost 90% of the requirments I typicaly have and I would recommend it wholeheartedly to my team. I have some feedback on the Many to Many support :
- The list can get pretty long so I would wrap it inside a div and give it a css property of "overflow:scroll" so that the user the vertical height can be controlled;
- Give the developer the option to split the checkboxes in x columns
- Give the developer the option order left to right or top to bottom Also, Is there a book on Dynamic Data in the pipeline that you know of?
Anonymous
October 26, 2008
Is this template evailable in the latest codeplex release?Anonymous
October 27, 2008
Shail, I'm not aware of plans to add Many To Many support to Linq To Sql. Geneally, Linq To Sql is a simpler/lighter technology, which does not have the same power of abstraction as Entity Framework. Of course, theoretically it may be possible to make this work in Dynamic Data by directly dealing with the 'Junction' table in the field template, though that might be non-trivial.Anonymous
October 27, 2008
Tom, thanks for all the feedback. Note that all the styling related changes you bring up (overflow scroll, # of columns, left to right) are things that can be done simply by changing ManyToMany_Edit.ascx (which has the CheckBoxList). Though of course, if the goal is to use different settings for different fields in the same app, it might make sense to create a metadata attribute that would pass that information in from the model to the field template. For your other points that don't directly relate to Many To Many, I would prefer to start a new discussion on the Dynamic Data forum, in order to keep this post focused on just Many To Many :) Thanks!Anonymous
October 27, 2008
Buckley, right now it is only available here, but it should make its way on to Codeplex at some point (and eventually in our next release).Anonymous
October 27, 2008
"I'm not aware of plans to add Many To Many support to Linq To Sql" That was definitely announced way back, just as support for other providers.Anonymous
October 27, 2008
Mike, you may very well be right, as I don't work in the Linq To Sql team (I'm in the ASP.NET/Dynamic Data team), and I'm aware of everything they are doing. It might be worth asking on the Linq To Sql forum (http://forums.microsoft.com/msdn/ShowForum.aspx?siteid=1&ForumID=123) to reach the 'experts' in that area. From a Dynamic Data point of view, if Linq To Sql adds this support we can support it accordingly in the field template.Anonymous
November 01, 2008
Great Post! Thank you. Anyone attended the PDC last week?Anonymous
November 03, 2008
Just got back from PDC 2008 last week, we showed all kinds of cool new stuff. Here is a list of linksAnonymous
November 10, 2008
I spent 10 hours, literally trying to get this to work with my SQL Server 2008 database. I downloaded this Many To Many example and it worked perfectly fine, except I couldn’t get mine to work the same. My application wouldn't update it would just stick so I combed through all of the code in both apps to find the difference. Well I'm embarrassed to admit I did not make the linking table (EmployeesTerritories) two ID fields (EmployeeID, TerritoryID) as primary keys and foreign keys respectfully. I tried to do this relationship connection through the entity model instead. So if anyone is having a similar issue, try this out! Build the relationships in your database first.Anonymous
December 03, 2008
Thank you for the great post on how to deal with Many-To-Many relationships. Unfortunately it doesn't work in its current form on the project I'm working on because the joining table contains some data about the relationship. I am looking into explicititly defining the relationship as opposed to implicity having it defined. I do have one question regarding the AutoFieldGenerator. Specifically the following lines. string uiHint = null; if (column.Provider.Association != null && column.Provider.Association.Direction == AssociationDirection.ManyToMany) { uiHint = "ManyToMany"; } fields.Add(new DynamicField() { DataField = column.Name, UIHint=uiHint }); I noticed that the UIHint is set to either null or ManyToMany yet it still seems to pick up UIHints defined in a MetadataType Class. [UIHint("Image")] public object Logo{ get; set; } Is that because the UIHint from the MetadataType is applied after the AutoFieldGenerator. If that is the case, any UIHint's for the FK field will remove the ManyToMany that is applied in the AutoFieldGenerator. I thought I should have used something like the following but found it made no difference: UIHint = uiHint != null ? uiHint : column.UIHint Cheers QAnonymous
December 03, 2008
Q, This works because if DynamicField has a null UIHint, then it always defaults to the one from the MetaColumn. Basically, what you set on the DynamicField overrides the MetaColumn uihint. DavidAnonymous
December 17, 2008
I'm getting a 'Repeater1 not found' type error in ManyToMany.aspx.cs when I include this in my own project...I've made the necessary mods to ensure it's in the proper namespace and everything. Any ideas?Anonymous
December 17, 2008
J Stroud: the field templates were written for a web site. To use them in a Web Application, right click the aspx files in VS and choose 'convert to web application'. You should then be ok. DavidAnonymous
December 18, 2008
Thanks! Still muddling about with 2008.Anonymous
December 19, 2008
Scott Hunter brings a summary of the new features coming in ASP.NET 4.0 and Visual Studio 2010. LearnAnonymous
December 19, 2008
Scott Hunter brings a summary of the new features coming in ASP.NET 4.0 and Visual Studio 2010. LearnAnonymous
December 20, 2008
Does this control work for Dynamic Data LinqToSQL apps ? If not would it be possible to ?Anonymous
December 22, 2008
Michael, copying from a recent forum thread: "I think in theory it can be made to work with Linq to SQL, but it would certainly be harder. The reason is that with L2S, the framework doesn't abstract out the 'junction table', so the field templates would need to do all the book keeping themselves to make it all work. But in theory, I don't see why it couldn't be made to work with the right field template."Anonymous
December 22, 2008
There are many ways to customize a ASP.NET Dynamic Data site, which can sometimes be a bit overwhelmingAnonymous
January 06, 2009
I tried your code, works very nice, thanks. Though, ManyToMany_Edit.ascx isn't loaded/executed when in 'Insert' mode. When I set a breakpoint in 'Page_Load' of ManyToMany_Edit, it doesn't even get there. Any ideas?! philipAnonymous
January 06, 2009
Philip, what exactly are you trying? In the sample I shared, if I:
- Go to Territories/ListDetails.aspx
- Click New on the DetailView The insert UI correctky uses the Many To Many field template to pick employee.
Anonymous
January 07, 2009
I have implemented this field on a web-application, and it works. It does however result in a lot of roundtrips to the database. It seems that for every foreignkey column in every row on the main table a call to the database is made to extract the related records. This means that a page with 10 records shown and 3 many to many columns makes 30 extra calls to the database. The call is made every time a RelatedEnd entityCollection is loaded. On the listDetails page you do load foreignkeycolumns explicitly, so why is it necessary to do this row by row loading of related entities?Anonymous
January 08, 2009
The comment has been removedAnonymous
January 08, 2009
Hi David Thank you for the quick response. It did the trick when I put this check on the related end: if (!entityCollection.IsLoaded) { entityCollection.Load(); } It does however require that I use a custom page in order to hardcode the table names that needs to be included in the GridDataSource query. I already have this, so that is ok. For later usage however I would like to know if you know a general way to get the names of the navigation properties on the main entity to be able to include the entitities behind them. If there is such I way, I suggest this as the default behavior for the ListDetails page. Regards, JanAnonymous
January 09, 2009
Jan, you should be able to do this by looking at table.Columns.OfType<MetaChildrenColumn>(), and add them to the include. Obviously, this needs to be done carefully, as it will cause one big query to happen. It's good if the data ends up being used, and bad if not (e.g. if the column ends up not being displayed).Anonymous
February 04, 2009
En samlig utav länkar för er som utvecklar. ASP.NET iTunes skin grid Check/Uncheck all Items in an ASPAnonymous
February 18, 2009
Please post corrections/new submissions to the Dynamic Data Forum . Put FAQ Submission/Correction inAnonymous
March 09, 2009
Hi All, If you want to know more about the new ASP.NET 4.0 and Visual Studio 2010 enhancements you canAnonymous
June 06, 2009
Articles and Blog Posts A Many-To-Many Field Template for Dynamic Data (David Ebbo) Dynamic Data PreviewAnonymous
January 25, 2011
Hello, pls bare with me, i'm very new to asp.net. I would like to know how to use this in my project. what files do i need to copy and what should i do with it. thank uAnonymous
January 26, 2011
@skaar: note that the Many to Many template is now part of Dynamic Data on 4.0, so you should have to use the template from this post unless you're on 3.5SP1.Anonymous
January 27, 2011
I'm currently using .NET Framework 3.5 shud i upgrade to 4.0 and also does this work with Visual Studio 2008 ??Anonymous
January 27, 2011
I am using Visual Studio 2008 and .NET framework 3.5, but still the website "ManyToMany.zip" posted above works perfectly fine.Anonymous
January 27, 2011
@skaar: yes, the included web site was written for 2008, so you don't need to upgrade. I was just pointing out that with 2010, the Many to Many template is part of the core product, so you would not need to get it separately.Anonymous
January 28, 2011
ok so i copy the FieldTemplates and Content folders right? what else do i need from the sample? The Model.Designer.cs says that it is auto-generated code. Do i need this in my project as well? if so how do i generate it. as i said i'm quite new to asp.net. I've been doing only php and now i'm learning asp.net, therefore sorry for asking any simple question.Anonymous
January 28, 2011
What you need to integrate into your app are the 4 ManyToMany template files under DynamicDataFieldTemplates, as well as the AutoFieldGenerator logic in App_Code.Anonymous
February 19, 2011
Thanks. Works well. Except when i try to do an insert of a new record, the junction table records have a 0 for the id of the entity im saving from, instead of the ID. this was causing foreign key errors, i removed those to test now it inserts and i see that. If i go in, and edit the record, and select the checkboxes again, then it works. Any ideas?Anonymous
February 20, 2011
@warren: are you using Framework 4.0? If so, note that the feature now comes with it so you should not have to use what's attached here. Not guaranteeing that it's work with 4.0, but at least you'll have the latest.Anonymous
March 11, 2011
Is there a way of upgrading this example to VS2010 / ASP.NET 4.0 / LINQ TO SQL? It seems that the current tweak is not longer working since a DetailsView is not used, but a FormsView instead. Any help will be appreciated! Thanks, Laurentiu, DotNetWiseAnonymous
March 12, 2011
@DotNetWise: you do not need this with 4.0 as it is built in. See Update comment at the top of the post.Anonymous
May 30, 2011
how you can implement on Dynamic Data that situation There are tables 1)City CityID NameCity 2)Region RegionID cityID NameRegion 3)Streets StreetsID RegionID NameStreets 4)Building BuildingID StreetsID NameBuilding on page Building / List.aspx before filtered Building I must first choose the City Region and then afterwards Streets turns out that the filter filters the filterAnonymous
May 31, 2011
@Ugene: that seems unrelated to the Many to Many template. Please use the forum (forums.asp.net/1145.aspx) for general questions. Thanks!Anonymous
June 30, 2011
Hello, Is it possible to change the template that way, that there is no related data directly shown, but a link to another table showing the related records. In some cases there are so much related rows that you simply cannot show them together with all the other data because it get's confusing then.Anonymous
September 21, 2011
Is it possible to get this working with DomainDataSource/Domain Services? I'm assuming there's some complication with it, since it's not included in the Dynamic Data Domain Service project. TIA.Anonymous
October 11, 2011
I'm writing my first Dynamic Data project with Entity Framework 4. And my M-to-M tables are not Pure Join Tables. (i.e. They contain additional columns to model the relationship.) Because fo this, EF models the join table and as far as I can tell, this M-to-M templaty doesn't apply any more. Is there a similar way to model many-to-many relationships when the join table is not a PJT?Anonymous
October 11, 2011
@Scott: I think this kind of UI just would not work well if the Join table has additional data, as there wouldn't be a good place to display/edit that extra data.