Is there a better way to do this LINQ statement block?

Question

I'm relatively new with LINQ, but I'm going to be getting into it a lot more. Is the following a practical application of LINQ, or is there a better way to do something like this?

Public Shared Function GetItems(ByVal itemsList As List(Of OrderItem), 
 ByVal whichForm As ItemsFor, ByVal formID As Integer) As List(Of OrderItem)
 Dim items As New List(Of OrderItem)
 Select Case whichForm
  Case ItemsFor.MfrCredit
   Dim query = From oi As OrderItem In itemsList _
Where oi.ManufacturerCreditID = formID Select oi
   items = query
  Case ItemsFor.CustomerCredit
   Dim query = From oi As OrderItem In itemsList _
 Where oi.CustomerCreditID = formID Select oi
   items = query
  Case ItemsFor.Invoice
   Dim query = From oi As OrderItem In itemsList _
 Where oi.InvoiceID = formID Select oi
   items = query
  Case ItemsFor.PurchaseOrder
   Dim query = From oi As OrderItem In itemsList _
 Where oi.PurchaseOrderID = formID Select oi
   items = query
  Case ItemsFor.Quote
   Dim query = From oi As OrderItem In itemsList _
 Where oi.QuoteID = formID Select oi
   items = query
  Case ItemsFor.StockingOrder
   Dim query = From oi As OrderItem In itemsList _
 Where oi.StockingOrderID = formID Select oi
   items = query
 End Select
 Return items
End Function

I was thinking if I could get the property name somehow as an object I could just do one LINQ statement, but I'm not sure exactly how...

Thanks!

Answer 1

You could do something like:

Dim condition As Func(Of OrderItem, Boolean)
Select Case whichForm
    Case ItemsFor.MfrCredit
        condition = Function(oi As OrderItem) oi.ManufacturerCreditID = formID
    Case ItemsFor.CustomerCredit
        condition = Function(oi as OrderItem) oi.CustomerCreditID = formID
    ...
End Select
Return items.Where(condition).ToList()

It's not perfect but at least it's less code duplication...

Answer 2

one way that you could turn that into a single query would be something like:

Dim query = From oi As OrderItem In itemsList _
Where ((whichForm = ItemsFor.MfrCredit) and (oi.ManufacturerCreditID = formID)) _
or ((whichForm = ItemsFor.CustomerCredit) and (oi.CustomerCreditID = formID)) _
or ((whichForm = ItemsFor.Invoice) and (oi.InvoiceID = formID)) _
...
select oi
items = query

Answer 3

There is a nice article here on Dynamic searchs in Linq with VB by creating an expression tree manually...quite a bit of work. Or another (better?) option is this article on Dynamic Linq (examples here are in C#)

Answer 4

You could use the Predicate delegate. I am imagining an array or list of Predicates, one for each ItemsFor.

Then your query is

Dim query = From oi As OrderItem In itemsList Where predicate select oi

See also this article on building predicates.
And this article on PredicateBuilder.

Answer 5

You can use LINQ expressions, like this: (My VB is rusty, so this might not compile)

Dim param = Expression.Parameter(GetType(OrderItem), "item")
Dim getter = Expression.Lambda(Of Func(Of OrderItem, Integer))( _
    Expression.Property(param, whichForm.ToString()), _
    param _
).Compile()

Return items.Where(Function(item) getter(item) == formId)

For optimal performance, cache the generated delegates in a Dictionary(Of ItemsFor, Func(Of OrderItem, Integer)).

EDIT:

The System.Linq.Expressions namespace allows you to create functions at runtime. This coe uses the feature to create a function that gets a property. Since the Compile method (which actually creates the function) is somewhat slow, it's better to reuse each generated delegate.