Home > LINQ > LINQ Dynamic Query

LINQ Dynamic Query

December 27, 2011 Leave a comment Go to comments

LINQ is a nice feature introduced in .Net 3.5 VS 2008 first. It is very effective because it can be used to query data from multiple sources.

LINQ queries are type safe which provides us lots of benefits like compile time error checking. When we use LINQ to SQL or Microsoft Entity Framework for data access we may need to create dynamic query based on different conditions. In such scenario we usually need to create queries concatenating strings and construct query strings on the fly.
To enable such a dynamic query working in .net Microsoft provides Dynamic Query Library which can be found here in http://msdn.microsoft.com/en-us/vstudio/bb894665.aspx

Also ScottGu writes a blog on how to use this in here http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

Bellow is a sample code

private IEnumerable<Order> GetOrdersSearchAdvanced(OrderAdvancedSearhViewModel advancedSearchOption, bool? enquiry)
        {
            IEnumerable<Order> orders;
            DateTime fromDate = DateTime.ParseExact(advancedSearchOption.FromDate, "dd.MM.yyyy", CultureInfo.InvariantCulture);
            DateTime toDate = DateTime.ParseExact(advancedSearchOption.ToDate, "dd.MM.yyyy", CultureInfo.InvariantCulture);
            var conditionQuery = "";

            if (!string.IsNullOrEmpty(advancedSearchOption.LeadName))
            {
                if (advancedSearchOption.Option1.ToUpper() == "AND")
                {
                    conditionQuery += " and NameGolf.Contains(@2)";
                }
                else if (advancedSearchOption.Option1.ToUpper() == "OR")
                {
                    conditionQuery += " or NameGolf.Contains(@2)";
                }
            }

            if (!string.IsNullOrEmpty(advancedSearchOption.Region))
            {
                if (advancedSearchOption.Option2.ToUpper() == "AND")
                {
                    conditionQuery += " and Region.Contains(@3)";
                }
                else if (advancedSearchOption.Option2.ToUpper() == "OR")
                {
                    conditionQuery += " or Region.Contains(@3)";
                }
            }

            orders = orderRepository.GetOrders(enquiry).Where("OrderDate>=@0 and OrderDate<=@1" + conditionQuery, fromDate, toDate, advancedSearchOption.LeadName, advancedSearchOption.Region);
            return orders;
        }

In order repository

public class OrderRepository
    {
private PAGSDataContext entities = new PAGSDataContext();

        public IQueryable<Order> GetOrders(bool? enquiry)
        {
            if (enquiry != null && enquiry.Value == true)
            {
                return entities.Orders.Where(x => x.Ordertype.ToUpper() == "E");
            }

            return entities.Orders.Where(x => x.Ordertype.ToUpper() == "O" || x.Ordertype.ToUpper() == "B");
        }
}
Advertisements
Categories: LINQ
  1. No comments yet.
  1. No trackbacks yet.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: