Entity Framework 4.1 – One To One Mapping

In the snippet below you can see that a customer has an address property.

    public class Customer
    {
        public Customer()
        {
           Address = new Address();
        }
 
        public Guid Id { get; set; }
        public string Name { get; set; }
        public Address Address { get; set; }
        public Guid AddressId { get; set; }
    }
 
    public class Address
    {
        public Guid Id { get; set; }
        public string City { get; set; }
        public string Country { get; set; }
        public string Street { get; set; }
    }

The database that gets generated though defines this as a one to many relationship.

Which is not what you’re expecting, or is it? I haven’t defined any restrictions on the address class so indeed an address could be shared between multiple customers. Let’s change that.

    public class Customer
    {
        public Customer()
        {
           Address = new Address();
        }
 
        public Guid Id { get; set; }
        public string Name { get; set; }
        public Address Address { get; set; }
 
    }
 
    public class Address
    {
        public Guid Id { get; set; }
        public string City { get; set; }
        public string Country { get; set; }
        public string Street { get; set; }
        [Required]
        public Customer Customer { get; set; }
    }

I’ve added a navigation property from Address to Customer and marked the Customer property as required. This basically is telling EF that there is a one to one mapping between the two and the primary key of the customer should be used in the relationship. The primary key of the customer will also become the primary key of the address. If you did not add the required attribute you’ll get an invalidoperationexception saying that it’s unable to determine the principal end of an association.

You can also use the fluent api.

    public  class  CustomerContext
        : DbContext
    {
        public IDbSet<Customer> Customers { get; set; }
 
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Address>().HasRequired(x => x.Customer);
            base.OnModelCreating(modelBuilder);
        }
 
    }

They both result in the same database structure.

What if both ends are required? A customer always has an address and an address always has a customer. Marking both ends as required will again result in the same exception as before.

In order to model this you have to use the fluent api and there are two ways to achieve the desired result.

    public  class  CustomerContext
        : DbContext
    {
        public IDbSet<Customer> Customers { get; set; }
 
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Address>()
                .HasRequired(x => x.Customer)
                .WithRequiredDependent(x => x.Address);
            base.OnModelCreating(modelBuilder);
        }
    }

I’m saying here that the address entity has a required customer property and that the customer class is actually in charge, MSDN reference.

Resulting database structure:

Another way to achieve the same result:

    public  class  CustomerContext
        : DbContext
    {
        public IDbSet<Customer> Customers { get; set; }
 
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Address>()
                .HasRequired(x => x.Customer)
                .WithRequiredDependent();
            modelBuilder.Entity<Customer>()
                .HasRequired(x => x.Address)
                .WithRequiredPrincipal();
            base.OnModelCreating(modelBuilder);
        }
    }

MSDN link on WithRequiredPrincipal.

You saw that the WithRequiredPrincipal and Depedant actually have one taking a lambda and one with no arguments. This allows you to exclude a navigation property and still get a proper one to one mapping. Which brings me back to my starting point.

public class Customer
    {
        public Customer()
        {
           Address = new Address();
        }
 
        public Guid Id { get; set; }
        public string Name { get; set; }
        public Address Address { get; set; }
    }
 
    public class Address
    {
        public Guid Id { get; set; }
        public string City { get; set; }
        public string Country { get; set; }
        public string Street { get; set; }
 
 
    }
 
    public  class  CustomerContext
        : DbContext
    {
        public IDbSet<Customer> Customers { get; set; }
 
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Customer>()
                .HasRequired(x => x.Address)
                .WithRequiredPrincipal();
            base.OnModelCreating(modelBuilder);
        }
    }

Entity Framework 4.1 – Component mapping

By default if you add ‘simple’ property types to your class (strings, ints,…), they are all translated into columns in the database. What if you had a group of properties that actually can have some functionality together. You also have this group of properties coming back at several places in your code base. You could introduce a base class in order to improve duplication but if the two entities are not really related you should stay away from overusing the inheritance approach. What you need here is a complextype. It was present in previous releases of the Entity Framework and can be used in 4.1.

We’ll modify the customer class posted below into something a bit more object oriented.

    public class Customer
    {
        public Guid Id { get; set; }
        public string Name { get; set; }
        public string City { get; set; }
        public string Country { get; set; }
        public string Street { get; set; }
    }

Customer could also have a bunch of methods that acted upon the address fields, Employee could have the same properties, the same logic and an invoice probably also has an address. So let’s move the properties, and logic, into a new class called Address. I don’t want a separate table that contains all the addresses though, I want all of the address fields to be present in the customer, employee and invoice table.

In order to achieve that result you need the ComplexType attribute.

    public class Customer
    {
        public Customer()
        {
            Address = new Address();
        }
 
        public Guid Id { get; set; }
        public string Name { get; set; }
        public Address Address { get; set; }
    }
 
    [ComplexType]
    public class Address
    {
        public string City { get; set; }
        public string Country { get; set; }
        public string Street { get; set; }
    }

Using the ComplexType attribute will result in a database table with the correct structure.

You can also use the fluent API to configure a complextype.

    public  class  CustomerContext
        : DbContext
    {
        public IDbSet<Customer> Customers { get; set; }
 
        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.ComplexType<Address>();
            base.OnModelCreating(modelBuilder);
        }
 
    }

How to support Contains in Entity Framework 3.5

How can you retrieve the products that are contained in a list of id’s ?

var ids = new List<int>() { 1, 2, 3, 500 };
using (var context = new AdventureWorksEntities())
{
  var products = from product in context.Products
                           where ids.Contains(product.ProductID)
                           select product;
  WriteProducts(products);
}

The good news, this is supported in Entity Framework v4.0, but since I’m on 3.5 I needed to find another solution. Luckily using the info from my previous post I can write an expression for this:

var ids = new List<int>() { 1, 2, 3, 500 };
using (var context = new AdventureWorksEntities())
{
  Expression<Func<Product, bool>> idMatching = null; 
  foreach (var id in ids)
  {
    int productId = id;
    if(idMatching == null)
    {
      idMatching = x => x.ProductID == productId;
    }
    else
    {
      idMatching = idMatching.Or(x => x.ProductID == productId);
    }
  }
  var products = context.Products.Where(idMatching);
  WriteProducts(products);
}

Combining expressions to use with the Entity Framework

You often have to create a screen where the information is filtered on a given condition and the user can set some more options through checkboxes. For instance, display all products of a certain category and those results should be able to be filtered on their active status.

We could send one query to the database to retrieve the products and then filter them in memory, but let’s try to use the database for all of this since they’re good at this. You’ll bump into several issues when trying to solve this and like most problems in software development someone else most likely encountered the same issue and has already found a solution. In this case take a look at this, albeit old, article which illustrates the problem and gives a solution. It will enable you to write code like this:

public class ProductFinder
{
  public  ICollection<Product> FindProductsBySubCategory(int subCategoryId, bool makeFlagMarked)
  {
    using(var context = new AdventureWorksEntities())
    {
      Expression<Func<Product, bool>> whereClause = x => x.ProductSubcategory.ProductSubcategoryID == subCategoryId; 
      if(makeFlagMarked)
      {
        whereClause = whereClause.And(x => x.MakeFlag);
      }
      return context.Products.Where(whereClause).ToList();
    }
  }
}

Changing Entity Framework model at runtime

Out of the box there is no way, or at least not an easy one, to change table or schema names used by the Entity Framework at runtime. If you used MyDbName.dbo.BlogPosts as table name during development you can not use MyDbName.dbo.CustomerA_BlogPosts if you want to support customer specific table names as explained in my previous post.

On CodePlex however you can find an adapter for your Entity Framework model to change it at runtime. You can change the connection to the database, change table names or use different schemas. So a lot of goodness to support multi tenant applications.

In order to use the adapter you should create a partial class with the name of your context class and inherit from AdaptingContext instead of ObjectContext.

public partial class AdventureWorksEntities
  : AdaptingObjectContext
{
  public AdventureWorksEntities()
    :base("name=AdventureWorksEntities", "AdventureWorksEntities",
            new ConnectionAdapter(new TablePrefixModelAdapter("MyPrefix"), Assembly.GetExecutingAssembly()))
  {
    OnContextCreated();
  }
}

This partial class has the same name as the partial class generated by the Entity Framework. You should remove the generated constructors and base class specification from that one or your code will not compile. Note that every time you update the model with the designer it will regenerate the constructors etc. so you have to remove them every time.

In this example I’m using the TablePrefixModelAdapter which will prefix all my tables with my specified prefix, in this case “MyPrefix”. Consuming this context is done just like a normal ObjectContext. You also need to specify where the mappings that need to be altered are located, in this sample I’m using a single console application so they are contained in the executing assembly.

using(var context = new AdventureWorksEntities())
{
  var products = context.Products;
  products.ToList().ForEach(x => Console.Write(x.Name));
}

And this generates the following SQL:

SELECT 
1 AS [C1], 
[Extent1].[ProductID] AS [ProductID], 
[Extent1].[Name] AS [Name], 
[Extent1].[ProductNumber] AS [ProductNumber], 
... 
FROM [Production].[MyPrefixProduct] AS [Extent1]

So we’re all set to support multi tenancy? Not quite. The code as available in the CodePlex project stores the updated mappings in a static variable for performance reasons, this is great if you’re developing against a development database and your code will run against a production database with different naming or schema.

For a multi tenant application we need a different model for each of our customers so we need to make another change to the code as available in the CodePlex project. If you don’t mind taking the performance hit to rewrite the mapping every single time an object context is created you can just open the ConnectionAdapter class and change the static variables which hold the model information to instance variables.

And optimized version would store all the mappings per customer so you only rewrite the mappings once a new customer hits the context, and from that point store them in i.e. a dictionary with key the customerID and value the updated mappings.

I’ve used Entity Framework 3.5 SP1 and Visual Studio 2008 in this post.