LINQ – Dynamic Where Clause (and a little bit of structure)
Jason Heine on December 1, 2009 in C#, LINQSo, I came across the idea that I needed to come up with a way to dynamically set the where clause for a LINQ statement. There are many articles out there that give different examples of how to do this. Some show how you can pass in “string” values such as “id = 2” and then you can append your where clause this way.
Personally, I did not like any of these solutions. Granted my solution is not the first time it has been used, and I did not invent it, however I found that it works quite well and I would share with the world how to do this.
Not only am I going over how to do dynamic where clauses, I will be showing you a structure that I like to use when dealing with data and business in my applications. It provides an easy way to organize your code and when you need to make changes later, you only ever have to update a few spots.
Before we start coding and getting into the how’s, I want to make sure that you can follow along with me. To do that, we need to prepare some stuff.
If you don’t have the tools to follow along, then keep reading and you will get to the how it is done shortly.
Step 1:
We are going to create a database that we can shove a table into. I have SQL 2005 installed on my local machine.
I created a database called LINQTester, and added 1 table:
CREATE TABLE TestData
(
NameKey INT IDENTITY NOT NULL PRIMARY KEY,
FirstName VARCHAR(20),
LastName VARCHAR(20)
)
We don’t need millions or records in this table, just a couple to prove a point. If you want to test performance, you can insert those records later, but performance is not the key point here (even though there is not a performance issue to really be discussed).
I am using the following insert statements to add the data:
INSERT INTO TestData VALUES ('Jason', 'Heine')
INSERT INTO TestData VALUES ('Bubba', 'Jones')
INSERT INTO TestData VALUES ('William', 'Tell')
INSERT INTO TestData VALUES ('Willie', 'Wonka')
So, now we have our data, we need to setup our project in Visual Studio. I am using Visual Studio 2008.
Here is the folder structure we are going to end up with (yes, yes, it may seem like a lot, but I am organizing it this way for a purpose).
If you want to go ahead and create this structure, with empty files, that will work. I will explain what each file is used for along the way.
We are going to start with our DBML file. Since we only have 1 table, that is all we need to add. Once you have added your table to the DBML file, make sure you build your project (this will enable the designer stuff to generate for the table).
We are going to start with the CoreConnection.cs
The CoreConnection.cs file is used for building the IDbConnection object, which will be used to physically connect to the database.
I am not going into the details of why all this works, if you need to ask questions, please do so in the comments.
Contents of the CoreConnection.cs file:
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace DynamicLinqTester.DataService.Core
{
public class CoreConnection
{
public static IDbConnection GetConnection()
{
return GetConnection(ConfigurationManager.AppSettings["CoreDatabase"]);
}
public static IDbConnection GetConnection(string databaseName)
{
var builder = new SqlConnectionStringBuilder();
builder["Data Source"] = ConfigurationManager.AppSettings["DatabaseServer"];
builder["Integrated Security"] = true;
builder["Initial Catalog"] = databaseName;
builder["MultipleActiveResultSets"] = true;
IDbConnection iConnection = new SqlConnection(builder.ConnectionString);
return iConnection;
}
}
}
Once you have this, we are going to move onto the TestContextFactory.cs
The TestContextFactory.cs is just an initializer for your DataContext. This will be utilized in the BaseQuery.cs file.
Contents of TestContextFactory.cs
namespace DynamicLinqTester.DataService.Context
{
public class TestContextFactory
{
public static TestDataContext CreateContext()
{
return new TestDataContext(CoreConnection.GetConnection());
}
}
}
The next thing we want to do is setup our object. Our object will be used to pass the information back to the program, and we will map the data from our table to this object.
Let’s take a look at our BaseQuery.cs file. This will be what each Query class uses to initialize the data context and dispose of it when it is done.
using DynamicLinqTester.DataService.Context;
namespace DynamicLinqTester.DataService.Queries
{
public abstract class BaseQuery
{
public readonly TestDataContext CoreContext;
protected BaseQuery()
{
CoreContext = TestContextFactory.CreateContext();
}
~BaseQuery()
{
CoreContext.Dispose();
}
}
}
So, on to OTestTable.cs
Contents:
namespace DynamicLinqTester.Objects
{
public class OTestTable
{
public int Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
}
Now that we have our object, we need to create our “mapper”. The mapper is what we are going to use to say “Column 1” = “Property 1”. Doing it like this will mean you don’t need to use object initialization on your methods, and it will help reduce your code by a lot.
Open TestTableMap.cs (this is our mapper file)
Contents:
using System;
using DynamicLinqTester.DataService.Context;
using DynamicLinqTester.Objects;
namespace DynamicLinqTester.DataService.Mappers
{
public class TestTableMap
{
public static readonly Func<TestData, OTestTable> DataToObject = mapper =>
new OTestTable
{
FirstName = mapper.FirstName,
LastName = mapper.LastName,
Id = mapper.NameKey
};
}
}
Okay you may be wondering what all this means. If you do not understand the Func<> method, I would suggest reading up on it on MSDN. They will do a far better job then I could.
Again, the main purpose of this is to map the table to your local object.
Now, we want to move to the heart of this blog entry, the where clause.
Before we do that, look at the following LINQ query:
var query = from c in CoreContext.TestDatas
where c.LastName == "Heine"
select new OTestTable
{
FirstName = c.FirstName,
LastName = c.LastName,
Id = c.NameKey
};
This is how you would normally write your LINQ, you would have your where clause and then your object initialization. You can do it without the initialization, but you will end up with more code.
The mapper part get’s rid of the new OTestTable object. Doing so enables you to take multiple queries and use the same mapping without having to update lots of methods when you add/remove a column.
Now, what we want is something that looks like this:
return CoreContext.TestDatas.Where(whereClause)
.Select(TestTableMap.DataToObject)
.FirstOrDefault();
As you can see, we are replacing the object initialization with the TestTableMap.DataToObject. Now, what about the “whereClause”, what is that?
This is the apex of the blog entry.
Here is the full method with this LINQ statement (this goes in TestTableQueries.cs)
using System;
using System.Linq;
using System.Linq.Expressions;
using DynamicLinqTester.DataService.Context;
using DynamicLinqTester.DataService.Mappers;
using DynamicLinqTester.Objects;
namespace DynamicLinqTester.DataService.Queries.TestQueries
{
public class TestTableQueries : BaseQuery
{
public OTestTable GetTestCode(Expression<Func<TestData, bool>> whereClause)
{
return CoreContext.TestDatas.Where(whereClause)
.Select(TestTableMap.DataToObject)
.FirstOrDefault();
}
}
}
Okay, again you can see we are using the Func<> again, but also we are using the Expression<>. If you do not use the Expression<> LINQ will not know how to evaluate the where clause into a SQL statement, and you will get a SELECT * FROM Table with no where clause.
So, how do we call something like this?
Open up testMethodsEngine.cs
Now add the following code:
using DynamicLinqTester.DataService.Queries.TestQueries;
using DynamicLinqTester.Objects;
namespace DynamicLinqTester.Business.TestMethods
{
public class TestMethodsEngine
{
private readonly TestTableQueries QueryEngine;
public TestMethodsEngine()
{
QueryEngine = new TestTableQueries();
}
public OTestTable GetTestCode(string lastName)
{
return QueryEngine.GetTestCode(id => id.LastName == lastName);
}
}
}
We are using a simple lambda expression to say Table.Column[Value] = ‘value’
If you want to add multiple statements to your where clause you can make it look like this:
public OTestTable GetTestCode(string lastName, string firstName)
{
return QueryEngine.GetTestCode(id => id.LastName == lastName && id.FirstName == firstName);
}
Now, you control what data you bring back via your business layer. Your data layer no longer controls the business requirements. You give the business the ability to query how it needs to without dictating what the business needs to query.
Let’s open our program.cs and add some test methods:
Contents:
using System;
using DynamicLinqTester.Business.TestMethods;
using DynamicLinqTester.Objects;
namespace DynamicLinqTester
{
class Program
{
static void Main(string[] args)
{
CodeSearchTest();
Console.WriteLine(Environment.NewLine);
Console.ReadLine();
}
private static void CodeSearchTest()
{
var engine = new TestMethodsEngine();
DateTime startTime = DateTime.Now;
OTestTable code = engine.GetTestCode("Heine");
DateTime stopTime = DateTime.Now;
TimeSpan span = stopTime - startTime;
Console.WriteLine("Searching for Last Name 'Heine' in TestTable");
Console.WriteLine("Time in MS:" + span.TotalMilliseconds);
Console.WriteLine(code.FirstName);
}
}
}
Also, before we run this, we need to modify our app.config file to point to the proper database and server:
<configuration>
<appSettings>
<add key="DatabaseServer" value="SERVER"/>
<add key="CoreDatabase" value="LINQTester"/>
</appSettings>
</configuration>
When you execute your program you should see the following output:
Well, I hope you enjoyed this little bit of information. Feel free to ask me questions, or add comments.
Happy Coding!
