linq entity framework

Entity Framework provides several methods from the System.String class that are supported and translated into SQL when building wildcard-enabled LINQ queries. These methods include:

  • Contains(string value)
  • StartsWith(string value)
  • EndsWith(string value)

csharp
var q = (from c in db.Customers
where c.CompanyName.Contains(name)
select c)
.ToList();

In this example, the query will always search for any matches within the CompanyName field. To provide users with more control over the match method, allowing them to supply wild-card characters at either the start or end of the text to match, you must dynamically build your query based on the presence and location of the wild-card characters.

Utilizing Expression Trees

To avoid writing cumbersome code, Expression Trees can be used to build the query dynamically. The following extension methods can be used for this purpose:

“`csharp
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Objects;
using System.Data.Objects.DataClasses;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

public static class LinqExtensions
{
public static IQueryable WhereLike(
this IQueryable source,
Expression> valueSelector,
string value,
char wildcard)
{
return source.Where(BuildLikeExpression(valueSelector, value, wildcard));
}

public static Expression<Func<TElement, bool>> BuildLikeExpression<TElement>(
    Expression<Func<TElement, string>> valueSelector,
    string value,
    char wildcard)
{
    if (valueSelector == null)
        throw new ArgumentNullException("valueSelector");

    var method = GetLikeMethod(value, wildcard);

    value = value.Trim(wildcard);
    var body = Expression.Call(valueSelector.Body, method, Expression.Constant(value));

    var parameter = valueSelector.Parameters.Single();
    return Expression.Lambda<Func<TElement, bool>>(body, parameter);
}

private static MethodInfo GetLikeMethod(string value, char wildcard)
{
    var methodName = "Contains";

    var textLength = value.Length;
    value = value.TrimEnd(wildcard);
    if (textLength > value.Length)
    {
        methodName = "StartsWith";
        textLength = value.Length;
    }

    value = value.TrimStart(wildcard);
    if (textLength > value.Length)
    {
        methodName = (methodName == "StartsWith") ? "Contains" : "EndsWith";
        textLength = value.Length;
    }

    var stringType = typeof(string);
    return stringType.GetMethod(methodName, new Type[] { stringType });
}

}
“`

These methods can be reused in future queries, allowing for more efficient and dynamic query building.