none
LINQ Contains function, for comma separated list?

    Question

  • Hi,

    This works,

    Dim result = (From certificate In context.SupplierCertificate _  
                  Where certificate.IsDeleted = 0 _  
                  And certificate.SupplierMaster.SourceId.Contains("AAA") _  
                  Select certificate).ToList() 

    This does not work,

    Dim result = (From certificate In context.SupplierCertificate _  
                  Where certificate.IsDeleted = 0 _  
                  And certificate.SupplierMaster.SourceId.Contains("AAA,BBB") _  
                  Select certificate).ToList() 

    why? How do I get a comma separated list to work, like an IN statement in normal SQL?
    I.W Coetzer
    Friday, March 06, 2009 1:37 PM

Answers

  • Hi, figured it out, by working through the examples with a colleague (Sasa Milovic)
    This solved the problem of having IN statements ...

    Here is a simple example,

    'instantiate a new LINQ context  
    Dim context As New SupplierDB.DataAccess.LINQ.SupplierDBEntities()  
     
    Dim sourceKeys As String = "'AAA','BBB','CCC'" 
    Dim query As String = String.Empty  
    Dim result As List(Of SupplierCertificate) = Nothing 
     
    query &= "SELECT VALUE certificate FROM SupplierCertificate AS certificate" 
    query &= " " 
    query &= "WHERE certificate.SupplierMaster.SourceID IN {" & sourceKeys & "} " 
     
    'retrieve the list of supplier certificates, based on the object query above  
    result = New ObjectQuery(Of SupplierCertificate)(query, context).ToList()  
     
    'return the list   
    Return result 

    And a more complex example! with additional where claueses ...


    'instantiate a new LINQ context  
    Dim context As New SupplierDB.DataAccess.LINQ.SupplierDBEntities()  
     
    Dim query As String = String.Empty  
    Dim result As List(Of SupplierCertificate) = Nothing 
     
    query &= "SELECT VALUE certificate FROM SupplierCertificate AS certificate" 
    query &= " " 
    query &= "WHERE certificate.IsDeleted = 0 " 
    query &= "AND certificate.SupplierMaster.SourceID IN {" & sourceKeys & "} " 
     
    If (countryCode <> "All"Then query &= "AND certificate.SupplierMaster.CountryCode = '" & countryCode & "' " 
    If (exclusionReason <> "All"Then query &= "AND certificate.SupplierMaster.BeeExclusionReason = '" & exclusionReason & "' " 
     
    'retrieve the list of supplier certificates, based on the object query above  
    result = New ObjectQuery(Of SupplierCertificate)(query, context).ToList()  
     
    'return the list   
    Return result 





    I.W Coetzer
    • Marked as answer by I.W Coetzer Monday, March 09, 2009 8:35 AM
    Monday, March 09, 2009 8:34 AM

All replies

  •  Hi,

    The method that you are invoking in  the queries is System.String  Boolean Contains(String value) method  and is supported by Linq to Entities.

    What you may be after is the the Linq Contains method that determines whether a sequence contains a specified element and corresponds to Sql's IN. That one is not supported in the current release of Linq to Entites but will be supported in the next release. 
    For a workaround in the current version please see Colin’s answer in http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/095745fe-dcf0-4142-b684-b7e4a1ab59f0.

    Hope that helps,
    Kati


     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Saturday, March 07, 2009 1:34 AM
  • Hi, Thanks but I do not know how to implement this function, I have translated the "C#" example to VB.Net (since that is the language I have to use at work) and I'm receiving errors in the syntax used.

    Dim result = context.SupplierCertificate.Where (BuildContainsExpression(Of SupplierCertificate, String)(Function(e) e.???, sourceKeys)


    This is another failed attempt ...

    Dim result = (From certificate In context.SupplierCertificate _  
                  Where certificate.IsDeleted = 0 _  
                  And certificate.SupplierMaster.SourceId.Contains("SAPR3,SWT") _  
                  And BuildContainsExpression(of SupplierCertificate, certificate)( => certificate.SupplierMaster.SourceId ,sourceKeys) _  
                  Select certificate).ToList() 






    I.W Coetzer
    Monday, March 09, 2009 6:19 AM
  • Hi,

    I basically have an entity in the ADO.NET Entity Framework Model called SupplierCertificate, this entity consists of a number of fields of which I want to do a where clause on two of the fields:

    for example:

    1) IsDeleted = 0
    2) SourceId IN ('AAA', 'BBB', 'CCC')

    I cannot decypher this predicate, buildexpression thing to build a linq statement that will return the entities filtered based on the example given above.

    please help.
    I.W Coetzer
    Monday, March 09, 2009 6:25 AM
  • Hi, figured it out, by working through the examples with a colleague (Sasa Milovic)
    This solved the problem of having IN statements ...

    Here is a simple example,

    'instantiate a new LINQ context  
    Dim context As New SupplierDB.DataAccess.LINQ.SupplierDBEntities()  
     
    Dim sourceKeys As String = "'AAA','BBB','CCC'" 
    Dim query As String = String.Empty  
    Dim result As List(Of SupplierCertificate) = Nothing 
     
    query &= "SELECT VALUE certificate FROM SupplierCertificate AS certificate" 
    query &= " " 
    query &= "WHERE certificate.SupplierMaster.SourceID IN {" & sourceKeys & "} " 
     
    'retrieve the list of supplier certificates, based on the object query above  
    result = New ObjectQuery(Of SupplierCertificate)(query, context).ToList()  
     
    'return the list   
    Return result 

    And a more complex example! with additional where claueses ...


    'instantiate a new LINQ context  
    Dim context As New SupplierDB.DataAccess.LINQ.SupplierDBEntities()  
     
    Dim query As String = String.Empty  
    Dim result As List(Of SupplierCertificate) = Nothing 
     
    query &= "SELECT VALUE certificate FROM SupplierCertificate AS certificate" 
    query &= " " 
    query &= "WHERE certificate.IsDeleted = 0 " 
    query &= "AND certificate.SupplierMaster.SourceID IN {" & sourceKeys & "} " 
     
    If (countryCode <> "All"Then query &= "AND certificate.SupplierMaster.CountryCode = '" & countryCode & "' " 
    If (exclusionReason <> "All"Then query &= "AND certificate.SupplierMaster.BeeExclusionReason = '" & exclusionReason & "' " 
     
    'retrieve the list of supplier certificates, based on the object query above  
    result = New ObjectQuery(Of SupplierCertificate)(query, context).ToList()  
     
    'return the list   
    Return result 





    I.W Coetzer
    • Marked as answer by I.W Coetzer Monday, March 09, 2009 8:35 AM
    Monday, March 09, 2009 8:34 AM
  • Hi Coetzer,
    I have a "WHERE IN" query which works fine with one condition, now i want to add one more condition in my existing query.
    Can you please help me out in writing the second condition.

    my second condition is "WHERE cust.Region == _strRegionName"

    below is my query with first condition with "WHERE IN or BuildContainsExpression" which is working fine, i want to add second condition in this query:

    var query = dao.LineItemInterface.Where(BuildContainsExpression<LineItemInterface, string>(l => l.BillingGroupID, strArray));


    static Expression<Func<TElement, bool>> BuildContainsExpression<TElement, TValue>(

                   Expression<Func<TElement, TValue>> valueSelector, IEnumerable<TValue> values)
                    {

                        if (null == valueSelector) { throw new ArgumentNullException("valueSelector"); }

                        if (null == values) { throw new ArgumentNullException("values"); }

                        ParameterExpression p = valueSelector.Parameters.Single();

                        // p => valueSelector(p) == values[0] || valueSelector(p) == ...

                        if (!values.Any())
                        {

                            return e => false;

                        }

                        var equals = values.Select(value => (Expression)Expression.Equal(valueSelector.Body, Expression.Constant(value, typeof(TValue))));

                        var body = equals.Aggregate<Expression>((accumulate, equal) => Expression.Or(accumulate, equal));

                        return Expression.Lambda<Func<TElement, bool>>(body, p);

                    }

    Thanka
    M A

    Wednesday, November 11, 2009 3:44 PM