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
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.