Create expression tree for "like" on a decimal field

Question

I would like to create an expression tree for a query expression that looks something like this: employee => employee.Salary.StartsWith("28")

So that the sql could appear as: where (employee.salary like '28%')

The problem is that the property Salary of the employee object is a decimal and StartsWith is not a property of a decimal. How can i get around to do this.

My erroneous expression tree syntax is as follows:

var searchTextExp = Expression.Constant("28");
var parameterExp = Expression.Parameter(typeof(EmployeeEntity), "employee");
var propertyExp = Expression.Property(parameterExp, "Salary");
var startsWithExp = Expression.Call(propertyExp, "StartsWith", null, 
   searchTextExp);
Expression<Func<EmployeeEntity, bool>> searchExpr = 
   Expression.Lambda<Func<EmployeeEntity, bool>>
     (startsWithExp, new ParameterExpression[] { parameterExp });

Answer 1

You know the easiest solution (that will probably also work in Linq to Sql) is:

employee => employee.Salary.ToString().StartsWith("28");

Answer 2

If llblgen does not support ToString() you can workaround the problem by creating a View that does the cast. What's your target database?

If it's SQL Server you can use the CAST() function to create the view.

e.g.

  CREATE VIEW viewName as 
    SELECT CAST(Salary as varchar) as Salary
    FROM Employee

After that you should be able to map that view against a new class and do the query like stated above.

employee = employee.Salary.StartsWith("28");

Answer 3

I have managed to solve this using Function Mappings which is a feature of LLBLGEN Pro.

public class Functions
{
    public static bool Like(string field, string value)
    {
        return true;
    }

    public static bool Like(decimal field, string value)
    {
        return true;
    }
}

public class FunctionMappings : FunctionMappingStore
{
    public FunctionMappings()
        : base()
    {
        FunctionMapping mapping = new FunctionMapping(
            typeof(Functions), 
            "Like", 
            2, 
            "{0} LIKE {1}");

        this.Add(mapping);
    }
}

I then attached an instance of FunctionMappings to the LINQ Metadata:

metadata.Mappings = new FunctionMappings();

Then used the Function as follows:

employee => Functions.Like(employee.Salary,"28")