Posted on

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);
}
Posted on

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();
    }
  }
}
Posted on

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.

Posted on

Covering multi tenancy in the database

You there, computer man! Make it a multi tenant application.

There are several ways to support multi tenancy on the database part of your application. The easiest approach would be to add a CustomerID column to each table. This is easy to maintain since any change you make in your development environment (add a column, change a type,…) needs to be done only once on the live environment. The downside however is that all data of all your customers is mixed in one table forcing you to add a where clause to all your queries to filter on the customer id, if you support different business logic per customer you might also run into issues. Customer A wants an additional field etc.

Another approach is to prefix or suffix your table names with some sort of customer identification. So you’d have CustomerA_BlogPosts, CustomerB_BlogPosts. You can have a single database for all your customers but now all data is stored in a different table. Since now you have multiple tables representing the same model you’ll have to figure out a way to migrate to new versions. It’s doable but keep it in mind, you’d also have to create some infrastructure to select the correct table when you issues your queries.

A variant on the customer specific table names is to create database schemas which are specific to your customer. So you’ll have CustomerA.BlogPosts and CustomerB.BlogPosts. Apart from the fact that all data is stored in specific tables per customer, like the approach stated above, you can also add an additional security layer by using a specific database user per customer which can only access the tables of the specific schema. All table names remain the same so you could even migrate specific customers to another database server as long as your infrastructure to look up the correct database / schema name.

The most flexible way however, but with quite an impact on your server, is to create an entire database instance per customer, this gives you the most available options. You can use all available database tooling to revert specific databases if the customer asks you to etc.

I’ve used all approaches stated in this post and even mixed and matched between them, it all depends on the scale and requirements of your project. For small projects I’d use the first approach with the additional column. For larger projects or where you need to support different data models per customer the schema specific approach or the entire database per customer are the way to go in my opinion.