LINQ To Entities: How to do a multi-criteria search?

My model is the following:

image_thumb[1]

Today’s subject is to use address properties to get all customers with pertinent address comparing to a string array to search.

At first, we will do it only on City:

public static IQueryable<Customer> SearchCustomersByAddressElements(this NorthwindEntities context, string[] addressElements)
{
    var q = context.Customers.Select(c => new { Customer = c, Nb = 0 });
    foreach (string addressElement in addressElements)
    {
        string elt = addressElement;
        q = q.Select(c => new { Customer = c.Customer, Nb = c.Nb + (c.Customer.Address.City.Contains(elt) ? 1 : 0) });
    }
    return from c in q
           where c.Nb != 0
           orderby c.Nb descending
           select c.Customer;
}

For array { "Par", "i", "s"}, the generated SQL query is the following:

exec sp_executesql N’SELECT
[Project1].[CustomerID] AS [CustomerID],
[Project1].[CompanyName] AS [CompanyName],
[Project1].[ContactName] AS [ContactName],
[Project1].[ContactTitle] AS [ContactTitle],
[Project1].[Phone] AS [Phone],
[Project1].[Fax] AS [Fax],
[Project1].[C1] AS [C1],
[Project1].[Address] AS [Address],
[Project1].[City] AS [City],
[Project1].[Region] AS [Region],
[Project1].[PostalCode] AS [PostalCode],
[Project1].[Country] AS [Country]
FROM ( SELECT 
     [Extent1].[CustomerID] AS [CustomerID], 
     [Extent1].[CompanyName] AS [CompanyName], 
     [Extent1].[ContactName] AS [ContactName], 
     [Extent1].[ContactTitle] AS [ContactTitle], 
     [Extent1].[Address] AS [Address], 
     [Extent1].[City] AS [City], 
     [Extent1].[Region] AS [Region], 
     [Extent1].[PostalCode] AS [PostalCode], 
     [Extent1].[Country] AS [Country], 
     [Extent1].[Phone] AS [Phone], 
     [Extent1].[Fax] AS [Fax], 
     0 AS [C1], 
     0 + (CASE WHEN ([Extent1].[City] LIKE @p__linq__0 ESCAPE N”~”) THEN 1 ELSE 0 END) + (CASE WHEN ([Extent1].[City] LIKE @p__linq__1 ESCAPE N”~”) THEN 1 ELSE 0 END) + (CASE WHEN ([Extent1].[City] LIKE @p__linq__2 ESCAPE N”~”) THEN 1 ELSE 0 END) AS [C2]
     FROM [dbo].[Customers] AS [Extent1]
     WHERE 0 <> (0 + (CASE WHEN ([Extent1].[City] LIKE @p__linq__0 ESCAPE N”~”) THEN 1 ELSE 0 END) + (CASE WHEN ([Extent1].[City] LIKE @p__linq__1 ESCAPE N”~”) THEN 1 ELSE 0 END) + (CASE WHEN ([Extent1].[City] LIKE @p__linq__2 ESCAPE N”~”) THEN 1 ELSE 0 END))
)  AS [Project1]

ORDER BY [Project1].[C2] DESC’,N’@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000),@p__linq__2 nvarchar(4000)’,@p__linq__0=N’%Par%’,@p__linq__1=N’%i%’,@p__linq__2=N’%s%’

Note that this one has the same execution plan than the following:

exec sp_executesql N’SELECT
[Project1].[CustomerID] AS [CustomerID],
[Project1].[CompanyName] AS [CompanyName],
[Project1].[ContactName] AS [ContactName],
[Project1].[ContactTitle] AS [ContactTitle],
[Project1].[Phone] AS [Phone],
[Project1].[Fax] AS [Fax],
[Project1].[C1] AS [C1],
[Project1].[Address] AS [Address],
[Project1].[City] AS [City],
[Project1].[Region] AS [Region],
[Project1].[PostalCode] AS [PostalCode],
[Project1].[Country] AS [Country]
FROM ( SELECT 
     [Extent1].[CustomerID] AS [CustomerID], 
     [Extent1].[CompanyName] AS [CompanyName], 
     [Extent1].[ContactName] AS [ContactName], 
     [Extent1].[ContactTitle] AS [ContactTitle], 
     [Extent1].[Address] AS [Address], 
     [Extent1].[City] AS [City], 
     [Extent1].[Region] AS [Region], 
     [Extent1].[PostalCode] AS [PostalCode], 
     [Extent1].[Country] AS [Country], 
     [Extent1].[Phone] AS [Phone], 
     [Extent1].[Fax] AS [Fax], 
     0 AS [C1], 
     0 + (CASE WHEN ([Extent1].[City] LIKE @p__linq__0 ESCAPE N”~”) THEN 1 ELSE 0 END) + (CASE WHEN ([Extent1].[City] LIKE @p__linq__1 ESCAPE N”~”) THEN 1 ELSE 0 END) + (CASE WHEN ([Extent1].[City] LIKE @p__linq__2 ESCAPE N”~”) THEN 1 ELSE 0 END) AS [C2]
     FROM [dbo].[Customers] AS [Extent1]
)  AS [Project1]
WHERE [Project1].[C2] <> 0
ORDER BY [Project1].[C2] DESC’,N’@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000),@p__linq__2 nvarchar(4000)’,@p__linq__0=N’%Par%’,@p__linq__1=N’%i%’,@p__linq__2=N’%s%’

And also than this one:

exec sp_executesql N’SELECT 
     CustomerID, 
     CompanyName, 
     ContactName, 
     ContactTitle, 
     Address, 
     City, 
     Region, 
     PostalCode, 
     Country, 
     Phone, 
     Fax, 
     0 AS [C1]
     FROM [dbo].[Customers]
     WHERE 0 <> (0 + (CASE WHEN ([City] LIKE @p__linq__0 ESCAPE N”~”) THEN 1 ELSE 0 END) + (CASE WHEN ([City] LIKE @p__linq__1 ESCAPE N”~”) THEN 1 ELSE 0 END) + (CASE WHEN ([City] LIKE @p__linq__2 ESCAPE N”~”) THEN 1 ELSE 0 END))
     ORDER BY (CASE WHEN ([City] LIKE @p__linq__0 ESCAPE N”~”) THEN 1 ELSE 0 END) + (CASE WHEN ([City] LIKE @p__linq__1 ESCAPE N”~”) THEN 1 ELSE 0 END) + (CASE WHEN ([City] LIKE @p__linq__2 ESCAPE N”~”) THEN 1 ELSE 0 END) DESC’
,N’@p__linq__0 nvarchar(4000),@p__linq__1 nvarchar(4000),@p__linq__2 nvarchar(4000)’,@p__linq__0=N’%Par%’,@p__linq__1=N’%i%’,@p__linq__2=N’%s%’

So it’s cool! [:)]

Now, we have to write this query for all mapped Address properties.

However, instead of duplicating previous code, we will use model metadata to generate it. Like this, if Address type changes, our query still stays good. // With T4, we would probably duplicate the code but then it wouldn’t be a problem because we would regenerate the code if Address type changes.

So how to do it? My first idea was to use ObjectQuery methods with ESQL fragments. Indeed, string is easy to generate. However, I find it cooler to “play” with Expression Trees.

And so, I write the following code:

public static IQueryable<Customer> SearchCustomersByAddressElements(this NorthwindEntities context, string[] addressElements)
{
    var anonymousCustomerForCast = new { Customer = (Customer)null, Nb = 0 };
    var q = context.Customers.Select(c => new { Customer = c, Nb = 0 });
    Type anonymousType = anonymousCustomerForCast.GetType();
    ConstructorInfo anonymousContructor = anonymousType.GetConstructors()[0];
    foreach (var property in context.MetadataWorkspace.GetItems(System.Data.Metadata.Edm.DataSpace.CSpace).OfType<ComplexType>().First(ct => ct.Name == typeof(Address).Name).Properties)
        foreach (string addressElement in addressElements)
        {
            string elt = addressElement;
            ParameterExpression parameter = Expression.Parameter(anonymousType, "c");
            Expression getCustomerExpression = Expression.MakeMemberAccess(parameter, GetMember(() => anonymousCustomerForCast.Customer));
            q = q.Select(CreateLambdaExpression (anonymousCustomerForCast, anonymousCustomerForCast, Expression.New(anonymousContructor, new Expression[] { getCustomerExpression, Expression.Add(Expression.MakeMemberAccess(parameter, GetMember(() => anonymousCustomerForCast.Nb)), Expression.Condition(Expression.Call(Expression.MakeMemberAccess(Expression.MakeMemberAccess(getCustomerExpression, GetMember((Customer c) => c.Address)), typeof(Address).GetProperty(property.Name)), typeof(string).GetMethod("Contains"), Expression.Constant(elt)), Expression.Constant(1), Expression.Constant(0)))}, GetMember(() => anonymousCustomerForCast.Customer), GetMember(() => anonymousCustomerForCast.Nb)), parameter)); 
        }
 
    return from c in q
           where c.Nb != 0
           orderby c.Nb descending
           select c.Customer;
}

 

public static Expression<Func<T1, T2>> CreateLambdaExpression<T1, T2>(T1 t1, T2 t2, Expression expression, params ParameterExpression[] parameters)
{
    return Expression.Lambda<Func<T1, T2>>(expression, parameters);
}

 

public static MemberInfo GetMember<T>(Expression<Func<T>> exp)
{
    if (exp.Body.NodeType != ExpressionType.MemberAccess)
        throw new ArgumentException();
    return ((MemberExpression)exp.Body).Member;
}

 

public static MemberInfo GetMember<T, T2>(Expression<Func<T, T2>> exp)
{
    if (exp.Body.NodeType != ExpressionType.MemberAccess)
        throw new ArgumentException();
    return ((MemberExpression)exp.Body).Member;
}

Now, if I look for “Paris”, “Québec” and “Canada”, I will find Montréal customers before Paris’s ones, which is what I wanted.

This entry was posted in 12253, 7671, 7674. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>