Having problem when applying filter on child collection

[elferone]

I have a class Project that contains a collection of ProjectResourceMembers.

My filter is defined like this:

//Security
                modelBuilder.Filter("ProjectFilter",
                    (Project p, string resourceCode) => (p.ProjectResourceMembers.Any(x => x.ResourceCode.ToLower() == GetResourceCodeFromPrincipal(Thread.CurrentPrincipal)) 
                    && p.IsConfidential == true) || p.IsConfidential == false,
                    () => GetResourceCodeFromPrincipal(Thread.CurrentPrincipal));

                modelBuilder.EnableFilter("ProjectFilter", () => !string.IsNullOrEmpty(GetResourceCodeFromPrincipal(Thread.CurrentPrincipal)));

However, when I query the context (via OData), I get this exception:

variable 'x' of type 'DataHub.Provider.Models.Atomic.ProjectResourceMember' referenced from scope '', but it is not defined

The error seems to be related to x.ResourceCode.ToLower()... Any idea what the error might be?

[JonathanMagnan]

Hello @elferone ,

I'm not exactly sure yet what's the error but something for sure doesn't work in this expression.

The filter modifies the SQL queries that will be sent to the database.

However, this part: x => x.ResourceCode.ToLower() == GetResourceCodeFromPrincipal(Thread.CurrentPrincipal)

Cannot be translated to a SQL query because of the method GetResourceCodeFromPrincipal

I believe you can already use the variable resourceCode instead.

Can you try to fix the filter expression and tell us if you still have the same issue?

Best Regards,

Jonathan

[elferone]

I have fixed the expression to:

//Security
                modelBuilder.Filter("ProjectFilter",
                    (Project p, string resourceCode) => (p.ProjectResourceMembers.Any(x => x.ResourceCode.ToLower() == resourceCode) 
                    && p.IsConfidential == true) || p.IsConfidential == false,
                    () => Thread.CurrentPrincipal.Identity.Name.ToLower());

The error still happens when I use the ToLower() function on x.ResourceCode:

x => x.ResourceCode.ToLower()

[JonathanMagnan]

Hello @elferone ,

Thank you, I will try to create a similar filter on my side and see if something is missing for the ToLower method.

Best Regards,

Jonathan

[jcachat]

.ToLower() is not supported. And I don't see an obvious way to map that C# function to the database function. It does not seem to be exposed in DbExpressionBuilder (we are limited in this library to what EF has decided to expose to us).

There are some other custom functions being handled in LambdaToDbExpressionVisitor.VisitMethodCall() - that would probably be the right place to add support if it's possible to map it.

[JonathanMagnan]

Thank you @jcachat for letting me know.

I believe there is certainly a way since a DbExpression must be returned but I'm not sure either how doing it yet.

Best Regards,

Jonathan

[JonathanMagnan]

Hello @elferone ,

The v3.0.0 has been released.

The support for ToLower and ToUpper for basic scenario should now work.

Let me know if you run into another issue.

@jcachat , using your code made this request pretty easy ;) I'm not sure if that handle all scenarios but that will at least handle some basic scenario,

Here is the code added

case "ToLower":
	expression = MapSimpleExpression(node, EdmFunctions.ToLower);
	break;

private Expression MapSimpleExpression(MethodCallExpression node, Func<DbExpression, DbFunctionExpression> dbExpressionFactory)
{
	var expression = base.VisitMethodCall(node) as MethodCallExpression;

	DbExpression srcExpression = GetDbExpressionForExpression(expression.Object);
	var dbExpression = dbExpressionFactory(srcExpression);
	MapExpressionToDbExpression(expression, dbExpression);
	return expression;
}

Best Regards,

Jonathan

[elferone]

Works like a charm. Good job and thanks again!