I'm revisiting some code in my LINQ to SQL business objects layer that I've written about before. The problem I ran into and described was that I had a class with generic business object behavior that needs to look up values based on a database field that wouldn't be known until runtime.

The concrete example I used is a Load(object x) method on the business object, where each instance of the class dynamically picks up the primary key (from the metadata available) and then loads an entity from the database through LINQ.

In application level code you'd just write:

int pk = 10;

CustomerEntity cust = customer.Context.CustomerEntities.SingleOrDefault(cust => cust.Pk == pk);

This is easy and works fine because the cust.Pk field is available at compile time - the compiler knows the type and all's well. But it's not so straight forward if the field is user specified and not available at compile time such as in a generic base class that needs to Load() a single entity.

So if I have a Load method:

public TEntity Load(object Pk)

{            

  Table<TEntity> table =  this.Context.GetTable( typeof(TEntity) ) as Table<TEntity>;

  return table.SingleOrDefault(ent => ??? == Pk);                        

}

how do I get at the dynamic primary key field through LINQ? I know what the field name is but that's string rather than a field instance.

There are a number of approaches:

• Skip LINQ and go straight to string SQL Queries
• Use DynamicQuery
• Use Lambdas instead of fixed parameters
• Use stored Lambda Expressions

Skip LINQ and use SQL strings with ExecuteQuery<T>

The easiest way - even if very unLINQ like - is to use SQL strings. After all we're talking about SQL here and we've written SQL for aeons, so NOT EVERYTHING has to be running through LINQ, right? In certain controlled circumstances (and also some instances where LINQ just won't work) using string queries is still useful and if you have dynamic expressions often times it's just easier to use a string query than to go through the mental gymnastics of figuring out to run the query using LINQ.

This is actually the route I chose, because in this case - doing a single key look up inside of the controlled business layer - is going to be the most efficient way to retrieve a value. You can do this through LINQ's DataContext.ExecuteQuery method and still get an IQueryable<T> back as a result.

public TEntity Load(object Pk)

{             

  string sql = "select * from " + this.TableInfo.Tablename + " where " + this.TableInfo.PkField + "={0}";

  return this.Context.ExecuteQuery<TEntity>(sql).SingleOrDefault();

}

This is easy and while it bypasses strong typing, this is actually a good solution for controlled queries that are dynamic. And it's more efficient to boot - there's no parsing the query into a SQL string since you get to do it yourself. The cool thing with this method is that it works against any type - entities or otherwise.

This only works with LINQ to SQL (and LINQ to Entities with entity Sql). Won't do you any good for LINQ to Objects.

Dynamic Query

Dynamic query is a sample that ships with Visual Studio (Help | Samples | Local Folder | CSharpSamples | DynamicQuery | DynamicQuery.cs) and it  provides overloads for several of the query operators like .Where that let you use string expressions rather than instance values.

Dynamic query is easy enough to use once you've added the source file to your project and System.Linq.Dynamic to your namespaces:

public TEntity Load(int pk)

{ 

        Table<TEntity> table = this.Context.GetTable(typeof(TEntity)) as Table<TEntity>;

        return table.Where(this.PkField + " == @0", pk).SingleOrDefault(); 

}

Dynamic method is a sample, and looking through the code there's a shitload of code using Reflection and expression parsing that executes to dynamically create these this string into a an expression. It's expensive in terms of CPU cycles expended to get the string into an expression.

Note that dynamic query overloads only the Where clause so the syntax is a little different - SingleOrDefault() isn't updated with the sting expression syntax.

Like the SQL Strings option using DynamicQuery doesn't give you strong typing on the strings obviously. But it does work with all flavors of LINQ so you can use it with LINQ to Objects.

Out of all the options this is the one I like least even though it's easy.

Use Lambdas instead of fixed Parameters

Several people suggested recreating the Load() method to take a Lambda instead as a parameter instead of a single value. This easy to do as well, but the method consumer has to know about Lamdas and maybe spend a few extra seconds thinking about what the Lambda parameters mean.

The implementation of this approach:

public TEntity Load(Func<TEntity,bool> predicate)

{

    Table<TEntity> table =  this.Context.GetTable( typeof(TEntity) ) as Table<TEntity>;

    return table.SingleOrDefault(predicate);

}

The input is passed as a lambda that matches the lamda expression that would be used for the actual call to the filter method - SingleOrDefault() in this case. That's easy enough - for the designer.

But the end user's experience  leaves something to be desired:

CustomerEntity entity = customer.Load( cust => cust.Pk == 3);

If one's comfortable with Lamdas I suppose this isn't terrible, but still writing this a bit more effort than a simple:

CustomerEntity entity = customer.Load(3);

I vastly prefer the latter syntax if for nothing else that everytime I have to write a Lamda I have to think about what the parameter input means.

[04/14/2008 - updated from comments. Thanks to Richard Deeming for catching my oversight and posting a sample. Incorporated here]

But there's a problem with this particular implementation if you use it with LINQ to SQL: The problem is by using a a simple Lambda delegate - rather than an Expression tree - LINQ to SQL will not be able to roll the expression into the final SQL statement but rather retrieve all records and filter on the client. Ooops, not really what we want here.

Rather what's required is that a LINQ Expression Tree gets created for the expression and that this value then gets used in the query. This results in the proper rolling of the expression into the final SQL parsing the LINQ to SQL performs.

Thanks to Richard Deeming for pointing out this issue and posting the Expression syntax. The following code is Richard's taken from the comments below and should be implemented on the business base class. Front end code stays the same as above.

protected Expression<Func<TEntity, object>> pkFieldExpression;

protected Expression<Func<TEntity, bool>> FilterByPk(object Pk)

{

    // ent => PkFieldExpression(ent) == Pk          

    ParameterExpression entity = Expression.Parameter(typeof(TEntity), "ent");

    Expression keyValue = Expression.Invoke(this.pkFieldExpression, entity);

    Expression pkValue = Expression.Constant(Pk, keyValue.Type);

    Expression body = Expression.Equal(keyValue, pkValue);

    return Expression.Lambda<Func<TEntity, bool>>(body, entity);

}

public virtual TEntity Load(object Pk)

{

    Table<TEntity> table = this.Context.GetTable<TEntity>();

    TEntity ent =  table.SingleOrDefault( this.FilterByPk(Pk) );

    this.Entity = ent;

    return ent;

}

This code basically requires that the Lambda is assigned as an Expression which is still assigned as:

protected override void Initialize()

 {

    base.Initialize();

    this.pkFieldExpression = cust => cust.Pk;

 }

But instead of directly executing the expression it's evaluated and returned as a parsed expression that describes the full predicate operation that is required for the filter conditions like Where and Single. The FilterByPk method handles this task and acts as the intermediary. This looks messy but remember in my situation this goes into the base business object and then never used again directly...

In general, using a lambda expression vs. a single value parameter on the load method also changes the behavior of the method - it essentially becomes filter function and no longer is a retrieve by pk function. This may not be desirable in some scenarios, but may actually provide more flexibility in others. Still - for a business level interface I don't think this is appropriate and the simpler syntax of just passing a Pk trumps it easily.

Storing and Reusing a Lambda Expression

Another similar approach that allows for using the simpler Load(x) syntax is: Rather than specifying the Lambda expression every time you call Load, the Lambda is assigned internally once when the object is initialized. This can be internal to the object and set up once initially when the business object is created. The lambda is then stored and rolled out in the Load method as needed.

Here's how this works. First there's a field on the business object base that holds the lambda - call it pkFieldExpression. This is declared in the base business object (wwBusinessObject for me):

protected Func<TEntity, object> pkFieldExpression;

Each concrete implementation instance class then sets pkFieldExpression  as part of its initialization code (say busCustomer):

protected override void  Initialize()

{

    base.Initialize();

    // *** Dynamic delegate for getting the Pk field instance

    this.pkFieldExpression = (CustomerEntity ent) => ent.Pk;

}

A Lambda is basically an anonymous delegate and this code creates this delegate with the Entity specific type applied and simply returns the field as a value.

With the expression set the Load method on the base business object can now be rewritten like this:

public TEntity Load(object Pk)

{            

    Table<TEntity> table =  this.Context.GetTable( typeof(TEntity) ) as Table<TEntity>;

    return table.SingleOrDefault(ent => this.pkFieldExpression == Pk);

}

And we're now back to being able to call the Load method with a single object parameter of int, string, guid or whatever else might work. Strong typing still works and we're doing it 'the LINQ way'.  The code is now generic in that that the business object can apply any type for a Pk (string, int, guid for example) and still use the simple Load(pk) syntax.

This latter approach of 'storing Lamdas away' is a good way to fake dynamic execution, but it takes a little getting used to. In fact, I looked cross-eyed at some of the code samples posted on my previous entry and tried to decipher the nested Lambda syntax. Actually what really helped me figure this out in the end was revisiting the Lambda chapter in LINQ in Action and staring at a bunch of different Lambda definitions again for a few minutes. Learning by absorption <g>...

Choices, Choices, Choices

Which choice works best depends on your scenarios. Personally I think I would choose the SQL string method if I do need dynamic SQL execution in generic framework level code which is the most common scenario where dynamic value and field assignments occur. Application level code should much less need to create dynamic expressions.

In addition using the SQL string approach is likely the most efficient since there's no query parsing involved just raw SQL. SQL String parsing though is limited to LINQ to SQL - LINQ to Objects and most other LINQ implementations don't have a way to turn strings into queries directly.

If using higher level code, explicit Lambdas are probably a good choice. In framework code cached Lambdas can provide some sort of dynamism without cluttering up the class's interface with potentially cryptic syntax.

This topic keeps coming up for me. I think this is the third time I've written about this now and something new comes up each time. Now if I can just unpretzel my mind from Lamda nesting sytnax. Sheesh this reminds me off doing pointer math in C <g>...