Geeks With Blogs

News My blog has moved. Check out my new location at www.davepaquette.com
David Paquette CDD (Caffeine Driven Development)

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:

image

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.

Posted on Sunday, September 23, 2012 4:54 AM | Back to top


Comments on this post: Calculated Columns in Entity Framework Code First Migrations

# re: Calculated Columns in Entity Framework Code First Migrations
Requesting Gravatar...
Thanks a lot. That was really helpful.
Left by Mahmood Dehghan on Jan 18, 2013 8:17 PM

# re: Calculated Columns in Entity Framework Code First Migrations
Requesting Gravatar...
Thanks for share dude
Left by Hassan on Jul 28, 2013 5:24 PM

# re: Calculated Columns in Entity Framework Code First Migrations
Requesting Gravatar...
Thank's for the share. Great solution!
Left by Matteo Tontini on Oct 15, 2013 9:04 AM

# re: Calculated Columns in Entity Framework Code First Migrations
Requesting Gravatar...
Thanks, an alternate solution for calculated properties:
http://www.nuget.org/packages/Microsoft.Linq.Translations
Left by Rakib on Feb 04, 2014 2:35 AM

# re: Calculated Columns in Entity Framework Code First Migrations
Requesting Gravatar...
You are awesome! Works like a charm.
Left by Mohamed Pussah on Mar 17, 2015 10:38 AM

# re: Calculated Columns in Entity Framework Code First Migrations
Requesting Gravatar...
What is the difference between doing this and tagging the property as NotMapped and removing the calculated column?
Left by Phil on Apr 08, 2015 9:14 AM

# re: Calculated Columns in Entity Framework Code First Migrations
Requesting Gravatar...
Problem is when you only want to select the FullName field from the DB omitting the other fields.
Left by Shimmy on Feb 19, 2017 6:55 PM

Your comment:
 (will show your gravatar)


Copyright © David Paquette | Powered by: GeeksWithBlogs.net