Using scalar valued functions vis Linq Expressions

[Musashi]

I've got a SVF that works fine in a standard linq query (var query = from a select b etc.).

I'm trying to construct the linq Expression manually and use a SVF in it. Problem is, the executed query calls the SVF stub (the one that throws new NotImplementedException)

The DebugView of the underlying Expression for both the standard linq and constructed linq look the same.

Is it possible to call scalar valued functions by constructing my own linq Expression?

Some code examples:

var query = Context.Persons.Select(p => new RelevanceResult
{
    ID = p.PRIMARYKEY,
    Relevance = ContextClass.GetEditDistance_S(p.Name, search)
});

var personQuery = Context.Persons;

var parExp = Expression.Parameter(typeof(Person));
var newExp = Expression.New(typeof(RelevanceResult));
var bindExps = typeof(RelevanceResult).GetProperties().Select(prop =>
    {
        Expression bindExp = null;
        if (prop.Name == "ID")
        {
            var propExp = Expression.Property(parExp, typeof(Person).GetProperty("PRIMARYKEY"));
            bindExp = Expression.Call(propExp, typeof(Int32).GetMethod("ToString", new Type[] { }));
        }
        else if (prop.Name == "Relevance")
        {
            var propExp = Expression.Property(parExp, typeof(Person).GetProperty("Name"));
            var editDistanceExp = Expression.Call(null, typeof(ContextClass).GetMethod("GetEditDistance_S"), propExp, Expression.Constant(search, typeof(string)));
            var intToDecExp = Expression.Convert(editDistanceExp, typeof(decimal));
            bindExp = intToDecExp;
        }
        return Expression.Bind(prop, bindExp);
    });
var lambdaExp = Expression.Lambda(Expression.MemberInit(newExp, bindExps), parExp);

//  QueryableHelper.SelectGenericMethod is the Queryable.Select<TSource, TResult>(this IQueryable<TSource> source, Expression<Func<TSource, TResult>> selector) generic MethodInfo
var query = QueryableHelper.SelectGenericMethod.MakeGenericMethod(typeof(Person), typeof(RelevanceResult)).Invoke(null, new object[] { personQuery, lambdaExp }) as IQueryable<RelevanceResult>;

[MappedFunctionAttribute(Name = "[dbo].[fn_EditDistance]", IsDeterministic = false, Backend = Backend.MsSql)]
public static int GetEditDistance_S(string fieldName, string search)
{
    throw new NotImplementedException();
}

[Boris Georgiev]

Hi Musashi,

We are not aware of such an issue. We are expecting that this should work but we will need more time to investigate this case.

Could you tell us why you want to do this instead using standard LINQ? What is your scenario?

You could also call this method via SQL:
contex.Persons.Select(p => new RelevanceResult() { ID = p.PRIMARYKEY, Relevance = "[dbo].[fn_EditDistance]({0},{1})".SQL<int>(p.Name, search)});

We will get back to you when we have more information about this case.

Regards,
Boris Georgiev
Telerik by Progress

 

[Musashi]

Basically I'm trying to query across many different entities at the same time to find the most relevant entity based on the search term.
To make it reasonably maintainable, I'm dynamically generating Expressions to avoid manually coding in a separate expression for each entity type.

The end result I want is a Google-like search of everything I have. Still in the experimental stages as to the best way to implement it.

[Thomas]

Hello Musashi,

please find attached a small example that I implemented on the fluent sample NuGet. That example should give you a starting point for dynamic expression generation. 
In the RelevanceSelect method there is a method call ToString that seems superfluous, but ensures that our runtime treats the string value as a parameter, rather than a literal in the SQL.

Best regards,
Thomas
Telerik by Progress