The Devlineator

Great crowd at the Houston DNUG tonight!! Over 110 in attendance.

Great Questions from the group during the talk. I especially like the follow ups. Thank you all for attending. Below is a link to the code you saw tonight.

http://db.tt/oJFvvrf

Here are those EF links

http://archive.msdn.microsoft.com/EFExtensions

http://www.codeproject.com/KB/database/CodeFirstStoredProcedures.aspx

For everyone interested in how to specify unique constraints that are not keys, please check this out.

http://stackoverflow.com/questions/4413084/unique-constraint-in-entity-framework-code-first

This is very similar to how we talked about adding cascade delete to foreign keys.

Thanks again, look forward to seeing you all at Houston TechFest!

Entity Framework, Community, Data Access

I know that we have all hit that point where we are going into a project, it has existing database structure and some old ADO.NET hand built data access layer. We think maybe this would be a great place for Entity Framework, and then we find that the business objects are serialized over WCF, or that they are somehow used in a way that makes standard EF cry. This is where we have that internal debate on if our boss will accept a week of writing code just to get code first running, not to solving the problem. In comes the EF Power Toys.

Just start up a new project in Visual Studio.

With nuget type Install-Package EntityFramework ( this avoids a bug later )

Right click the project and select Entity Framework Reverse Engineer Code first

Punch in your connection information

Then watch the bottom left side status messages, it will load schema information, create objects and configuration for them.

You should now have a context, an entities folder, and a mappings folder.

This includes every entity in the database and a mapping for every property. It doesn’t leave off properties that could have taken advantage of convention.

You can use this the same way you used a code first context before.

Enjoy.

Data Access, Entity Framework, Productivity, Visual Studio 2010

I have gotten the question several time over the last few weeks and usages for the fluent configuration of Code First POCO classes where the database does not match the convention. Here is a simple example on how to address this.

Take a product for example.

public class Product
   {
       public int ID { get; set; }
       public string Name { get; set; }
       public string ProductNumber { get; set; }
       public bool MakeFlag { get; set; }
   }

To configure this to use different column names for the properties you simply need to add a configuration for that class like so.

public class ProductConfiguration : EntityTypeConfiguration<Product>
   {
       public ProductConfiguration()
       {
           this.ToTable("Product","Production");
           this.HasKey(x => x.ID);
           this.Property(x => x.ID).HasColumnName("ProductID");
       }
   }

Notice how the inheritance tells it which class it is setting up a type configuration for.

Then you load the configuration to the context in an override like so.

public class AdventureWorkContext : DbContext
    {
        public AdventureWorkContext(string adventureWorks)
            : base(adventureWorks)
        {
            this.Database.CompatibleWithModel(false);
        }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Configurations.Add(new ProductConfiguration());
            base.OnModelCreating(modelBuilder);
        }

        public DbSet<Product> Products { get; set; }

}

Entity Framework, Data Access

I am sure that we have all looked at the generated EDMX files with some architectural concerns. This is probably due to the business objects and the context being tightly coupled. I have other blogs for separating this out but once you do you end up with the need to have this metadata embedded connection string for the Entites to function. This string can be placed in the app or web config and you are off the the races but what happens when your EDMX is in another assembly that lives under a different namespace and you get the dreaded “Cannot locate metadata resource specified” error? You solve it be giving a connection string that points to the location of the file. Like so.

public static class EntityFrameworkConnectionStringHelper
    {
        public static string GetSqlConnectionString(string serverName, string databaseName)
        {
            var providerCs = new SqlConnectionStringBuilder
                                                        {
                                                            DataSource = serverName,
                                                            InitialCatalog = databaseName,
                                                            IntegratedSecurity = true
                                                        };

            return GetSqlConnectionString(providerCs.ToString());
        }

        public static string GetSqlConnectionString(string providerConnectionString)
        {
            var namespaceString = typeof ($YourEdmxNameHere$Entities).Namespace;

            var fullName = typeof ($YourEdmxNameHere$Entities).Assembly.FullName;
            //This allows you to trim just the assembly name off the namespace while

            //leaving the  rest of the namespace

            string trimmedNamespace = namespaceString.Remove(0, fullName.IndexOf(",") + 1);

            var csBuilder = new EntityConnectionStringBuilder();
            csBuilder.Provider = "System.Data.SqlClient";
            csBuilder.ProviderConnectionString = providerConnectionString;
            csBuilder.Metadata = string.Format(
                "res://{0}/{1}.$YourEdmxNameHere$.csdl|res://{0}/{1}.$YourEdmxNameHere$.ssdl|res://{0}/{1}.$YourEdmxNameHere$.msl",
                fullName, trimmedNamespace);

            return csBuilder.ToString();
        }
    }

Enjoy!

Entity Framework, Productivity, Data Access

We have all had those times where we have had to build chains on complex where statements, with several OR / AND operations. These make LINQ queries look really complex and most of the time need to be reused in several places. To clean this up and give us the ability to compose complex queries on the fly from say a UI filter or something similar give the following a try.

public class Test
    {
        public void Testing()
        {
            Expression<Func<Product, bool>> isRed = c1 => c1.Color == "Red";

            Expression<Func<Product, bool>> isCheap = c2 => c2.StandardCost < 10.0m;

            Expression<Func<Product, bool>> isClothing = c3 => c3.Class == "Clothing";

            Expression<Func<Product, bool>> isAcceptable = Utility.BuildOrElse(isRed, isCheap, isClothing);

            IQueryable<Product> products = null;
            var query = products.Where(isAcceptable);
        }

    }

    public class ParameterRebinder : ExpressionVisitor
    {

        private readonly Dictionary<ParameterExpression, ParameterExpression> map;



        public ParameterRebinder(Dictionary<ParameterExpression, ParameterExpression> map)
        {

            this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>();

        }



        public static Expression ReplaceParameters(Dictionary<ParameterExpression, ParameterExpression> map, Expression exp)
        {

            return new ParameterRebinder(map).Visit(exp);

        }



        protected override Expression VisitParameter(ParameterExpression p)
        {

            ParameterExpression replacement;

            if (map.TryGetValue(p, out replacement))
            {

                p = replacement;

            }

            return base.VisitParameter(p);

        }

    }

    public static class Utility
    {

        public static Expression<T> Compose<T>(this Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge)
        {

            // build parameter map (from parameters of second to parameters of first)

            var map = first.Parameters.Select((f, i) => new { f, s = second.Parameters[i] }).ToDictionary(p => p.s, p => p.f);



            // replace parameters in the second lambda expression with parameters from the first

            var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body);



            // apply composition of lambda expression bodies to parameters from the first expression 

            return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters);

        }



        public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
        {

            return first.Compose(second, Expression.And);

        }



        public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
        {

            return first.Compose(second, Expression.Or);

        }

        public static Expression<Func<T, bool>> OrElse<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
        {

            return first.Compose(second, Expression.Or);

        }

        public static Expression<Func<T, bool>> BuildAnd<T>(params Expression<Func<T, bool>>[] conditions)
        {
            return conditions.Aggregate<Expression<Func<T, bool>>, Expression<Func<T, bool>>>(null, (current, expression) => current == null ? expression : current.And(expression));
        }

        public static Expression<Func<T, bool>> BuildOr<T>(params Expression<Func<T, bool>>[] conditions)
        {
            return conditions.Aggregate<Expression<Func<T, bool>>, Expression<Func<T, bool>>>(null, (current, expression) => current == null ? expression : current.Or(expression));
        }

        public static Expression<Func<T, bool>> BuildOrElse<T>(params Expression<Func<T, bool>>[] conditions)
        {
            return conditions.Aggregate<Expression<Func<T, bool>>, Expression<Func<T, bool>>>(null, (current, expression) => current == null ? expression : current.OrElse(expression));
        }

    }

Adapted and added to from

Entity Framework, Data Access, C#, C# Helpful Functions, Productivity

One of the problems that I have found is that I have trouble separating the data base calls from my unit tests while still covering my repository. We are going to talk about how to get around this, and pick up after this post to avoid some of the prep work.

We First add a Test Project to the solution to house the tests.

We want to setup a Product Repository so that we can test it but we want to mock the IRepository that it takes to avoid the database call.

var mockRepository = MockRepository.GenerateMock<IRepository>();
mockRepository.Stub(x => x.AsQueryable<Product>()).Return(new List<Product>
                                                                          {
                                                                              new Product()
                                                                                  {
                                                                                      DaysToManufacture = 4
                                                                                  },
                                                                              new Product()
                                                                                  {
                                                                                      DaysToManufacture = 2
                                                                                  }
                                                                          }.AsQueryable());
var target = new ProductRepository(mockRepository);

We setup the return with a value that should get filtered and a value that should not get filtered as to give us a control set. Make sure to call the AsQueryable on the object initialization so that it matches the return type on the repository.

After we have setup the test them we call our Long running products method.

//act
var result = target.GetLongRunningProducts();

//assert
Assert.IsTrue(result.Count() == 1);
Assert.AreEqual(4, result.FirstOrDefault().DaysToManufacture);

This test now gives us the ability to test our filters and our logical separation at the repository level without a database. We can leverage this to run integration performance and query tests against the database as well, but that is another post.

Source code is here

Data Access, Design Patterns, Entity Framework

I have found that there are often times that I use the same queries against an object set but I end up writing the same or very similar LINQ statements against my context. This however can be solved by use a object specific repository. Below is the implementation over Entity Framework 4.1 and the AdventureWorks sample.

We start with a plain console application and add a EntityContext folder.

Then we add new item ADO.NET Entity Data Model – Name it AdventureWorks.edmx. Use the wizard to connect to the local database for AdventureWorks. Then we add another Folder for our Repository Implementations. This is going to follow the patterns discussed in the Repository patterns post and POCO separation posts.

using System;
using System.Data.Objects;
using System.Linq;
using ObjectRepository.EntityContext;

namespace ObjectRepository.Repository
{
    public class EntityFrameworkRepository : IRepository
    {
        private readonly ObjectContext _context;

        public EntityFrameworkRepository(ObjectContext context)
        {
            _context = context;
        }

        public IQueryable<T> AsQueryable<T>() where T : class
        {
            return _context.CreateObjectSet<T>();
        }

        public void SaveChanges()
        {
            _context.SaveChanges();
        }

        public void Delete<T>(T item) where T : class
        {
           _context.DeleteObject(item);
        }

        public void Add<T>(T item) where T : class
        {
            _context.CreateObjectSet<T>().AddObject(item);
        }

        public void Attach<T>(T item) where T : class
        {
            _context.CreateObjectSet<T>().Attach(item);
        }

        public void Detach<T>(T item) where T : class
        {
            _context.Detach(item);
        }
    }
}

This gives us the base to use, and we could use it as is.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ObjectRepository.EntityContext;
using ObjectRepository.Repository;

namespace ObjectRepository
{
    class Program
    {
        static void Main(string[] args)
        {
            IRepository repository = new EntityFrameworkRepository(new AdventureWorksEntities());

            var longRunningProducts = repository.AsQueryable<Product>().Where(x => x.DaysToManufacture > 3);
        }
    }
}

If we have to use the same long running products query a few times it is going to get old typing it and we have no control over the magic “3”. So how about we wrap a Product specific Repository.

using System.Linq;
using ObjectRepository.EntityContext;

namespace ObjectRepository.Repository
{
    public class ProductRepository : IProductRepository
    {
        private readonly IRepository _repository;

        public ProductRepository(IRepository repository)
        {
            _repository = repository;
        }

        public IQueryable<Product> GetLongRunningProducts()
        {
            return _repository.AsQueryable<Product>().Where(x => x.DaysToManufacture > 3);
        }
    }
}

namespace ObjectRepository.Repository
{
    public interface IProductRepository
    {
        IQueryable<Product> GetLongRunningProducts();
    }
}

Then our usage becomes this…

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using ObjectRepository.EntityContext;
using ObjectRepository.Repository;

namespace ObjectRepository
{
    class Program
    {
        static void Main(string[] args)
        {
            IRepository repository = new EntityFrameworkRepository(new AdventureWorksEntities());
            IProductRepository productRepository = new ProductRepository(repository);
            var longRunningProducts = productRepository.GetLongRunningProducts();
        }
    }
}

Now no matter where you use the long running products you have the same result, no one has to remember the magic “3” and if it changes it only changes once. This will also allow you to wrap the queries in unit test and integration tests for performance but that is another post.

Source Code is here

Data Access, Design Patterns, Entity Framework

I recently gave a talk on this and wanted to post the source code. The purpose of the talk is to walk through how to use EF without violating the rules of architecture that you have placed in your solution. You will need to setup Adventure Works Database on your box or have one you can connect to for this to function correctly.

This is the end point code.

http://dl.dropbox.com/u/15486441/EntityFrameworkFileNew.zip

This is the download link.

This code is the end point of using

http://visualstudiogallery.msdn.microsoft.com/en-us/23df0450-5677-4926-96cc-173d02752313

A little editing of the T4 Template and adherence to the onion architecture.

http://jeffreypalermo.com/blog/the-onion-architecture-part-1/

.NET, Data Access, Entity Framework

I found that I like the streamlined process of working with Entity Framework. The auto generation of domain class is nice when dealing with Existing databases. I dislike having to put domain classes in Infrastructure or putting data base context in Core.

Using the POCO Entities T4 templates you can separate these.

Download here

Add a new Entity Data Model and Generate from the data base.

Then right click the background of the model and add a generated code item.

Select the POCO Objects

This will create Two .tt files. One is the POCO Classes and one is the Context. We are going to use the onion architecture for this example. We want to move the context to infrastructure so that our model and POCO classes and model are in core.

and

To separate the context from the Domain we will need to edit the context template. You do not want to edit the classes generated because this will just get wiped out.

The edit we have to make is to simply add the namespace of the Domain objects to the using statements, and the path of the EDMX file to the input. I am still hunting a way to keep from hard coding a absolute path to the EDMX but until then this will have to work.

Enjoy.

C#, Data Access, Design Patterns, Entity Framework