dynamic linq query with parameters

Use the code expressed in this article: http://stackoverflow.com/questions/41244/dynamic-linq-orderby

There is a another good example here: http://aonnull.blogspot.com/2010/08/dynamic-sql-like-linq-orderby-extension.html

You'll just extra your URL query parameters, determine the order and use the string as the property name.

hi,

you need to do few things

get the type of the sort field
set the sort field for the order by clause
sort it based on the sort order using if else statement or something alike

you code should look something like this

Type type = typeof("sortname");
if(sortorder == "ascending")
{
var adlist = from ad in ct.Advertisements
orderby ad.Field<type>("sortname") ascending
select ad;
}
else
{
var adlist = from ad in ct.Advertisements
orderby ad.Field<type>("sortname") descending
select ad;
}

Select allOpen in new window

hope this helps

The only problem is VWD/VS 2010 throws an error here becuase the initialization of

var adlist = from ad in ct.Advertisements

is inside an if statement, causing VWD/VS not to like that.  says that the potential of being called while not initialized is there, and errors out.

Try it like this:

var adlist = from ad in ct.Advertisements

if(sortorder == "ascending")
{
adlist = from ad in adlist
orderby ad.Field<type>("sortname") ascending
select ad;
}
else
{
adlist = from ad in adlist
orderby ad.Field<type>("sortname") descending
select ad;
}

Select allOpen in new window

ad.Field<Type>(sortname)

That part is erroring out....

 says no definition for Field in Advertisements...

I will not know which field to sort by prior...
the string variable sortname declares that.

See if this functions better. I haven't set up a context to test this, so I can't guarantee it.

var adlist = from ad in ct.Advertisements

if(sortorder == "ascending")
{
    adlist = from ad in adlist
             orderby ad.Field<object>("sortname") ascending
             select ad;
}
else
{
    adlist = from ad in adlist
             orderby ad.Field<object>("sortname") descending
             select ad;
}

Select allOpen in new window

Actually, strike that. I didn't read your error message properly.

any ideas? please ;)

I'll try to work up an example in a little while--if no one else beats me to it. I'll have to set up a DataContext and such, so I'll need some time  : )

I forgot that you are using a List...

Field<>() can be used with datatables and cant use that with lists...

here is the corrected working sample code for you...

public class Advertisement
    {
        public int AddID{get;set;}
        public string AddName{get;set;}
    }
    public class test
    {
        public IList<Advertisement> Advertisements  = new List<Advertisement>();
    }

Select allOpen in new window

and the sorting goes here

public void DynamicLinq()
        {
            test adver = new test();
            adver.Advertisements.Add(new Advertisement() { AddID = 1, AddName = "Allan" });
            adver.Advertisements.Add(new Advertisement() { AddID = 3, AddName = "Catter" });
            adver.Advertisements.Add(new Advertisement() { AddID = 2, AddName = "Bob" });

            string sortname = "AddName";//Assume that this value comes from requested Url
            string sortorder = "ascending";//Assume that this value comes from requested Url

            Type type = sortname.GetType();
            if(sortorder == "ascending")
            {
                var adlist = from ad in adver.Advertisements
                             orderby ad.GetType().GetProperty(sortname).GetValue(ad,null) ascending
                             select ad;
                var res = adlist.ToList();
            }
            else
            {
                var adlist = from ad in adver.Advertisements
                             orderby ad.GetType().GetProperty(sortname).GetValue(ad,null) descending
                             select ad;
            }
        }

Select allOpen in new window

sortresult.JPG

ok...u are referencing var res=adlist.ToList()....

how do I foreach res?

so I can push it into the XML?
Thanks

you can change

var res=adlist.ToList() to type safe var res = adlist.ToList<Advertisement>();

foreach(var ad in res)
{
//
}

@dexterrajesh

It would be a good idea to use the BindingFlags option IgnoreCase since the default search for GetProperty is case-sensitive. Also, since the parameters are coming from the URL, that code should really be wrapped in a try/catch since it is possible to receive invalid field names via the URL.

its' throwing me an error:
System.InvalidOperationException: Cannot order by type 'System.Object'.

What is the definition of the field you are trying to sort by? i.e. what is its type?

it's a string...comes into the function as a string.


sortname = "Title" //<-- like this
sortorder = "ascending" //<--like this.

Now, the variable's value may change, but it will always be a string.

That's not what I meant. Look at dexterrajesh's example ( http:#codeSnippet20-36340807-1 ). AddID is defined as int and AddName is defined as string. For the field you are trying to sort by, what is its type defined as within your "Advertisement" class (or whatever the name of your class is)?

OH...I'm sorry...

Two strings and a date.

Title, Text, and Date_entered.

Which line throws the error? I am currently unable to reproduce.

Here's what I'm using to test with (a slight tweak of dexterrajesh's example):

public void DynamicLinq()
{
    test adver = new test();

    adver.Advertisements.Add(new Advertisement() { AddID = 3, AddName = "Catter", Date_entered = DateTime.Now });
    adver.Advertisements.Add(new Advertisement() { AddID = 1, AddName = "Allan", Date_entered = DateTime.Now.AddDays(-3) });
    adver.Advertisements.Add(new Advertisement() { AddID = 2, AddName = "Bob", Date_entered = DateTime.Now.AddHours(-3) });

    string sortname = "Date_entered";//Assume that this value comes from requested Url
    string sortorder = "descending";//Assume that this value comes from requested Url

    IOrderedEnumerable<Advertisement> adlist;

    if (sortorder == "ascending")
    {
        adlist = from ad in adver.Advertisements
                 orderby ad.GetType()
                           .GetProperty(sortname, BindingFlags.IgnoreCase | BindingFlags.Instance | BindingFlags.Public)
                           .GetValue(ad, null) ascending
                 select ad;
    }
    else
    {
        adlist = from ad in adver.Advertisements
                 orderby ad.GetType()
                           .GetProperty(sortname, BindingFlags.IgnoreCase | BindingFlags.Instance | BindingFlags.Public)
                           .GetValue(ad, null) descending
                 select ad;
    }

    foreach (var item in adlist.ToList())
    {
        Response.Write(item.AddName + ": " + item.Date_entered.ToString() + "<br />");
    }
}

Select allOpen in new window

screencapture
 
plus I had to throw the var res out of the if-statement...it was erroring out as non-declared in the foreach statement.
 

<%@ WebHandler Language="C#" Class="Handler" %>

using System;
using System.Web;
using System.Xml;
using System.Xml.Linq;
using System.Linq;
using System.Text;
using System.Data;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Text.RegularExpressions;

public class Handler : IHttpHandler {
    
    public void ProcessRequest (HttpContext context) {

        context.Response.ContentType = "text/xml";
        context.Response.ContentEncoding = System.Text.Encoding.UTF8;
        //context.Response.Headers.Add("Pragma", "no-cache");
        //context.Response.Headers.Add("Cache-Control", "no-cache, must-revalidate");
        
        ConcentrationTree ct = new ConcentrationTree();
        int page = 1;
        int rp = 10;
        string sortname = "title";
        string sortorder = "ascending";
        double total;

        if (context.Request.Params.Get("page") != null)
        {
            page = int.Parse(context.Request.Params.Get("page").ToString());
        };

        if (context.Request.Params.Get("rp") != null)
        {
            rp = int.Parse(context.Request.Params.Get("rp").ToString());
        };

        if (context.Request.Params.Get("sortname") != null)
        {
            sortname = context.Request.Params.Get("sortname").ToString();
        };

        if (context.Request.Params.Get("sortorder") != null)
        {
            if (context.Request.Params.Get("sortorder").ToString() == "desc")
            {
                sortorder = "descending";
            };
        };

        
        total = (from count in ct.Advertisements
                       select count).Count();


        
        List<String> fieldlist = new List<string>();

        var adlist = (from ad in ct.Advertisements
                      select ad);

        Type type = sortname.GetType();
        if (sortorder == "ascending")
        {
            adlist = from ad in ct.Advertisements
                     orderby ad.GetType().GetProperty(sortname).GetValue(ad, null) ascending
                     select ad;

        }
        else
        {
            adlist = (from ad in ct.Advertisements
                      orderby ad.GetType().GetProperty(sortname).GetValue(ad, null) descending
                      select ad);
            
        }
        var res = adlist.ToList<Advertisement>();
  

        XDocument AdXML = new XDocument(
                new XDeclaration("1.0", "utf-8", "yes")
            );

        XElement rows = new XElement("rows");
        XElement pager = new XElement("page", page.ToString());
        XElement totals = new XElement("total", total.ToString());
        
        
        AdXML.Add(rows);
        rows.Add(pager);
        rows.Add(totals); 
        
        foreach (var ad in res)
        {
            XElement row = new XElement("row",
                new XAttribute ("id", ad.PK),
                new XElement("cell", new XCData(ad.Title)),
                new XElement("cell", new XCData((ad.Text.Length > 50 ? Regex.Replace(ad.Text, "<.*?>", string.Empty).Substring(0,50) + "..." : Regex.Replace(ad.Text, "<.*?>", string.Empty)))),
                new XElement("cell", new XCData(ad.date_entered.ToShortDateString()))
                );

            rows.Add(row);

            fieldlist.Clear();
            var fields = from field in ct.Concentrations
                         join ads in ct.Concentration_Advertisements on field.PK equals ads.FK_Concentration
                         where ads.FK_Advertisement == ad.PK
                         select field;

            foreach (var field in fields)
            {
                fieldlist.Add(field.Concentration1);
            }
            
        XElement fldlst = new XElement("cell",  new XCData(string.Join(", ", fieldlist)));
        row.Add(fldlst);

        };


        context.Response.Write(AdXML);
                     
    }
 
    public bool IsReusable {
        get {
            return false;
        }
    }

}

Select allOpen in new window

I uploaded the server: http://cutlerplace.net/data_services/AdForm.cs

I was instructed to go this route...
This method worked...

        IQueryable<Advertisement> ads = (from ad in ct.Advertisements
                                        select ad).Skip(start).Take(rp);

        total = ads.Count();

        
        ParameterExpression AdParam = Expression.Parameter(typeof(Advertisement), "ad");
        MemberExpression Admbr = Expression.PropertyOrField(AdParam, sortname);
        LambdaExpression adLambda = Expression.Lambda(Admbr, AdParam);

        Type[] exprArgTypes = { ads.ElementType, adLambda.Body.Type };

        MethodCallExpression AdCall = Expression.Call(typeof(Queryable), sortorder, exprArgTypes, ads.Expression, adLambda);

        //IQueryable orderedads = ads.Provider.CreateQuery(AdCall);

        List<Advertisement> orderedads = ads.Provider.CreateQuery(AdCall).Cast<Advertisement>().ToList();

Select allOpen in new window

Thank you so much...
I was told to go a different route...
I am so thankful for your help...
Thanks much..