订购linq查询 [英] Order a linq query
问题描述
对于我的通用网格，我目前正在执行此操作以激活排序:
For my generic grid I currently do this to activate the sorting:
Elements.OrderBy(column.SortExpression).AsQueryable();
其中SortExpression的类型为Func<T, object>，column是通用类Column<T>
In which SortExpression is of type Func<T, object> and column is a generic class Column<T>
我在像这样的控制器中设置SortExpression:
I set SortExpression in a controller like this:
new Column<OrderViewData>{Key = "ShippingDate", SortExpression = e => e.ShippingDate}
"OrderBy"将导致执行我不希望的sql语句.
所以我想用这个代替它:
So I'm trying to replace it with this:
Elements = from element in Elements
orderby column.SortExpression
select element;
哪个不会触发sql执行.
Which doesn't trigger the sql execution.
现在，当然是列.SortExpression应该是另一种类型.只有我真的不能弄清楚它应该是什么类型以及如何在控制器的通用类上设置它.
Now, ofcourse column.SortExpression should be of another type. Only I can't really figure out what type it should be and how to set it on the generic class in the controller.
我仍然应该能够以某种通用的强类型化方式设置SortExpression.
I should still be able to set SortExpression in a generic strongtyped way of some sort.
关于如何在应用程序的其他位置设置表达式集而不在将命令应用于IQueryable时执行sql的任何建议?
Any suggestions on how I can order by an expression set somewhere else in the application, without executing the sql when applying the order to the IQueryable?
@ Earwicker:
这有效:
Expression<Func<Employee, DateTime?>> sortexpression = e => e.BirthDate;
var db = new NorthwindDataContext();
var query = from e in db.Employees
select e;
query = query.OrderBy(sortexpression);
int count = query.Count();
并生成:
SELECT COUNT(*) AS [value]
FROM [dbo].[Employees] AS [t0]
当我替换DateTime时?在对象的第一行:
When I replace DateTime? in the first line with object:
Expression<Func<Employee, object>> sortexpression = e => e.BirthDate;
我收到一个InvalidOperationException:无法通过'System.Object'类型进行排序
I get an InvalidOperationException: Cannot order by type 'System.Object'
现在，您可能会说:然后只使用DateTime吗?"，但我想在通用网格中构建列以要求尽可能少的代码.我不希望人们键入整个Expression<Func<Employee, some_type>>.我希望人们能够像我的第一次尝试
Now, you might say: "then just use DateTime?", but I'd like building the columns in my generic grid to require the least amount of code possible. I don't want people to have to type the whole Expression<Func<Employee, some_type>>. I want people to be able to just type something small like my first attempt SortExpression = e => e.BirthDate, where I take advantage of the generic Column class to define 'T'.
您是否认为可以创建某种扩展名，从而以某种方式获取e.BirthDate的类型，然后将Func<T, object>强制转换为Expression<Func<T,some_type>>?
然后，我可以在内部代码中执行以下操作:
Elements.OrderBy(column.SortExpression.FixCast())
Do you think it would be possible to create some kind of extension that somehow gets the type of e.BirthDate and then casts the Func<T, object> to Expression<Func<T,some_type>> ?
Then I could do something in the internal code like:
Elements.OrderBy(column.SortExpression.FixCast())
我现在不太在乎我的内部代码是否丑陋或复杂.我需要正确执行SQL查询&照顾使用网格的开发人员的可用性.
I don't care much that my internal code is ugly or complex at this moment. I need to get the SQL queries right & take care of usability for developers using the grid.
非常感谢您的帮助！
@ earwicker 2:
var gridBuilder = new RainbowGridBuilder<Employee>("Examples_Employees")
{
Elements = GetEmployees(), //The elements (records) we will show in our grid.
//Define the columns for our grid.
Columns = new List<Column<Employee>>{
new Column<Employee> {
Key = "EmployeeId", //Key is used for sorting, selecting columns, ...
Header = "Employee Id", //The header of the column. Also used as caption in the column selection checkboxlist.
ValueExpression = e => e.EmployeeID.ToString(), //The Linq expression which will be executed on each element to fill the cell
SortExpression = e => e.EmployeeID, //The Linq expression by which to sort the elements in the grid.
Display = false}, //Is this column visible by default?
new Column<Employee> {Key = "Name", ValueExpression = e => e.FirstName + " " + e.LastName, SortExpression = e => e.LastName},
},
// Some other properties here that are irrelevant.
}
推荐答案
我想出了一个可以实现我所期望的解决方案.
I came up with a solution that achieves what I was after.
用法:在列上设置强类型的sortexpression:
Columns = new List<Column<Employee>>{
new Column<Employee> {
Key = "EmployeeId",
SortExpression = new SortExpression<Employee>(e => e.EmployeeID)
// ... other irrelevant column properties ...
},
new Column<Employee> {
Key = "EmployeeBirthDate",
SortExpression = new SortExpression<Employee>(e => e.BirthDate)
}
};
基本代码
RainbowGridBuilder类的"buildGrid()"方法中的以下几行适用于排序:
These few lines in the "buildGrid()" method in class RainbowGridBuilder apply the sorting:
if (columnToSort != null) //sort the elements according to the set column key and the sortexpression
{
var column = Columns.Where(c => c.Key == columnToSort).SingleOrDefault();
if (column != null)
{
Elements = column.SortExpression.ApplySortExpression(Elements, descending);
}
}
工作原理:
在我使我可以设置私有Expression<Func<T, some_specific_Type>>的SortExpression类中使用重载的构造函数.
Trough the use of overloaded constructors in the SortExpression class that I made I can set a private Expression<Func<T, some_specific_Type>>.
SortExpression类内部是ApplySortExpression方法，该方法搜索一个非null的私有成员并进行相应的排序:
Inside the SortExpression class is a method ApplySortExpression that searches for a non-null private member and sorts accordingly:
public class SortExpression<T>
{
private Expression<Func<T, DateTime?>> DateTimeExpression { get; set; }
private Expression<Func<T, int?>> intExpression { get; set; }
private Expression<Func<T, string>> stringExpression { get; set; }
public SortExpression(Expression<Func<T, DateTime?>> expression)
{
DateTimeExpression = expression;
}
public SortExpression(Expression<Func<T, int?>> expression)
{
intExpression = expression;
}
public SortExpression(Expression<Func<T, string>> expression)
{
stringExpression = expression;
}
public IQueryable<T> ApplySortExpression(IQueryable<T> elements, bool? descending)
{
if (DateTimeExpression != null)
{
if (descending.HasValue && descending.Value)
return elements.OrderByDescending(DateTimeExpression);
else
return elements.OrderBy(DateTimeExpression);
}
else if (intExpression != null)
{
if (descending.HasValue && descending.Value)
return elements.OrderByDescending(intExpression);
else
return elements.OrderBy(intExpression);
}
else if (stringExpression != null)
{
if (descending.HasValue && descending.Value)
return elements.OrderByDescending(stringExpression);
else
return elements.OrderBy(stringExpression);
}
else
throw new Exception("Unsuported sortkey type");
}
}
此解决方案在幕后可能并不是超级干净，但这是我追求的可用性.使用代码的唯一更改是添加了"new SortExpression<Employee>".
This solution may not be super clean under the hood, but it's the usability that I was after. The only change to the consuming code was to add "new SortExpression<Employee>".
这篇关于订购linq查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
