Calculated Columns in Entity Framework Code First Migrations
- by David Paquette
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: 1: public string FullName
2: {
3: get { return string.Format("{0} {1}", FirstName, LastName); }
4: }
Of course, this works fine, but this does not give us the ability to write queries using the FullName property. For example, this query:
1: 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:
1: public class UserProfile
2: {
3: public int Id { get; set; }
4:
5: public string FirstName { get; set; }
6: public string LastName { get; set; }
7:
8: [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
9: public string FullName { get; private set; }
10: }
11:
12: public class UserContext : DbContext
13: {
14: public DbSet<UserProfile> Users { get; set; }
15: }
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.
1: public partial class Initial : DbMigration
2: {
3: public override void Up()
4: {
5: CreateTable(
6: "dbo.UserProfiles",
7: c => new
8: {
9: Id = c.Int(nullable: false, identity: true),
10: FirstName = c.String(),
11: LastName = c.String(),
12: //FullName = c.String(),
13: })
14: .PrimaryKey(t => t.Id);
15: Sql("ALTER TABLE dbo.UserProfiles ADD FullName AS FirstName + ' ' + LastName");
16: }
17:
18:
19: public override void Down()
20: {
21: DropTable("dbo.UserProfiles");
22: }
23: }
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:
1: [DatabaseGenerated(DatabaseGeneratedOption.Computed)]
2: public string FullName
3: {
4: get { return FirstName + " " + LastName; }
5: private set
6: {
7: //Just need this here to trick EF
8: }
9: }
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:
1: using(UserContext context = new UserContext())
2: {
3: UserProfile userProfile = new UserProfile {FirstName = "Chanandler", LastName = "Bong"};
4:
5: Console.WriteLine("Before saving: " + userProfile.FullName);
6:
7: context.Users.Add(userProfile);
8: context.SaveChanges();
9:
10: Console.WriteLine("After saving: " + userProfile.FullName);
11:
12: UserProfile chanandler = context.Users.First(u => u.FullName == "Chanandler Bong");
13: Console.WriteLine("After reading: " + chanandler.FullName);
14:
15: chanandler.FirstName = "Chandler";
16: chanandler.LastName = "Bing";
17:
18: Console.WriteLine("After changing: " + chanandler.FullName);
19:
20: }
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.
