LinqToLLBL and the Like SQL operator

Posts   
 
    
omar avatar
omar
User
Posts: 569
Joined: 15-Oct-2004
# Posted on: 03-Jul-2011 11:33:22   

We have a search screen in the UI where the user enters data and then the code should build a dynamic query to fetch the results.

In pre-LINQ era, this was done by building a filterBucket for any of the dirty UI controls and then sending this filterBucket to the BL to fetch the results.

In building the filterBucket, handling the Like operator was trivial as that is supported directly in LLBL's filtering syntax.

When trying to do the same with LINQ, we tried using the LINQ functions StartWith, EndsWith, and Contains instead of LLBL's Like. The problem is this would not support such cases when the text being filtered looks like "firstName%LastName" or "firstName%middleName%anotherMiddleName%LastName"

Is there a way to have the same support (or simulate that support) for LIKE in LLBL's filtering syntax to be also in LLBL's LINQ ?

daelmo avatar
daelmo
Support Team
Posts: 8152
Joined: 28-Nov-2005
# Posted on: 03-Jul-2011 23:32:34   

omar wrote:

Is there a way to have the same support (or simulate that support) for LIKE in LLBL's filtering syntax to be also in LLBL's LINQ ?

Hi Omar. This is not built-in, but you can easily write your own Function mapping to do that:

FunctionMappings

using SD.LLBLGen.Pro.ORMSupportClasses;

namespace NW.LLBL.MSSQL.Adapter.v31.Tests
{
    /// Class which is used to specify the call to the LIKE construct. 
    public class NorthwindFunctions
    {
        public static bool Like(string fieldToSearch, string pattern)
        {
            // empty body, as it's just here to make the query compile. The call is converted to a SQL function.
            return true;
        }
    }

    /// Class which defines the custom mapping between NorthwindFunctions.Lile and SQL LIKE function
    public class NorthwindFunctionMappings : FunctionMappingStore
    {
        public NorthwindFunctionMappings()
            : base()
        {
            // Field LIKE pattern
            this.Add(new FunctionMapping(typeof(NorthwindFunctions), "Like", 2, "({0} LIKE {1})"));
        }
    }
}

Usage

[TestMethod]
public void FilterWithLinqLikePattern()
{
    string searchPattern = "J%n";
    var customers = new List<CustomerEntity>();

    using (var adapter = new DataAccessAdapter())
    {
        var metaData = new LinqMetaData(adapter);
        metaData.CustomFunctionMappings = new NorthwindFunctionMappings();

        customers = (from c in metaData.Customer                        
                        where NorthwindFunctions.Like(c.ContactName, searchPattern)
                        select c).ToList();
    }

    Assert.AreEqual(2, customers.Count);
}
omar avatar
omar
User
Posts: 569
Joined: 15-Oct-2004
# Posted on: 03-Jul-2011 23:36:40   

Thank you daelmo. I will give your function-mapping tip a try and get back to you