Tip 25 – How to Get Entities by key the easy way

  • Article

Sometimes rather than writing this:

var customer = ctx.Customers.First(c => c.ID == 5);

You would rather write something like this:

var customer = ctx.Customers.GetCustomerById(5);

In .NET 4.0 this would be trivial to do by modifying the T4 templates that do code gen. You would simply produce an extension method for each EntityType, something like this:

public static Customer GetCustomerById(
this ObjectQuery<T> query,
int Id
)
{
return query.First(c => c.ID == Id);
}

This is fine, but is there a way to support this without resorting to a method for each Type?

General Purpose Solution

There is indeed an approach that is more general purpose, it isn’t type safe, but it work great nevertheless.

The core to the idea is to use eSQL to build a query. This function will work in both 3.5 and 4.0:

public static T Get<T, K>(this ObjectQuery<T> query, K key)
{
//Get the EntityType
EntityType entityType = query.Context.MetadataWorkspace
                                 .GetCSpaceEntityType<T>();

if (entityType.KeyMembers.Count != 1)
throw new Exception("You need to pass all the keys");

//Build the ESQL
string eSQL = string.Format("it.{0} = @{0}",
                                entityType.KeyMembers[0].Name);

//Execute the query
return query.Where(
eSQL,
new ObjectParameter(entityType.KeyMembers[0].Name, key)
).First();
}

So how does it work?

  1. It uses the GetCSpaceEntityType<T>() extension method I wrote in Tip 13, to get the EntityType for T.
  2. Once we have the EntityType we check that we have the right number of parameters, i.e. this method only accepts one key value, so the EntityType in question must have a simple single column key.
  3. Then we generate a piece of eSQL to modify the where clause of the query. For example if you are looking for a customer and the key is ID we will generated “it.ID = @ID”.
  4. Next we create an ObjectParameter for the parameter we referenced in the above eSQL, the value of the parameter comes from the methods key parameter.
  5. Finally we execute the query and get the First() result. In 4.0 you should change this to use Single() but that isn’t supported in 3.5.

And we are done.

Now you can write this:

Customer c = ctx.Customers.Get(5);

Now a word of warning, this method isn’t type safe, so this will compile:

Customer c = ctx.Customers.Get(“Some Random String”);

Even though Customer has an integer key. You will only notice the error at runtime when the eSQL is parsed.

This doesn’t support compound keys, but it would be pretty easy to add more overloads that accept more parameters.

I’ll leave that as an exercise for the reader!

This is 25th post in my ongoing series of Entity Framework Tips.