EntitySpaces and LINQ to SQL with multiple where statements
At my current workplace, we are using EntitySpaces a lot. I feel guilty but sometimes I am too lazy to write a stored procedure, so I rely on EntitySpaces dynamic queries. Usually, it involves a simple where statement, but sometimes there are multiple where statements based on a certain application logic condition. Let’s look at the example based on a table of orders:
| Id | Name | Price | Created |
|---|---|---|---|
| 1 | Viktar | 10.00 | 2010-01-02 |
| 2 | Viktar | 12.00 | 2010-02-02 |
| 3 | Alex | 15.00 | 2010-02-02 |
| 4 | Lucy | 11.00 | 2010-03-10 |
Let's say you need to filter this table by name and date, but filter values come from different sources. Something like this:
TestTableQuery testTableQuery = new TestTableQuery();
testTableQuery.Where(testTableQuery.Name == "Viktar");
// Some crazy logic here
// ...........
testTableQuery.Where(testTableQuery.Created < customDate);
So, what is the relation between the first and second where statements? Does the second one overwrite the first one? Apparently, the relation is T-SQL AND statement, but you can change it to OR clause as follow:
TestTableQuery testTableQuery = new TestTableQuery();
testTableQuery.Where(testTableQuery.Name == "Viktar");
// Some crazy logic here
// ...........
testTableQuery.es.DefaultConjunction = EntitySpaces.Interfaces.esConjunction.Or;
testTableQuery.Where(testTableQuery.Created < customDate);
I think this is a big advantage that you can do something like this and still get clean T-SQL without nested queries. Here is an output from SQL Server Profiler:
exec sp_executesql
N'SELECT [Id] AS ''Id'',[Name] AS ''Name'',[Price] AS ''Price'',[Created] AS ''Created''
FROM [TestTable] WHERE [Name] = @Name1 OR [Created] < @Created2'
,N'@Name1 varchar(6),@Created2 date',
@Name1='Viktar',
@Created2='2010-03-23'
Let’s try to solve the same task using LINQ to SQL:
using (CoPalletDataContext db = new CoPalletDataContext())
{
var q = from t in db.TestTables where t.Name == "Viktar" select t;
q = q.Where(t => t.Created < DateTime.Now);
q.ToList();
}
The code above produces the following T-SQL:
exec sp_executesql N'SELECT [t0].[Id], [t0].[Name], [t0].[Price], [t0].[Created] FROM [dbo].[TestTable] AS [t0] WHERE ([t0].[Created] < @p0) AND ([t0].[Name] = @p1)',
N'@p0 date,@p1 varchar(6)',
@p0='2010-03-23',
@p1='Viktar'
It looks similar to EntitySpaces output. But what if we want OR clause instead of a default AND clause? As far as I know, it is not possible. So you need to do something like this:
using (CoPalletDataContext db = new CoPalletDataContext())
{
var q = from t in db.TestTables where t.Name == "Viktar" || t.Created < DateTime.Now select t;
if (true) // Some crazy logic here
{
q = from t in db.TestTables where t.Name == "Viktar" || t.Created > DateTime.Now select t;
}
q.ToList();
}
In other words, rewrite the whole query according to your condition.
Dynamic queries are good and save you a lot of time, but it might be challenging for a DBA person to optimize them. EntitySpaces queries or LINQ to SQL should be used only for simple queries. If you have more complicated logic put it in a stored procedure.