29 janeiro 2009

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.

Sem comentários:

Enviar um comentário