Error importing SQL Server function in Entity Framework ADO.NET Entity Data Model

Question

My project is based on ADO.NET Entity Data Model and I use Entity Framework 6.0.

Well, I received this error when I import function in my project:

Error 6046: Unable to generate function import return type of the store function 'fn_PWDCOMPARE'. The store function will be ignored and the function import will not be generated.

I don't get an error if I import a procedure.

The function is:

CREATE FUNCTION fn_PWDCOMPARE (@pwd NVARCHAR(MAX),@pwdhash NVARCHAR(MAX))
RETURNS BIT
BEGIN
  RETURN PWDCOMPARE(@pwd, @pwdhash)
END

Answer 1

EF 6.1.3 still doesn't generate code for Scalar Functions. That said, you can extend the your DbContext class and add your own implementation to your function. Afterwards, you can call your function as any other object inside your EF model.

Create a file containing a [partial class] on the same [namespace] as your DB model, and with your same models context name. And add this code:

public partial class YourDBContext : DbContext
{
    public System.Data.Entity.Core.Objects.ObjectContext AsObjectContext()
    {
        return (this as System.Data.Entity.Infrastructure.IObjectContextAdapter).ObjectContext;
    }

    [DbFunction("YourDBModel.Store", "fn_PWDCOMPARE")]
    public bool fn_PWDCOMPARE(string pwd, string pwdhash)
    {
        var paramList = new ObjectParameter[]
        {
            new ObjectParameter("pwd", pwd),
            new ObjectParameter("pwdhash", pwdhash)
        };

        return this.AsObjectContext().CreateQuery<bool>("YourDBModel.Store.fn_PWDCOMPARE", paramList).Execute(MergeOption.NoTracking).FirstOrDefault();
    }

Then you just call this new function from your code:

bool retVal = YourDBContext.fn_PWDCOMPARE(pass, hash);