Dynamic Queries using LINQ – Part 1

A Dynamic SQL Query is one built at runtime. This is often used to generalize a simple query to cope with parameters that change the way data is filtered and ordered while some rules are fixed.
Take for instance this method:

1: public IList<Entry> Load(long blogId, int lastN, string sortBy, string sortOrder)

It returns a list of entries in a Blog where:

• lastN – list the last N entries in the Blog. If 0, then return all values.
• sortBy – sorts the entries by some field. If null return values unsorted.
• sortOrder – sets the order by which the list returned is sorted (ascending/descending)
• None of these arguments are mandatory. That is one can simply want a list of the last 10 entries without any kind of sorting or order.

Clearly a static query cannot cope with this, so we need to dynamically generate one.

With LINQ we can do the following:

1: public IList<Entry> Load(long blogId, int lastN, string sortBy, string sortOrder)

2: {

3:  

4:     var q = from entry in OB.Entities<Entry>() where entry.Blog.ID == blogId select entry;

5:  

6:     if (!StringUtils.IsNullOrEmpty(sortBy))

7:     {

8:         switch (sortBy)

9:         {

10:             case "Date":

11:                 if (StringUtils.IsNullOrEmpty(sortOrder))

12:                 {

13:                     q = q.OrderBy(entry => entry.Date); break;

14:                 }

15:                 else

16:                 {

17:                     switch (sortOrder)

18:                     {

19:                         case "asc": q =  q.OrderBy(entry => entry.Date); break;

20:                         case "desc": q = q.OrderByDescending(entry => entry.Date); break;

21:                     }

22:                 }

23:                 break;

24:             // more cases ...

25:         }

26:     }

27:  

28:     if (lastN >= 0) q = q.Take(lastN);

29:  

30:     return OB.Execute<IList<Entry>>(() =>

31:     {

32:         return q.ToList();

33:     });

34: }

Bellow is SQL generated by NHibernate when we call it with: blogId=1, sorBy=null and sortOrder=null.

1: SELECT top 10 this_._ID              as column1_1_1_,

2:               this_._Title           as column2_1_1_,

3:               this_._Body            as column3_1_1_,

4:               this_._PostDate        as column4_1_1_,

5:               this_._PublicationDate as column5_1_1_,

6:               this_._BlogID          as column6_1_1_,

7:               blog1_._ID             as column1_0_0_,

8:               blog1_._Name           as column2_0_0_,

9:               blog1_._Owner          as column3_0_0_,

10:               blog1_._PostDate       as column4_0_0_

11: FROM   BlogPost this_

12:        left outer join Blog blog1_

13:          on this_._BlogID = blog1_._ID

14: WHERE  blog1_._ID = 1 /* @p0 */

Naturally ORDER BY SQL keyword is not included in the query, neither is the sort order.
Bellow is SQL generated by NHibernate when we call it with: blogId=1, sorBy=”Date” and sortOrder=“asc”.

1: SELECT   top 10 this_._ID              as column1_1_1_,

2:                 this_._Title           as column2_1_1_,

3:                 this_._Body            as column3_1_1_,

4:                 this_._PostDate        as column4_1_1_,

5:                 this_._PublicationDate as column5_1_1_,

6:                 this_._BlogID          as column6_1_1_,

7:                 blog1_._ID             as column1_0_0_,

8:                 blog1_._Name           as column2_0_0_,

9:                 blog1_._Owner          as column3_0_0_,

10:                 blog1_._PostDate       as column4_0_0_

11: FROM     BlogPost this_

12:          left outer join Blog blog1_

13:            on this_._BlogID = blog1_._ID

14: WHERE    blog1_._ID = 1 /* @p0 */

15: ORDER BY this_._PostDate asc

Now the ORDER BY and the sort order keywords are there.
Next time I’ll explore ways build dynamically the where clause. Until then …
Stay cool.