Im having a little trouble getting this linq query working with some new requirements. - I am using all the latest llblgen dlls

I am trying to reduce a data set based on locations. It works fine for exact matches but does not find any items that begin with or end with the location name term.

        Dim lmd As New Linq.LinqMetaData()
        Dim linq As IQueryable(Of VwCrsCustomerEntity)
        'LoggedInSysUser.BranchRestrictions is a list of string

        linq = From cus In lmd.VwCrsCustomer

        'BranchSecurity
        If LoggedInSysUser.BranchRestrictions.Count > 0 Then
            linq = From cus In linq Where LoggedInSysUser.BranchRestrictions.Contains(cus.Branch)
        End If

-Returns customer if branch restriction is 'winona' and the cus.branch is 'winona' -Does NOT return customer if branch restriction is 'winona' and the cus.branch is 'winona blending' or 'blending winona'

this seems like an easy one but im having trouble. any help?

Change: Where LoggedInSysUser.BranchRestrictions.Contains(cus.Branch)

To: Where cus.Branch.Contains(LoggedInSysUser.BranchRestrictions)

doesnt fly - contains expects a string and .BranchRestrictions is a list of string

if I change to

Where cus.Branch.ContainsAny(LoggedInSysUser.BranchRestrictions)

I get the following error

The binary expression '(EntityField(LPLA_1.Branch AS Branch).ContainsAny(value(System.Collections.Generic.List`1[System.String])) == True)' can't be converted to a predicate expression.

Ive also tried the below query based on what is discussed here http://stackoverflow.com/questions/1757214/linq-entity-string-field-contains-any-of-an-array-of-strings but I still receive an error

linq = From cus In linq Where LoggedInSysUser.BranchRestrictions.Any(Function(x) cus.Branch.Contains(x)) Select cus

{"Unable to cast object of type 'System.Linq.Expressions.ConstantExpression' to type 'SD.LLBLGen.Pro.LinqSupportClasses.ExpressionClasses.SetExpression'."}

Where .... LoggedInSysUser.BranchRestrictions.Contains(cus.Branch)

won't work because that a collection.Contains(field) predicate will be translated into an "IN" predicate:

WHERE 
     Customer.Branch IN ("Branch1", "Branch2", "BranchN")

So, you have to add the filter one by one, in order to have "ORs" with FilterLikePredicate.

You also could use PredicateBuilder for that (this is aprox. code, it might contains VB typos/erros):

Dim lmd As New Linq.LinqMetaData()
Dim linq As IQueryable(Of VwCrsCustomerEntity)

linq = From cus In lmd.VwCrsCustomer

If LoggedInSysUser.BranchRestrictions.Count > 0 Then

     Dim predicate = PredicateBuilder.Null(Of VwCrsCustomerEntity)
     ForEach branch in BranchRestrictions
          predicate = predicate.Or(cus => cus.Branch.Contains(branch))
     End For
    
     linq = linq.Where(predicate)

End If

Dim results = linq.ToList()

Interesting. Its a bummer its not easier to implement. Hard coding is not an option.

Im tinkering with the predicate builder. It seems like its returning the correct results. I will have to look into performance etc but this may work. Thanks for the info.

for future reference - final code in vb.net

predicate extension

Imports SD.LLBLGen.Pro.LinqSupportClasses.ExpressionHandlers
Imports System.Runtime.CompilerServices
Imports System.Linq.Expressions
Imports LinqExpression = System.Linq.Expressions.Expression

Public Module LLBLGenPredicateBuilder
    Public Function Null(Of T)() As System.Linq.Expressions.Expression(Of Func(Of T, Boolean))
        Return Nothing
    End Function

    Public Function CreateFromToReplaceSet(from As IList(Of ParameterExpression), [to] As IList(Of ParameterExpression)) As Dictionary(Of LinqExpression, LinqExpression)
        Dim toReturn As New Dictionary(Of LinqExpression, LinqExpression)()
        For i As Integer = 0 To from.Count - 1
            toReturn.Add(from(i), [to](i))
        Next
        Return toReturn
    End Function

    <Extension()>
    Public Function [Or](Of T)(expr1 As System.Linq.Expressions.Expression(Of Func(Of T, Boolean)), expr2 As System.Linq.Expressions.Expression(Of Func(Of T, Boolean))) As System.Linq.Expressions.Expression(Of Func(Of T, Boolean))
        If expr1 Is Nothing Then Return expr2

        Dim replacer As New ExpressionReplacer(CreateFromToReplaceSet(expr2.Parameters, expr1.Parameters), Nothing, Nothing, Nothing, Nothing)
        Dim rightExpression As LambdaExpression = DirectCast(replacer.HandleExpression(expr2), LambdaExpression)

        Return System.Linq.Expressions.Expression.Lambda(Of Func(Of T, Boolean))(System.Linq.Expressions.Expression.[OrElse](expr1.Body, rightExpression.Body), expr1.Parameters)
    End Function

    <Extension()>
    Public Function [And](Of T)(expr1 As System.Linq.Expressions.Expression(Of Func(Of T, Boolean)), expr2 As System.Linq.Expressions.Expression(Of Func(Of T, Boolean))) As System.Linq.Expressions.Expression(Of Func(Of T, Boolean))
        If expr1 Is Nothing Then Return expr2
        Dim replacer As New ExpressionReplacer(CreateFromToReplaceSet(expr2.Parameters, expr1.Parameters), Nothing, Nothing, Nothing, Nothing)
        Dim rightExpression As LambdaExpression = DirectCast(replacer.HandleExpression(expr2), LambdaExpression)
        Return System.Linq.Expressions.Expression.Lambda(Of Func(Of T, Boolean))(System.Linq.Expressions.Expression.[AndAlso](expr1.Body, rightExpression.Body), expr1.Parameters)
    End Function
End Module

code useage

Dim lmd As New Linq.LinqMetaData()
Dim linq As IQueryable(Of VwCrsCustomerEntity)

linq = From cus In lmd.VwCrsCustomer

        'BranchSecurity
        If LoggedInSysUser.BranchRestrictions.Count > 0 Then
            'Below code only finds exact matches  
           '  linq = From cus In linq Where LoggedInSysUser.BranchRestrictions.Contains(cus.Branch)

            'Predicate expression builder finds any customers containing location name in associated branch
            Dim predicate = LLBLGenPredicateBuilder.Null(Of VwCrsCustomerEntity)()
            For Each i As String In LoggedInSysUser.BranchRestrictions
                Dim _branch = i  'Very Important
                predicate = predicate.Or(Function(x) x.Branch.Contains(_branch))
            Next

            linq = linq.Where(predicate)
        End If