将动态Linq抽象为SQL where
本文关键字：SQL where 抽象 动态 Linq | 更新日期: 2023-09-27 18:25:40
更新：Groo的答案被标记为正确，因为这将是一个很好的替代方案。我决定坚持使用select/switch语句，以避免使用反射的性能问题。据我所知，如果不使用动态LINQ（和反射）或反编译器（手动编写每个LINQ语句，我已经在手动编写了），就无法实现我想要的功能。注意：为了进行此更新，删除了DropDownList16到DropDownList 20（需要字符）。
有没有一种方法可以抽象动态Linq-to-SQL查询的构建？
我正在尝试构建一个基于用户提供的过滤器的带有动态where子句的Linq-to-SQL查询。用户需要能够使用高级选项（相等、不相等、包含、以等开头）过滤字符串、整数和日期。用户需要能够根据需要将这些过滤器与任意多列或任意少列一起使用。
我当前的一列切换：
VB
Select Case type
Case StringFilterTypes.Any ''//Do nothing (same as else)
Case StringFilterTypes.Contains
query = From view In query Where view.Name.Contains(userValue) Select view
Case StringFilterTypes.Exactly
query = From view In query Where view.Name = userValue Select view
Case StringFilterTypes.StartsWith
query = From view In query Where view.Name.StartsWith(userValue) Select view
Case StringFilterTypes.EndsWith
query = From view In query Where view.Name.EndsWith(userValue) Select view
Case Else ''//Do nothing (same as Any).
End Select
C#
switch (type) {
case StringFilterTypes.Any: //Do nothing (same as else)
break;
case StringFilterTypes.Contains:
query = from view in querywhere view.Name.Contains(userValue)view;
break;
case StringFilterTypes.Exactly:
query = from view in querywhere view.Name == userValueview;
break;
case StringFilterTypes.StartsWith:
query = from view in querywhere view.Name.StartsWith(userValue)view;
break;
case StringFilterTypes.EndsWith:
query = from view in querywhere view.Name.EndsWith(userValue)view;
break;
default: //Do nothing (same as Any).
break;
}
我需要将这个选择/开关用于所有字符串列，并将类似的开关用于所有integer和datetime列。因此，我想把这些选择/切换状态变成一个类型为view的函数。Name和userValue是提供的变量。我可以传递type和userValue，因为它们是局部变量，但我如何传递view。函数中的名称？
我已经研究过扩展，但它们似乎要求我传递视图，已经知道列，并且不支持select/switch语句
我还考虑过将where语句添加为字符串，但我觉得这消除了使用linq的两个原因（编译器验证的查询、自动转义用户输入以防止sql注入）
表达式树看起来很有前途，但我不确定如何使用它们，也不确定它们是否是性能问题的好主意。
最好的解决方案是：
VB
Public Function ApplyFilters(query As IQueryable(Of DBName.ViewName)) As IQueryable(Of DBName.ViewName)
''//...
Filter_String (query, Type, View.Name, userValue)
''//...
End Function
Public Function Filter_String (query As IQueryable(Of DBName.ViewName), type As StringFilterTypes, column as ???, userValue As String) As IQueryable(Of DBName.ViewName)
Select Case type
Case StringFilterTypes.Any ''//Do nothing (same as else)
Case StringFilterTypes.Contains
query = From view In query Where column.Contains(userValue) Select view
Case StringFilterTypes.Exactly
query = From view In query Where column = userValue Select view
Case StringFilterTypes.StartsWith
query = From view In query Where column.StartsWith(userValue) Select view
Case StringFilterTypes.EndsWith
query = From view In query Where column.EndsWith(userValue) Select view
Case Else ''//Do nothing (same as Any).
End Select
Return query
End Function
C#
public IQueryable<DBName.ViewName> ApplyFilters(IQueryable<DBName.ViewName> query)
{
//...
Filter_String (query, Type, View.Name, userValue);
//...
}
public IQueryable<DBName.ViewName> Filter_String(IQueryable<DBName.ViewName> query, StringFilterTypes type, ??? column, string userValue)
{
switch (type) {
case StringFilterTypes.Any: //Do nothing (same as else)
break;
case StringFilterTypes.Contains:
query = from view in querywhere column.Contains(userValue)view;
break;
case StringFilterTypes.Exactly:
query = from view in querywhere column == userValueview;
break;
case StringFilterTypes.StartsWith:
query = from view in querywhere column.StartsWith(userValue)view;
break;
case StringFilterTypes.EndsWith:
query = from view in querywhere column.EndsWith(userValue)view;
break;
default:
break; //Do nothing (same as Any).
}
return query;
}
更新2012/02/27：
对于BlueRaja-Danny Pflughoeft，我提供了一个示例应用程序。显然，数据连接必须由最终用户提供（名为"Test"的MS SQL数据库，表名为"testTable"，列名为"Column#"，其中#都是数字1-20）。
Default.aspx
<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Default.aspx.vb" Inherits="WebApplication1.test._Default" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DropDownList ID="DropDownList1" runat="server">
<asp:ListItem Selected="True" Text ="Any" Value="-1" />
<asp:ListItem Selected="False" Text ="Contains" Value="0" />
<asp:ListItem Selected="False" Text ="Is exactly" Value="1" />
<asp:ListItem Selected="False" Text ="Starts with" Value="2" />
<asp:ListItem Selected="False" Text ="Ends with" Value="3" />
</asp:DropDownList>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<br />
<asp:DropDownList ID="DropDownList2" runat="server">
<asp:ListItem Selected="True" Text ="Any" Value="-1" />
<asp:ListItem Selected="False" Text ="Contains" Value="0" />
<asp:ListItem Selected="False" Text ="Is exactly" Value="1" />
<asp:ListItem Selected="False" Text ="Starts with" Value="2" />
<asp:ListItem Selected="False" Text ="Ends with" Value="3" />
</asp:DropDownList>
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
<br />
<asp:DropDownList ID="DropDownList3" runat="server">
<asp:ListItem Selected="True" Text ="Any" Value="-1" />
<asp:ListItem Selected="False" Text ="Contains" Value="0" />
<asp:ListItem Selected="False" Text ="Is exactly" Value="1" />
<asp:ListItem Selected="False" Text ="Starts with" Value="2" />
<asp:ListItem Selected="False" Text ="Ends with" Value="3" />
</asp:DropDownList>
<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
<br />
<asp:DropDownList ID="DropDownList4" runat="server">
<asp:ListItem Selected="True" Text ="Any" Value="-1" />
<asp:ListItem Selected="False" Text ="Contains" Value="0" />
<asp:ListItem Selected="False" Text ="Is exactly" Value="1" />
<asp:ListItem Selected="False" Text ="Starts with" Value="2" />
<asp:ListItem Selected="False" Text ="Ends with" Value="3" />
</asp:DropDownList>
<asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
<br />
<asp:DropDownList ID="DropDownList5" runat="server">
<asp:ListItem Selected="True" Text ="Any" Value="-1" />
<asp:ListItem Selected="False" Text ="Contains" Value="0" />
<asp:ListItem Selected="False" Text ="Is exactly" Value="1" />
<asp:ListItem Selected="False" Text ="Starts with" Value="2" />
<asp:ListItem Selected="False" Text ="Ends with" Value="3" />
</asp:DropDownList>
<asp:TextBox ID="TextBox5" runat="server"></asp:TextBox>
<br />
<asp:DropDownList ID="DropDownList6" runat="server">
<asp:ListItem Selected="True" Text ="Any" Value="-1" />
<asp:ListItem Selected="False" Text ="Contains" Value="0" />
<asp:ListItem Selected="False" Text ="Is exactly" Value="1" />
<asp:ListItem Selected="False" Text ="Starts with" Value="2" />
<asp:ListItem Selected="False" Text ="Ends with" Value="3" />
</asp:DropDownList>
<asp:TextBox ID="TextBox6" runat="server"></asp:TextBox>
<br />
<asp:DropDownList ID="DropDownList7" runat="server">
<asp:ListItem Selected="True" Text ="Any" Value="-1" />
<asp:ListItem Selected="False" Text ="Contains" Value="0" />
<asp:ListItem Selected="False" Text ="Is exactly" Value="1" />
<asp:ListItem Selected="False" Text ="Starts with" Value="2" />
<asp:ListItem Selected="False" Text ="Ends with" Value="3" />
</asp:DropDownList>
<asp:TextBox ID="TextBox7" runat="server"></asp:TextBox>
<br />
<asp:DropDownList ID="DropDownList8" runat="server">
<asp:ListItem Selected="True" Text ="Any" Value="-1" />
<asp:ListItem Selected="False" Text ="Contains" Value="0" />
<asp:ListItem Selected="False" Text ="Is exactly" Value="1" />
<asp:ListItem Selected="False" Text ="Starts with" Value="2" />
<asp:ListItem Selected="False" Text ="Ends with" Value="3" />
</asp:DropDownList>
<asp:TextBox ID="TextBox8" runat="server"></asp:TextBox>
<br />
<asp:DropDownList ID="DropDownList9" runat="server">
<asp:ListItem Selected="True" Text ="Any" Value="-1" />
<asp:ListItem Selected="False" Text ="Contains" Value="0" />
<asp:ListItem Selected="False" Text ="Is exactly" Value="1" />
<asp:ListItem Selected="False" Text ="Starts with" Value="2" />
<asp:ListItem Selected="False" Text ="Ends with" Value="3" />
</asp:DropDownList>
<asp:TextBox ID="TextBox9" runat="server"></asp:TextBox>
<br />
<asp:DropDownList ID="DropDownList10" runat="server">
<asp:ListItem Selected="True" Text ="Any" Value="-1" />
<asp:ListItem Selected="False" Text ="Contains" Value="0" />
<asp:ListItem Selected="False" Text ="Is exactly" Value="1" />
<asp:ListItem Selected="False" Text ="Starts with" Value="2" />
<asp:ListItem Selected="False" Text ="Ends with" Value="3" />
</asp:DropDownList>
<asp:TextBox ID="TextBox10" runat="server"></asp:TextBox>
<br />
<asp:DropDownList ID="DropDownList11" runat="server">
<asp:ListItem Selected="True" Text ="Any" Value="-1" />
<asp:ListItem Selected="False" Text ="Contains" Value="0" />
<asp:ListItem Selected="False" Text ="Is exactly" Value="1" />
<asp:ListItem Selected="False" Text ="Starts with" Value="2" />
<asp:ListItem Selected="False" Text ="Ends with" Value="3" />
</asp:DropDownList>
<asp:TextBox ID="TextBox11" runat="server"></asp:TextBox>
<br />
<asp:DropDownList ID="DropDownList12" runat="server">
<asp:ListItem Selected="True" Text ="Any" Value="-1" />
<asp:ListItem Selected="False" Text ="Contains" Value="0" />
<asp:ListItem Selected="False" Text ="Is exactly" Value="1" />
<asp:ListItem Selected="False" Text ="Starts with" Value="2" />
<asp:ListItem Selected="False" Text ="Ends with" Value="3" />
</asp:DropDownList>
<asp:TextBox ID="TextBox12" runat="server"></asp:TextBox>
<br />
<asp:DropDownList ID="DropDownList13" runat="server">
<asp:ListItem Selected="True" Text ="Any" Value="-1" />
<asp:ListItem Selected="False" Text ="Contains" Value="0" />
<asp:ListItem Selected="False" Text ="Is exactly" Value="1" />
<asp:ListItem Selected="False" Text ="Starts with" Value="2" />
<asp:ListItem Selected="False" Text ="Ends with" Value="3" />
</asp:DropDownList>
<asp:TextBox ID="TextBox13" runat="server"></asp:TextBox>
<br />
<asp:DropDownList ID="DropDownList14" runat="server">
<asp:ListItem Selected="True" Text ="Any" Value="-1" />
<asp:ListItem Selected="False" Text ="Contains" Value="0" />
<asp:ListItem Selected="False" Text ="Is exactly" Value="1" />
<asp:ListItem Selected="False" Text ="Starts with" Value="2" />
<asp:ListItem Selected="False" Text ="Ends with" Value="3" />
</asp:DropDownList>
<asp:TextBox ID="TextBox14" runat="server"></asp:TextBox>
<br />
<asp:DropDownList ID="DropDownList15" runat="server">
<asp:ListItem Selected="True" Text ="Any" Value="-1" />
<asp:ListItem Selected="False" Text ="Contains" Value="0" />
<asp:ListItem Selected="False" Text ="Is exactly" Value="1" />
<asp:ListItem Selected="False" Text ="Starts with" Value="2" />
<asp:ListItem Selected="False" Text ="Ends with" Value="3" />
</asp:DropDownList>
<asp:TextBox ID="TextBox15" runat="server"></asp:TextBox>
<br />
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
默认.aspx.vb
Public Class _Default
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
End Sub
Protected Sub Page_LoadComplete(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.LoadComplete
Dim _db As TestDataContext = New TestDataContext(ConfigurationManager.ConnectionStrings("TestDataConn").ConnectionString)
Dim query As IQueryable(Of testTable) = _
From view In _db.testTables
Select view
Select Case DropDownList1.SelectedValue
Case "-1"
''//Do nothing (same as else)
Case "0"
query = From view In query Where view.Column1.Contains(TextBox1.Text) Select view
Case "1"
query = From view In query Where view.Column1 = TextBox1.Text Select view
Case "2"
query = From view In query Where view.Column1.StartsWith(TextBox1.Text) Select view
Case "3"
query = From view In query Where view.Column1.EndsWith(TextBox1.Text) Select view
Case Else
''//Do nothing (same as Any).
End Select
Select Case DropDownList2.SelectedValue
Case "-1"
''//Do nothing (same as else)
Case "0"
query = From view In query Where view.Column2.Contains(TextBox2.Text) Select view
Case "1"
query = From view In query Where view.Column2 = TextBox2.Text Select view
Case "2"
query = From view In query Where view.Column2.StartsWith(TextBox2.Text) Select view
Case "3"
query = From view In query Where view.Column2.EndsWith(TextBox2.Text) Select view
Case Else
''//Do nothing (same as Any).
End Select
Select Case DropDownList3.SelectedValue
Case "-1"
''//Do nothing (same as else)
Case "0"
query = From view In query Where view.Column3.Contains(TextBox3.Text) Select view
Case "1"
query = From view In query Where view.Column3 = TextBox3.Text Select view
Case "2"
query = From view In query Where view.Column3.StartsWith(TextBox3.Text) Select view
Case "3"
query = From view In query Where view.Column3.EndsWith(TextBox3.Text) Select view
Case Else
''//Do nothing (same as Any).
End Select
Select Case DropDownList4.SelectedValue
Case "-1"
''//Do nothing (same as else)
Case "0"
query = From view In query Where view.Column4.Contains(TextBox4.Text) Select view
Case "1"
query = From view In query Where view.Column4 = TextBox4.Text Select view
Case "2"
query = From view In query Where view.Column4.StartsWith(TextBox4.Text) Select view
Case "3"
query = From view In query Where view.Column4.EndsWith(TextBox4.Text) Select view
Case Else
''//Do nothing (same as Any).
End Select
Select Case DropDownList5.SelectedValue
Case "-1"
''//Do nothing (same as else)
Case "0"
query = From view In query Where view.Column5.Contains(TextBox5.Text) Select view
Case "1"
query = From view In query Where view.Column5 = TextBox5.Text Select view
Case "2"
query = From view In query Where view.Column5.StartsWith(TextBox5.Text) Select view
Case "3"
query = From view In query Where view.Column5.EndsWith(TextBox5.Text) Select view
Case Else
''//Do nothing (same as Any).
End Select
Select Case DropDownList6.SelectedValue
Case "-1"
''//Do nothing (same as else)
Case "0"
query = From view In query Where view.Column6.Contains(TextBox6.Text) Select view
Case "1"
query = From view In query Where view.Column6 = TextBox6.Text Select view
Case "2"
query = From view In query Where view.Column6.StartsWith(TextBox6.Text) Select view
Case "3"
query = From view In query Where view.Column6.EndsWith(TextBox6.Text) Select view
Case Else
''//Do nothing (same as Any).
End Select
Select Case DropDownList7.SelectedValue
Case "-1"
''//Do nothing (same as else)
Case "0"
query = From view In query Where view.Column7.Contains(TextBox7.Text) Select view
Case "1"
query = From view In query Where view.Column7 = TextBox7.Text Select view
Case "2"
query = From view In query Where view.Column7.StartsWith(TextBox7.Text) Select view
Case "3"
query = From view In query Where view.Column7.EndsWith(TextBox7.Text) Select view
Case Else
''//Do nothing (same as Any).
End Select
Select Case DropDownList8.SelectedValue
Case "-1"
''//Do nothing (same as else)
Case "0"
query = From view In query Where view.Column8.Contains(TextBox8.Text) Select view
Case "1"
query = From view In query Where view.Column8 = TextBox8.Text Select view
Case "2"
query = From view In query Where view.Column8.StartsWith(TextBox8.Text) Select view
Case "3"
query = From view In query Where view.Column8.EndsWith(TextBox8.Text) Select view
Case Else
''//Do nothing (same as Any).
End Select
Select Case DropDownList9.SelectedValue
Case "-1"
''//Do nothing (same as else)
Case "0"
query = From view In query Where view.Column9.Contains(TextBox9.Text) Select view
Case "1"
query = From view In query Where view.Column9 = TextBox9.Text Select view
Case "2"
query = From view In query Where view.Column9.StartsWith(TextBox9.Text) Select view
Case "3"
query = From view In query Where view.Column9.EndsWith(TextBox9.Text) Select view
Case Else
''//Do nothing (same as Any).
End Select
Select Case DropDownList10.SelectedValue
Case "-1"
''//Do nothing (same as else)
Case "0"
query = From view In query Where view.Column10.Contains(TextBox10.Text) Select view
Case "1Column10"
query = From view In query Where view.Column10 = TextBox10.Text Select view
Case "2"
query = From view In query Where view.Column10.StartsWith(TextBox10.Text) Select view
Case "3"
query = From view In query Where view.Column10.EndsWith(TextBox10.Text) Select view
Case Else
''//Do nothing (same as Any).
End Select
Select Case DropDownList11.SelectedValue
Case "-1"
''//Do nothing (same as else)
Case "0"
query = From view In query Where view.Column11.Contains(TextBox11.Text) Select view
Case "1"
query = From view In query Where view.Column11 = TextBox11.Text Select view
Case "2"
query = From view In query Where view.Column11.StartsWith(TextBox11.Text) Select view
Case "3"
query = From view In query Where view.Column11.EndsWith(TextBox11.Text) Select view
Case Else
''//Do nothing (same as Any).
End Select
Select Case DropDownList12.SelectedValue
Case "-1"
''//Do nothing (same as else)
Case "0"
query = From view In query Where view.Column12.Contains(TextBox12.Text) Select view
Case "1"
query = From view In query Where view.Column12 = TextBox12.Text Select view
Case "2"
query = From view In query Where view.Column12.StartsWith(TextBox12.Text) Select view
Case "3"
query = From view In query Where view.Column12.EndsWith(TextBox12.Text) Select view
Case Else
''//Do nothing (same as Any).
End Select
Select Case DropDownList13.SelectedValue
Case "-1"
''//Do nothing (same as else)
Case "0"
query = From view In query Where view.Column13.Contains(TextBox13.Text) Select view
Case "1"
query = From view In query Where view.Column13 = TextBox13.Text Select view
Case "2"
query = From view In query Where view.Column13.StartsWith(TextBox13.Text) Select view
Case "3"
query = From view In query Where view.Column13.EndsWith(TextBox13.Text) Select view
Case Else
''//Do nothing (same as Any).
End Select
Select Case DropDownList14.SelectedValue
Case "-1"
''//Do nothing (same as else)
Case "0"
query = From view In query Where view.Column14.Contains(TextBox14.Text) Select view
Case "1"
query = From view In query Where view.Column14 = TextBox14.Text Select view
Case "2"
query = From view In query Where view.Column14.StartsWith(TextBox14.Text) Select view
Case "3"
query = From view In query Where view.Column14.EndsWith(TextBox14.Text) Select view
Case Else
''//Do nothing (same as Any).
End Select
Select Case DropDownList15.SelectedValue
Case "-1"
''//Do nothing (same as else)
Case "0"
query = From view In query Where view.Column15.Contains(TextBox15.Text) Select view
Case "1"
query = From view In query Where view.Column15 = TextBox15.Text Select view
Case "2"
query = From view In query Where view.Column15.StartsWith(TextBox15.Text) Select view
Case "3"
query = From view In query Where view.Column15.EndsWith(TextBox15.Text) Select view
Case Else
''//Do nothing (same as Any).
End Select
GridView1.DataSource = query
GridView1.DataBind()
End Sub
End Class
请注意，动态LINQ不倾向于以与从用户提供的字符串连接的查询相同的方式进行SQL注入。传递的字符串被标记化和解析，以创建将被转换为查询的表达式树，并且它不支持无限数量的操作。
例如，不能使用动态LINQ查询删除表。
不过，需要注意的是，您需要使用带有参数占位符（或"命名参数"）的动态LINQ。这与在DBComand中使用命名参数具有相同的好处。
此外，您还可以查看Albahari的PredicateBuilder。
它允许您转换如下静态表达式：
p => p.Price > 100 &&
p.Price < 1000 &&
(p.Description.Contains ("foo") || p.Description.Contains ("far"))
进入：
var outer = PredicateBuilder.True<Product>();
outer = outer.And (p => p.Price > 100);
outer = outer.And (p => p.Price < 1000);
{
var inner = PredicateBuilder.False<Product>();
inner = inner.Or (p => p.Description.Contains ("foo"));
inner = inner.Or (p => p.Description.Contains ("far"));
outer = outer.And (inner);
}
不需要编写自定义的Filter_String方法-已经有一个方法可以由调用方调用来根据字符串的内容筛选查询：Where()。我不知道你为什么坚持需要switch语句-不需要switch语句
例如，您的呼叫者可以简单地编写，而不是编写Filter_String(query, StringFilterTypes.StartsWith, column, value)
query.Where(o => o.column.StartsWith(value))
当LINQ已经原生地支持您需要的东西时，就不需要新的接口了。
类似地，他们可以写而不是Filter_String(query, StringFilterTypes.EndsWith, column, value)
query.Where(o => o.column.EndsWith(value))
而不是Filter_String(query, StringFilterTypes.Contains, column, value)，他们可以写
query.Where(o => o.column.Contains(value))
我希望这能说明我在上面的评论中想说的话。