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 Expressions in Linq to Sql


:P
On this page:

There are two things in LINQ to SQL that I've been fretting about quite a bit and it has to do with issues of getting stuck either by the possibilty of LINQ not being able to express a query and the fact that LINQ queries need to be expressed as cold, hard types that cannot easily be created dynamically.

The first scenario I haven't actually run into in my experiments directly although I can see it potentially happening. LINQ is statically typed so there's a limited set of SQL features that are actually supported through the LINQ language set. While common queries and joins may very work just fine, more complex queries that rely on internal functionality may have much less luck with.

For example try to express > and < in strings with LINQ. C# doesn't support < and > on strings so this fails:

var query11 = from c in context11.Customers
              where c.CompanyName > "D" && c.CompanyName < "F"
              select c;

Instead you have to resort to the somewhat less intuitive C# compatible syntax of:

var query11 = from c in context11.Customers
              where c.CompanyName.CompareTo("D") > -1  && c.CompanyName.CompareTo("G") < 1
              select c;

It works, but then again it produces an interesting query:

SELECT [t0].[CustomerID], [t0].[CompanyName], [t0].[ContactName], [t0].[ContactTitle], 
       [t0].[Address], [t0].[City], [t0].[Region], [t0].[PostalCode], [t0].[Country], 
       [t0].[Phone], [t0].[Fax] 
   FROM [dbo].[Customers] AS [t0] 
   WHERE (( (CASE WHEN [t0].[CompanyName] < @p0 THEN -1 WHEN [t0].[CompanyName] = @p0 THEN 0 ELSE 1 END)) > @p1) AND 
         (( (CASE WHEN [t0].[CompanyName] < @p2 THEN -1 WHEN [t0].[CompanyName] = @p2 THEN 0 ELSE 1 END)) < @p3)

Hoo boy.That might be just a little suboptimal if you run that against a largish database (which is probably the only reason you'd ever use string segmenting with < and > in the first place). So this might be a query you'd want to optimize.

But lets say you wanted to create a query that includes a function not supported - like say Rand() and newid() in SQL Server. How would you express an admittedly contrived query like this?

select CAST(Rand(CAST(CAST(newid() AS binary(4)) AS int)) * 10 as int) + 1 as Random,* from Customers 

C# has a random number function as well as Guid generation but you can't necessarily use it in a query.

Option 1 - Create a Stored Procedure or View

Ok so lets say you do hit the wall with some query. One option you have is to create a stored procedure or View that simplifies the result and input parameters significantly so that you can in fact get the results you want.

Stored procedures are certainly an option if you have the power to force change in the database. In many scenarios however that's not possible as Admins have the database locked down - you're just a slave working against the data and SP model provided.

If you can create a Stored Procedures or a view you can simply drag the stored procedure or view onto the LINQ designer and then consume the result. The Entity editor will automatically create a new type for the result set including the dynamic value:

NorthwindDataContext context11 = new NorthwindDataContext();
ISingleResult<RandomCustomersResult> res = context11.RandomCustomers();
 
foreach (RandomCustomersResult randomResult in res)
{
    Response.Write(randomResult.Random.ToString() + " " + randomResult.CompanyName + "<br/>");
 
}

And it works. It's one of the easier ways to create result sets that don't fit LINQ syntax. Rather than writing code on the client you can write it on the server and parameterize it as an SP or View. Assuming the code is complex enough that you can't express it with LINQ it's probably not a bad idea to have as an SP on the server in the first place.

If this option is available to you it's probably a good idea to take advantage of it as it's the easiest way to get around problem queries.

Option 2 - Create your own Sql Command Strings

Matt Warren posted a comment on a recent post I had about using a LINQ query to retrieve a DataReader instead of an IEnumerable list in order to improve databinding performance where perf is critical. I lamented that there was no matching method to do the opposite - take a SQL statement and turn it into an entity list.

So Matt also mentioned something I missed: You can create a LINQ result set by providing your own SQL command string using context.ExecuteCommand() or providing an existing DataReader with context.Translate().

This solves a problem for the > and < query shown above nicely. So I could express the > and < query much more simply:

string lowBound = "D";
string highBound = "G";
string sql = "select * from Customers where CompanyName > {0} and CompanyName < {1}";
 
var query11 = context11.ExecuteQuery<Customer>(sql, lowBound, highBound);

Now, this isn't the kind of thing you'll want to do unless you are otherwise stuck, but at least it gives the opportunity to work around data.

Couple of interesting things here. Above I'm using select * to grab all the fields and it appears that LINQ is doing the right thing grabbing each field and mapping it. Internally the DataReader assignment still works the same, looking for resulting field values to match property names. But the following works as well:

string sql = "select contactname,company from Customers where CompanyName > {0} and CompanyName < {1}";

Note that here I'm returning only contactname and company, but a Customer entity is still created and the two values are assigned so cust11.CompanyName still works. That's nice. You do need a concrete type however - you can't return a list of anonymous types so you either need to create an explicit result type for a specific non-entity query or use an entity type for the result list.

What's also interesting is that when you provide LINQ a query like this it still works with the DataContext's change tracking. For example, the following code actually works as you'd expect:

IEnumerable<Customer> custList =  context11.ExecuteQuery<Customer>("select * from Customers where CustomerId={0}", "ALFKI");
Customer cust11 = custList.Single<Customer>();
Response.Write(cust11.CompanyName);
 
cust11.CompanyName = "Alfreds Futterkiste " + DateTime.Now.ToString();
context11.SubmitChanges();
 
custList = context11.ExecuteQuery<Customer>("select * from Customers where CustomerId={0}", "ALFKI");
cust11 = custList.Single<Customer>();
Response.Write(cust11.CompanyName);

This code retrieves a single instance of an object unhooks, then makes changes to it and submits changes. Even though the Customer was loaded off a 'manual' query, change tracking works. Impressive - but something you have to be careful with. SubmitChanges works in this scenario as long as the primary key is part of the download list. If you try to update without the pk field set the update fails.

The following also works as well although it may seem like it shouldn't:

string sql = "select CustomerId,CompanyName,ContactName from Customers where CustomerId={0}";

IEnumerable<Customer> custList = context11.ExecuteQuery<Customer>(sql, "ALFKI");
Customer cust11 = custList.Single<Customer>();
Response.Write(cust11.CompanyName);
cust11.CompanyName = "Alfreds Futterkiste " + DateTime.Now.ToString("d");
context11.SubmitChanges();

In this code only a few fields are retrieved for the entities, but when you update LINQ only updates fields that have actually changed. Since CompanyName has been changed that change is written to the database, but all the NULL values for the fields that weren't retrieved don't have any effect. If you assign a value say to the Address field:

string sql = "select CustomerId,CompanyName,ContactName from Customers where CustomerId={0}";
IEnumerable<Customer> custList = context11.ExecuteQuery<Customer>(sql, "ALFKI");
Customer cust11 = custList.Single<Customer>();
Response.Write(cust11.CompanyName);
 
cust11.CompanyName = "Alfreds Futterkiste " + DateTime.Now.ToString("t");
 
// *** Field that wasn't retrieved above - still works!
cust11.Address = "Obere Strasse " + DateTime.Now.ToString("t");
 
context11.SubmitChanges();

the update still does the right thing. The Orders also come down via delayed loading and they can be updated as well by submit changes.

One thing to keep in mind is that ExecuteQuery<> requires a concrete type - I don't see how you can return an anonymous type from it so if you look back at the Random query I stuck in the stored procedure I'd have to create an explicit type for the result first:

public class RandomResult
{
    public int Random = -1;
    public string CompanyName = "";
    public string ContactName = "";
}

then use ExecuteQuery with the above type:

NorthwindDataContext context11 = new NorthwindDataContext();
 
string sql = "select CAST(Rand(CAST(CAST(newid() AS binary(4)) AS int)) * 10 as int) + 1 as Random," +
             "CompanyName,ContactName,CustomerId from Customers";

IEnumerable
<RandomResult> result = context11.ExecuteQuery<RandomResult>(sql);

foreach
(RandomResult res in result)
{
    Response.Write(res.Random.ToString() + " " + res.CompanyName + "<br/>");
}
 
return;

And that works as well.

All of this  provides a solution for the 'fear of the black box' syndrome I've been having with LINQ to SQL. Hitting the wall a long way into a project because you can't express a query would be a disaster - both of the above address this scenario to some degree. Going to string based SQL may not be the most elegant solution, but at least it will get you out of a tight spot. But more importantly it will allow framework level code to get access to dynamic code execution with SQL which is crucial at the framework level that knows nothing of your actual data model.

Dynamic Expressions - Manual Sql

The above actually also addresses the dynamic expressions issues scenario I mentioned a bit back. The issue is that LINQ requires hard typed expression on the left side of an expression. For example, it's difficult to create an expression that dynamically uses a field name in a SQL query to retrieve data. So in a generic business layer you may have a Load(int Pk) method that loads an individual entity which would be generic in the base business object class. So the simplified scenario is this where you have TEntity and TContext as a generic types on the base business object:

public TEntity Load(int pk)
{
        TContext context = this.CreateContext();
 
        Table<TEntity> table =  context.GetTable( typeof(TEntity) ) as Table<TEntity>;




string pkField = this.PkField;
        TEntity entity = table.Single( c =>  ??????  == pk)
 
        return entity;
}

The problem is how do you specify pkField as the expression on the left in the effort to retrieve this single entity.

So one way to solve this problem is to use a manual SQL statement which is probably the most efficient way:

public TEntity Load(int pk)
{
    try
    {
        TContext context = this.CreateContext();
  
        MetaTable metaTable = context.Mapping.GetTable(typeof(TEntity));
        string tableName = metaTable.TableName;
 
        string Sql = "select * from "  + tableName + " where " + this.PkField + "={0}";
        IEnumerable<TEntity> entityList = context.ExecuteQuery<TEntity>(Sql,pk);
 
        TEntity entity = entityList.Single();
        if (entity == null)
        {
            this.SetError("Invalid Pk");
            return null;
        }
 
        return entity;
    }
    catch (Exception ex)
    {
        this.SetError(ex);
        return null;
    }
 
    return null;
}

This works. Notice that I have the get the table name from the Mapping object which provides schema information for the database or rather its mapped types. This allows retrieving the tablename that the entity belongs to which is required for generic operation. The query retrieves a list rather than a single entity, so .Single() is used to convert the result into a single entity.

This code  could probably be optimized by retrieving the table name only once in the constructor or in a property get. But this ends up running an efficient query and it's fully dynamic. So here's a scenario where using a manual SQL statement actually makes sense.

Ok, so that works.

Dynamic Query

Another way - as was mentioned by several commenters in my previous post - is to use DynamicQuery. DynamicQuery is one of the samples installed with the 101 LINQ samples and you can find it by clicking on Help | Samples in Visual Studio. If you drill into the sample folders there's a DynamicQuery sample project, which basically consists of a class that provides string based lambda expression parsing.

The class DynamicQuery class is self contained and you can simply add it to your project. It provides additional extension methods that let you use string expressions for various of the query methods including the .Where() method (but unfortunately for the above example not the .Single() method). So with Dynamic Query the above .Load() method can also be written as follows:

 
public TEntity Load(int pk)
{
    try
    {
        TContext context = this.CreateContext();
  
        // *** using System.Linq.Dynamic Sample code                
        // ***  equivalent of: TEntity entity =  table.Single(c => c.Pk == pk);
        Table<TEntity> table = context.GetTable(typeof(TEntity)) as Table<TEntity>;
        List<TEntity> entityList = table.Where(this.PkField + " == @0", pk).ToList();
 
 
        TEntity entity = entityList.Single();
        if (entity == null)
        {
            this.SetError("Invalid Pk");
            return null;
        }
 
        return entity;
    }
    catch (Exception ex)
    {
        this.SetError(ex);
        return null;
    }
 
    return null;
}

This code is a little easier to read - it simply uses a string expression instead of a Lambda expression for the .Where() clause call, which allows injecting the dynamic Pk field name into the left side of the expression. The syntax here is different - for the manual SQL statement I was basically using T-SQL code, here I need to write a C# expression as a string. Not only that the expression must also evaluate into something that makes sense in LINQ to SQL. What comes out of the string .Where() is essentially the same c=> c.Pk == pk.

That's handy, but looking under the covers of dynamic query it's easy to see that there's a ton of code running to perform this kind of expression parsing. This akin to Reflection code only more resource intensive.

So using the .CreateQuery() method in the previous example is probably much more performant than the method using DynamicQuery, but DynamicQuery is a little more inline with LINQ's syntax.

The thing to remember is that these kind of dynamic expressions are probably not all that common in the front end or middle tier layers of an application where you do have access to the data model. They are most likely going to be found in framework level code and base classes where the actual data model is not directly accessible to the framework and so lots of fields and expression are going to be somewhat dynamic. In these scenarios performance is probably most important and so performance probably outweighs the 'politically correct' approach <g>... I'd venture to guess that using ExecuteQuery<> is considerably faster than using DynamicQuery for expression parsing since there's no query parsing to do for LINQ if you provide the query yourself.

Posted in ASP.NET  LINQ  Visual Studio  

The Voices of Reason


 

Rick Strahl
August 28, 2007

# re: Dynamic Expressions in Linq to Sql

Ah crap - talk about funky timing. Scott Gu has just posted an article on the same topic (actually last night). http://weblogs.asp.net/scottgu/archive/2007/08/27/linq-to-sql-part-8-executing-custom-sql-expressions.aspx#comments.

That'll teach me to keep up with my RSS feeds. Scott covers much of the same content but there's a little more detail here.

Ian Cooper
August 28, 2007

# re: Dynamic Expressions in Linq to Sql

Hi Rick,

If you want to use the Specification pattern to implement dynamic queries then I have an article about how to do that here:

http://iancooper.spaces.live.com/blog/cns!844BD2811F9ABE9C!451.entry

The final steps do involve some meta-programming (expression trees) but the advantage is that the queries are both type safe and can be used in-memory if you want to test against in-memory collections, switching to the Db at runtime.

Mike Taulty also has a useful set of articles on how to do dynamic queries:
http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/06/15/9489.aspx
http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/06/18/9490.aspx
http://mtaulty.com/CommunityServer/blogs/mike_taultys_blog/archive/2007/06/18/9492.aspx

but again we are delving into meta-programming

Rick Strahl
August 28, 2007

# re: Dynamic Expressions in Linq to Sql

@Mike - interesting but that applies more to LINQ than to LINQ to SQL. You can apply this against result sets once they've been returned from SQL but not into the SQL itself because there's no way to translate that.

Mike's link are good read - it's maybe the first dynamic code article/code I'm looking at that I actually 'get'. This stuff's a bit hairy to understand.

Steve from Pleasant Hill
August 28, 2007

# re: Dynamic Expressions in Linq to Sql

I don't know man, is this really a better mousetrap...?

Rick Strahl
August 28, 2007

# re: Dynamic Expressions in Linq to Sql

@Steve - I'm trying to find out <s>.

I have really mixed feelings, LINQ's CRUD stuff is pretty nice and relatively easy to work with. I have built my own hybrid entity layer some time back and I can tell you it saves a huge amount of time during development to not have to deal with CRUD issues. But it's not as consistent as I'd like it to be and I've been for a long time waiting to plug a more robust entity engine into it. And LINQ's implementation of the basic CRUD functionality is pretty nice and the code it generates is reasonably clean.

What I'm finding out now is with this dynamic stuff is that you can extend it fairly easily and still get the benefits of the entity creation and change tracking and that's a pretty big deal. This means it might be possible to plug this into existing frameworks without too much trouble - or as is the case for my own framework, to re-work it around LINQ (or possibly another entity engine) and migrate as much of the old framework code that is still relevant (a fair amount).

I'm by no means sold on LINQ - I think there are a number of gaping holes, but it's still worth exploring in some depth and see what can be done to wrap it up in framework code that can work around some of the holes. It appears to me that some of it is doable...

I'm kind of keeping this as the discovery diary - like it or not <g>. So there will be some missteps and I'm sure people will point of the errors in my ways as well. I think it's important to find the bad as well as the good. I tend to start out with the bad and work back into the good <shrug>...

Ian Cooper
August 29, 2007

# re: Dynamic Expressions in Linq to Sql

Rick,

Just to be clear. The comment was from me Ian, and my article is the first one, but I linked to Mike Taulty's articles too.

Rick Strahl
August 29, 2007

# re: Dynamic Expressions in Linq to Sql

Ooops... sorry. Right articles, wrong names. Sorry Ian. And many thanks for the links. They are very useful!!!

Vikram
August 31, 2007

# re: Dynamic Expressions in Linq to Sql

Interesting!. I have been reading ur posts for a while and really liking them. Keep the good work going

Jason Farrell
October 05, 2007

# re: Dynamic Expressions in Linq to Sql

Hey,
Rick - Great post, very helpful in clarifying and echoing some of my concerns with LINQ as well. One thing I wanted to point out that you didnt mention, if you have a foreign key relationship within the class and you dont include it in the SELECT list of the manual query submitChanges will not work.

Example:
Series
SeriesId PK
Name
StudioId FK

in the select you have to select both the studio and series ids.

SHSE
May 21, 2008

# re: Dynamic Expressions in Linq to Sql

Table<TEntity> table = context.GetTable(typeof(TEntity)) as Table<TEntity>; <= wrong

use instead:

ITable table = context.GetTable(typeof(TEntity));
IEnumerable<TEntity> entities = table.Cast<TEntity>();
. . .
var result = enttities.Where(p=> p.Name == "testname").Select(p => p.Name);

SHSE
May 21, 2008

# re: Dynamic Expressions in Linq to Sql

Sorry, i meaned:

Table<TEntity> table = context.GetTable(typeof(TEntity)) as Table<TEntity>; <= wrong

use instead:

ITable table = context.GetTable(typeof(TEntity));
IQueryable<TEntity> entities = table.Cast<TEntity>();
. . .
var result = enttities.Where(p=> p.Name == "testname").Select(p => p.Name);

pramod
July 22, 2008

# How to create dynamic Linq query in vb.net

below is Linq query which returns the New records which exist in dtFreshdata but not in dtSnapShot.
My question is that
How to generalize this code so that it works for all dataTables of different schema
in this query i hard coded column name but i want to construct it dynamically.
Constraint column can be more than one.


Dim objquery = From FreshData In dtFreshData.AsEnumerable() _
Group Join SnapShotData In dtSnapShot.AsEnumerable() _
On FreshData("ACCTID") Equals SnapShotData("ACCTID") Into HaveMatch = Any() _
Where Not HaveMatch _
Select ACCTID = FreshData("ACCTID"), ACCTNAME = FreshData("ACCTNAME"), ACCTADDRESS = FreshData("ACCTADDRESS")

Todd Kruse
July 28, 2008

# re: Dynamic Expressions in Linq to Sql

I have been trying to get a dynamic query to work that I saw on a different website, the logic is below:

using (AdventureWorksModel.AdventureWorksEntities dev = new AdventureWorksModel.AdventureWorksEntities())
{
string condition = "LastName==\"Kruse\" && FirstName==\"Todd\"";
var booga = dev.Contact.Where(condition);

foreach (AdventureWorksModel.Contact x in booga)
{
txtTitle.Text = x.MiddleName.ToString();
}

}

The problem comes right away after the query is executed. It doesn't recognize the LastName field. Here is the error message:
{"'LastName' could not be resolved in the current scope or context. Make sure that all referenced variables are in scope, that required schemas are loaded, and that namespaces are referenced correctly., near simple identifier, line 6, column 1."} System.Data.EntityException {System.Data.EntitySqlException}


HELP!
todd.kruse@amentra.com

Abhijit
December 27, 2010

# re: Dynamic Expressions in Linq to Sql

thanks for this nice article...
i have question..tha
i want to pass table name dynamically to the wcf service using linq to sql, so that it is possible to retrive the data according to passing table,
can u please answee....

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