New “Cube”: new post, new blog engine, new website version.

My new post on this new blog engine (after Microsoft decided to shutdown Live Spaces), is about a new version of my company website (also if we’re still in beta, that means we’re trying to do something in the spare time).

So, enjoy it!

Technorati Tags: PasteSoft,website,company

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 ).

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?

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.

 

Technorati Tags: LINQ Dinamic Query Library,LINQ,jQuery Grid