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 stored procedure, so I rely on EntitySpaces dynamic queries. Usually it involves simple where statement, but sometimes there are multiple where statements based on certain application logic condition. Lets look at example based on table of orders as follow:

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 relation between first and second where statement? Does second one overwrites first one? Apparently 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();
 }

Code above produces 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 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 whole query according to your condition.

Dynamic queries are good and save you a lot of time, but it might be challenging for DBA person to optimize it. EntitySpaces queries or LINQ to SQL should be used only for simple queries. If you have more complicated logic put it in stored procedure.


Posted on Tuesday, March 23, 2010 by | Add Comment

New Comment

Your Name:
Email (for internal use only):
Comment:
 
Code above:

Categories

Recent Tweets

  • Simon Ince's Blog: Hierarchies with HierarchyID in SQL 2008 http://t.co/xSDwiF6rRS.
  • Visual Studio 2010 WAS painfully slow - CodeProject http://t.co/Usba1x6CZy

Valid HTML5