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.