# Wednesday, December 19, 2012

At BUILD, during this session by Entity Framework Program Manager Rowan Miller, Entity Framework 6 beta was announced and made publicly available.  You can get the Entity Framework 6 beta bits via NuGet.  Currently they are only available via the Package Manager Console, more info can be found on the EF NuGet page

Entity Framework 6 is the first new version of EF since the announcement earlier this year that the product would be Open Sourced.  This means that not only is the source available but the builds of EF6 beta are available as soon as the code is checked in.  Entity Framework is hosted on CodePlex.

The EF 6 roadmap defines in detail what is currently in the works and what is still on the board as far as features and improvements for EF 6.  In this post I want to focus on one new addition to Entity Framework that I find very compelling.

One of the great features introduced in .NET 4.5 was the task-based asynchronous functionality using the async and await keywords.  Now with EF 6 comes support for asynchronous querying and saving using the same async and await coding convention.  Let’s first look at asynchronous querying.

The following asynchronous extension methods have been implemented for querying your data context and as you’d expect they are the asynchronous versions of their synchronous namesakes:

  • AllAsync
  • AnyAsync
  • AverageAsync
  • ContainsAsync
  • CountAsync
  • FindAsync
  • FirstAsync
  • FirstOrDefaultAsync
  • LoadAsync
  • LongCountAsync
  • MaxAsync
  • MinAsync
  • SingleAsync
  • SingleOrDefaultAsync
  • SumAsync
  • ToArrayAsync
  • ToDictionaryAsync
  • ToListAsync

In the code snippet below you can see an example of using the ForEachAsync method to asynchronously loop through all the manufacturers in my data context and print them to the console.

private static async Task PrintAllManufacturers() {
    using (DataContext context = new DataContext()) {
        await context.Manufacturers.ForEachAsync(m => Console.WriteLine("{0} : {1}", m.Name, m.Country));

Another piece of Entity Framework functionality that the asynchronous goodness has been added too is saving. Below is an example of adding a manufacturer and asynchronously saving the changes.

private static async Task AddManufacturer(string name, string country) {
    using (DataContext context = new DataContext()) {
        context.Manufacturers.Add(new Manufacturer {Name = name, Country = country});
        await context.SaveChangesAsync();

Using the asynchronous pattern is a great way to provide a more responsive user experience and now loading data asynchronously with Entity Framework has become much easier.

posted on Wednesday, December 19, 2012 11:08:48 AM (Central Standard Time, UTC-06:00)  #    Comments [0]
# Monday, January 23, 2012

Support for spatial data was introduced in SQL Server 2008.  What this allows you to do is capture data related to 2D geometric or geographical images in SQL Server.  It actually allows you to do much more than just storing a bunch of numbers representing this data.  In the initial releases of Entity Framework the spatial data type was not available for use, but with the Entity Framework June 2011 CTP release this functionality is now available.

I am once again working with the code from my Entity Framework Code First Demo post.  This simple application is dream car tracker that tracks manufacturers and models of cars that someday I would love to own.  As part of the manufacturer data I currently store the Country but would like to be able to map out the corporate headquarters for the day when I get to take a trip and take delivery.  I am able to retrieve the latitude and longitude from Google Maps by knowing the address.

The Ferrari factory located at: Via Abetone Inferiore n. 4, I-41053 Maranello Italy has a latitude and longitude of 44.53244, 10.86412.  At this point if you are new to the spatial data type, like I was, you are probably thinking “that is just a decimal and you could always store decimals in SQL Server” and you are correct.  I could certainly just add two decimal properties to my model that represent latitude and longitude and go on my merry way but using spatial data functionality introduced in the June CTP I get so much more.

Because I am dealing with geographical data and not geometric data I am going to use the DbGeography type, that can be found in System.Data.Spatial, and add a Location property to my Manufacturer model.

  1. public class Manufacturer {
  3.     public long ManufacturerId { get; set; }
  5.     [Required]
  6.     [StringLength(40)]
  7.     public string Name { get; set; }
  9.     [Required]
  10.     [StringLength(40)]
  11.     public string Country { get; set; }
  13.     [Required]
  14.     public DbGeography Location { get; set; }
  16.     #region << Navigation Properties >>
  18.     public virtual IList<Model> Models { get; set; }
  20.     #endregion
  22. }

Once I have Entity Framework Code First regenerate my database I can take a look and see that the .NET Framework type DbGeography translates to the SQL Server type Geography.


Now that I have my property I can populate the Location data by converting my latitude and longitude values to a DbGeography using the static parse method.  I can add this to my SeedManufacturer method.

  1. private void SeedManufacturer(DataContext context) {
  2.     _ferrari = new Manufacturer {Name = "Ferrari", Country = "Italy"};
  3.     _lamborghini = new Manufacturer {Name = "Lamborghini", Country = "Italy"};
  4.     _astonMartin = new Manufacturer {Name = "Aston Martin", Country = "United Kingdom"};
  5.     _porsche = new Manufacturer {Name = "Porsche", Country = "Germany"};
  6.     _ferrari.Location = DbGeography.Parse("POINT(44.53244    10.86412)");
  7.     _lamborghini.Location = DbGeography.Parse("POINT (44.65934    11.12693)");
  8.     _astonMartin.Location = DbGeography.Parse("POINT (52.18708    -1.48351)");
  9.     _porsche.Location = DbGeography.Parse("POINT (48.83498    9.15231)");
  10.     new List<Manufacturer> {_ferrari, _lamborghini, _astonMartin, _porsche}.ForEach(x => context.Manufacturers.Add(x));
  11. }

Now if you look at the data in SQL Server you an see that the data is now represented as a nasty looking hex.


I know you are still wondering what the point is, pun intended!  Well, now I can get to the cool part.  I can use the methods that come along with DbGeography to do things such as getting distance between two manufacturers as illustrated below in line 3.

  1. Manufacturer ferrari = dc.Manufacturers.First(x => x.Name == "Ferrari");
  2. Manufacturer lamborghini = dc.Manufacturers.First(x => x.Name == "Lamborghini");
  3. double distanceInKilometers = ferrari.Location.Distance(lamborghini.Location);
  4. double distanceInMiles = distanceInKilometers/1609.344;
  5. string outputText = string.Format("{0} is {1} miles from {2}", ferrari.Name, distanceInMiles, lamborghini.Name);

You can also use the methods in LINQ.  The query below finds all the manufacturers that are within a 100 mile radius of my house.

  1. DbGeography myHouse = DbGeography.Parse("POINT (4.65934    1.12693)");
  2. IList<Manufacturer> closeToMe = manufacturers.Where(x => (x.Location.Distance(myHouse)*.00062) <= 100).ToList();
posted on Monday, January 23, 2012 8:30:40 AM (Central Standard Time, UTC-06:00)  #    Comments [3]
# Monday, January 09, 2012

The most requested feature for Entity Framework has been Enum support for a very long time.  At TechEd this year it was announced that Enums would be supported in the next version of the Entity Framework and in June the Entity Framework June CTP was released with Enum support as well some other enhancements.

In the past I dealt with Entity Framework not supporting Enum by having an int property on my model that represented the Enum’s id.  My model also had a property of the Enum type that was marked as ignored so that EF did not try create a database column for the property.  The setter of the property would set the Enum id field and the getter would convert the Enum id field to the Enum type and return it.  If you are confused by that statement, like me, see the code below.

  1. public class Model {
  3.         public long ModelId { get; set; }
  5.         [Required]
  6.         [StringLength(50)]
  7.         public string Name { get; set; }
  9.         [Required]
  10.         public int Year { get; set; }
  12.         [Required]
  13.         public decimal BasePrice { get; set; }
  15.         public int EngineLocation { get; set; }
  17.         public EngineLocationType EngineLocationType {
  18.             get { return (EngineLocationType) EngineLocation; }
  19.             set { EngineLocation = (int) value; }
  20.         }
  22.         #region << Navigation Properties >>
  24.         public long ManufacturerId { get; set; }
  25.         public virtual Manufacturer Manufacturer { get; set; }
  27.         public virtual IList<Engine> AvailableEngines { get; set; }
  29.         #endregion
  31.     }
  1. public class DataContext : DbContext {
  3.         public DataContext() : base("name=EFCodeFirst") {}
  5.         public IDbSet<Manufacturer> Manufacturers { get; set; }
  6.         public IDbSet<Model> Models { get; set; }
  7.         public IDbSet<Engine> Engines { get; set; }
  9.         protected override void OnModelCreating(DbModelBuilder modelBuilder) {
  10.             modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
  11.             modelBuilder.Entity<Model>().Ignore(x => x.EngineLocationType);
  12.             modelBuilder.Entity<Model>().HasMany(x => x.AvailableEngines).WithMany(x => x.AvailableOn).Map(x => x.MapLeftKey("ModelId").MapRightKey("EngineId").ToTable("ModelEngine"));
  13.             base.OnModelCreating(modelBuilder);
  14.         }
  15.     }

This gets the job done and really isn’t that messy, but with the new Enum support in the June CTP I can make things cleaner.  To make use of the new Enum support all I have to do is declare a property of EngineLocationType in my model and EF will make all the connections for me.

  1. public class Model {
  3.         public long ModelId { get; set; }
  5.         [Required]
  6.         [StringLength(50)]
  7.         public string Name { get; set; }
  9.         [Required]
  10.         public int Year { get; set; }
  12.         [Required]
  13.         public decimal BasePrice { get; set; }
  15.         public EngineLocationType EngineLocation { get; set; }
  17.         #region << Relationships >>
  19.         public long ManufacturerId { get; set; }
  20.         public virtual Manufacturer Manufacturer { get; set; }
  22.         public virtual IList<Engine> AvailableEngines { get; set; }
  24.         #endregion
  27.     }

So that is the new EF Enum support.  The only issue I have with the implementation is that ideally I would like to be able to have an EngineLocationType table in my database that Entity Framework would create and populate based on my Enum definition and assign a foreign key relationship to EngineLocation on my Model entity.  That would make the entire process complete and architecturally sound IMHO.

If you are looking for a code download this is part of the Entity Framework Code First Demo that I did in a previous blog post and the code is available on GitHub.

posted on Monday, January 09, 2012 8:18:40 AM (Central Standard Time, UTC-06:00)  #    Comments [3]
# Monday, December 12, 2011

Integration Testing is a necessary evil that often gets overlooked or is implemented, ran once, and then not maintained.  The reasoning behind this is that by definition Integration Testing relies on external resources that are often outside of our control and some times completely hands off.  Also these external resources are in constant use by a large number of people making whatever resource the external systems are serving very volatile.  In this post I am going to focus on the most common external resource; the Database.

Integration Testing when you have a Database as an external resource can be a nightmare.  First off you need to have access to the database.  Once that is accomplished you need to have elevated access to the database in order to run scripts.  Scripts you say?  Yes scripts!  Scripts that can put the database in an expected state every time you run the Integration Tests.  Oh, yeah then you have to make sure that the script is run.

Luckily if you are using Entity Framework Code First you more than likely already have elevated access to the Database and are probably running a local instance of SQL Server on your dev box that you are in control of.  But in the typical scenario you would still have to write the SQL Scripts, which if you are like me you don’t enjoy, and then you have to make sure they are run and that they don’t impact the data that you use to manually UI test your application…we all have that one piece of data that rely on…don’t deny it!

So how does EF Code First help me with this overwhelming problem?

All example code is from my EFCodeFirstDemo.

Seeded Data:

In Entity Framework Code First you are given the ability to hook into the database generation process and seed the database with relevant data for testing.  The best part about this is that you do it all using your POCO domain entities and LINQ to Entities…NO SQL SCRIPTS!  WOOT!

The first step in the process is to create a custom data context initializer that implements an implementation IDatabaseInitializer.

  1. public class TestDataContextInitializer : DropCreateDatabaseIfModelChanges<DataContext> {}

The next step is to override the Seed method that hooks into the generation of the database.

  1. protected override void Seed(DataContext context) {
  2.     base.Seed(context);
  3. }

Finally you can use this Seed method to build up your test data and populate the database either every time your model changes (DropCreateDatabaseIfModelChanges) or if you choose every time you spin up a new session that accesses the database (DropCreateDatabaseAlways), I don’t recommend the latter.

In the code below I am building up object graphs using my standard POCO entities and LINQ to Entities.

  1. public class TestDataContextInitializer : DropCreateDatabaseIfModelChanges<DataContext> {
  3.     private Manufacturer _astonMartin;
  4.     private Model _astonMartinDB9;
  5.     private Engine _astonMartinV12;
  6.     private Model _astonMartinV12Vantage;
  7.     private Manufacturer _ferrari;
  8.     private Model _ferrari458Italia;
  9.     private Model _ferrariScaglietti;
  10.     private Engine _ferrariV12;
  11.     private Engine _ferrariV8;
  12.     private Manufacturer _lamborghini;
  13.     private Model _lamborghiniAventador;
  14.     private Model _lamborghiniGallardo;
  15.     private Engine _lamborghiniV10;
  16.     private Engine _lamborghiniV12;
  17.     private Manufacturer _porsche;
  18.     private Model _porsche911GT2R2;
  19.     private Engine _porscheStraight6;
  21.     protected override void Seed(DataContext context) {
  22.         SeedManufacturer(context);
  23.         SeedEngine(context);
  24.         SeedModel(context);
  25.         base.Seed(context);
  26.     }
  28.     private void SeedManufacturer(DataContext context) {
  29.         _ferrari = new Manufacturer {Name = "Ferrari", Country = "Italy"};
  30.         _lamborghini = new Manufacturer {Name = "Lamborghini", Country = "Italy"};
  31.         _astonMartin = new Manufacturer {Name = "Aston Martin", Country = "United Kingdom"};
  32.         _porsche = new Manufacturer {Name = "Porsche", Country = "Germany"};
  33.         _ferrari.Location = DbGeography.Parse("POINT(44.53244    10.86412)");
  34.         _lamborghini.Location = DbGeography.Parse("POINT (44.65934    11.12693)");
  35.         _astonMartin.Location = DbGeography.Parse("POINT (52.18708    -1.48351)");
  36.         _porsche.Location = DbGeography.Parse("POINT (48.83498    9.15231)");
  37.         new List<Manufacturer> {_ferrari, _lamborghini, _astonMartin, _porsche}.ForEach(x => context.Manufacturers.Add(x));
  38.     }
  40.     private void SeedEngine(DataContext context) {
  41.         _astonMartinV12 = new Engine {Name = "6.0L V12", BreakHorsepower = 510, NumberOfCylinders = 12, Liters = 6.0m};
  42.         _ferrariV8 = new Engine {Name = "5.4L V8", BreakHorsepower = 570, NumberOfCylinders = 8, Liters = 5.4m};
  43.         _ferrariV12 = new Engine {Name = "5.7L V12", BreakHorsepower = 532, NumberOfCylinders = 12, Liters = 5.7m};
  44.         _lamborghiniV10 = new Engine {Name = "5.2L V10", BreakHorsepower = 562, NumberOfCylinders = 10, Liters = 5.2m};
  45.         _lamborghiniV12 = new Engine {Name = "6.5L V12", BreakHorsepower = 700, NumberOfCylinders = 12, Liters = 6.5m};
  46.         _lamborghiniV12 = new Engine {Name = "6.5L V12", BreakHorsepower = 700, NumberOfCylinders = 12, Liters = 6.5m};
  47.         _porscheStraight6 = new Engine {Name = "3.6L Straight 6", BreakHorsepower = 620, NumberOfCylinders = 6, Liters = 3.6m};
  48.         new List<Engine> {_astonMartinV12, _ferrariV8, _ferrariV12, _lamborghiniV10, _lamborghiniV12, _porscheStraight6}.ForEach(x => context.Engines.Add(x));
  49.     }
  51.     private void SeedModel(DataContext context) {
  52.         _ferrari458Italia = new Model {Name = "458 Italia", BasePrice = 220000, Year = 2012, Manufacturer = _ferrari, AvailableEngines = new List<Engine> {_ferrariV8, _ferrariV12}, EngineLocation = EngineLocationType.Mid};
  53.         _ferrariScaglietti = new Model {Name = "Scaglietti", BasePrice = 313000, Year = 2012, Manufacturer = _ferrari, AvailableEngines = new List<Engine> {_ferrariV12}, EngineLocation = EngineLocationType.Front};
  54.         _lamborghiniGallardo = new Model {Name = "Gallardo LP 570-4 Superleggera", BasePrice = 237600, Year = 2012, Manufacturer = _lamborghini, AvailableEngines = new List<Engine> {_lamborghiniV10}, EngineLocation = EngineLocationType.Mid};
  55.         _lamborghiniAventador = new Model {Name = "Aventador LP 700-4", BasePrice = 387000, Year = 2012, Manufacturer = _lamborghini, AvailableEngines = new List<Engine> {_lamborghiniV12}, EngineLocation = EngineLocationType.Mid};
  56.         _astonMartinDB9 = new Model {Name = "DB9", BasePrice = 185000, Year = 2012, Manufacturer = _astonMartin, AvailableEngines = new List<Engine> {_astonMartinV12}, EngineLocation = EngineLocationType.Front};
  57.         _astonMartinV12Vantage = new Model {Name = "V12 Vantage", BasePrice = 180000, Year = 2012, Manufacturer = _astonMartin, AvailableEngines = new List<Engine> {_astonMartinV12}, EngineLocation = EngineLocationType.Mid};
  58.         _porsche911GT2R2 = new Model {Name = "911 GT2 R2", BasePrice = 245000, Year = 2012, Manufacturer = _porsche, AvailableEngines = new List<Engine> {_porscheStraight6}, EngineLocation = EngineLocationType.Rear};
  59.         new List<Model> {_ferrari458Italia, _ferrariScaglietti, _lamborghiniAventador, _lamborghiniGallardo, _astonMartinDB9, _astonMartinV12Vantage, _porsche911GT2R2}.ForEach(x => context.Models.Add(x));
  60.     }
  61. }

The result of this process is that every time my database is dropped and recreated it is also seeded with test data. 

I know what your saying…”But I don’t use the same data for manually walking through my UI that I would use for Integration Testing!”  Luckily I can easily specify a different Connection String and Database Initializer to use when running my Integration Tests.

The first thing you have to do is modify the connection string in the AppConfig of your Integration Test project to use a different initial catalog.  As you can see all I did was suffix mine with “_TEST”…clever I know!  This is going to setup a new database for you that will only be used for testing.

  1. <connectionStrings>
  2.     <add name="EFCodeFirst" connectionString="Data Source=(local);Initial Catalog=EFCodeFirst_TEST;Integrated Security=True;MultipleActiveResultSets=True;" providerName="System.Data.SqlClient"/>
  3.   connectionStrings>

Next you need to tell EF what Database Initializer to use.  This can be accomplished two ways.  The first way is to explicitly set it in code.  In the example below I am doing this in the ClassInitialize of my Integration Test class.

  1. [ClassInitialize]
  2. public static void ClassInitialize(TestContext context) {
  3.     Database.SetInitializer(new TestDataContextInitializer());
  4. }

The second way is to set it configure it in the App/Web Config.

  1. <appSettings>
  2.     <add key="DatabaseInitializerForType EFCodeFirst.DataContext.DataContext, EFCodeFirst" value="EFCodeFirst.DataContext.TestDataContextInitializer, EFCodeFirst" />
  3.   appSettings>
That’s it!  Now you have a separate Database instance just for Integration Testing that will always contain the clean fresh data that is required to exercise your Integration Test suite.  Better yet, every developer that runs your Integration Tests will also have this data and everything will just work.
posted on Monday, December 12, 2011 9:25:24 AM (Central Standard Time, UTC-06:00)  #    Comments [2]
# Monday, October 31, 2011

I recently put together an Entity Framework Code First demo that I presented to my colleagues at Skyline Technologies.

The demo includes:

  • Generating DB from POCO’s
  • Fluent and Attribute EF configuration examples
  • Creating Relationships (1 –> Many, Many –> Many)
  • Seeding Data
  • Using a separate DB instance for integration testing
  • How using Data Annotations saves you time in MVC
  • New Features Coming In EF vNext
    • Enums
    • Spatial Data Type

I put the code up on GitHub so if you are looking for an EF Code First kick starter took a look!

posted on Monday, October 31, 2011 7:35:41 AM (Central Standard Time, UTC-06:00)  #    Comments [3]