CODEWITHSUNDEEP
.netlinqentity-frameworkc#-4.0dynamic
Don
Don

Reputation: 1572

.NET Dynamic LINQ OrderBy Joined and Nested Objects

I have a function like this -

public void ReadTable(string sortField, string sortOrder, string RegID)
{
    sortOrder = sortOrder == "desc" ? "descending" : "";

    var db = new MyEntities();


    var results = (from CA in db.tblCalifornia
                       join BN in db.tblBeach on CA.BeachID equals BN.BeachID into ps
                       from BN in ps.DefaultIfEmpty()
                   where CA.RegID == regID && CA.tblLife.duration != null
                   orderby sortField + " " + sortOrder
                   select new { CA, BN }).ToList();
 }

I want to dynamically sort using a field called "city" in tblCalifornia or the "duration" field in tblLife.

I call the function as ReadTable("city", "asc", "120") or as ReadTable("CA.city", "asc", "120"). Neither way works, although compiling is OK and not run time exception is thrown.

How to make it work?

Thanks,

Upvotes: 0

Views: 533

Answers (1)

Adam
Adam

Reputation: 1212

You are mixing Linq-To-Entities w/ dynamic sql - which isn't going to work. The runtime is building the sql query from your linq query, and it doesn't understand how to convert the raw strings in the orderby clause into dynamic SQL.

This will get you close:

var q = (from CA in db.tblCalifornia
           join BN in db.tblBeach on CA.BeachID equals BN.BeachhID into ps
           from BN in ps.DefaultIfEmpty()
           where CA.RegID == regID && CA.tblLife.duration != null
           select new { CA, BN }
        );
if (order == "desc") {
     q = q.OrderByDescending(m => m.CA.city);
}
else {
     q = q.OrderBy(m => m.CA.city); 
}
var results = q.ToList();

OrderBy and OrderByDescending are Linq-To-Entities compatible methods, so the order by SQL generation works correctly. Notice; however, that I explicitly set the expressions that they use to CA.city. Unfortunately I don't think it's possible to dynamically set the sorts like you want with EF alone.

If you really want to do that - check out LinqKit http://www.albahari.com/nutshell/linqkit.aspx.

Its "AsExpandable()" method will allow you to pass in an expression into your method, which you can then compile and insert into your linq query.

So your new method would eventually be called like this:

ReadTable("asc", m => m.CA.City, "120");

You'd have to create an explicit containing class for CA and BN to sit in, and then use an expression as follows

public class Container {
    public tblCalifornia CA { get; set; }
    public tblBeach BN { get; set; }
}
public void ReadTable(string sortOrder, Expression<Func<Container,string>> fnSortExpr, string regID) {

    var sort = fnSortExpr.Compile();
    var q = (from CA in db.tblCalifornia.AsExpandable() 
           join BN in db.tblBeach on CA.BeachID equals BN.BeachhID into ps
           from BN in ps.DefaultIfEmpty()
           where CA.RegID == regID && CA.tblLife.duration != null
           select new Container { BN = BN, CA = CA }
        );
    if (order == "desc") {
       q = q.OrderByDescending(sort);
    }
    else {
       q = q.OrderBy(sort); 
    }
    var results = q.ToList();
    ...
}

AsExpandable() creates a wrapper around IQueryable which allows the runtime to convert your expressions to something that LINQ-To-Entities can understand. You first have to call .Compile() on your expression, and then use it in the new expandable IQueryable. I'm assuming that your sortBy field is a string coming up from the browser, in which case you'd have to map the supported strings to expressions before calling this method.

I haven't tested the code above, but hopefully it puts you on the right track. What you're trying to do is by no means an easy task, so be patient with yourself.

Good luck.

Upvotes: 1

Related Questions