Calculated Columns in Entity Framework Code First Migrations

I had a couple people ask me about calculated properties / columns in Entity Framework this week. The question was, is there a way to specify a property in my C# class that is the result of some calculation involving 2 properties of the same class. For example, in my database, I store a FirstName and a LastName column and I would like a FullName property that is computed from the FirstName and LastName columns. My initial answer was:

public string FullName 
get { return string.Format("{0} {1}", FirstName, LastName); }

Of course, this works fine, but this does not give us the ability to write queries using the FullName property.  For example, this query:

var users = context.Users.Where(u => u.FullName.Contains("anan"));

Would result in the following NotSupportedException:

The specified type member ‘FullName’ is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.

It turns out there is a way to support this type of behavior with Entity Framework Code First Migrations by making use of Computed Columns in SQL Server. While there is no native support for computed columns in Code First Migrations, we can manually configure our migration to use computed columns.

Let’s start by defining our C# classes and DbContext:

public class UserProfile
public int Id { get; set; }

public string FirstName { get; set; }
public string LastName { get; set; }

public string FullName { get; private set; }

public class UserContext : DbContext
public DbSet Users { get; set; }

The DatabaseGenerated attribute is needed on our FullName property.  This is a hint to let Entity Framework Code First know that the database will be computing this property for us.

Next, we need to run 2 commands in the Package Manager Console.  First, run Enable-Migrations to enable Code First Migrations for the UserContext. Next, run Add-Migration Initial to create an initial migration. This will create a migration that creates the UserProfile table with 3 columns: FirstName, LastName, and FullName. This is where we need to make a small change. Instead of allowing Code First Migrations to create the FullName property, we will manually add that column as a computed column.

public partial class Initial : DbMigration
public override void Up()

c => new
Id = c.Int(nullable: false, identity: true),
FirstName = c.String(),
LastName = c.String(),
//FullName = c.String(),
.PrimaryKey(t =>; t.Id);
Sql("ALTER TABLE dbo.UserProfiles ADD FullName AS FirstName + ' ' + LastName");

public override void Down()


Finally, run the Update-Database command. Now we can query for Users using the FullName property and that query will be executed on the database server. However, we encounter another potential problem. Since the FullName property is calculated by the database, it will get out of sync on the object side as soon as we make a change to the FirstName or LastName property.

Luckily, we can have the best of both worlds here by also adding the calculation back to the getter on the FullName property:

public string FullName
get { return FirstName + "; " + LastName; }
private set
//Just need this here to trick EF

Now we can both query for Users using the FullName property and we also won’t need to worry about the FullName property being out of sync with the FirstName and LastName properties.  When we run this code:

using(UserContext context = new UserContext())
UserProfile userProfile = new UserProfile {FirstName = "Chanandler", LastName = "Bong"};

Console.WriteLine("Before saving: " + userProfile.FullName);


Console.WriteLine("After saving: " + userProfile.FullName);

UserProfile chanandler = context.Users.First(u => u.FullName == "Chanandler Bong");
Console.WriteLine("After reading: " + chanandler.FullName);

chanandler.FirstName = "Chandler";
chanandler.LastName = "Bing";

Console.WriteLine("After changing: " + chanandler.FullName);


We get this output:

It took a bit of work, but finally Chandler’s TV Guide can be delivered to the right person.

The obvious downside to this implementation is that the FullName calculation is duplicated in the database and in the UserProfile class.

This sample was written using Visual Studio 2012 and Entity Framework 5. Download the source code here.

Windows 8 Store App Crash Logs

I was recently working on a Windows 8 app, and the application was crashing occasionally.  When resuming the application, the app would crash and close immediately without providing any feedback or information on what went wrong.  The crash was very difficult to reproduce, and I could never get the crash to occur when I was debugging through Visual Studio.  My app was crashing, and I had no idea what was going wrong!  HELP!!!

After doing some digging, I found that when a Windows 8 Store App crashes, an error is logged in Windows Administrative Events.  You can view the details of any app crash by launching the Event Viewer and selecting Administrative Events under Custom Views.  The Source of the error will be listed as AppHost.  AppHost is the process that runs your Windows 8 Store App.  The error details contain all the information you would expect to find, including a stack trace and line numbers.


Windows 8 Tip:  A shortcut for launching the Event Viewer in Windows 8.  Right click on the bottom left corner of your desktop (where you normally click to go to the Start Screen).  A menu will appear with shortcuts to a number of common system tasks such as Event Viewer, Task Manager, Command Prompt, and Device Manager.

Where'd My Data Go? (and/or...How Do I Get Rid of It?)

Want to get a better idea of how cascade deletes work in Entity Framework Code First scenarios? Want to see it in action? Stick with us as we quickly demystify what happens when you tell your data context to nuke a parent entity. This post is authored by Calgary .NET User Group Leader David Paquette with help from Microsoft MVP in Asp.Net James Chambers.
We got to spend a great week back in March at Prairie Dev Con West, chalk full of sessions, presentations, workshops, conversations and, of course, questions. One of the questions that came up during my session: “How does Entity Framework Code First deal with cascading deletes?”. James and I had different thoughts on what the default was, if it was different from SQL server, if it was the same as EF proper and if there was a way to override whatever the default was.

So we built a set of examples and figured out that the answer is simple: it depends. (Download Samples)

Consider the example of a hockey league. You have several different entities in the league including games, teams that play the games and players that make up the teams. Each team also has a mascot. If you delete a team, we need a couple of things to happen:

  1. The team, games and mascot will be deleted, and
  2. The players for that team will remain in the league (and therefore the database) but they should no longer be assigned to a team.
    So, let’s make this start to come together with a look at the default behaviour in SQL when using an EDMX-driven project.

The Reference – Understanding EF’s Behaviour with an EDMX/DB First Approach

First up let’s take a look at the DB first approach. In the database, we defined 4 tables: Teams, Players, Mascots, and Games. We also defined 4 foreign keys as follows:

Players.TeamId (NULL)_ –> Teams.Id

Mascots.Id (NOT NULL) –> Teams.Id (ON DELETE CASCADE)

Games.HomeTeamId (NOT NULL)_ –> Teams.Id

Games.AwayTeamId (NOT NULL)_ –> Teams.Id
Note that by specifying ON DELETE CASCADE for the Mascots –> Teams foreign key, the database will automatically delete the team’s mascot when the team is deleted. While we want the same behaviour for the Games –> Teams foreign keys, it is not possible to accomplish this using ON DELETE CASCADE in SQL Server. Specifying a ON DELETE CASCADE on these foreign keys would cause a circular reference error:
The series of cascading referential actions triggered by a single DELETE or UPDATE must form a tree that contains no circular references. No table can appear more than one time in the list of all cascading referential actions that result from the DELETE or UPDATE – MSDN
When we create an entity data model from the above database, we get the following:


In order to get the Games to be deleted when the Team is deleted, we need to specify End1 OnDelete action of Cascade for the HomeGames and AwayGames associations.


Now, we have an Entity Data Model that accomplishes what we set out to do. One caveat here is that Entity Framework will only properly handle the cascading delete when the the players and games for the team have been loaded into memory. For a more detailed look at Cascade Delete in EF Database First, take a look at this blog post by Alex James.


Building The Same Sample with EF Code First

Next, we’re going to build up the model with the code first approach. EF Code First is defined on the Ado.Net team blog as such:

Code First allows you to define your model using C# or VB.Net classes, optionally additional configuration can be performed using attributes on your classes and properties or by using a Fluent API. Your model can be used to generate a database schema or to map to an existing database.
Entity Framework Code First follows some conventions to determine when to cascade delete on a relationship. More details can be found on MSDN.aspx):
If a foreign key on the dependent entity is not nullable, then Code First sets cascade delete on the relationship. If a foreign key on the dependent entity is nullable, Code First does not set cascade delete on the relationship, and when the principal is deleted the foreign key will be set to null. The multiplicity and cascade delete behavior detected by convention can be overridden by using the fluent API. For more information, see Configuring Relationships with Fluent API (Code First).aspx).
Our DbContext consists of 4 DbSets:

public DbSet Teams { get; set; }
public DbSet Players { get; set; }
public DbSet Mascots { get; set; }
public DbSet Games { get; set; }

When we set the Mascot –> Team relationship to required, Entity Framework will automatically delete the Mascot when the Team is deleted. This can be done either using the [Required] data annotation attribute, or by overriding the OnModelCreating method of your DbContext and using the fluent API.

Data Annotations:

public class Mascot
public int Id { get; set; }
public string Name { get; set; }

public virtual Team Team { get; set; }

Fluent API:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
modelBuilder.Entity().HasRequired(m => m.Team);

The Player –> Team relationship is automatically handled by the Code First conventions. When a Team is deleted, the Team property for all the players on that team will be set to null. No additional configuration is required, however all the Player entities must be loaded into memory for the cascading to work properly.

The Game –> Team relationship causes some grief in our Code First example. If we try setting the HomeTeam and AwayTeam relationships to required, Entity Framework will attempt to set On Cascade Delete for the HomeTeam and AwayTeam foreign keys when creating the database tables. As we saw in the database first example, this causes a circular reference error and throws the following SqlException:

Introducing FOREIGN KEY constraint ‘FK_Games_Teams_AwayTeam_Id’ on table ‘Games’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint.
To solve this problem, we need to disable the default cascade delete behaviour using the fluent API:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
modelBuilder.Entity().HasRequired(m => m.Team);

                    .HasMany(t => t.HomeGames)
                    .WithRequired(g => g.HomeTeam)

                    .HasMany(t => t.AwayGames)
                    .WithRequired(g => g.AwayTeam)


Unfortunately, this means we need to manually manage the cascade delete behaviour. When a Team is deleted, we need to manually delete all the home and away Games for that Team.

foreach (Game awayGame in jets.AwayGames.ToArray())

foreach (Game homeGame in homeGames)


Overriding the Defaults – When and How To

As you have seen, the default behaviour of Entity Framework Code First can be overridden using the fluent API. This can be done by overriding the OnModelCreating method of your DbContext, or by creating separate model override files for each entity. More information is available on MSDN.aspx).

Going Further

These were simple examples but they helped us illustrate a couple of points. First of all, we were able to demonstrate the default behaviour of Entity Framework when dealing with cascading deletes, specifically how entity relationships affect the outcome. Secondly, we showed you how to modify the code and control the behaviour to get the outcome you’re looking for.

Finally, we showed you how easy it is to explore this kind of thing, and we’re hoping that you get a chance to experiment even further. For example, did you know that:

  • Entity Framework Code First also works seamlessly with SQL Azure (MSDN)
  • Database creation defaults can be overridden using a variety of IDatabaseInitializers (Understanding Database Initializers)
  • You can use Code Based migrations to manage database upgrades as your model continues to evolve (MSDN)

Next Steps

There’s no time like the present to start the learning, so here’s what you need to do:

  1. Get up-to-date in Visual Studio 2010 (VS2010 | SP1) or Visual Studio 2012 (VS2012)
  2. Build yourself a project to try these concepts out (or download the sample project)
  3. Get into the community and ask questions! There are a ton of great resources out there and community members willing to help you out (like these two guys!).
    Good luck!

About the Authors

David Paquette works as a lead developer at P2 Energy Solutions in Calgary, Alberta where he builds commercial software products for the energy industry. Outside of work, David enjoys outdoor camping, fishing, and skiing. David is also active in the software community giving presentations both locally and at conferences. David also serves as the President of Calgary .Net User Group.

James Chambers crafts software awesomeness with an incredible team at LogiSense Corp, based in Cambridge, Ontario. A husband, father and humanitarian, he is currently residing in the province of Manitoba where he resists the urge to cheer for the Jets and maintains he allegiance to the Calgary Flames. When he’s not active with the family, outdoors or volunteering, you can find James speaking at conferences and user groups across the country about web development and related technologies.

The Iron Bird Approach

It turns out that designing software is not so different than designing commercial aircraft.  I just finished watching a video that talked about the approach that Bombardier is taking in designing the new C Series aircraft.  I was struck by the similarities to agile approaches to software design.  In the video, Bombardier describes how they are using an Iron Bird to work through a number of design questions in advance of ever having a version of the aircraft that can ever be flown.  The Iron Bird is a life size replica of the plane.  Based on the name, I would assume the plane is built in a very heavy material that could never fly.  Using this replica, Bombardier is able to valid certain assumptions such as the length of each wire in the electric system.  They are also able to confirm that some parts are working properly (like the rudders).  They even go as far as to have a complete replica of the cockpit.  This allows Bombardier to put pilots in the cockpit to run through simulated take-off and landing sequences.

The basic tenant of the approach seems to be

Validate your design early with working prototypes

Get feedback from users early, well in advance of finishing the end product


In software development, we tend to think of ourselves as special.  I often tell people that it is difficult to draw comparisons to building items in the physical world (“Building software is nothing like building a sky scraper”).  After watching this video, I am wondering if designing/building software is actually a lot like designing/building commercial aircraft.


Watch the video here (

Think before you animate

Animations are becoming more and more common in our applications.  With technologies like WPF, Silverlight and jQuery, animations are becoming easier for developers to use (and abuse).  When used properly, animation can augment the user experience.  When used improperly, animation can degrade the user experience.  Sometimes, the differences can be very subtle.

I have recently made use of animations in a few projects and I very quickly realized how easy it is to abuse animation techniques.  Here are a few things I have learned along the way.

1) Don’t animate for the sake of animating

We’ve all seen the PowerPoint slides with annoying slide transitions that animate 20 different ways.  It’s distracting and tacky.  The same holds true for your application.  While animations are fun and becoming easy to implement, resist the urge to use the technology just because you think the technology is amazing.


2) Animations should (and do) have meaning

I recently built a simple Windows Phone 7 (WP7) application, Steeped (download it here).  The application has 2 pages.  The first page lists a number of tea types.  When the user taps on one of the tea types, the application navigates to the second page with information about that tea type and some options for the user to choose from. 

Screenshot1     Screenshot2

One of the last things I did before submitting Steeped to the marketplace was add a page transition between the 2 pages.  I choose the Slide / Fade Out transition.  When the user selects a tea type, the main page slides to the left and fades out.  At the same time, the details page slides in from the right and fades in.  I tested it and thought it looked great so I submitted the app.  A few days later, I asked a friend to try the app.  He selected a tea type, and I was a little surprised by how he used the app.  When he wanted to navigate back to the main page, instead of pressing the back button on the phone, he tried to use a swiping gesture.  Of course, the swiping gesture did nothing because I had not implemented that feature. 

After thinking about it for a while, I realized that the page transition I had chosen implied a particular behaviour.  As a user, if an action I perform causes an item (in this case the page) to move, then my expectation is that I should be able to move it back.  I have since added logic to handle the swipe gesture and I think the app flows much better now.

When using animation, it pays to ask yourself:  What story does this animation tell my users?


3) Watch the replay

Some animations might seem great initially but can get annoying over time.  When you use an animation in your application, make sure you try using it over and over again to make sure it doesn’t get annoying.  When I add an animation, I try watch it at least 25 times in a row.  After watching the animation repeatedly, I can make a more informed decision whether or not I should keep the animation.  Often, I end up shortening the length of the animations.


4) Don’t get in the users way

An animation should never slow the user down.  When implemented properly, an animation can give a perceived bump in performance.  A good example of this is a the page transitions in most of the built in apps on WP7.  Obviously, these page animations don’t make the phone any faster, but they do provide a more responsive user experience.  Why?  Because most of the animations begin as soon as the user has performed some action.  The destination page might not be fully loaded yet, but the system responded immediately to user action, giving the impression that the system is more responsive.  If the user did not see anything happen until after the destination page was fully loaded, the application would feel clumsy and slow.  Also, it is important to make sure the animation does not degrade the performance (or perceived performance) of the application.


Jut a few things to consider when using animations.  As is the case with many technologies, we often learn how to misuse it before we learn how to use it effectively.

Deal Groupie – Learning WP7 and ASP.NET MVC 3

Lately, I have been taking some time to learn some new technologies.  For me, the best way to learn a new technology is to jump head first into a project:

Daily deal websites such as GroupOn, LivingSocial, DealFind, etc. have really taken off over the last 6 months.  With so many sites out there, my inbox was being flooded with emails from all these daily deal websites.  This seemed like it might be a fun project,  so I set out to build a daily deal aggregator.

First, I built a Windows Phone 7 app.  I built this app using Visual Studio 2010. Silverlight for WP7, the silverlight toolkit for WP7 and WCF.  Overall, I was pleased with the developer tools for Windows Phone 7 and I am looking forward to building more apps for WP7


The app conveniently presents deals for your city in a format very similar to the email client for Windows Phone 7.  Users can flag deals, share deals with friends, and buy deals from over 20 daily deal websites.  If you have a Windows Phone, you can download the Deal Groupie App here.


Next, I set out to learn some new web technologies.  I had recently attended a Microsoft Web Camp in Calgary (presented by Jonathan McCracken) and I was impressed with what I saw.  I decided to build a web version of Deal Groupie using ASP.NET MVC3.  I also made use of jQuery, jQuery UI, SQL Server CE 4 and WCF.  Since I had most of the backend already built, the web version did not take me nearly as long to build as the WP7 version did.  I spent more time tweaking the css than anything else.  The website provides daily deals for the selected city in 3 ways: a simple webpage listing all the active deals, a free daily email, and an RSS feed.  The RSS feed was exceptionally easy to implement using Syndication mechanism built in to WCF.


Find deals in your city –

Currently, Deal Groupie is finding more than 2000 deals per day in over 300 cities in the USA, Canada, UK, and Australia.

Overall, building the Deal Groupie app and website has been a really good learning experience.  What’s next?  I’m thinking of building an Android version of the app and adding some filtering options to the website.