Entity Framework with POCOs

Without any working knowledge of the Entity Framework I have decided to dive in and see if it is a viable option for data access. In the past I have used LINQ to SQL and was not really that impressed. I had a hard time making use of the Context object inside of a web environment. I also did an enterprise application with LLBLGEN PRO and found it to be quite easy to use, but always had issues with serialization because of its large entity objects.

I have always preferred iBatis for data access because it was light weight, separated queries and made use of Plain Old CLR Object (POCO)s. Entity Framework (EF) has had several iterations and with this latest release is supporting POCOs without inheriting from any special interfaces.

The ability to separate domain objects completely from any kind of persistance code makes systems much more extensible and maintainable. Testing is made much easier when business logic can be tested separately from storage code. The use of POCOs with the Entity Framework (EF) is definitely a step in the right direction!

In order to get started on my journey I hit up quite a few useful blogs. The most useful is the ADO.NET Blog which gives great information about the current state of EF and plans for future releases.

There are currently two ways to connect POCOs to EF. The main way which is currently supported in the VS 2010 RC is through the use of an Entity Data Model. Even though POCOs are being used, the EDMX is still created and used to create mappings of fields to columns in the database. The designer is still in play, but code generation of each entity is turned off. Custom contexts are then created that return each POCO.

The second way to connect POCOs to EF is still in the preview stage and will not be available in the general release of .net 4. This option is called the ‘Code Only’ approach. The ‘Code Only’ approach removes the need to use the EDMX file and allows manual mapping of columns, relationships and keys. This is currently only in the preview stage. For this blog I will use the first approach and in a following blog will follow the same example using the ‘Code Only’ approach.

All code and examples will be from a small app that I am building to keep score of a Spades card game. Data access is the first step of this application which will eventually make use of RIA Services and Silverlight.

1.) First step is to create a database table. I have created a table called ‘Games’ which will hold a record for each Spades game that is played.

2.) Once the table is made the next step is to create our POCO object. The Game object has a property for each column that is in the database. Any column that allows null in the database will be nullable in the object as well.

public class Game { public int GameId { get; set; } public DateTime StartDate { get; set; } public DateTime? CompleteDate { get; set; } public int? CurrentHomeScore { get; set; } public int? CurrentAwayScore { get; set; } public int? FinalHomeScore { get; set; } public int? FinalAwayScore { get; set; } public int CurrentHandNumber { get; set; } }

3.) Now that we have our POCO we need to map its properties to the database. To do this we need to create a new project called Spades.DataAccess. The reason we create a new project is to ensure that the POCOs in the domain project have no relationship to any persistence code. This makes our design alot more testable.

4.) Add a Entity Data Model (.edmx) to the Spades.DataAccess project. This model allows us to map fields in the database to the POCO object.

5.) Choose the ‘Generate from Database’ option and select the Spades database. Select the ‘Games’ table and create the entity model.

6.) Once the Entity Model has been created we need to stop the auto generation of the entities. This can be achieved by setting the ‘Custom Tool’ property of the Entity Model to null. In the next step we will manually attach the POCO to the Entity Model.

7.) We now create a class that will be used for accessing data and returning POCO objects. This class inherits from the ObjectContext class. A couple things to note: References to the lines 1-4 need to be added for the code to compile. Line 10 passes in the ConnectionString name for the database. The CreateObjectSet() method is used to return the actual POCO object.

using System.Data.Entity;
using System.Data.EntityModel;
using SpadesDomain.Entities;
using System.Data.Objects;

namespace Spades.DataAccess
{
    public class SpadesContext : ObjectContext
    {
        public SpadesContext() : base("name=SpadesEntities","SpadesEntities")
        {
            _games = CreateObjectSet<Game>();
        }

        private ObjectSet<Game> _games;
        public ObjectSet<Game> Games
        {
            get
            {
                return _games;
            }
        }
    }
}

8.) Finally a unit test is used to test that we can actually pull some test data out of the database. (Use the SQL Studio Management tool to add in a couple rows into the Games table) The unit test references all other projects in the solution. The unit test uses LINQ to pull out all Games in the system and checks to make sure at least one Game came back.

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using Spades.DataAccess;
using SpadesDomain.Entities;

namespace SpadesTests.DataTests
{
    [TestClass]
    public class SpadesContextTests
    {
        [TestMethod]
        public void GetGamesTest()
        {
            var db = new SpadesContext();

            var games = (from g in db.Games select g).ToList<Game>();

            Assert.IsTrue(games.Count > 0);
        }
    }
}

8.) A successful unit test! Up next will be doing this example with the ‘Code Only’ approach.