VB.Net I'm trying to write an extension for a generic linq search, however I'm not sure how to return more than one result 0.o

Question

I'm a bit new to vb.net and used to working in perl, this is what I'd like to do.

I wanted something similar to DBIX::Class::Resultset's search (from cpan) in my vb.net project, so that I can give my function a hash containing keys and values to search on a table.

Currently it returns a single matching result of type T where I want it to return all results as a data.linq.table(of T)

How should I alter my expression.lambda so that I can say table.Select(Predicate) to get a set of results? After that I think it should be as simple as saying results.intersect(result) instead of Return test.

Any help will be very much appreciated.

Thanks in advance

-Paul

<System.Runtime.CompilerServices.Extension()> _
Public Function Search(Of T As Class)(ByVal context As DataContext, _
                                       ByVal parameters As Hashtable) As T
    Dim table = context.GetTable(Of T)()
    Dim results As Data.Linq.Table(Of T)
    For Each Parameter As DictionaryEntry In parameters
        Dim column As Object = Parameter.Key
        Dim value As String = Parameter.Value
        Dim param = Expression.Parameter(GetType(T), column)
        Dim Predicate = Expression.Lambda(Of Func(Of T, Boolean)) _
                        (Expression.[Call](Expression.Convert(Expression.Property(param, column), _
                            GetType(String)), GetType(String).GetMethod("Contains"), _
                            Expression.Constant(value)), New ParameterExpression() {param})
        Dim test = table.First(Predicate)

        Return test
        ' result.intersect(result)
    Next
    'Return results
End Function

Answer 1

Well, for starters let's change the return type to Data.Linq.Table(Of T).

Then instead of table.First(Predicate), try table.Where(Predicate)

Finally 'Intersect' will only give you results that contain all your parameters. If that's what you want, then fantastic! If not, then try 'Union' instead.

Let me know where that gets you and we can work from there.

Answer 2

This works assuming you want an "AND" conjunction between predicates

For instance:

    Dim h = New System.Collections.Hashtable
    h.Add("FieldA", "01 5149")
    h.Add("FieldB", "WESTERN")
    Dim t = (New DBDataContext).Search(Of DBrecord)(h)
    Debug.Print(t.Count.ToString)

Would return those records where fieldA matched AND fieldb matched. If you wanted OR, DiceGuy's right, use UNION.

Here's the search... Note, I used STARTSWITH instead of contains because it's alot faster for large sets You can always change it back.

<System.Runtime.CompilerServices.Extension()> _
Public Function Search(Of T As Class)(ByVal context As DataContext, _
                                   ByVal parameters As Hashtable) As IQueryable(Of T)
    Dim table = context.GetTable(Of T)()
    Dim results As IQueryable(Of T) = Nothing
    For Each Parameter As DictionaryEntry In parameters
        Dim column = DirectCast(Parameter.Key, String)
        Dim value As String = DirectCast(Parameter.Value, String)
        Dim param = Expression.Parameter(GetType(T), column)
        Dim Predicate = Expression.Lambda(Of Func(Of T, Boolean)) _
                        (Expression.[Call](Expression.Convert(Expression.Property(param, column), _
                            GetType(String)), GetType(String).GetMethod("StartsWith", New Type() {GetType(String)}), _
                            Expression.Constant(value)), New ParameterExpression() {param})
        Dim r = table.Where(Predicate)
        If results Is Nothing Then
            results = r
        Else
            results = results.Intersect(r)
        End If
    Next
    Return results
End Function