使用Linq查询进行VB.NET筛选
我的任务是将筛选机制添加到结果集中。我理解使用Linq中的Where子句进行过滤的基本概念,但是这有一个更好的方法来做到这一点,对吧?使用Linq查询进行VB.NET筛选
场景: 我在结果集上有5个可过滤的列。我必须在任何时候考虑这5个滤波器的所有变化。这意味着我必须手动输入一个具有120种不同变体的If/ElseIf语句!
下面是事情进展的一般方法,我只是简单地问:有没有更好,更快的方法来做到这一点?
的过滤器
ByVal SearchMxMID As Integer,
ByVal SearchProfile As Integer,
ByVal SearchCZ As String,
ByVal SearchTerm As Integer,
ByVal SearchFwMth As Integer
的If语句的过程
If SearchMxMID = 0
And SearchProfile = 0
And SearchCZ = "All"
And SearchTerm = 0
And SearchFwMth = 0 Then
Dim queryMM = (From a In DBPricingContext.tbl_Matrix_Margins
Where a.CompanyID = CInt(sCompanyID)
Order By a.Profile
Select a)
Return New StoreResult(queryMM)
ElseIf SearchMxMID > 0
And SearchProfile = 0
And SearchCZ = "All"
And SearchTerm = 0
And SearchFwMth = 0 Then
Dim queryMM = (From a In DBPricingContext.tbl_Matrix_Margins
Where a.CompanyID = CInt(sCompanyID) And a.MarGroupID = SearchMxMID
Order By a.Profile
Select a)
Return New StoreResult(queryMM)
ETC ETC ETC
120总次数为这5个过滤器的每个组合(无论它们是否是空白或有价值)。有没有更快的方法,我可以做到这一点,可能在一个单一的Linq查询?
If SearchMxMID > 0 Then a.MarGroupID = SearchMxMID Else DO NOT APPLY WHERE CLAUSE
???
Linq查询是可链接的。你可以根据你的情况简单地拥有一个,如果它是真的,那么在另一个地方添加另一个。即(在C#中,但应该很容易遵循任何.NET编码器 - 或者你可以使用Telerik的代码转换器和修改代码位):
string country = "Germany";
string city = "Berlin";
int? productId = 3;
var data = db.Customers;
var result = data.AsQueryable();
if (!string.IsNullOrEmpty(country))
{
result = result.Where(r => r.Country == country);
}
if (!string.IsNullOrEmpty(city))
{
result = result.Where(r => r.City == city);
}
if (productId.HasValue)
{
result = result
.Where(r =>
r.Orders.Any(o =>
o.OrderDetails
.Any(od => od.ProductID == productId.Value)));
}
另一种方法是就是使用一个NULL或。 ..方法。即:
Private Sub Main()
Dim country As String = "" '"Brazil"
Dim city As String = "" '"Sao Paulo"
Dim orderDate As System.Nullable(Of DateTime) = Nothing 'new DateTime(1996,8,28);
Dim data = Orders.Where(Function(c) _
String.IsNullOrEmpty(country) OrElse c.ShipCountry.StartsWith(country)) _
.Where(Function(c) String.IsNullOrEmpty(city) OrElse c.ShipCity.StartsWith(city)) _
.Where(Function(c) orderDate Is Nothing OrElse c.OrderDate = orderDate) _
.Select(Function(c) New Order() With { _
.OrderId = c.OrderID, _
.CustomerId = c.CustomerID, _
.OrderDate = c.OrderDate, _
.ShipCountry = c.ShipCountry, _
.ShipCity = c.ShipCity _
})
Dim f As New Form() With { .Text = "Query Results" }
Dim dgv As New DataGridView() With { .Dock = DockStyle.Fill }
f.Controls.Add(dgv)
dgv.DataSource = data.ToList()
f.ShowDialog()
End Sub
Public Class Order
Public Property OrderId() As Integer
Get
Return m_OrderId
End Get
Set
m_OrderId = Value
End Set
End Property
Private m_OrderId As Integer
Public Property OrderDate() As System.Nullable(Of DateTime)
Get
Return m_OrderDate
End Get
Set
m_OrderDate = Value
End Set
End Property
Private m_OrderDate As System.Nullable(Of DateTime)
Public Property CustomerId() As String
Get
Return m_CustomerId
End Get
Set
m_CustomerId = Value
End Set
End Property
Private m_CustomerId As String
Public Property ShipCountry() As String
Get
Return m_ShipCountry
End Get
Set
m_ShipCountry = Value
End Set
End Property
Private m_ShipCountry As String
Public Property ShipCity() As String
Get
Return m_ShipCity
End Get
Set
m_ShipCity = Value
End Set
End Property
Private m_ShipCity As String
End Class
注意:代码是Linq To SQL代码。使用EF这将不被支持,你需要使用前一种方法。
您可以链Where
条款并动态构建您的查询是这样的:
Dim query = DBPricingContext.tbl_Matrix_Margins.AsQueryable()
If compID > 0 Then
query = query.Where(Function(a) a.CompanyID = compID)
End If
If SearchMxMID > 0 Then
query = query.Where(Function(a) a.MarGroupID = SearchMxMID)
End If
...
query = query.OrderBy(Function(a) a.Profile)
请注意,我使用的扩展方法的语法和lambda表达式,而不是专门的LINQ查询语法。如果您只是选择参数a
本身,则不需要致电Select
。
如果您使用的是LINQ到对象而不是LINQ到some_database,请将AsQueryable()
替换为AsEnumerable()
。
您还可以使用LINQ语法按顺序编写查询。
Dim TempQuery = (From a In DbPricingContext.tbl_Matrix_Margins
Where a.CompanyID = CInt(sCompanyID)
Select a)
If SearchMxMID > 0 Then
TempQuery = (From a In TempQuery
Where a.MarGroupID = SearchMxMID
Select a)
End If
'... Add your other conditions in here...
Return New StoreResult(From a In TempQuery Order By a.Profile Select a)
使用逻辑一样以同样的方式SearchValue = DefaultValue OrElse SearchValue = PropertyValue
Dim result = DBPricingContext.tbl_Matrix_Margins.
Where(Function(a) a.CompanyID = CInt(sCompanyID)).
Where(Function(a) SearchMxMID = 0 OrElse a.MarGroupID = SearchMxMID)
并添加其他搜索条件。
它将如果SearchMxMID = 0
返回所有行和返回,如果SearchMxMID <> 0
为了精确结果,我将用Nullable
型
Where(Function(a) SearchMxMID.HasValue = False OrElse a.MarGroupID = SearchMxMID.Value)
您可以实现的规范模式只匹配的行。像这样的:
Sub Main()
Dim someList As New List(Of SomeClass)
Dim now As DateTime = DateTime.Now
someList.Add(New SomeClass() With {.Id = 1, .Title = "001", .EntryDate = now})
someList.Add(New SomeClass() With {.Id = 2, .Title = "002", .EntryDate = now.AddSeconds(10)})
someList.Add(New SomeClass() With {.Id = 3, .Title = "003", .EntryDate = now.AddSeconds(20)})
Dim idParam As Integer = 1
Dim titleParam As String = "" '"001"
Dim dateParam As DateTime = now
' first approach, one selector
Dim selector As Func(Of SomeClass, Boolean) = Function(item)
With item
Return ((idParam <= 0) OrElse (.Id = idParam)) AndAlso
((String.IsNullOrEmpty(titleParam)) OrElse (.Title = titleParam)) AndAlso
((dateParam.CompareTo(DateTime.MinValue) = 0) OrElse (.EntryDate = dateParam))
End With
End Function
Dim list = From o In someList Where selector(o) Select o
For Each o In list
Console.WriteLine(o)
Next
' second approach, one selector per parameter
Dim selectorId As Func(Of SomeClass, Boolean) = Function(item)
Return ((idParam <= 0) OrElse (item.Id = idParam))
End Function
Dim selectorTitle As Func(Of SomeClass, Boolean) = Function(item)
Return ((String.IsNullOrEmpty(titleParam)) OrElse (item.Title = titleParam))
End Function
Dim selectorEntryDate As Func(Of SomeClass, Boolean) = Function(item)
Return ((dateParam.CompareTo(DateTime.MinValue) = 0) OrElse (item.EntryDate = dateParam))
End Function
Dim list2 = From o In someList
Where
selectorId(o) AndAlso
selectorTitle(o) AndAlso
selectorEntryDate(o)
Select o
For Each o In list2
Console.WriteLine(o)
Next
Console.ReadLine()
End Sub
Public Class SomeClass
Public Property Id As Integer
Public Property Title As String
Public Property EntryDate As DateTime
Public Overrides Function ToString() As String
Return String.Format("Id:{0} Title:{1} EntryDate:{2}", Id, Title, EntryDate)
End Function
End Class
希望这个例子能帮助你一点。
**场景:我的结果集上有5个可筛选的列。我必须在任何时候考虑这5个滤波器的所有变化。这意味着我必须用120种不同的变体手动输入一个If/ElseIf语句!**不,你不知道。只使用相关的东西 - 是的,你的where块有五个IF语句,但你只需要插入值。我一直在SQL中这样做,所以它应该也可以在Linq中使用。我在SQL中使用IN,在Linq中有类似的功能。 – Tim