dynamic linq query with parameters

Evan Cutler
Evan Cutler used Ask the Experts™
on
I have this Linq query:

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

I get sortname and sortorder from the GET line in the URL...

how do I dynamically re-order the adlist?

Thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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;
}

Open in new window


hope this helps
Evan CutlerVolunteer Chief Information Officer

Author

Commented:
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.

Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

kaufmedGlanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
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;
}

Open in new window

Evan CutlerVolunteer Chief Information Officer

Author

Commented:
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.
kaufmedGlanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
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;
}

Open in new window

kaufmedGlanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
Actually, strike that. I didn't read your error message properly.
Evan CutlerVolunteer Chief Information Officer

Author

Commented:
any ideas? please ;)
kaufmedGlanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
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>();
    }

Open 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;
            }
        }

Open in new window

sortresult.JPG
Evan CutlerVolunteer Chief Information Officer

Author

Commented:
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)
{
//
}
kaufmedGlanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
@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.
Evan CutlerVolunteer Chief Information Officer

Author

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

kaufmedGlanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
What is the definition of the field you are trying to sort by? i.e. what is its type?
Evan CutlerVolunteer Chief Information Officer

Author

Commented:
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.
kaufmedGlanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
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)?
Evan CutlerVolunteer Chief Information Officer

Author

Commented:
OH...I'm sorry...

Two strings and a date.

Title, Text, and Date_entered.

kaufmedGlanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
Which line throws the error? I am currently unable to reproduce.
kaufmedGlanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015

Commented:
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 />");
    }
}

Open in new window

Evan CutlerVolunteer Chief Information Officer

Author

Commented:
screencapture
 error screen
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;
        }
    }

}

Open in new window


I uploaded the server: http://cutlerplace.net/data_services/AdForm.cs
Glanced up at my screen and thought I had coded the Matrix...  Turns out, I just fell asleep on the keyboard.
Most Valuable Expert 2011
Top Expert 2015
Commented:
In line 26, I see you have "title" (lowercase), but above ( http:#36344904 ) you mentioned "Title" (uppercase). As I mentioned ( http:#36343293 ), case matters with respect to the GetProperty method. My last example makes the search case-insensitive. Barring using my example, if the property's name truly is "Title", then change line 26 to reflect such.

Also, line 64 in your post above can be removed. dexterrajesh never used it anywhere that I saw, so it is basically just superfluous code.
Evan CutlerVolunteer Chief Information Officer

Author

Commented:
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();

Open in new window

Evan CutlerVolunteer Chief Information Officer

Author

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial