基于DropDownList中的selected值，将Linq转换为SQL OrderBy

Question

我有一个10篇文章的列表，在页面加载事件中显示以下Linq-to-SQL查询：

if (!Page.IsPostBack)
{
MyDataClassesDataContext db = new MyDataClassesDataContext();
var pressRelease = from pr in db.PressReleases
orderby pr.DatePublished ascending 
where pr.Published == true
select pr;
int i = 0;
foreach (var release in pressRelease)
{
Literal literal = new Literal();
literal.Text = "<div class='row no-gutters pb-5'><div class='col-12'><h2>" +
release.Title + "</h2>" + Server.HtmlDecode(release.Content) + "</h2>" +
"<a href='Press-Release.aspx?id=" + release.Id +
"'><b>Read article...</b></a></div></div>";
pnlPressReleaases.Controls.Add(literal);
}
}

在我的网页上，我有一个包含以下值的dropdownlist，并且autopostback设置为true。

<asp:DropDownList ID="ddlPressSortBy" AutoPostBack="True" runat="server" OnSelectedIndexChanged="ddlPressSortBy_SelectedIndexChanged">
<asp:ListItem Value="pr.DatePublished descending">Date Published Desc</asp:ListItem>
<asp:ListItem Selected="True" Value="pr.DatePublished ascending">Date Published Asc</asp:ListItem>
<asp:ListItem Value="pr.Title ascending">Alphabetical</asp:ListItem>
</asp:DropDownList>

一旦我更改了dropdownlist的值，返回的数据在相应的select语句上就没有orderby。这是我为OnSelectIndexChanged事件编写的代码：

protected void ddlPressSortBy_SelectedIndexChanged(object sender, EventArgs e)
{
MyDataClassesDataContext db = new MyDataClassesDataContext();
var pressRelease = from pr in db.PressReleases
orderby ddlPressSortBy.SelectedValue
where pr.Published == true
select pr;

foreach (var release in pressRelease)
{
Literal literal = new Literal();
literal.Text = "<div class='row no-gutters pb-5'><div class='col-12'><h2>" +
release.Title + "</h2>" + Server.HtmlDecode(release.Content) + "</h2>" + "<a href='Press-Release.aspx?id=" + release.Id + "'><b>Read article...</b></a></div></div>";
pnlPressReleaases.Controls.Add(literal);
}
}

我有点不确定为什么它不向勤务兵开火。

如有任何帮助，我们将不胜感激。

谢谢Rob

Answer 1

您不能以这种方式进行排序：

var pressRelease = from pr in db.PressReleases
orderby ddlPressSortBy.SelectedValue
where pr.Published == true
select pr;

因为得到的查询类似于(假设您选择了第一个项目(：

SELECT * FROM PressReleases
WHERE Published = 1
ORDER BY 'pr.DatePublished descending'

ORDER BY子句将使用固定值，从而导致默认排序。

LinqOrderBy采用Expression<Func<T, object>>，如果您使用Fluent语法，则类似于pr => pr.DatePublishing。查询语法只是以漂亮的形式隐藏了这一点，但它是如何工作的。

根据本文，您可以为IQueryable创建一个扩展方法，该方法允许您使用属性名称进行排序：

public static class extensionmethods
{
public static IQueryable<T> OrderByField<T>(this IQueryable<T> q, string SortField, bool Ascending)
{
var param = Expression.Parameter(typeof(T), "p");
var prop = Expression.Property(param, SortField);
var exp = Expression.Lambda(prop, param);
string method = Ascending ? "OrderBy" : "OrderByDescending";
Type[] types = new Type[] { q.ElementType, exp.Body.Type };
var mce = Expression.Call(typeof(Queryable), method, types, q.Expression, exp);
return q.Provider.CreateQuery<T>(mce);
}
}

然后在你的代码中使用它，如下所示：

var sortingInfo = ddlPressSortBy.SelectedValue.Split(' ');
var sortingProperty = sortingInfo[0];
var descending = sortingInfo[1] == "descending";
var pressRelease = from pr in db.PressReleases
where pr.Published == true
select pr;
pressRelease = pressRelease.OrderByField(sortingProperty, descending);
// rest of code