This is a migrated thread and some comments may be shown as answers.

Using scalar valued functions vis Linq Expressions

3 Answers 354 Views
Development (API, general questions)
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Musashi
Top achievements
Rank 1
Musashi asked on 03 Aug 2016, 04:53 PM

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();
}

3 Answers, 1 is accepted

Sort by
0
Boris Georgiev
Telerik team
answered on 05 Aug 2016, 12:45 PM
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
 
Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
0
Musashi
Top achievements
Rank 1
answered on 08 Aug 2016, 11:53 PM

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.

0
Thomas
Telerik team
answered on 12 Aug 2016, 03:00 PM
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
 
Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
Tags
Development (API, general questions)
Asked by
Musashi
Top achievements
Rank 1
Answers by
Boris Georgiev
Telerik team
Musashi
Top achievements
Rank 1
Thomas
Telerik team
Share this question
or