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.

Details mode

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!

Edit mode

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.

ManyToMany.zip

Comments (48)

  1. Shail says:

    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 ?

    Shail

  2. Tom Pester says:

    Thanks for making Dynamic Data more complete David !

    I think this is one of the 3 missing features that were missing in V1

    1. Not able to reorder columns (in detail & list view)

    2. No support for many to many relations

    3. 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?

  3. Buckley says:

    Is this template evailable in the latest codeplex release?

  4. davidebb says:

    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.

  5. davidebb says:

    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!

  6. davidebb says:

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

  7. Mike says:

    "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.

  8. davidebb says:

    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.

  9. Great Post! Thank you. Anyone attended the PDC last week?

  10. Scott Hunter says:

    Just got back from PDC 2008 last week, we showed all kinds of cool new stuff. Here is a list of links

  11. ryan martin says:

    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.

  12. Quitnen Miller says:

    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

    Q

  13. davidebb says:

    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.

    David

  14. J Stroud says:

    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?

  15. davidebb says:

    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.

    David

  16. J Stroud says:

    Thanks! Still muddling about with 2008.

  17. Scott Hunter brings a summary of the new features coming in ASP.NET 4.0 and Visual Studio 2010. Learn

  18. Scott Hunter brings a summary of the new features coming in ASP.NET 4.0 and Visual Studio 2010. Learn

  19. Michael says:

    Does this control work for Dynamic Data LinqToSQL apps ? If not would it be possible to ?

  20. davidebb says:

    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."

  21. There are many ways to customize a ASP.NET Dynamic Data site, which can sometimes be a bit overwhelming

  22. philip says:

    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?!

    philip

  23. davidebb says:

    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.

  24. Jan Ehlers says:

    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?

  25. davidebb says:

    Jan, this happens because by default we only Include the entity ref columns in the query, and not the entity sets.  See this line in list.aspx.cs:

       GridDataSource.Include = table.ForeignKeyColumnsNames;

    If you were to add the ManyToMany column in that list, you should get all the data in one query.  e.g. in the Territories case, with the current code the Include only gets set to "Region".  Instead, you’d want to set it to "Region,Employees".

  26. Jan Ehlers says:

    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, Jan

  27. davidebb says:

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

  28. En samlig utav länkar för er som utvecklar. ASP.NET iTunes skin grid Check/Uncheck all Items in an ASP

  29. Please post corrections/new submissions to the Dynamic Data Forum . Put FAQ Submission/Correction in

  30. Hi All, If you want to know more about the new ASP.NET 4.0 and Visual Studio 2010 enhancements you can

  31. Articles and Blog Posts A Many-To-Many Field Template for Dynamic Data (David Ebbo) Dynamic Data Preview

  32. skaar says:

    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 u

  33. davidebb says:

    @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.

  34. skaar says:

    I'm currently using .NET Framework 3.5

    shud i upgrade to 4.0 and also does this work with Visual Studio 2008 ??

  35. skaar says:

    I am using Visual Studio 2008 and .NET framework 3.5, but still the website "ManyToMany.zip" posted above works perfectly fine.

  36. davidebb says:

    @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.

  37. skaar says:

    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.

  38. davidebb says:

    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.

  39. Warren says:

    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?

  40. davidebb says:

    @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.

  41. DotNetWise says:

    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, DotNetWise

  42. davidebb says:

    @DotNetWise: you do not need this with 4.0 as it is built in. See Update comment at the top of the post.

  43. Ugene says:

    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 filter

  44. davidebb says:

    @Ugene: that seems unrelated to the Many to Many template. Please use the forum (forums.asp.net/1145.aspx) for general questions. Thanks!

  45. Christoph says:

    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.

  46. Matt says:

    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.

  47. Scott says:

    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?

  48. davidebb says:

    @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.