Thursday, June 5, 2008

Piece By Piece Query Building With Linq

In numerous projects in the past, I've found myself needing to write code and build sql queries that filtered results based on one or more criteria and any combination of criteria. I've also needed to write code that returns extra parts of an entity (typically involving the joining of tables, but only under certain conditions.

In this post, I'm assuming that you know at least the very basics about Linq - how to create a DataContext class from a the Linq to SQL designer, select from it's tables and filter the results.

In the past, I have done a few things to solve this type of problem. I would create queries for each scenario, which resulted in a maintenance headache due to the numerous code changes every time a small change was introduced, and of course, more places to mess up. I also tried formulating my stored procedures to return different result sets depending on boolean parameters passed in. This, of course proved to be non-intuitive and error prone due to the variation in the type of data that was coming back. As a result, I felt I always had to choose between a lot of code, or non-intuitive code.

Linq to SQL - Building Queries Piece By Piece

Thanks to expression building capabilities of Linq, we can now build our queries one piece at a time without having to do tricky dynamic sql building while avoiding the caveats of the aforementioned approaches.

An example of a case when you would want to do this sort of thing would be (using an annoying Northwind database example) if you wanted to search for Orders based on any combination of criteria that you allow (e.g. searching by OrderDate, Freight, ShipCountry etc.) and possibly populating the order details of each order.

Traditionally, this would prove to be a bit of a challenge due to the number of queries or complexity of the query you would write to accomplish this, not to mention all of the CRUD code you would have to write. Thanks to Linq, we can now accomplish this in a matter of a handful of lines.

Writing Your Query Piece By Piece

So how is this done? As you create your queries, instead of returning result sets, you are returning an expression tree. An 'expression tree' can be thought of as a query builder in the sql sense. When you are writing your 'select statements' in your code, Linq is storing what it needs to know to query the database when you finally are ready to get the results back.

This means, using the Northwind orders example above, we can just keep defining our Order query piece by piece in a very natural way and we can know that when we are hitting the database, we are getting back exactly what we want.

Here's an example of how you might query Orders in a Northwind database using this 'piece by piece' query approach:





using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using System.Data.Linq;

 

namespace NorthwindLinqExample

{

    public partial class NorthwindQuery : Form

    {

        public NorthwindQuery()

        {

            InitializeComponent();

        }

 

        private void btnSearch_Click(object sender, EventArgs e)

        {

 

            using (NorthwindDataContext database = new NorthwindDataContext())

            {

                // get the query for all orders

                var orderQuery = from o in database.Orders select o;

 

                // checks to see if you want to filter by order date

                if (this.chkUseOrderDate.Checked)

                {

                    orderQuery = filterOrderQueryByOrderDate(orderQuery);

                }

 

                // checks to see if you want to filter by freight

                if (this.txtFreight.Text != String.Empty)

                {

                    orderQuery = filterOrderQueryByFreight(orderQuery);

                }

 

                // checks to see if you want to filter by shipping country

                if (this.cmbShipCountry.Text != String.Empty)

                {

                    orderQuery = filterOrderQueryByShipCountry(orderQuery);

                }

 

                // checks to see if you want to load order details also

                if (this.chkLoadOrderDetails.Checked)

                {

                    setQueryToLoadOrderDetails(database);

                }

 

                // sets the results of the query as the data source on our data grid

                this.gvResults.DataSource = orderQuery.ToList();

            }

        }

 

 

        private void setQueryToLoadOrderDetails(NorthwindDataContext database)

        {

            var loadOptions = new DataLoadOptions();

            loadOptions.LoadWith<Order>(o => o.Order_Details);

            database.LoadOptions = loadOptions;

        }

 

        private IQueryable<Order> filterOrderQueryByOrderDate(IQueryable<Order> orderQuery)

        {

            var filterValue = this.dateTimeOrderDate.Value.Date;

 

            return from o in orderQuery

                   where o.OrderDate >= filterValue &&

                   o.OrderDate <= filterValue.AddDays(1)

                   select o;

        }

 

        private IQueryable<Order> filterOrderQueryByFreight(IQueryable<Order> orderQuery)

        {

            var filterValue = this.txtFreight.Text;

 

            return from o in orderQuery

                   where o.Freight == filterValue

                   select o;

        }

 

        private IQueryable<Order> filterOrderQueryByShipCountry(IQueryable<Order> orderQuery)

        {

            var filterValue = this.cmbShipCountry.Text;

 

            return from o in orderQuery

                   where o.ShipCountry == filterValue

                   select o;

        }

    }

}

2 comments:

Kunal Mehta said...

vLinq is really Great Tool. By using this tool easily create linq query.

I came to know about this tool from
the blog http://www.elevatesoftsolutions.in/post/2008/11/01/LINQ-Query-Builder-Tools.aspx

In this blog one more LINQ Query Builder Tool is mentioned that is LinqPad.

Some demerits are in LinqPad that I came to know after using it. Thus I prefer to use vLinq. Good thing about this that It add-in the VS and query generate can be reused.

Really Great Tool

---
KUnal MEhta

The Blog have Fun n FUN
http://360by2.blogspot.com

Dave Maul said...

great post -- helpful, thank you!