Call custom function in EF LINQ query Where clause

Question

Env: EF6 + Code First

I want to be able to call a custom function in the Where clause of a LINQ query

So this line:

var activeStaff = Repo.Staff.Where(s => s.EndDate == null || s.EndDate.Value > DateTime.Today);

becomes:

var activeStaff = Repo.Staff.Where(s => MyEdmFunctions.IsCurrentStaff(s));

This is what I have tried,

public class MyContext : DbContext
{
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Conventions.Add(new MyCustomConvention());
    }
}

public class MyCustomConvention : IConceptualModelConvention<EdmModel>
{
    public void Apply(EdmModel item, DbModel model)
    {
        var boolType = PrimitiveType.GetEdmPrimitiveType(PrimitiveTypeKind.Boolean);
        var staffType = item.EntityTypes.Single(e => e.Name == "Staff");

        var payLoad = new EdmFunctionPayload
        {
            ParameterTypeSemantics = ParameterTypeSemantics.AllowImplicitConversion,
            IsComposable = true,
            IsNiladic = false,
            IsBuiltIn = false,
            IsAggregate = false,
            IsFromProviderManifest = true,
            Parameters = new[] { FunctionParameter.Create("Staff", staffType, ParameterMode.In) },
            ReturnParameters = new[] { FunctionParameter.Create("ReturnType", boolType, ParameterMode.ReturnValue) }
        };
        var function = EdmFunction.Create("IsCurrentStaff", "My.Core.Data", DataSpace.CSpace, payLoad, null);
        item.AddItem(function);
    }
}

public static class MyEdmFunctions
{
    [DbFunction("My.Core.Data", "IsCurrentStaff")]
    public static bool IsCurrentStaff(Staff s)
    {
        return s.EndDate == null || s.EndDate > DateTime.Today;
    }
}

But I'm getting "Specified method is not supported." error from EntityFramework's internal CTreeGenerator class (after decompilation)

    public override DbExpression Visit(NewRecordOp op, Node n)
    {
        throw new NotSupportedException();
    }

Can someone please confirm if there is really no way to call a custom function in the where clause?

I know it's possible to create a stored procedure and map it in the model. But is there a way to write it in C#?

Thanks.

Answer 1

Just to answer my own question:

I'm going to follow this thread to create a AndAlso expression to solve my problem.

Extension method in where clause in linq to Entities