Site Meter
 
 

MvcScaffolding: One-to-Many Relationships

This post is part of a series about the MvcScaffolding NuGet package:

  1. Introduction: Scaffold your ASP.NET MVC 3 project with the MvcScaffolding package
  2. Standard usage: Typical use cases and options
  3. This post: One-to-Many Relationships
  4. Scaffolding Actions and Unit Tests
  5. Overriding the T4 templates
  6. Creating custom scaffolders
  7. Scaffolding custom collections of files

Recently I’ve been adding features at a frantic pace, the most significant of which is support for relationships between entities. This means you can quickly create Create-Read-Update-Delete (CRUD) interfaces with drop-down lists, for example to set which “category” a “product” is in, or who is the “manager” or an “employee”.

Installing or Upgrading MvcScaffolding

The features I describe in this post are available in MvcScaffolding 0.9.4 or later. If you already have a previous version installed (find out by typing Get-Package into the NuGet console), you can upgrade it as follows:

Update-Package MvcScaffolding

After upgrading, you must restart Visual Studio, otherwise the old version will still be loaded into the VS appdomain, and bad things will happen. Restart Visual Studio now!

If you’ve never installed MvcScaffolding into your project, install it by entering the following into the NuGet Package Manager Console:

Install-Package MvcScaffolding

Defining simple relations between model classes

I’m going to continue the tutorial in the introductory post, which assumes you’ve already installed created the following model class:

public class Team
{
    public int TeamId { get; set; }
    [Required] public string Name { get; set; }
    public string City { get; set; }
    public DateTime Founded { get; set; }
}

Now I’d like to define another model class, Player. Each Player is associated with a team:

public class Player
{
    public int PlayerId { get; set; }
    public string Name { get; set; }
 
    // Having a property called <entity>Id defines a relationship
    public int TeamId { get; set; }
}

Simply having the TeamId property on Player is enough for both MvcScaffolding and EF Code First to realise there’s a 1:many relationship. Actually MvcScaffolding supports two conventions for defining relations – this is the simple one; I’ll explain the alternative later.

Note that defining TeamId as a non-nullable int, it’s mandatory. Each player must be in a team. If you wanted the relationship to be optional, use a nullable link property instead (i.e., public int? TeamId { get; set; }).

If you scaffold your UI now, by executing the following commands…

Scaffold Controller Team -Force
Scaffold Controller Player

(note the –Force parameter tells scaffolding to overwrite anything you already had for Team)

… then you can go to the URL /Teams and create, read, update, delete teams:

image

… and then you can go to the URL /Players and do the same for players. When you’re creating or editing a player, you’ll get a drop-down list to choose a team:

image

So, how does this work? How can the Edit action know what teams to offer in the dropdown? MvcScaffolding has created a PlayersController in which the Create/Edit actions send a list of PossibleTeams to the view to be rendered in a dropdown:

// This is in PlayersController
public ActionResult Create()
{
    ViewBag.PossibleTeams = context.Teams;
    return View();
}

If you wanted to filter the available teams in some way, you could easily edit this and put a .Where(team => somecondition) clause on the end. Scaffolding isn’t supposed to generate a finished application; it just gives you a useful starting point very quickly!

Showing relationships on Index and Details views

Right now, the Index and Details views don’t show any information about relationships. The list of teams doesn’t tell you about players:

image

… and the list of players doesn’t tell you about teams:

image

The reason no relations are shown is that neither the Team nor Player class has a property to hold instances of the related entities, so there’s nowhere to get the information from. We can improve the situation by telling Player to have a property that holds its Team:

public class Player
{
    public int PlayerId { get; set; }
    public string Name { get; set; }
    public int TeamId { get; set; }
 
    public virtual Team Team { get; set; } // This is new
}

… and by telling Team to have a property that holds its Players:

public class Team
{
    public int TeamId { get; set; }
    [Required] public string Name { get; set; }
    public string City { get; set; }
    public DateTime Founded { get; set; }
 
    public virtual ICollection<player> Players { get; set; } // This is new
}

Notice that both of these new properties are marked virtual. This lets Entity Framework use its Lazy Loading feature so the associated entities will be fetched as needed, and spares you having to write code to fetch them in your controller. I’ll talk more about this in a moment.

Now if you were to scaffold the UI with models like this, by issuing the following commands:

Scaffold Controller Team -Force
Scaffold Controller Player -Force

… then you’d find the list of teams now states how many players are associated with each:

image

…and the list of players now states which team each player belongs to:

image

Similarly, if you go onto the other views (Details, Delete, etc.), you’ll see the related model objects displayed there too:

image

This is probably exactly what you expected to see. But pause for a moment and consider: when we want to display a related model object in a grid or in a details view, how do we know how to represent that other object as a string? There could be many properties on “Team” – how do we know which one to display in the Players grid?

The answer is that MvcScaffolding uses its own conventions to go looking for a property with a likely name: Name, Title, Surname, Subject, Count. If there’s no matching property, we fall back on using ASP.NET MVC’s Html.DisplayTextFor helper. If you don’t agree with the property it chose, you can either:

  • Put a [DisplayColumn] attribute on your model class. For example, by putting [DisplayColumn("SomeProperty")] right before “public class Team…” and then rerunning the scaffolder, you’ll cause it to choose the property SomeProperty, if there is one.
  • Or, modify the T4 template to specify your own conventions (see the forthcoming post for info about customising T4 templates)
  • Or, just edit the generated views and change them! This is the easiest and best choice normally. Remember, scaffolding just gives you a quick starting point. It’s up to you to work on the code to meet all your own application’s requirements.

Eager Loading and SELECT N+1

If you’ve used ORMs before, then when you heard me talk about lazy loading you probably caught the scent of possible blood. If the Player’s “Team” property is marked virtual and hence lazy loaded, then when we display the grid of Player entities, does rendering each row cause us to issue yet another SQL query to fetch the Team for that Player? That, by the way, is called a “SELECT N+1” problem and leads to serious performance issues.

Fortunately, when the scaffolder generates your Index actions, it anticipates this and instructs EF to “eager load” all the related entities up front in a single SQL query. Example:

// This is on PlayersController
public ViewResult Index()
{
    return View(context.Players.Include(player => player.Team).ToList());
}

If your model has multiple relations, they will all be eager loaded for the grid. However, we don’t eager load for the other actions (Edit, Create, etc.) because there’s no scale problem with those – you’re only loading a single entity, and the code is easier to understand if we use normal lazy loading in those cases.

It also works when you’re generating repository classes, too, by the way. If you were to build everything with repositories like this:

Scaffold Controller Team -Repository -Force
Scaffold Controller Player -Repository -Force

… then your controllers would have constructors that receive all of the repositories they need, e.g.:

public PlayersController(ITeamRepository teamRepository, IPlayerRepository playerRepository)
{
    this.teamRepository = teamRepository;
    this.playerRepository = playerRepository;
}

… and the Index actions would still eager-load relations, using a slightly different syntax that the repository interface understands:

// This is on PlayersController
public ViewResult Index()
{
    return View(playerRepository.GetAllPlayers(player => player.Team));
}

You can thank Scott Hanselman for this feature of doing eager loading automatically. He hassled me about it yesterday, so here it is :)

Defining more complex relations between model classes

Depending on what data access technology you’re using, there may be many possible ways of defining how models are related together. If you’re using EF Code First, for example, you could be using its fluent configuration interface, which means writing code with statements like “someEntity.HasOptional(x => x.Players).WithForeignKey(x => x.TeamId).CascadeDelete()” and so on.

MvcScaffolding doesn’t know about all of that. For one thing, fluent configuration exists only at runtime (and scaffolding works at design time), and for another, you might be using any data access technology with its own configuration system. You can still use fluent configuration or anything else; just don’t expect scaffolding to know about it.

Instead, MvcScaffolding supports two simple and broadly applicable conventions that will work fine with most data access technologies, including EF.

(1) You can define a simple parent relation to the model class <SomeEntity> by adding a property called <SomeEntity>Id, as you’ve already seen:

public class Player
{
    public int PlayerId { get; set; }
    public string Name { get; set; }
 
    // This links Player to Team
    public int TeamId { get; set; }
}

Note that if you also have a Team property on Player (as in the code earlier), EF recognizes this convention and populates Team with the entity specified by TeamId. Scaffolding, however, doesn’t need to know or care about this: it just displays Team information in Player views because that property is there.

(2) You can define a complex parent relation by adding a property called <Anything>Id, plus another property called <Anything> that defines the type of the relation. For example, you could make Team objects reference other Team objects, or multiple Person objects:

public class Team
{
    public int TeamId { get; set; }
    // ... other Team properties go here
 
    // Each Team has an optional "next opponent" which is another Team
    public int? NextOpponentId { get; set; }
    [ForeignKey("NextOpponentId")] public virtual Team NextOpponent { get; set; }
 
    // Each Team also has a required Manager and Administrator, both of which are people
    public int ManagerId { get; set; }
    public int AdministratorId { get; set; }
    [ForeignKey("ManagerId")] public virtual Person Manager { get; set; }
    [ForeignKey("AdministratorId")] public virtual Person Administrator { get; set; }
}

The [ForeignKey] attributes are there for EF’s benefit, so it understands what we’re doing. If you’re using another data access technology, you may not need them, or you may need something else. MvcScaffolding doesn’t know or care about those them.

Of course, you don’t have to follow these conventions if you don’t want, but these are the ways that MvcScaffolding knows how to generate typical CRUD UI respecting the associations.

What else is new

There are a lot of other smaller improvements in MvcScaffolding 0.9.4. For example,

  • Based on your feedback, controller names are now pluralized by default (e.g., you get PeopleController rather than PersonController for a model of type Person, unless you explicitly enter PersonController as the controller name when scaffolding)
  • There’s now a single CreateOrEdit partial shared by both the Create and Edit views
  • Support for master pages in ASPX views is better
  • It runs on NuGet 1.1 now
  • You no longer need to compile (or even save!) your code before scaffolding it. Because it now uses the VS CodeModel API, compilation is irrelevant. However, make sure your code is syntactically valid when you scaffold, otherwise you’ll get a big spew of errors.

In fact, there are loads of improvements so I won’t list them all – see the source code log if you want to know everything!

In the next post I’ll show how you can customise the T4 templates and possibly even how to create completely new scaffolders of your own.

Reporting issues and giving feedback

By now, MvcScaffolding has a lot of nontrivial functionality and when you’re first starting to use it, you’ll inevitably run into something that doesn’t do what you expected. Personally, I’m still kind of new to EF, so I usually fail to configure it property. Or I make a change and can’t work out why it’s not appearing, then remember I haven’t re-run the scaffolder yet.

By all means please post brief comments about the design or feature ideas here on this blog, but note that it’s not the ideal place for involved discussions. Also, apologies in advance for slow replies: I’ll be away for the next week, and won’t reply to anything in that time :)

59 Responses to MvcScaffolding: One-to-Many Relationships

  1. Patric

    Did the first part without any problems, but in this part when i first try to visit /Players i get an error.

    “Server Error in ‘/’ Application.
    Invalid object name ‘dbo.Players’.”

    Same error when i try to save a person (/Players/Create works if i type it in manually. I guess its because it doesnt do any db-queries). Thx for this awesome guide btw :P

  2. Steve

    Patric, I guess EF is having trouble creating your database tables for some reason. You might want to drop the database so it can reconstruct it.

  3. Patric

    Thank you, that helped!

  4. Steve, already your off to a great start with your new team – this is good stuff! Thanks and hoping your enjoying it as much as we are :)

  5. Liam B

    For anyone else who got this error when trying to update the package:

    Update-Package : Unable to find type [T4Scaffolding.NuGetServices.Services.ScaffoldingPackagePathResolver]: make sure that the assembly containing this type is loaded.

    A reboot of visual studio solved it for me, then an additional reboot of VS is required (as per the blog instructions).

    Great stuff Steve

  6. Felix

    Liam B or Steve -
    Can you elaborate… What assembly should I load?

  7. Felix

    Oh, sorry, never mind… Once you get this error – just restart VS, and it will be loaded from the packages that were just installed before you got the error.

  8. Hey Steve.
    Great post. Would love to see a similar for many to many relationships.

    Loved your last book. Looking forward to your next. Any ETA on it?

  9. KevDog

    Awesome stuff, nice job!

    But please tell me you aren’t a Man U backer.

  10. Ely

    Hi Steve,

    Does the project support many-to-many relationships yet? I had some poco classes I’m using in another EFCTP5 project that I imported and ran the scaffolding, and it didn’t seem to do anything with the many to many relationships in the classes.

    Also, is it possible to scaffold against classes in a referenced assembly? I trying the -ModelType param passing in the full namespace to the class, but it didn’t like it?

    Thanks, and keep up the great work.

  11. Pingback: The Morning Brew - Chris Alcock » The Morning Brew #782

  12. Neno

    Very nice,Steve.Thank you.

  13. Neno

    Can you say somthing about generate type Image in MvcScaffolding.

  14. Kyle

    Wouldn’t it be better to get the count of players using a Count linq query? Is this considered an unnecessary optimization in most cases?

    Where are the MVC3 videos for TekPub?:) The MVC2 videos rocked!

  15. Franky

    This is a great tools. Very usefull to start quickly a project or try new stuff… Great job! And great post also! I’m waiting the next one… ;)

  16. Peter

    Great Post Steve, I really love the tool and these post are really informative. I would LOVE to see next two post in the series. Have been trying to create a couple of custom templates. Hope to see them soon.

  17. Pingback: Vinicius Quaiato » Blog Archive » MVC Scaffolding e relacionamentos no Model

  18. Richard Forrest

    Hi I’ve been using your scaffold tool. Really nice peace of work.

    Having one issue, when I started I used convention of ID to define foriegn keys in entity framework code first e.g.

    public int TeamId {get;set;}

    Which scaffold the drop downs on create and edit a treat.

    When you move to using the object ref e.g.

    public virtual Team Team {get;set;}

    It now scaffolds the display for index and details but you lose the drop down functionality on create and edit screens.

  19. SteveC

    Wonderful work with the scaffolding :)

    Is it possible to use this approach with a common lookup table?

  20. Jarek

    Great!
    Can’t wait for #5 :)

  21. Hey Sanderson, the scaffolding is great, thanks!

    If you guys could make the DBContext class implement an interface that contains the list of properties that would be awesome.

    This way it’ll be easy to setup a test context where the data is recycled per use? I found where its happening but I am by no means a PowerShell Wizard and while I think I could of made it work, I didn’t want to risk it w/it not being finished yet (or at least a 1.0 product).

  22. Great work, Steve, thanks! Gotta second @Jarek, can’t wait for #4 and #5… (any outlook on those…?)

  23. Hi Steve, my English is very bad, sorry:) but I did have a problem step by step example. I want to edit the records re-adds. For example, the new one is added to the record when I say http://localhost:6770/Teams/Edit/1 Save. Come about because of the problem but could not figure out the entry blank idsi, I’ll be very happy to help wherever you are. Good work.

  24. Hello again, I found the problem. Edit.cshtml into the @Html.HiddenFor (model => model.TeamId) must send the form id. I think I need to add it into T4Template ..

  25. Deke

    Steve, you make me so happy I could cry. Seriously great job. Looking forward to your next post on modifying the T4 Templates.

  26. Great!!! thanks!!!
    Waiting for nex tutorials and releases! :)

  27. I am crying! this is so awesome. Really glad you are part of the asp.net team. I can’t wait for more posts, especially on building my own templates! I’d love for you to blog about your dev process on how you setup to do template mod’s and powershell mods with testing. That is, not automated testing, but just iterative dev process.

    Thanks for such great work, can’t wait for more.

  28. Nice work on the scaffolder Steve. I recently ran into this problem though when generating the scaffolding for our CTP5 models. The same also happens in a plain MVC3/Razor application.

    https://gist.github.com/868207

    This is with version 0.9.7

  29. Feez

    Hi,
    Very nice work.
    how can I change auto generated database name ?

  30. Hey Stephen,

    Do you know of any probelms with using MvcScaffolding in conjunction with the Entity Framework 4.1 Release Candidate?

  31. Gordon

    How does scaffolding handle many to many relationships? Such as A Worker could have many tasks and a task could have many workers.

  32. Jalal

    Thank you!
    please use Courier New for your code dives! make feel code better! :D

  33. Jalal

    Oops! Seems blog layout have problem by FireFox 4.0 :-<
    Now i’m with IE9 ;)

  34. Alex

    Absolutely brilliant! Thank you!

    There is only one major, major flaw in your example … there is no ‘S’ in FIFA, it’s called ‘Football’ ;)

  35. jose pulido

    Excellent work!!! I only have some problems with EF, but the rest was generated. thanks

  36. Nate

    Following the above examples, everything worked fine up until trying to define the complex parent relation. When I added the code to make the Team object reference other Team objects and ran the Scaffolding on it with the repository option, it created duplicate lines “private readonly ITeamRepository teamRepository;” in the Teams Controller which throws the obvious ambiguity errors. Can’t tell if this is a bug or just a negligent error on my part.

  37. Steve

    Hi Nate

    Sorry about that. It was a known issue (tracked here: http://mvcscaffolding.codeplex.com/workitem/5)

    This has already been fixed in the latest source code version, and the fix will be included in the next release. In the meantime, the workaround is just to delete the duplicated line manually after running the scaffolder.

  38. Nate

    Thanks, good to know!! Excellent work by the way, looking forward to the next parts! :D

  39. Pingback: MvcScaffolding: Overriding the T4 Templates « Steve Sanderson’s blog

  40. Pingback: MvcScaffolding: Scaffolding custom collections of files « Steve Sanderson’s blog

  41. Pingback: MVC Scaffolding Provides configurable Code Generation for ASP.NET MVC | BeiJing Parking

  42. Pingback: Mvc Scaffolding’ e Giriş – Video | asp.net, jquery ve diğer web teknolojileri üzerine

  43. John

    The model backing the ‘Mvc3Application1Context’ context has changed since the database was created. Either manually delete/update the database, or call Database.SetInitializer with an IDatabaseInitializer instance. For example, the DropCreateDatabaseIfModelChanges strategy will automatically delete and recreate the database, and optionally seed it with new data.

    what’s happened?

  44. Christian

    hi scott,
    I’m using mvcscaffolding with razor and it has helped me a lot. Now, I switched from dropdonwlist to jquery/son autocomplete. The CRUD work normally in details,delete, and insert mode, but I don’t know how to do with razor in case of _CreateOrEdit.cshtml in edit mode! Under the current code. Can you help me?

    $ (Function () {

    $ (“# ProdutoNome). Autocomplete
    (
    {
    source: function (request, response)
    {
    $. Ajax
    (
    {
    url: “/ Produtoes / FindProdutos”, type: “POST”, dataType: “json”
    Date: {searchText: request.term, maxResults: 10},
    success: function (date)
    {
    response ($. map (data, function (item) {return {label: item.ProdutoNome, value: item.ProdutoNome, id: item.ProdutoId, price: item.PrecoCusto}}))
    }
    })
    }
    select: function (event, ui)
    {
    $ (“# ProdutoId.”) Val (ui.item.id);
    $ (“# Value.”) Val (ui.item.preco);
    }
    }
    );

    }
    );

    Category

    @ Html.Editor (CategoriaNome)
    @ Html.HiddenFor (model => model.CategoriaId)
    @ * Html.ValidationMessageFor (model => model.ProdutoNome) * @

  45. Christian

    Stevee!!! I’m sorry

  46. Christian

    Steve,
    I’m near the solution, now is figuring out how to put dinamically one or another line code under the situation (edit or create)
    @ Html.Editor (CategoriaNome) @*autocomplete*@
    @ Html.EditorFor (model => model.Categoria.CategoriaNome)
    @*editmode*@

  47. Christian

    God, the solution was ridiculous. I just put one more parameter in html helper! And the values ​​of the database and autocomplete came to be recognized.
    @Html.EditorFor(model => model.Categoria.CategoriaNome,”",”CategoriaNome”) tks a lot!

  48. Hi Steven,

    Loving this stuff, keep up the great work.

    In this post you state, “For example, you could make Team objects reference other Team objects, or multiple Person objects” and go on to provide the example.

    Did this break in recent builds? The example as provided does not build the database here. I have filed a bug on the CodePlex site, hopefully there is some workaround.

    Cheers.

  49. Found the answer, but it’s not related your work at all. It’s a limitation in EF and Code First at this time: http://weblogs.asp.net/manavi/archive/2011/05/01/associations-in-ef-4-1-code-first-part-5-one-to-one-foreign-key-associations.aspx

    Cheers,
    James

  50. you

    I dont comment on web blogs often however I had to comment on yours. You write with a lot of interest. It was a great read. Bless you for posting

  51. Pingback: MVC3 / Code First Entity Framework 4.1 Links | Functional Business Intelligence

  52. Dencio Nobita

    Hi steve. Thanks for this great post. I have one question though, is eager loading really better than lazy loading when displaying records on a table?

    I think I misunderstood some references that say lazy loading has performance advantage than eager loading because it loads references only when invoked.

  53. hi steve,
    nice article, I am using EF code first to build a demo website and I am stuck with many to many relationships as then are not scaffolding automatically. I have posted this issue on stackoverflow at http://goo.gl/e0Rbz Can you please have a look and tell me what is the correct way of doing it?

  54. Pingback: MvcScaffolding: Standard Usage - Steve Sanderson’s blog - As seen on YouTube™

  55. Anand More

    Absolutely brilliant! Thank you!

    Great Efforts…..

    Anand

  56. gee

    Best posting I can find on the web about this stuff. Well done!

  57. Jon

    Help. Pluralization is not working fine.

    Thank you!
    ——————————————–
    PM> Get-PluralizedWord link
    link

    PM> Scaffold Controller Link -DbContextType DBASContext -NoChildItems
    Scaffolding LinkController…
    Added controller Controllers\LinkController.cs

  58. A.Chananantachot

    very very nice and useful post. I love it

  59. TN

    Small typo?
    In the 5th box with source code from the top (not couting screenshots and package consoles), last line: should be ICollection (capital “P”) instead of ICollection