Dynamically create OrderBy statement

Nov 30, 2010 at 4:58 PM

Hi!

How to dynamically add multiple order by expressions ?

For example 

string[] orderBy = new[] {"Name", "Age"}

How to create corresponding Caml QueryBy ?

And second question, is any way to user sort direction (Ascending = false attribute for FieldRef)

Thank you

Developer
Nov 30, 2010 at 5:26 PM
Edited Nov 30, 2010 at 5:28 PM

Probably, you are asking about the following code:

string caml = Camlex.Query()
.Where(x => (string)x["Status"] != "Completed" || x["Status"] == null)
.OrderBy(x => new[] { x["Modified"] as Camlex.Desc, x["Created"] as Camlex.Asc }).ToString();

(so you can supply multiple filed names to order by and also you can specify an order direction for each such sorting field)

If not, please clarify. Thanks!

Dec 1, 2010 at 10:52 PM
Edited Dec 1, 2010 at 11:00 PM

Camlex.Asc / Camlex.Desc - superb!

For multiple field names:

I need dynamically provide field name for sorting.

I don't know order by field names when i write c# code (for example user on UI want to sort by Title or Date or any field)

For Where query, i simple check for field, and add expression to expressions list

if (id != null) expressions.Add(x => (int)x["ID"] == id.Value);
if (!string.IsNulOrEmpty(title)) expressions.Add(y => (string)y["Title"] == title);

I need something like this for order by

Thank you for 

 

Coordinator
Dec 2, 2010 at 7:49 AM
Edited Dec 2, 2010 at 8:59 AM

umike,

currently there is no such functionality which allows dynamic creation of OrderBy clauses. I think we should add it in Camlex. We will discuss how to make it in more easier way. We had similiar case with Where clause: please check the following posts

Camlex.NET 2.0 for Sharepoint released - 1. Dynamic filtering conditions section

Build dynamic expressions for CAML queries based on list of values

But with OrderBy it is not so straightforward because along with Title we need to provide sort direction. May be we can use anonymous types here something like this:

 

var orderByFields = new[] { new { Title = "Title", Order = "Asc"}, new {Title = "Date", Order = "Desc"}};
var query = Camlex.Query().OrderBy(orderByFields).ToString();

 

But we lost compile time checking for sort direction in this case because it is represented by strings. Another possible approach is more preferable I think:

 

var orderBy = new List<Expression<Func<SPListItem, object>>>();
orderBy.Add(x => x["Title"] as Camlex.Asc);
orderBy.Add(x => x["Date"] as Camlex.Desc);
...
var query = Camlex.Query().OrderBy(orderBy).ToString();

 

 

So wherever you have a list of pairs (field name, sort direction) you will be able to construct list of type List<Expression<Func<SPListItem, object>>> from it and pass it into the Camlex.

Anyways thanks for your feedback. We will provide response in near days.

Coordinator
Dec 2, 2010 at 8:16 AM

BTW, if you need to sort only by single field dynamically (i.e. not by several fields simultaneously), you can do it with current Camlex version. Check the following code:

// this parameters are coming from UI
string fieldName = "Title";
bool asc = false;

var orderBy = asc ? ((Expression<Func<SPListItem, object>>)(x => x[fieldName] as Camlex.Asc)) :
    ((Expression<Func<SPListItem, object>>)(x => x[fieldName] as Camlex.Desc));
var query = Camlex.Query().Where(x => (string) x[fieldName] == "foo").OrderBy(orderBy);
Console.WriteLine(query);

fieldName and asc are dynamic parameters which come from UI somehow. If you will specify asc - true it will produce the following CAML:

<Where>
  <Eq>
    <FieldRef Name="Title" />
    <Value Type="Text">foo</Value>
  </Eq>
</Where>
<OrderBy>
  <FieldRef Name="Title" Ascending="True" />
</OrderBy>

But if you will specify asc = false, you will get the following CAML:

<Where>
  <Eq>
    <FieldRef Name="Title" />
    <Value Type="Text">foo</Value>
  </Eq>
</Where>
<OrderBy>
  <FieldRef Name="Title" Ascending="False" />
</OrderBy>
Notice that "Ascending" attribute is different in these cases. So for dynamic single field sorting solution exists. We should think about dynamic multiple fields sorting.

Coordinator
Dec 2, 2010 at 8:18 PM
Edited Dec 3, 2010 at 6:25 AM

umike,

this feature is implemented. See details in the following post: Dynamic OrderBy CAML statements via Camlex.NET. You cam use the following code:

 

var orderByList = new List<Expression<Func<SPListItem, object>>>();
orderByList.Add(x => x["Title"] as Camlex.Asc);
orderByList.Add(y => y["Date"] as Camlex.Desc);

var caml = Camlex.Query().OrderBy(orderByList).ToString();

 

which will produce the following CAML:

 

<OrderBy>
  <FieldRef Name="Title" Ascending="True" />
  <FieldRef Name="Date" Ascending="False" />
</OrderBy>

Thanks for feedback.

 

Dec 2, 2010 at 9:31 PM

Thanks a lot! 

p.s. i think blog title is Dynamic OrderBy CAML statements not GroupBy

 

Coordinator
Dec 3, 2010 at 6:25 AM

thanks,

typo was fixed