Rick Strahl's Weblog  

Wind, waves, code and everything in between...
.NET • C# • Markdown • WPF • All Things Web
Contact   •   Articles   •   Products   •   Support   •   Advertise
Sponsored by:
West Wind WebSurge - Rest Client and Http Load Testing for Windows

Dynamic Queries and LINQ Expressions


:P
On this page:

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>...

Posted in .NET  ADO.NET  LINQ  

The Voices of Reason


 

Richard Deeming
April 14, 2008

# re: Dynamic Queries and LINQ Expressions

By storing the pkFieldExpression as a Func<TEntity, object> and passing a Func<TEntity, bool> to the SingleOrDefault method, you'll be retrieving all of the entities and filtering them on the client-side. If you store it as an Expression<Func<TEntity, object>>, you can move the filtering to the server-side.

Also, if your base class is abstract, I would be inclined to make the pkFieldExpression an abstract property, just in case you ever forget to set the field.

public abstract class wwBusinessObject<TEntity> where TEntity : class
{
    protected abstract Expression<Func<TEntity, object>> pkFieldExpression
    {
        get;
    }
    
    protected Expression<Func<TEntity, bool>> FilterByPk(object Pk)
    {
        // ent => PkFieldExpression(ent) == Pk
        
        Expression 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 TEntity Load(object Pk)
    {
        Table<TEntity> table = this.Context.GetTable<TEntity>();
        return table.SingleOrDefault(this.FilterByPk(Pk));
    }
}

public class busCustomer : wwBusinessObject<CustomerEntity>
{
    protected override Expression<Func<CustomerEntity, object>> pkFieldExpression
    {
        get { return ent => ent.Pk; }
    }
}

Richard Deeming
April 14, 2008

# re: Dynamic Queries and LINQ Expressions

As a further option, if you're using standard LINQ to SQL objects, you can retrieve the primary key property using LINQ to Reflection and build the expression from that:

public static class GenericController<TEntity> where TEntity : class
{
    private static readonly PropertyInfo primaryKey = 
        (from p in typeof(TEntity).GetProperties()
         where p.GetIndexParameters().IsNullOrEmpty()
         from a in p.GetCustomAttributes(typeof(ColumnAttribute), true)
         where ((ColumnAttribute)a).IsPrimaryKey
         select p).FirstOrDefault();
    
    protected static Expression<Func<TEntity, bool>> FilterByPk(object Pk)
    {
        Expression entity = Expression.Parameter(typeof(TEntity), "ent");
        Expression keyValue = Expression.Property(entity, primaryKey);
        Expression pkValue = Expression.Constant(Pk, keyValue.Type);
        Expression body = Expression.Equal(keyValue, pkValue);
        return Expression.Lambda<Func<TEntity, bool>>(body, entity);    
    }
    
    public static TEntity Load(DataContext context, object Pk)
    {
        Table<TEntity> table = context.GetTable<TEntity>();
        return table.SingleOrDefault(FilterByPk(Pk));
    }
}

Richard Deeming
April 14, 2008

# re: Dynamic Queries and LINQ Expressions

Whoops! "Expression entity = ..." needs to be either "ParameterExpression entity = ..." or "var entity = ...", otherwise you'll get a compiler error.

Steve
April 14, 2008

# re: Dynamic Queries and LINQ Expressions

I guess I'm dubious as to the value of LINQ in a commercial production environment, with multiple developers wrangling with this syntax, mixing approaches, and so forth. Perhaps I am an old dog...

Rick Strahl
April 14, 2008

# re: Dynamic Queries and LINQ Expressions

@Richard - thanks for catching this. I actually checked this out last night on my flight back to the mainland and saw that the queries were indeed pulling down all the data and realized that expression usage is indeed required for LINQ to SQL. I mucked around with the expression syntax for a while (cut off from the Web <s>), but you beat me to it.

I've updated the post above with additional comments.

Thanks again Richard!!!

@Steve - yes, there are things to think about, but there are two things to consider in regards to this: This particular issue of dynamic right side expressions should be fairly rare, so this shouldn't be required often.

You do have the override option for using raw SQL strings which basically gives you the same capabilities as ADO.NET would. So you're not losing anything if you hit a road block somewhere. This is highly underrated in the rush to get strong typing to everything, but you can still run an untyped query per string AND even still retrieve (optionally) a typed result set.

Posts like this are pointing out things to think about and getting a feeling how to work around these issues, so there are some things that are arcane <s>. I certainly don't feel comfortable looking at the Expression syntax that Richard posted - in fact I'm glad he posted it. It would have taken me probably another hour to figure it out experimenting with the syntax. But that's to be expected with a new technology. This stuff will serve not only in LINQ to SQL but LINQ in general but it takes time to absorb and understand some of the power that sits behind these tools...

Adnan Khan
April 15, 2008

# re: Dynamic Queries and LINQ Expressions

What I see is lot of fancy ways of producing SQL statements. I have nice dal built that I use in all projects. I saw LINQ to Objects as more of a value compared to LINQ to SQL. My dal has no problem in returning list of entities. Many times I ran into issues in joining company and employees list or summarizing a list and here what I am thinking the power of LINQ comes in for me. Basically, i think it will sit on top of my dal or work together with dal layer. I do like the SQL syntax and take full advantage of server capabilites to return the right data and one the data in app domain LINQ can assist in any further data manipulaions. On should always know what the entity primary keys are on the app level. They should be strognly typed and enforces like on the database so developers do not make mistake or worst spend time trying to guess it.

Rick Strahl
April 16, 2008

# re: Dynamic Queries and LINQ Expressions

@Adnan - you're missing the whole point: this is *not* the app level, this is the framework level where there is generic code that has to figure out certain things like the primary key and say a timestamp field dynamically...

And yes this stuff is important. You may not need it often but when you do it's crucial that you can turn a dynamic expression in the required LINQ or possibly SQL String expression.

Josh Heyse
April 21, 2008

# re: Dynamic Queries and LINQ Expressions

Rick,

Excellent article. I think LINQ is great, but it becomes even more powerful once you can append dynamic Lambdas which get compiled to type-safe code & parameterized SQL queries. I have been experimenting with Dynamic Queruies as well, specifically around the Dynamic Data Modules in the ASP.NET 3.5 Extensions library.

You can view some of my contributions to the Dynamic Data components here:
http://blogs.catalystss.com/blogs/josh_heyse/archive/tags/Dynamic+Data/default.aspx

p_romm
September 18, 2008

# re: Dynamic Queries and LINQ Expressions

Good, good, it's really helps me.
But now for me this solution work good:

// you need to define interface
public interface IBaseEntity
{
    int pkField
    {
        get;
    }
}

// when you generate Entities with SQLMetal you can set base class to IBaseEntity or you can add this manualy in partial class
public partial class MyEntity: IBaseEntity
{}

// your generic class need to know that type is implementing IBaseEntity
public class Loader<TEntity> where TEntity : IBaseEntity
{
    public TEntity Load(int pk)
    {
        using (DataContext dc = new DataContext(""))
        {
            IQueryable<TEntity> entities = from p in dc.GetTable<TEntity>()
                                           where p.pkField == pk
                                           select p;
            TEntity entity = entities.SingleOrDefault();

            //...


        }
    }
}


but it don't work with:
TEntity entity = dc.GetTable<TEntity>().SingleOrDefault( p=> p.pkField == pk);

Sakito
September 30, 2008

# re: Dynamic Queries and LINQ Expressions

I'd like to add to the "SQL string" approach the way to retreive the name of the table PK column using the table type generated by LINQ to SQL:

MetaTable tableMetaData = _dataContext.Mapping.GetTable(TTableType);
string tablePKColumn;

if(tableMetaData.RowType.DBGeneratedIdentityMember != null)
{
tablePKColumn = tableMetaData.RowType.DBGeneratedIdentityMember.MappedName;
}
else
....

Thanks for you post,

Sakito

JP
April 06, 2009

# re: Dynamic Queries and LINQ Expressions

Is there a way to use the Like clause in the Dynamic Query Library ?

Eg. in Linq I use the following command :

Dim q = From test In db.Customers _
Where test.City Like "?Sa*" _
Select test


while when using the Dynamic Queries I use the following command

Dim q = db.Customers.Where("City like @0)", "?Sa*")

Rick Strahl
April 06, 2009

# re: Dynamic Queries and LINQ Expressions

@JP - you can use .StartsWith(), .EndsWith() and .Contains() but full like clause parsing is not supported I think.

Not everything translates into LINQ and that's why this layer supports raw queries for one. The other option is setting up a stored proc to handle queries that require queries that can't be expressed.

Michael Muehl
August 28, 2009

# re: Dynamic Queries and LINQ Expressions

Hey Rick,

Great article. In your last comment you mentioned using .Contains(). Unfortunately, .Contains() is not implemented in LINQ to Entities until .NET 4.0. It works with LINQ to SQL, but not LINQ to Entities. If you try, you will get:

LINQ to Entities does not recognize the method 'Boolean Contains(System.[ListTypeHere])'


I haven't had much trouble building my dynamic Where and OrderBy clauses. What I actually seem to be struggling with is building dynamic Select statements.

For example, lets say I want to link a set of tables to a grid - but I want the end user to be able to select which columns to display. With lots of columns and lots of data, I obviously want to be able to limit which columns from the db get pulled back, too, and not just which rows. Any thoughts?

Thanks.

Michael Muehl
August 28, 2009

# re: Dynamic Queries and LINQ Expressions

Edit - the exception is a run-time exception and it's a nasty looping exception. .Contains will not throw a compile-time error using LINQ to Entities.

Rick Strahl
August 28, 2009

# re: Dynamic Queries and LINQ Expressions

Re: contains() on the Entity framework. You REALLY have to wonder how the FUCK Microsoft can design a query language provider that doesn't support something as essential as .Contains(). They've only had 5 years to get to the EF.

Jeff French
October 15, 2009

# re: Dynamic Queries and LINQ Expressions

I'm using a similar approach to create an expression tree to access a property that is not known at compile time. I ran into a wall when trying to build an expression tree that access a property of a property (if that makes any sense at all).

Here's what I'm trying to accomplish:

public class Customer
{
     public string Name {get; set;}
}

public class Order
{
     public string CouponCode {get; set;}
     public Customer ThisCustomer {get; set;}
}

public MyClass<T> //T will be Order
{
    public void BuildExpression()
    {
        //Logic to determine the property to get has been omitted.
        // In this case the property returned is Order.CouponCode
        PropertyInfo propInfo = typeof(T).GetProperties()[0];       

        //The code below will get me the equivilent of 
        // (o => o.CouponCode)
        ParameterExpression param = Expression.Parameter(typeof(T), "c");        
        var expression =
                    Expression.Lambda<Func<T, object>>(
                        Expression.Convert(
                            Expression.Property(param, propInfo.Name),
                                typeof (object)),
                                    param);
    }

    public BuildComplexExpression()
    {
         //Logic to determine the property to get has been omitted.
        // In this case the property returned is Order.Customer
        PropertyInfo propInfo = typeof(T).GetProperties()[0];

        //Logic to determine what property of Customer to get has been omitted.
        //In this case the property returned will be Customer.Name
        PropertyInfo subPropInfo = typeof(propInfo.PropertyType).GetProperties()[0];

        //Here's where I'm lost: I need an expression tree like the
        //one in the method above for (o => o.Customer.Name)
      
    }
}


Any help that you can provide will be greatly appreciated.

Thanks,
Jeff French

Jeff French
October 15, 2009

# re: Dynamic Queries and LINQ Expressions

Oops! In my comment above I meant to say that I was trying to create an expression tree for (o => o.ThisCustomer.Name)

Jeff French
October 19, 2009

# re: Dynamic Queries and LINQ Expressions

Well, once I took the time to break the statement down logically I realized the answer to my own question! :)

If any one needs the info it's on by blog.

http://geekindulgence.com/post/2009/10/19/LINQ-Expression-tree-to-access-a-deep-loaded-property.aspx

Maksym Kozlenko
September 07, 2010

# re: Dynamic Queries and LINQ Expressions

Complete working example how to pass Linq expression as a function parameter could be found here:

http://www.kozlenko.info/blog/2009/12/08/passing-object-properties-as-a-parameter-using-linq-expressions/

West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2024