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:

… 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:

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:

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

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:

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

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

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.

What’s New in Entity Framework 5.0?

• Email

The performance of the Object Relational Mappers

In the world of Object Relational Mappers, there is one topic that have always been on the table: Performance.

We have automatic configuration, generation and mapping. We have LINQ to Entities doing a great deal of the work for us, including generating the SQL and allowing us to use the built-in CLR functionality in order to produce working queries relatively easily. 

The main problem, of course, is that all of this goodness comes with a price in terms of performance. All object relational mappers produce a well-known overhead and many companies, which are developing high-performance applications, ask themselves: “Is it worth it? Aren't they too slow or too heavy?”

The answer is, "Yes, they are". But that’s why we, the engineers, are here – to make it work faster and keep the benefits measured in production time. If you just install Entity Framework and start it, you’ll certainly need to make a nice refactor sooner or later.

Auto-compiled queries

Getting straight to the point, Entity Framework 5.0 uses the so-called “auto-compiled” queries feature which compiles (read “translates”) the Entity SQL or LINQ to Entities expression tree in a pure SQL (or T-SQL) code. The question is "Are these generated queries actually effective ?". 

In order to do some accurate measurements, we need to get few things straight:

• Don’t look at the very first query sent.
  During that query, the framework “warms up” and configures the views and all other components it needs.
• Next, don’t look at the first invocation of a given query.
  During that time, EF caches parts of the query, so the subsequent calls can be faster. This doesn’t mean the query is compiled. It’s not…yet.
• Take into consideration that .NET 4.5 is an “in-place” upgrade
  This means that once it is installed, you will not have any indications that the new version is actually installed (no GAC folder, no 4.5 assemblies, no registry entry). You also can’t go back to .NET 4.0 so even if you target 4.0 in the project properties, it will still use the 4.5 run-time.

Few performance tests

I've created a  quite slow query (few joins, dozens of filters and ternary operators) and measured its performance using .NET 4.0 with Entity Framework 4.0 (on another machine, of course), and .NET 4.5 with Entity Framework 5.0.

The results were the following (tested with 4 core CPU and 8GB of RAM, Windows 7, and SQL Server 2008):

Without auto-compiled query – 40ms

With auto-complied query – 10ms

With manual use of the CompliedQuery class – 5ms

This means that you are indeed going to get some out-of-the-box performance benefits by simply installing EF5 and .NET 4.5. But if you want faster query generation, use the CompiledQuery class instead, but be prepared to change your current DAL architecture to support this.

Unfortunately, I no longer have the sources of the examples I've used. 

Enum Support

There is support for enumerations in the EF 5.0 release. In order to activate it, create a scalar property of structureInt32 in the .edmx, right-click on it, and select “Convert to Enum”.

This will create an Enum which, upon DDL generation, is translated into a simple column of type Int. This feature provides additional abstraction and integrity constraint in the DAL layer, but from a database point of view, there is no way to find out what a given value in a table really means. You will need to check the project code for that, or simply remember it.

Spatial Data Types

Another interesting feature is the support fo spatial data types in SQL server. For those who are not aware of them, these types are geography and geometry related classes which allow us to work directly with such data inside the SQL Server (like geographic locations or geometrical representation). Below are the related available CLR types. The blue boxes in the following diagram are concrete classes.

There is also a built-in spatial result view in SQL Server, which looks like this:

It is actually quite impressive. For example, if you have a table with points (longitude and latitude) of office locations, for example, you can visualize them directly in SQL Server. You can also utilize a number of functions, such as calculating the distance between two points. With Entity Framework 5.0, we can do it in C# (or any CLS supported language, for that matter). Note that there are still too few resources on the internet related to this feature, so prepare to dig if you plan to use it.

Code First now works with LocalDB

The Entity Framework Code First approach now works with LocalDB. For those of you who haven’t heard aboutLocalDB, think of it as a hybrid between SQL Server Express and SQL Server Compact. It runs in a child-process, rather than in a full-blown Windows service. It’s the default Visual Studio 2012 development server, and it’s meant to be light and easy to configure.

DbContext is now the default generated context

DbContext is not something new. A wrapper around ObjectContext, generated with the help of T4 templates, utilizing the convention-over-configuration principle of the POCO classes. It generates something like this:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

public partial class AdventureWorks2008Entities : DbContext     {         public AdventureWorks2008Entities()             : base("name=AdventureWorks2008Entities")         {         }         protected override void OnModelCreating(DbModelBuilder modelBuilder)         {             throw new UnintentionalCodeFirstException();         }         public DbSet Products { get; set; }         public DbSet ProductModels { get; set; }         public DbSet ProductSubcategories { get; set; }         public DbSet UnitMeasures { get; set; }     }

And the entities look like this:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

public partial class ProductSubcategory     {         public ProductSubcategory()         {             this.Products = new HashSet();         }         public int ProductSubcategoryID { get; set; }         public int ProductCategoryID { get; set; }         public string Name { get; set; }         public System.Guid rowguid { get; set; }         public System.DateTime ModifiedDate { get; set; }         public virtual ICollection Products { get; set; }     }

As you can see, we are talking about pure POCO classes here, but that doesn’t mean that we are actually working with these simple classes at run-time. In fact, dynamic proxies are being generated every time you use the class, enabling lazy initialization and change tracking. 

Multiple diagrams per model

The new Visual Studio 2012 designer supports multiple diagrams per model. We all know how big models are maintained. Huge databases can quickly become unmanageable without the use of the proper tools.

Here is a screenshot of the standard designer screen in Visual Studio 2012. You can see multiple diagrams on the upper right-hand corner. On the context menu you can select “Move to new Diagram” or “Include” related, which will include all entities with relations to the selected one. You can also change the color if you want. 

Table-valued functions

Table-valued functions are also not new in SQL Server, but until now there was no built-in support in Entity Framework. For those of you who are not familiar with these type of functions, they are somewhat of a hybrid between a view and a stored procedure. They support procedural code inside parameters, but can be called from a SELECT statement. They return a single value, but this value is actually a table. This means that it can be chained in a LINQ-to-Entities query.

Here is a simple DDL script for a table-valued function in SQL:

1 2 3 4 5 6 7 8

CREATE FUNCTION [dbo].[GetCategories]()     RETURNS TABLE     RETURN         SELECT [CategoryID],         ,[CategoryName]         ,[Description]         ,[Picture]         FROM [dbo].[Categories]

which can be used in LINQ-to-Entities like this:

1 2	var query = from c in context.GetCategoriesDirect()                 select c;

Batch import of stored procedures

The last feature I want to mention is the ability to import a batch of stored procedures/functions into the .edmx model. Not much to write about here as it’s a matter of click-and-go.

What about Entity Framework 6.0?

Actually, the alpha version of Entity Framework 6.0 is now available in NuGet. You can download it from here if you like.

Here is a list of pre-announced features:

Task-based Async – Allowing EF to take advantage of .NET 4.5 async support with async queries, updates, etc.

Stored Procedures & Functions in Code First – Allow mapping to stored procs and database functions using the Code First APIs.

Custom Code First Conventions – Allowing custom conventions to be written and registered with Code First.