where can I find a good example of using linq & la

Question

where can I find a good example of using linq & lambda expressions to generate dynamic sql? For example, I need a method to take these parameters

GoupOperator ('And' or 'OR')
A list of objects each with the following parameters:
    SearchColumn.
    SearchValue.
    SearchOperator (equals, contains, does not equal ...)

And another method to orderby any particular column ascending or descending

If this question has been properly answered before , I will gladly delete it - none of previous answers Ive seen are comprehensive enough for a person new to linq expressions to plug into an existing application with little trouble --thanks

Answer 1

I found a couple of linq extension methods (generic Where and OrderBy methods written by Ilya Builuk that take the columnname, search value and search operations plus a grouping operator) on codeproject here that shows how to do this using asp.net mvc. the methods construct a dynamic expression tree -very elegant solution. Since I had started using a traditional asmx web service, I used his helpers in my project and just made a few changes to get it running here -

Here are the 2 methods

public static class LinqExtensions
{
    /// <summary>Orders the sequence by specific column and direction.</summary>
    /// <param name="query">The query.</param>
    /// <param name="sortColumn">The sort column.</param>
    /// <param name="ascending">if set to true [ascending].</param>
    public static IQueryable<T> OrderBy<T>(this IQueryable<T> query, string sortColumn, string direction)
    {
        string methodName = string.Format("OrderBy{0}",
            direction.ToLower() == "asc" ? "" : "descending");

        ParameterExpression parameter = Expression.Parameter(query.ElementType, "p");

        MemberExpression memberAccess = null;
        foreach (var property in sortColumn.Split('.'))
            memberAccess = MemberExpression.Property
               (memberAccess ?? (parameter as Expression), property);

        LambdaExpression orderByLambda = Expression.Lambda(memberAccess, parameter);

        MethodCallExpression result = Expression.Call(
                  typeof(Queryable),
                  methodName,
                  new[] { query.ElementType, memberAccess.Type },
                  query.Expression,
                  Expression.Quote(orderByLambda));

        return query.Provider.CreateQuery<T>(result);
    }


    public static IQueryable<T> Where<T>(this IQueryable<T> query,
        string column, object value, WhereOperation operation)
    {
        if (string.IsNullOrEmpty(column))
            return query;

        ParameterExpression parameter = Expression.Parameter(query.ElementType, "p");

        MemberExpression memberAccess = null;
        foreach (var property in column.Split('.'))
            memberAccess = MemberExpression.Property
               (memberAccess ?? (parameter as Expression), property);

        //change param value type
        //necessary to getting bool from string
        ConstantExpression filter = Expression.Constant
            (
                Convert.ChangeType(value, memberAccess.Type)
            );

        //switch operation
        Expression condition = null;
        LambdaExpression lambda = null;
        switch (operation)
        {
            //equal ==
            case WhereOperation.Equal:
                condition = Expression.Equal(memberAccess, filter);
                lambda = Expression.Lambda(condition, parameter);
                break;
            //not equal !=
            case WhereOperation.NotEqual:
                condition = Expression.NotEqual(memberAccess, filter);
                lambda = Expression.Lambda(condition, parameter);
                break;
            //string.Contains()
            case WhereOperation.Contains:
                condition = Expression.Call(memberAccess,
                    typeof(string).GetMethod("Contains"),
                    Expression.Constant(value));
                lambda = Expression.Lambda(condition, parameter);
                break;
        }


        MethodCallExpression result = Expression.Call(
               typeof(Queryable), "Where",
               new[] { query.ElementType },
               query.Expression,
               lambda);

        return query.Provider.CreateQuery<T>(result);
    }
}

Below is how I used these methods, the return object is simply a custom object that supplies data to a client side grid

public class Service1 : System.Web.Services.WebService
{
    [WebMethod]
    [ScriptMethod(ResponseFormat = ResponseFormat.Json)]
    public JgGrid SearchGrid(int rows, int page, string sidx, string sord,string filters)
    {
        AdvWorksDataContext dc = new AdvWorksDataContext();
        JavaScriptSerializer serializer = new JavaScriptSerializer();
        filters f = serializer.Deserialize<filters>(filters);

        var p = dc.vProductAndDescriptions.AsQueryable();
        if (f.groupOp == "AND")
            foreach (var rule in f.rules)
                p = p.Where<vProductAndDescription>(
                    rule.field, rule.data,
                    (WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op)
                    );
        else
        { 
            //Or
            var temp = (new List<vProductAndDescription>()).AsQueryable();
            foreach (var rule in f.rules)
            {
                var t = p.Where<vProductAndDescription>(
                    rule.field, rule.data,
                    (WhereOperation)StringEnum.Parse(typeof(WhereOperation), rule.op)
                    );
                temp = temp.Concat<vProductAndDescription>(t);
            }
            p = temp;
        }
        p = p.OrderBy<vProductAndDescription>(sidx, sord);

        return new JgGrid(page, p, rows);
    }
}

Answer 2

For cases where I have many columns that need dynamic query composition I use Dynamic Linq. This is a library written as an example for .net 3.5 and it illustrates how you can write linq extensions that operate on the expression tree.

It can also be used for composing dynamic queries based on strings received from the client, such as column names, sorting, etc.

Here's a link a to an article posted by Scott Guthrie http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

In the article you will find the links to the examples library which contains the source code for the Dynamic Linq library.

Answer 3

It seems to me that you're trying to build a linq provider... Try to check this tutorial serie on how to implement a custom Linq to SQL provider : LINQ: Building an IQueryable provider series