How to map user defined table valued function to EF 6 database first entity collection?

c# entity-framework entity-framework-6 sql-server user-defined-functions

Question

I am trying to solve a problem with mapping original entity on UDF execution. I have this scenario:

    CREATE FUNCTION [dbo].[GetUsers]
    (
        @password nvarchar(50) NULL
    )
    RETURNS TABLE 
    AS
    RETURN 
    (
     SELECT UserID, Username FROM User
    )

Assume table users has only those two columns(int and nvarchar).For obvious reasons I skipped any logic from this procedure. It simply returns all rows and columns from table User now.

I have mapped table User as entity User and is accessible from dbcontext. And now I want it to be returned from this procedure as IQueryable. I specifically dont want to use result set of "Complex" type but the original entity User.

When I do the function import and set "Returns collection of" to entities - User , it is not working, throws an exception.

Code it generates in dbcontext class looks like this:

    [DbFunction("Entities", "GetUsers")]
    public virtual IQueryable<User> GetUsers(string password)
    {
        var passwordParameter = password != null ?
            new ObjectParameter("password", password) :
            new ObjectParameter("password", typeof(string));

        return ((IObjectContextAdapter)this).ObjectContext.CreateQuery<User>("[Entities].[GetUsers](@password)", passwordParameter);
    }

so when I call the function using:

   var result = dbcontext.GetUsers("pass");

it throws and exception: 'Entities.GetUsers' cannot be resolved into a valid type or function. Near member access expression, line 1, column 11.

"Entities" is indeed correct name of my dbcontext class.

Do I have my function wrong or what can be done about it to make this scenario work?

Thanks a lot

1
2
11/16/2017 6:38:09 PM

Popular Answer

The only problem consisted in bug of entity framework where it cant obviously work with different name/type of connection strings than "Entities" (its the default one what ADO.NET model creates). I modified the T4 template a long time ago. Till moment i wanted to call UDF i never had any problem. It was using different name and kind of connection string which I am sharing across multiple libraries and not all of them support that weird type of connection string looking like:

connectionString="metadata=res:///DataModel.csdl|res:///DataModel.ssdl|res://*/..." one.

public partial class Entities : DbContext
{
    public Entities() : base("name=Entities")
    {
    }

    ...
}   

When I put it back like above, it started working.

0
11/16/2017 7:06:02 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow