entity-framework

Fixing SQL Server Spatial "Not a Valid Instance of Geography" Errors in C#

The project we're working on needs the ability for a user to define a polygon on a map, like this:

A screenshot of Bing Maps, showing a fully-created polygon

Then, we need to save that defined polygon as a SQL Geography type in our database, which we access using Entity Framework. EF supports the DbGeography type, so we are using that to actually send the polygons to the database.

NOTE: for this post, the database server is SQL Server 2008 R2, though I am not sure if the error presented below will occur in other versions of SQL Server.

In order to get to the point where we can create the DbGeography object, though, one of the things we do is submit a string in the well known text format, a commonly-accepted markup language for dealing with vector geometry (i.e. representing areas on a map). We were taking that well-known text and converting it into type DbGeography like so. Our well-known text might look like this:

"{{ "Polygon": "POLYGON((-112.27117 33.4646,-112.27186 33.54476,-112.23959 33.61741,-112.2286 33.666,-111.9972 33.67057,-112.01162 33.5768,-112.03771 33.56307,-112.0384 33.46117,-112.27117 33.4646))" }}"

Which we then converted into DbGeography by using the FromText() method:

var mapObject = DbGeography.FromText(wellKnownText);  

This worked for a little while, but then we started noticing a rather strange bug. If we created a polygon by defining the points in a counter-clockwise manner, then that polygon would be created with no issues. A counter-clockwise manner looks like this (first point created is in lower left, near Tolleson):

A screenshot of Bing Maps, showing a polygon being created counter-clockwise

However, if we created a polygon in a clockwise manner, the system would throw a nasty error (first point is again near Tolleson):

A screenshot of Bing Maps, showing a polygon being created clockwise

That nasty error that we started encountering was this doosy:

"The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 2 ("@1"): The supplied value is not a valid instance of data type geography. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision."

Which tells me absolutely nothing. It might as well have been written in Klingon.

I took to googling around, and came across a few interesting forum posts, one of which pointed me toward the right answer. The issue was that SQL Geography expects any polygons to be defined using the left-hand rule, which is this:

"If you walk along the polygon from the first point defined to each successive point, the interior of the polygon will be on your left-hand side."

When we are defining the polygons, doing so counter-clockwise places the interior of the polygon as the left-hand side of the imaginary line-walker. However, defining the polygons clockwise makes the exterior of the polygon the line-walker's left-hand side, and so SQL Server thought the polygon we were defining was the entire rest of the world, minus the polygon. Obviously SQL Server won't allow an area so huge (the upper limit for area in SQL Geography is a hemisphere), so it gave us the above error.

So now that we know what the error is (and we care about fixing it), how do we fix it? Like this:

  1. First take the well-known text string and create an instance of SQL Geography from it.
  2. Then, get the inversion of that object (e.g. get the complete area not "inside" the defined polygon). The two polygons, when combined together, will cover the entire world.
  3. Whichever of these two polygons is smaller is the one we want, so use that one.

The code for this looks like the following:

public static class DbGeographyExtensions  
{
    public static DbGeography CreatePolygon(string wellKnownText)
    {
        //First, get the area defined by the well-known text using left-hand rule
        var sqlGeography =
        SqlGeography.STGeomFromText(new SqlChars(wellKnownText), DbGeography.DefaultCoordinateSystemId)
        .MakeValid(); 

        //Now get the inversion of the above area
        var invertedSqlGeography = sqlGeography.ReorientObject(); 

        //Whichever of these is smaller is the enclosed polygon, so we use that one.
        if (sqlGeography.STArea() > invertedSqlGeography.STArea())
        {
            sqlGeography = invertedSqlGeography;
        }
        return DbSpatialServices.Default.GeographyFromProviderValue(sqlGeography);
    }

}

That works like a charm, at least for our uses. Hopefully it'll work for yours as well.

If this helps you out (or you have a better solution, or you can read Klingon), please let me know in the comments!

Happy Coding!

Entity Change Tracking using DbContext in Entity Framework 6

We've been searching for a way to audit changes made to our database, and one method we found was doing so via Entity Framework change tracking. Here's how to do it, what's required to set it up, and some drawbacks of this method. Let's get started!

Background

Entity Framework tracks changes made to entities when those entities are loaded from the Context. So, if we load an entity and modify it, like so:

var movie = context.Movies.Find(2);  
movie.Title = "The Great Gatsby";

context.SaveChanges()  

When the call to SaveChanges is made, Entity Framework is aware of what values have changed, what the new values are, and what the original values are. We need to access this information to accomplish the change tracking we want.

Setup

For this demo, let's assume we have the following entities and context:

public class Movie  
{
    public int Id { get; set; }
    public string Title { get; set; }
    public DateTime ReleaseDate { get; set; }
}

public class Actor  
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime DateOfBirth { get; set; }
}

public class MoviesContext : DbContext  
{
    public virtual DbSet<Movie> Movies { get; set; }
    public virtual DbSet<Actor> Actors { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        //omitted for brevity
    }
}

How can we record changes made to these entities automatically, via Entity Framework? Let's find out!

Implementing Change Tracking

For this demo, we're going to store all changes made to any entity in a single data table. Said table can be represented by this entity:

public class ChangeLog  
{
    public int Id { get; set; }
    public string EntityName { get; set; }
    public string PropertyName { get; set; }
    public string PrimaryKeyValue { get; set; }
    public string OldValue { get; set; }
    public string NewValue { get; set; }
    public DateTime DateChanged { get; set; }
}

Notice the property PrimaryKeyValue. Part of recording "which entity changed" is both which table changed (e.g. "Movie", "Actor", etc) and which specific record in that table changed (e.g. 1, 2, etc.). In our implementation, we will need both of these for proper, thorough auditing. Let's see how we can actually implement auditing using Entity Framework.

In order to get all the data we need to produce these change logs, we will follow five steps:

  1. Override the Context's SaveChanges Method
  2. Get the Change Details
  3. Get the Primary Key Values
  4. Save the Logs

Let's see what we need to accomplish, step-by-step.

Step 1: Override the Context's SaveChanges Method

public override int SaveChanges()  
{
    return base.SaveChanges();
}

This allows us to automatically generate change logs any time an entity is saved to our database.

Step 2: Get the Change Details

How can we get the actual changes that will occur for this execution of SaveChanges? We will use the ChangeTracker property of DbContext, like so:

List<DbEntityEntry> modifiedChanges = ChangeTracker.Entries().Where(x => x.State == EntityState.Modified).ToList();  

So what is DbEntityEntry? That type represents the change that will be made to a particular entity, including the original and current values and the state (Added, Modified, Deleted, etc) of that entity. What we now have is a list of all the entities being modified by this context.

Using this list of changes, we can cycle through each change and determine what values are actually changing:

public override int SaveChanges()  
{
    var modifiedEntities = ChangeTracker.Entries()
        .Where(p => p.State == EntityState.Modified).ToList();
    var now = DateTime.UtcNow;

    foreach (var change in modifiedEntities)
    {
        var entityName = change.Entity.GetType().Name;
        var primaryKey = GetPrimaryKeyValue(change);

        foreach(var prop in change.OriginalValues.PropertyNames)
        {
            var originalValue = change.OriginalValues[prop].ToString();
            var currentValue = change.CurrentValues[prop].ToString();
            if (originalValue != currentValue) //Only create a log if the value changes
            {
                //Create the Change Log
            }
        }
    }
    return base.SaveChanges();
}

That method now allows us to get most of the data we need to create a change log. However, we haven't implemented GetPrimaryKeyValue yet.

Step 3: Get the Primary Key Values

We'll use a private method in the DbContext to get the primary key values (taken from this wonderful StackOverflow answer):

object GetPrimaryKeyValue(DbEntityEntry entry)  
        {
            var objectStateEntry = ((IObjectContextAdapter)this).ObjectContext.ObjectStateManager.GetObjectStateEntry(entry.Entity);
            return objectStateEntry.EntityKey.EntityKeyValues[0].Value;
        }

In order to retrieve the primary keys, we must cast our DbContext down to IObjectContextAdapter and query the ObjectStateManager. Once we have access to that manager, we can get the primary key value (note that this method assumes a single-column primary key, which is not necessarily a good real-world scenario, see Drawbacks).

Step 4: Save the Logs

All we have to do now is add the logs to the Context and save the changes.

public override int SaveChanges()  
{
    var modifiedEntities = ChangeTracker.Entries()
        .Where(p => p.State == EntityState.Modified).ToList();
    var now = DateTime.UtcNow;

    foreach (var change in modifiedEntities)
    {
        var entityName = change.Entity.GetType().Name;
        var primaryKey = GetPrimaryKeyValue(change);

        foreach(var prop in change.OriginalValues.PropertyNames)
        {
            var originalValue = change.OriginalValues[prop].ToString();
            var currentValue = change.CurrentValues[prop].ToString();
            if (originalValue != currentValue)
            {
                ChangeLog log = new ChangeLog()
                {
                    EntityName = entityName,
                    PrimaryKeyValue = primaryKey.ToString(),
                    PropertyName = prop,
                    OldValue = originalValue,
                    NewValue = currentValue,
                    DateChanged = now
                };
                ChangeLogs.Add(log);
            }
        }
    }
    return base.SaveChanges();
}

The Complete Code

public class Movie  
{
    public int Id { get; set; }
    public string Title { get; set; }
    public DateTime ReleaseDate { get; set; }
}

public class Actor  
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime DateOfBirth { get; set; }
}

public class ChangeLog  
{
    public int Id { get; set; }
    public string EntityName { get; set; }
    public string PropertyName { get; set; }
    public string PrimaryKeyValue { get; set; }
    public string OldValue { get; set; }
    public string NewValue { get; set; }
    public DateTime DateChanged { get; set; }
}

public class MoviesContext : DbContext  
{
    public virtual DbSet<Movie> Movies { get; set; }
    public virtual DbSet<Actor> Actors { get; set; }
    public virtual DbSet<ChangeLog> ChangeLogs { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        //omitted for brevity
    }


    public override int SaveChanges()
    {
        var modifiedEntities = ChangeTracker.Entries()
            .Where(p => p.State == EntityState.Modified).ToList();
        var now = DateTime.UtcNow;

        foreach (var change in modifiedEntities)
        {
            var entityName = change.Entity.GetType().Name;
            var primaryKey = GetPrimaryKeyValue(change);

            foreach(var prop in change.OriginalValues.PropertyNames)
            {
                var originalValue = change.OriginalValues[prop].ToString();
                var currentValue = change.CurrentValues[prop].ToString();
                if (originalValue != currentValue)
                {
                    ChangeLog log = new ChangeLog()
                    {
                        EntityName = entityName,
                        PrimaryKeyValue = primaryKey.ToString(),
                        PropertyName = prop,
                        OldValue = originalValue,
                        NewValue = currentValue,
                        DateChanged = now
                    };
                    ChangeLogs.Add(log);
                }
            }
        }
        return base.SaveChanges();
    }
}

Drawbacks

There are a couple of significant drawbacks to this particular solution:

  1. No auditing for Added entities. This is because, in my system, the database is responsible for creating the primary key values (via IDENTITY columns) and therefore the primary keys do not exist before the entity is added to the database. Attempting to use the database-generated primary keys for Added entities would result in two round-trips to the database on every save.
  2. Support for single-column primary keys only. This code makes an explicit assumption that only one column per table in your database is the primary key, which is not true in the real world.

Summary

The method outlined about is a good way to track and audit changes made to existing entities. It allows you to record the entity changed, the value changed, the primary key of the changed record, and the date of change. However, it does not track the adding of entities and it does not support multiple-column primary keys. With all of that said, I feel this is a pretty good step toward having full auditing in Entity Framework for our system.

Did I miss something, or can you improve my code? Please feel free to do so, and share in the comments!

Happy Coding!

Loading Entity Framework Related Entities with AutoMapper and Reflection

In a previous post I mentioned how we would be using AutoMapper to create Data-Transfer Objects out of Entity Framework entities; the DTOs would then be transmitted over a WCF service. Well, as often happens, a new requirement surfaced recently, and the solution we came up with turned out to be pretty useful, so now I'm sharing it with you all.

The Problem

The issue we had was this: because this app needs to keep performance in mind (and because I really, really hate clutter), we wanted to include a way for the system calling our service to specify if they wanted any additional entities related to the entities they asked for initially.

Let's set this up with a diagram:

A diagram of the Leagues database, showing that a League has many Teams, and a Team has many Players

So, a League has many Teams, and a Team has many players.

If the service requested to get a Team for a specific ID, we would return a Data-Transfer Object for the Team class that looks something like this:

[DataContract]
public class TeamDTO  
{
    [DataMember]
    public int Id { get; set; }

    [DataMember]
    public int LeagueId { get; set; }

    [DataMember]
    public string Name { get; set; }

    [DataMember]
    public DateTime FoundingDate { get; set; }

}

But what if the user also wanted the players on that team? We could make them do another call to the service, but then they are making two calls when they could just be making one. Plus, the calling user won't always want the players for the specific team, so we needed a way for them to "opt-in" to selecting the players in addition to the team object.

So, we had two competing problems:

  1. The user will want the related entities sometimes, but not always.
  2. The user does not want to have to make multiple calls to the service to retrieve related data.

That's the issue we tackled. How did we do it? Read on to find out!

Getting Active Navigation Properties

Before we started writing any code at all, we decided that for any query against an entity, we would only load "immediately-related" entities. That means that if the user queried for one or more Leagues, s/he could get the related Teams, but not those Teams' players (at least, not during the same call). We figured that if they really want that data, they'll have to make another call.

For reference, we were using Entity Framework Code First for our data layer, and our Team model class looked like this:

[Table("Team")]
public partial class Team  
{
    public Team()
    {
        Players = new HashSet<Player>();
    }

    public int Id { get; set; }

    public int LeagueID { get; set; }

    [Required]
    [StringLength(200)]
    public string Name { get; set; }

    public DateTime FoundingDate { get; set; }

    public virtual League League { get; set; }

    public virtual ICollection<Player> Players { get; set; }
}

We also modified our TeamDTO data-transfer object to include the optional Players and League, as well as a TeamOptions contract that would let the user choose if they wanted the related entities:

[DataContract]
public class TeamDTO  
{
    [DataMember]
    public int Id { get; set; }

    [DataMember]
    public string Name { get; set; }

    [DataMember]
    public DateTime FoundingDate { get; set; }

    [DataMember]
    public List<PlayerDTO> Players { get; set; }

    [DataMember]
    public LeagueDTO League { get; set; }
}

[DataContract]
public class TeamOptions  
{
    [DataMember]
    public bool IncludePlayers { get; set; }

    [DataMember]
    public bool IncludeLeague { get; set; }
}

The problem was, how would we translate the boolean values of the properties of TeamOptions into statements we could actually use in LINQ-to-Entities? Reflection turned out to be the answer.

First, we created an attribute called NavigationPropertyAttribute, which allowed us to decorate the properties in TeamOptions with the name of the navigation property they corresponded to:

[AttributeUsage(AttributeTargets.Property)]
public class NavigationPropertyAttribute : Attribute  
{
    protected string _navigationPropertyName { get; set; }

    public string NavigationPropertyName
    {
        get
        {
            return _navigationPropertyName;
        }
        set
        {
            _navigationPropertyName = value;
        }
    }

    public NavigationPropertyAttribute()
    {
        _navigationPropertyName = string.Empty;
    }

    public NavigationPropertyAttribute(string name)
    {
        _navigationPropertyName = name;
    }
}

We also needed an OptionsBase class that uses Reflection to return a list of the Navigation Property names attached to option properties that are active:

public abstract class OptionsBase  
{
    public List<string> GetSelectedOptions()
    {
        List<string> names = new List<string>();
        var type = GetType();
        var properties = type.GetProperties(BindingFlags.Public | BindingFlags.Instance);
        foreach(PropertyInfo info in properties)
        {
            bool value = (bool)info.GetValue(this);
            if (info.PropertyType == typeof(bool) && value)
            {
                NavigationPropertyAttribute attribute = (NavigationPropertyAttribute)info.GetCustomAttribute(typeof(NavigationPropertyAttribute));
                names.Add(attribute.NavigationPropertyName);
            }
        }
        return names;
    }
}

That enabled us to modify TeamOptions like so:

[DataContract]
public class TeamOptions : OptionsBase  
{
    [DataMember]
    [NavigationProperty("Players")]
    public bool IncludePlayers { get; set; }

    [DataMember]
    [NavigationProperty("League")]
    public bool IncludeLeague { get; set; }
}

If we had an instance of TeamOptions and wanted to get a list of the property names, we could do this:

var options = new TeamOptions()  
{
    IncludePlayers = true,
    IncludeLeague = true
}

var navProperties = options.GetSelectedOptions(); //returns new List<string>(){"Players", "League"}  

Now that we knew which navigation properties we needed to include, we wanted a way to do this simply, preferably in one line of code. That's where this extension method comes in:

public static IQueryable<T> AddIncludeStatements<T>(this IQueryable<T> query, OptionsBase options)  
{
    var names = options.GetSelectedOptions();
    var dbQuery = (DbQuery<T>)query;
    foreach (var name in names)
    {
        dbQuery = dbQuery.Include(name);
    }
    return dbQuery;
}

Let's break this method down:

  • An IQueryable<> is a representation of a query. In this case, the extension method is taking a query that it is going to modify as a this parameter.
  • A DbQuery<> is a non-generic instance of a query. Because we cannot use the Include() method on an IQueryable<>, we needed to cast to DbQuery<>

What this method does is take a LINQ-to-Entities query, insert the needed include statements into said query, then return the modified query that can be executed elsewhere.

We use it like this (this is our TeamService class, which provides data access for the Team objects):

public class TeamService  
{
    private LeaguesDataContext _context;

    public TeamService()
    {
        _context = new LeaguesDataContext();
    }

    public TeamDTO GetByID(int id, TeamOptions options)
    {
        return Mapper.Map<TeamDTO>(_context.Teams.Where(x => x.Id == id).AddIncludeStatements(options).First());
    }
}

Woohoo! We're done, now we just pull up WCFTestClient to test this and we'll be ready to rock.....

An error message from WCFTestClient, saying a generic error occurred.

Crap. That's not good. What went wrong?

Tracking Down the Bug

I may be an experienced bug hunter, but this thing stymied me. The error message didn't give any useful details, it just said that a "connection was forcibly closed by the remote host." That's as helpful as a slap to the face.

Eventually, though, I discovered the problem. I'm gonna lay out most of the code (call to create AutoMapper maps excluded); see if you see the issue.

[DataContract]
public class TeamDTO  
{
    [DataMember]
    public int Id { get; set; }

    [DataMember]
    public string Name { get; set; }

    [DataMember]
    public DateTime FoundingDate { get; set; }

    [DataMember]
    public List<PlayerDTO> Players { get; set; }

    [DataMember]
    public LeagueDTO League { get; set; }
}

[DataContract]
public class PlayerDTO : Player  
{
    [DataMember]
    public int Id { get; set; }

    [DataMember]
    public int TeamId { get; set; }

    [DataMember]
    public string FirstName { get; set; }

    [DataMember]
    public string LastName { get; set; }

    [DataMember]
    public DateTime DateOfBirth { get; set; }

    [DataMember]
    public TeamDTO Team { get; set; }
}

public static class AutoMapperConfiguration  
{
    public static void Configure()
    {
        Mapper.CreateMap<EntityFrameworkModel.League, Contracts.DataContracts.LeagueDTO>();
        Mapper.CreateMap<EntityFrameworkModel.Team, Contracts.DataContracts.TeamDTO>();
        Mapper.CreateMap<EntityFrameworkModel.Player, Contracts.DataContracts.PlayerDTO>();
    }
}

public class TeamService  
{
    private LeaguesDataContext _context;

    public TeamService()
    {
        _context = new LeaguesDataContext();
    }

    public TeamDTO GetByID(int id, TeamOptions options)
    {
        return Mapper.Map<TeamDTO>(_context.Teams.Where(x => x.Id == id).AddIncludeStatements(options).First());
    }
}

See the issue? I sure didn't; not the first ten times, anyway.

The problem was that we defined a circular reference. See how PlayerDTO and TeamDTO each have properties of the other's type? When AutoMapper encountered those properties, it attempted to map them by reading the corresponding EF navigation properties, and EF promptly loaded that data from the database. This caused an infinite loop of loading (load them Team, get the related Players, get their related Teams, get those Teams' related Players, etc.) and eventually the system crashed.

Well, that sucks. I had been hoping to keep the structure nice and clean, but this infinite loop wasn't going to let me. I had to find some way to eliminate the circular references.

We did this by creating two levels of DTOs. Here's the two classes for Team:

namespace WCFNavigationPropertyMapping.Contracts.DataContracts  
{
    [DataContract]
    public class Team
    {
        [DataMember]
        public int Id { get; set; }

        [DataMember]
        public string Name { get; set; }

        [DataMember]
        public DateTime FoundingDate { get; set; }
    }

    [DataContract]
    public class TeamDTO : Team
    {
        [DataMember]
        public League League { get; set; }

        [DataMember]
        public List<Player> Players { get; set; }
    }
}

Note that in TeamDTO, the reference for the two NavigationProperties are for the basic contract classes, not the complete DTO objects. This design will prevent the circular-reference infinite-loop errors. Now if we run this in WCF Test Client, it behaves exactly like we want it to.

Summary

This design allows us to:

  1. Let the user decide which related entities s/he wants on a given call.
  2. Let the developers use concise, clear code to describe what the LINQ-to-Entities queries should be doing.

I'm pretty satisfied with this solution; we'll see how it holds up under load. I have, however, noticed a couple of drawbacks:

  • I don't like needing two data contracts classes. I think there ought to be a cleaner way of designing this, but it's escaping me at the moment.
  • Reflection and Entity Framework are both reputed (even by me) to be slow compared to other equivalent solutions, so I'm really interested to see if my developer-friendly solution can pull its weight in a production environment.

There's a sample project that demos this solution over on GitHub. I'd be happy to hear any suggestions you dear readers have about how to improve this design.

Happy Coding!

Mapping Entity Framework Entities to DTOs with AutoMapper

One of the projects that my team is working on is a major WCF service that will serve as a hub for our company-specific data. This hub will allow multiple other projects to query for and receive certain data. Because we want to use Entity Framework for the data-access layer and WCF for the service layer, we figured we could just use the created Entity Framework Code First classes as DataContracts over WCF. Something like this:

[Table("Team")]
[DataContract]
public class Team  
{
    [DataMember]
    [Key]
    public int Id { get; set; }

    [DataMember]
    [Required]
    [StringLength(500)]
    public string Name { get; set; }

    ...

    [DataMember]
    public virtual ICollection<Player> Players { get; set; }
}

Anybody who has tried this before, though, will immediately tell you that this is a horrible idea, for the simple reason that Entity Framework Entities have trouble serializing (specifically Navigation Properties have all kinds of issues with serialization), and WCF requires any DataContracts to be serializable. So what were we to do?

We were to use Data Transfer Objects (DTOs).

Data Transfer Objects

DTOs are exactly what they sound like: a construct designed to pass data from one system to another, nothing more and nothing less. In general, DTOs should contain only data and not business logic; it is up to the supplying and receiving systems to determine what to do with the data a DTO carries. In our case, the DTOs ended up being our DataContract classes. So, our Entity Framework object might look like this:

[Table("Team")]
public class Team  
{
    [Key]
    public int Id { get; set; }

    [Required]
    [StringLength(500)]
    public string Name { get; set; }

    public DateTime OrganizationDate { get; set; }

    ...

    public virtual ICollection<Player> Players { get; set; }
}

And our corresponding DTO would look like this:

[DataContract]
public class TeamDTO  
{
    [DataMember]
    public int Id { get; set; }

    [DataMember]
    public string Name { get; set; }

    [DataMember]
    public DateTime FoundingDate { get; set; }

    ...

    [DataMember]
    public List<PlayerDTO> Players { get; set; }
}

Now we could simply use the TeamDTO and PlayerDTO objects to move data back and forth from our service to the client systems and vice versa. Voila!

Mapping Entities to DTOs with AutoMapper

Except, we weren't quite done. Now we'd end up with a ton of mapping code, like this:

public TeamDTO GetByID(int id)  
{
    var team = ... //Get Team entity from data store
    var teamDto = new TeamDTO()
    {
        Id = team.Id,
        Name = team.Name,
        FoundingDate = team.OrganizationDate
        ...
    }
}

That sucks to write, it sucks to read, it sucks to maintain. There must be a better way, and there is: AutoMapper. AutoMapper is a conventions-based mapping system that allows you to copy values from an instance of one class to an instance of another class using pre-defined maps. Once we've got AutoMapper set up, the mapping code becomes super simple:

public TeamDTO GetByID(int id)  
{
    var team = ... //Get Team entity from data store
    return Mapper.Map<TeamDTO>(team);
}

See? Much easier to read.

Of course, with that kind of power comes some setup. We need to define the maps in advance. Often, my team will do this in a static AutoMapperConfiguration class:

public static class AutoMapperConfiguration  
{
    public static void Configure() 
    {
        Mapper.CreateMap<Team, TeamDTO>();
    }
}

Then, we call that Configure() method from the Global.asax file (or elsewhere on application startup).

But how does it actually create that map? AutoMapper uses reflection to look at the properties of the source and destination classes (in the example above, the source class is the Team entity and the destination class is the TeamDTO data transfer object). For any property with the same name and type that occurs in both classes, a map definition is created that specifies that the value from an property of the source class should be moved to the value of a property of the destination class.

But what about the navigation properties that EF uses? Generally, we want to ignore those values, as mapping them could have unintended consequences (e.g. attempting to map navigation properties while still in the same context could end up loading a LOT of your database into memory). So, if we want to ignore a particular property in the source value, we could modify our AutoMapper map like so:

public static class AutoMapperConfiguration  
{
    public static void Configure() 
    {
        Mapper.CreateMap<Team, TeamDTO>()
              .ForMember(dest => dest.Players, opt => opt.Ignore());
    }
}

If your names don't exactly match, you could use MapFrom:

public static class AutoMapperConfiguration  
{
    public static void Configure() 
    {
        Mapper.CreateMap<Team, TeamDTO>()
              .ForMember(dest => dest.Players, opt => opt.Ignore())
              .ForMember(dest => dest.FoundingDate, opt => opt.MapFrom(src => src.OrganizationDate));
    }
}

AutoMapper is hugely configurable and provides us a lot of power and brevity. That said, it's possible you'll encounter performance issues in high-performance apps, so as always, do some research yourself to determine if AutoMapper is right for your scenario.

Regardless of whether you use AutoMapper to map them, using DTOs has, in my opinion, consistently been proven to solve the gap between EF Entities and WCF DataContracts. It's really just another application of the Single Responsibility Principle: your classes should only do one thing.

Anybody out there using another solution (rather than DTOs) to bind Entity Framework and WCF? I would love to hear what you're working on and how it's going. Let me know in the comments!

Happy Coding!