Linq Provider Does not Translate User Defined Function in Select

I am having trouble with the following query which needs to execute a user defined function in the select clause. I have the function mapped as described in this post http://www.mindscapehq.com/documentation/lightspeed/Advanced-Querying-Techniques/Invoking-SQL-Functions . However, the select seems to be being executed by the .NET tier. Is there any way to force the select to be evaluated as part of the query. If not could you provide some insight on what triggers the lightspeed linq provider to translate the select statement as opposed to letting it run on the application tier?

(from proj in uow.Projects
                           join prop in uow.Properties on proj.Id equals prop.ProjectId
                           join pt in uow.PropertyTypes on prop.PropertyTypeSystemName equals pt.SystemName 
                           where proj.Id == projectId && prop.PropertyNumber.Contains(searchText)
                           orderby prop.PropertyNumber
                           select new
                           {
                               prop.Id,
                               Project = proj.ProjectNumber + "-" + proj.ProjectName,
                               Tract = prop.PropertyNumber,
                               TractType = pt.DisplayName,
                               Owner = DbFunctions.FN_GETLANDOWNERNAME_ETAL(prop.Id)
                           }
            ).ToList();

In addition to just the projection I cannot get the function to be translated anywhere without error. I added it to the where clause and got the following error.

Object reference not set to an instance of an object.

at Mindscape.LightSpeed.Linq.Translations.MultiFunctionBase.Apply(QueryExpression applyTo, DataProvider provider, QueryExpression[] arguments) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractBinaryCriteria(LinqQueryPlanExpression plan, BinaryExpression expression) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractBinaryCriteria(LinqQueryPlanExpression plan, BinaryExpression expression) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(LinqQueryPlanExpression plan, Expression expression) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(LinqQueryPlanExpression plan, Expression expression) at Mindscape.LightSpeed.Linq.Sqo.CriteriaSqo.ExtractCriteria(LinqQueryPlanExpression plan, Expression expression) at Mindscape.LightSpeed.Linq.Sqo.Where.ExtractWhereCriteria(MethodCallExpression expression, LinqQueryPlanExpression plan, GroupResultsPlan groupPlan) at Mindscape.LightSpeed.Linq.Sqo.Where.Evaluate(ExpressionVisitor visitor, MethodCallExpression expression) at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall(MethodCallExpression exp) at Mindscape.LightSpeed.Linq.Sqo.OrderSqo.Evaluate(ExpressionVisitor visitor, MethodCallExpression expression) at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall(MethodCallExpression exp) at Mindscape.LightSpeed.Linq.Sqo.Select.Evaluate(ExpressionVisitor visitor, MethodCallExpression expression) at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall(MethodCallExpression exp) at Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression) at Mindscape.LightSpeed.Linq.LinqQuery`1.GetEnumerator()

(from proj in uow.Projects
                           join prop in uow.Properties on proj.Id equals prop.ProjectId
                           join pt in uow.PropertyTypes on prop.PropertyTypeSystemName equals pt.SystemName
                           where proj.Id == projectId && prop.PropertyNumber.Contains(searchText) && DbFunctions.FN_GETLANDOWNERNAME_ETAL(prop.Id) == searchText
                           orderby prop.PropertyNumber
                           select new
                           {
                               prop.Id,
                               Project = proj.ProjectNumber + "-" + proj.ProjectName,
                               Tract = prop.PropertyNumber,
                               TractType = pt.DisplayName,
                               Owner = "Not Implemented"
                           }
            ).ToList();

I also tried adding it to the order by clause and got

This type of ordering is not supported by LightSpeed, check that your ordering does not contain a value or aggregate function.

at Mindscape.LightSpeed.Linq.Sqo.OrderSqo.Evaluate(ExpressionVisitor visitor, MethodCallExpression expression) at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall(MethodCallExpression exp) at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Visit(Expression exp) at Mindscape.LightSpeed.Linq.Sqo.Select.Evaluate(ExpressionVisitor visitor, MethodCallExpression expression) at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.VisitMethodCall(MethodCallExpression exp) at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Visit(Expression exp) at Mindscape.LightSpeed.Linq.Plan.LinqQueryBuilder.Build(Expression translation, LinqQueryProvider provider) at Mindscape.LightSpeed.Linq.LinqQueryProvider.GetExecutionPlan(Expression expression) at Mindscape.LightSpeed.Linq.LinqQueryProvider.Execute(Expression expression) at Mindscape.LightSpeed.Linq.LinqQueryProvider.System.Linq.IQueryProvider.Execute(Expression expression) at Mindscape.LightSpeed.Linq.LinqQuery1.GetEnumerator() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source)

You need to define the function as either a member function on the entity class or an extension method for the entity class, having these in a seperate "helpers" class is not supported.

Additionally these are not supported in an order by as per the exception.

Jeremy

I defined the function as a extension method of my properties entity and still got the same result. Looking at the trace logs including this call makes the query include all columns from all referenced entities and then tries to execute this code on the application tier. I thought this may be because of string concatenation in on the Project property of the results set when I comment this out I get the same result. I have also provided the function definition and mapping.

P.S. In regards to the previous post about not supporting order by, one of lightspeed blog posts shows this functionality. http://www.mindscapehq.com/blog/index.php/2009/08/10/custom-functions-in-linq/

            var retvalTyped = (from proj in uow.Projects
                           join prop in uow.Properties on proj.Id equals prop.ProjectId
                           join pt in uow.PropertyTypes on prop.PropertyTypeSystemName equals pt.SystemName into ptg
                           from propType in ptg.DefaultIfEmpty()
                           where proj.Id == projectId && prop.PropertyNumber.Contains(searchText)
                           orderby prop.PropertyNumber
                           select new
                           {
                               prop.Id,
                               //Project = proj.ProjectNumber + "-" + proj.ProjectName,
                               Tract = prop.PropertyNumber,
                               TractType = propType.DisplayName,
                               Owner = prop.FN_GETLANDOWNERNAME_ETAL(prop.Id)
                           }
            ).ToList();

    public static class DbFunctions
{
    private static bool _isInitilized;
    public static void DbFunctionsInit()
    {
        if (_isInitilized)
            return;

        _isInitilized = true;

        var methodInfo = typeof(DbFunctions).GetMethod(FnGetlandownernameEtalFunctionName);
        ServerFunctionDescriptor.Register(methodInfo, FnGetlandownernameEtalFunctionName);
    }

    private const string FnGetlandownernameEtalFunctionName = "FN_GETLANDOWNERNAME_ETAL";
    public static string FN__GETLANDOWNERNAME_ETAL(this Property prop, long propertyId)
    {
        throw new NotImplementedException();
    }
}

Try updating the definition to:

public static string FN__GETLANDOWNERNAME_ETAL(this long propertyId)
{
    throw new NotImplementedException();
}

And calling it via:

select new
{
  prop.Id,
  Tract = prop.PropertyNumber,
  TractType = propType.DisplayName,
  Owner = prop.Id.FN_GETLANDOWNERNAME_ETAL()
}

It needs to be structured as a function attached to an entity property.

Jeremy

I made the changes mentioned above and they seem to have no effect on the outcome. Just as as a side note when I used the version of the function with the extension method on Property it seems to have worked in the where clause. The query builder knew to use the property id implicitly. The syntax above seems very strange as it puts the extension method on the primitive type which then becomes available for all instances of long. This seems like it could become a very confusing convention as the extension methods will show in intellisense for all longs.

My take on this issue is that lightspeed does not seem to support calling UDF's in a select statement via linq. Any thoughts on what it will take to enable this functionality in nightly build or major release? Formatting data on the database server for display is the main reason I use UDF's as they tend to be very inefficient in where clauses. This is the main reason why I have to abandon lightspeed and revert to the standard command connection model for data access.

The extension methods do need to be mapped against a property so thats correct they would be available for all primative types when that extension method is in scope.

Are you able to send through the above as a repro project and I can investigate what would be involved for this and if we are able to add the required support.

Thanks!
Jeremy

Attached is a repro project. Sorry for the delay.

Frustratingly this is a LINQ oddity in that the expression tree we are presented to parse is subtly different between the version with the where clause and without the where clause having the effect that we see a client side traversal.

Similarly the ordering of your statement itself impacts this, e.g.

from prop in uow.Properties
join pt in uow.PropertyTypes on prop.PropertyTypeSystemName equals pt.SystemName
where prop.PropertyNumber.Contains("")

Does not work as it introduces a client side projection due to the join, however..

from prop in uow.Properties
where prop.PropertyNumber.Contains("")
join pt in uow.PropertyTypes on prop.PropertyTypeSystemName equals pt.SystemName

Will work as you expect as it produces a different expression tree despite appearing logically equivalent. So what you want to do here is use the second statement.

Jeremy

I still can't get this to work even when I don't have a where clause. The following forces a client side projection even though all the fields can be evaluated in the database. It seems like any non entity field in the select new forces a client side projection which is this case is incorrect as the function can be evaluated on the database side. It seems like the code in your expression evaluator should be able to figure this from the expression tree and the presence of the db function mapping. Any thoughts? Thanks.

(from proj in uow.Projects
                           join prop in uow.Properties on proj.Id equals prop.ProjectId
                           join pt in uow.PropertyTypes on prop.PropertyTypeSystemName equals pt.SystemName into ptg
                           from propType in ptg.DefaultIfEmpty()
                           //where proj.Id == projectId && prop.PropertyNumber.Contains(searchText)
                           orderby prop.PropertyNumber
                           select new
                           {
                               prop.Id,
                               proj.ProjectNumber,
                               proj.ProjectName,
                               Tract = prop.PropertyNumber,
                               TractType = propType.DisplayName,
                               Owner = prop.Id.FN_GETLANDOWNERNAME_ETAL()
                           }
            ).ToList();

Ive had a look into this and found that this is occurring due to the types of anonymous projection structures involved which we are incorrectly classing as not natively projectable. I have added in an improvement to handle this and we will be looking at merging this into the nightly build shortly. I will update you once its available - should be either tonight or tomorrows build.

Jeremy

This has now been merged in and will be available in the next nightly build.

Jeremy

Thanks. I'll pull it down and give it a whirl. Thanks for all your help on this issue.

I'm running into issues with nightly build LightSpeed50Professional-20140718. Its causing queries that previously worked fine in my application to fail. The query below is throwing

Specified argument was out of the range of valid values. Parameter name: The provided string argument [path] cannot be empty

       var metaDataLookupsQuery = from mda in ContextFactory.Repo.DocumentMetaDataAttributes
                                   join mdl in ContextFactory.Repo.DocumentMetaDataLookups on mda.Id equals
                                       mdl.DocumentMetaDataAttributeId
                                   where mdl.DocumentGroupName == documentGroupName
                                   select new DynamicMetaDataLookUpEntry(mdl.AsDto(), mda.AsDto());
        var groupLookups = metaDataLookupsQuery.ToList();