Dynamically create a LINQ query


LINQ (Language INtegrated Query) is one of the most powerful new features coming with C# 3.0, that allow to compose queries and access data through your programming language. LINQ has different benefits, and one of these is, for sure, to write type-safe queries using C# or Visual Basic (also with the help of the Visual Studio Intellisense).

But, there is always a “but”, sometimes also the coolest feature cannot be what we’re actually looking for.

One drawback on working with Linq could be that you need to build your query dinamically… but you can’t.

I was searching a bit around the net and in the C# 4.0 documentation/samples, I found a nice set of extension methods that allow Linq to overtake this problem: the LINQ Dynamic Query Library (actually searching a little bit better I found that this library was included also in the VS2008 SP1 examples, but this is another story Winking smile).

By importing this library in our projects we’ll be able to write a code like this:

   1: NorthwindContext nwContext = new NorthwindContext();
   2: var prods = from p in nwContext.Products
   3:             .Where("CategoryID=2 And UnitPrice>3")
   4:             .OrderBy("SupplierId");
   5: ...

and this will work in the same way of the following snippet:

   1: NorthwindContext nwContext = new NorthwindContext();
   2: var prods = from p in nwContext.Products
   3:             where p.CategoryId == 2 && p.UnitPrice > 3
   4:             orderby p.SupplierId
   5:             select p;
   6:  
   7:         ...

So.. what is the point? Nerd smile

Quite simple… Let’s try to figure out if we need to filter a set of data but we don’t know yet how to do it, maybe cause we need some input from the user, like in most business analysts applications. Using this library we will be able to take advantage of all LINQ’s features and power without loosing flexibility and usability.

Ok, ok I know.. this looks too much general and also a “bit” marketing oriented.

An actual scenario where this library is really useful, it’s when you’re working with the jQuery Grid plugin and a Asp.Net MVC (or Asp.Net website).

In the following little javascript snippet you can see how to put a jQuery Grid in your website:

   1: <script type="text/javascript">
   2:     jQuery(document).ready(function(){ 
   3:       jQuery("#list").jqGrid({
   4:         url:'/MyData/GridExample/',
   5:         datatype: 'json',
   6:         mtype: 'GET',
   7:         colNames:['Id',... ... ],
   8:         colModel :[
   9:           {name:'Id', index:'Id', width:40, align:'left' },
  10:           ...
  11:           ...],
  12:         pager: jQuery('#pager'),
  13:         rowNum:10,
  14:         rowList:[5,10,20],
  15:         sortname: 'Id',
  16:         sortorder: "desc",
  17:         viewrecords: true,
  18:         imgpath: '/scripts/themes/custom/images',
  19:         caption: 'Grid Test'
  20:       }); 
  21:     }); 
  22: </script>

and here is the part of the GridExample method where the use of the Linq Dinamiyc Query Library is really useful:

   1: public ActionResult GridExample (string sidx, string sord, int page, int rows)
   2: {
   3:     MyTextContext context = new MyTextContext();
   4:     int pageIndex = Convert.ToInt32(page) - 1;
   5:     int pageSize = rows;
   6:     int totalRecords = context.Count();
   7:     int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);
   8:     var testEnts = context.OrderBy(sidx + " " + sord).Skip(pageIndex * pageSize).Take(pageSize);
   9:  
  10:     List<string[]> lista = new List<string[]>();
  11:     foreach (TestEntity te in testEnts)
  12:     {
  13:         string[] lstr = new string[8];
  14:         lstr[0] = te.IdEntity.ToString();
  15:         ...
  16:         string str;
  17:         if(te.MyNullableDate.HasValue)
  18:             str = te.MyNullableDate.Value.ToShortDateString();
  19:         else 
  20:             str = String.Empty;
  21:         ...
  22:         lista.Add(lstr);
  23:     }
  24:  
  25:     var jsonData = new
  26:     {
  27:         total = totalPages,
  28:         page = page,
  29:         records = totalRecords,
  30:         rows =  (from l in lista
  31:             select new
  32:             {
  33:                 i = l[0],
  34:                 cell = l
  35:             }).ToArray()
  36:     };
  37:     return Json(jsonData);
  38: }

I’m testing now this library and I’m really looking forward to see it integrated in the next LINQ realease.

 

Advertisements

About palutz

Ex-MSFT SDE. Still a freak geek curious about everything. Trying to keep my mind open and to run my own start-up: http://pastesoft.com View all posts by palutz

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: