Using scalar valued functions vis Linq Expressions

4 posts, 0 answers
  1. Musashi
    Musashi avatar
    20 posts
    Member since:
    Jan 2014

    Posted 03 Aug Link to this post

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

  2. Boris Georgiev
    Admin
    Boris Georgiev avatar
    190 posts

    Posted 05 Aug Link to this post

    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.
  3. DevCraft banner
  4. Musashi
    Musashi avatar
    20 posts
    Member since:
    Jan 2014

    Posted 08 Aug Link to this post

    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.

  5. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 12 Aug Link to this post

    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.
Back to Top