I've upgraded to EF 5 in order to use TFV. The project is targetting framework 4.5.
I've imported the TVF (GetProjects) without any problem. Function import is composable - checked.
However, got the error below at run-time.
The function import 'KPS_SecurityEntities.GetProjects' is composable. Only non-composable function imports can be executed as stored procedures.
Generated code in context.vb as below:
Public Overridable Function GetProjects(userName As String, applicationCode As String) As ObjectResult(Of GetProjects_Result) DirectCast(Me, IObjectContextAdapter).ObjectContext.MetadataWorkspace.LoadFromAssembly(GetType(GetProjects_Result).Assembly)
Dim userNameParameter As ObjectParameter = If(userName IsNot Nothing, New ObjectParameter("UserName", userName), New ObjectParameter("UserName", GetType(String)))
Dim applicationCodeParameter As ObjectParameter = If(applicationCode IsNot Nothing, New ObjectParameter("ApplicationCode", applicationCode), New ObjectParameter("ApplicationCode", GetType(String)))
Return DirectCast(Me, IObjectContextAdapter).ObjectContext.ExecuteFunction(Of GetProjects_Result)("GetProjects", userNameParameter, applicationCodeParameter) End Function
Decker Dong, it's a Table-valued Function as below, thanks.
ALTER FUNCTION [dbo].[GetProjects]
(
-- Add the parameters for the function here
@UserName varchar(50)
,@ApplicationCode varchar(50)
)
RETURNS
@tb TABLE
(
-- Add the column definitions for the TABLE variable here
ProjectNo varchar(10)
,isAdmin bit
,isExclude bit DEFAULT 0
,RegionCode char(1)
,CountryCode varchar(2)
,BUCode varchar(1)
)
AS
BEGIN
DECLARE @userId uniqueidentifier
DECLARE @applicationId uniqueidentifier
DECLARE @roles table(idx int identity(1,1), RoleType int,RoleScopeType int,Scope varchar(50))
DECLARE @idx int
DECLARE @RoleType int
DECLARE @RoleScopeType int
DECLARE @Scope varchar(50)
-- get user id
SELECT @userId = UserId
FROM dbo.aspnet_Users
WHERE UserName = @UserName
-- get company application Id
SELECT @applicationId = KPSApplicationID
FROM KPSSEC.M_Applications
WHERE KPSApplicationCode = @ApplicationCode
-- userid and applicationid must not be null
IF NOT @userId IS NULL AND NOT @applicationId IS NULL
BEGIN
-- populate roles
INSERT INTO @roles(RoleType,RoleScopeType,Scope)
SELECT b.RoleTypeCode,b.RoleScopeCode,a.ScopeOfRole
FROM KPSSEC.SYS_UsersInRoles a
INNER JOIN KPSSEC.M_RoleDefinitions b
ON a.KpsRoleId = b.KpsRoleId
WHERE a.UserId = @userId AND a.KPSApplicationID = @applicationId
ORDER BY b.RoleTypeCode,b.RoleScopeCode
-- if GLOBAL ADMIN & SYS then insert all projects
IF EXISTS (SELECT * FROM @roles
WHERE RoleType IN (0,2))
BEGIN
INSERT INTO @tb(ProjectNo,isAdmin,RegionCode,CountryCode,BUCode)
SELECT ProjectNo, 1,RegionCode,CountryCode,BUCode
FROM KPSSEC.M_Projects
END
ELSE
BEGIN
-- for each role, populate project number
SELECT @idx = idx
FROM
(SELECT TOP (1) idx FROM @roles) AS Import
WHILE @@ROWCOUNT = 1
BEGIN
SELECT @RoleType = RoleType
,@RoleScopeType = RoleScopeType
,@Scope = Scope
FROM @roles
WHERE idx = @idx
-- insert projects, lowest (project) supersede highest (region) to allow
-- for admin assignment at lowest
-- if project
IF @RoleScopeType = 4
BEGIN
INSERT INTO @tb(ProjectNo,isAdmin,RegionCode,CountryCode,BUCode)
SELECT ProjectNo, CASE WHEN @RoleType = 1 THEN 1 ELSE 0 END,RegionCode,CountryCode,BUCode
FROM KPSSEC.M_Projects
WHERE ProjectNo = @Scope
AND NOT EXISTS(SELECT * FROM @tb WHERE ProjectNo = KPSSEC.M_Projects.ProjectNo)
END
-- if country
IF @RoleScopeType = 2
BEGIN
INSERT INTO @tb(ProjectNo,isAdmin,RegionCode,CountryCode,BUCode)
SELECT ProjectNo, CASE WHEN @RoleType = 1 THEN 1 ELSE 0 END,RegionCode,CountryCode,BUCode
FROM KPSSEC.M_Projects
WHERE CountryCode = @Scope
AND NOT EXISTS(SELECT * FROM @tb WHERE ProjectNo = KPSSEC.M_Projects.ProjectNo)
END
-- if region
IF @RoleScopeType = 1
BEGIN
INSERT INTO @tb(ProjectNo,isAdmin,RegionCode,CountryCode,BUCode)
SELECT ProjectNo, CASE WHEN @RoleType = 1 THEN 1 ELSE 0 END,RegionCode,CountryCode,BUCode
FROM KPSSEC.M_Projects
WHERE RegionCode = @Scope
AND NOT EXISTS(SELECT * FROM @tb WHERE ProjectNo = KPSSEC.M_Projects.ProjectNo)
END
SELECT @idx = idx
FROM
(SELECT TOP (1) idx FROM @roles WHERE (idx > @idx)) AS Import
END
END
-- find exclusion in projects
UPDATE @tb
SET isExclude = 1
WHERE EXISTS(SELECT * FROM KPSSEC.SYS_UsersInProjects
WHERE UserId = @userId AND KPSApplicationID =@applicationId
AND ProjectNo = [@tb].ProjectNo AND isNoAccess = 1)
END
RETURN
END
I tried a manual workaround, which is replacing the EF5 code generation template (tt files) in the current project with the ones from a new project created in VS 2012.
It works currently, i can select from TVF. But don't know if this will create new problem afterwards.
Marked as answer by appleseedb on Nov 28, 2012 06:33 AM
appleseedb
Member
2 Points
5 Posts
EF5 TVF Error: Only non-composable function imports can be executed as stored procedures.
Nov 26, 2012 04:46 AM|LINK
Need help on EF5, thanks.
I've upgraded to EF 5 in order to use TFV. The project is targetting framework 4.5.
I've imported the TVF (GetProjects) without any problem. Function import is composable - checked.
However, got the error below at run-time.
The function import 'KPS_SecurityEntities.GetProjects' is composable. Only non-composable function imports can be executed as stored procedures.
Generated code in context.vb as below:
Public Overridable Function GetProjects(userName As String, applicationCode As String) As ObjectResult(Of GetProjects_Result) DirectCast(Me, IObjectContextAdapter).ObjectContext.MetadataWorkspace.LoadFromAssembly(GetType(GetProjects_Result).Assembly)
Dim userNameParameter As ObjectParameter = If(userName IsNot Nothing, New ObjectParameter("UserName", userName), New ObjectParameter("UserName", GetType(String)))
Dim applicationCodeParameter As ObjectParameter = If(applicationCode IsNot Nothing, New ObjectParameter("ApplicationCode", applicationCode), New ObjectParameter("ApplicationCode", GetType(String)))
Return DirectCast(Me, IObjectContextAdapter).ObjectContext.ExecuteFunction(Of GetProjects_Result)("GetProjects", userNameParameter, applicationCodeParameter) End Function
Any one knows the solution to this? Thanks.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: EF5 TVF Error: Only non-composable function imports can be executed as stored procedures.
Nov 27, 2012 12:25 AM|LINK
How did you write your Stored Procdure? Can you show it out to us to see?
appleseedb
Member
2 Points
5 Posts
Re: EF5 TVF Error: Only non-composable function imports can be executed as stored procedures.
Nov 27, 2012 04:56 AM|LINK
Decker Dong, it's a Table-valued Function as below, thanks.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: EF5 TVF Error: Only non-composable function imports can be executed as stored procedures.
Nov 27, 2012 05:43 AM|LINK
As far as I see, EF doesn't support such type that is of Table. Because EF must map each column to a real table defined in your own tables in a db.
appleseedb
Member
2 Points
5 Posts
Re: EF5 TVF Error: Only non-composable function imports can be executed as stored procedures.
Nov 27, 2012 07:59 AM|LINK
Hi,
I assume EF5 support for TVF map to complex type, based on this http://msdn.microsoft.com/en-us/data/hh859577.aspx
Else, would it work if i just create a dummy table?
Thanks
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: EF5 TVF Error: Only non-composable function imports can be executed as stored procedures.
Nov 27, 2012 11:56 PM|LINK
Hi again,
It would be better for you to create a real table instead.
appleseedb
Member
2 Points
5 Posts
Re: EF5 TVF Error: Only non-composable function imports can be executed as stored procedures.
Nov 28, 2012 05:45 AM|LINK
Thanks..
I tried a manual workaround, which is replacing the EF5 code generation template (tt files) in the current project with the ones from a new project created in VS 2012.
It works currently, i can select from TVF. But don't know if this will create new problem afterwards.
Decker Dong ...
All-Star
118619 Points
18779 Posts
Re: EF5 TVF Error: Only non-composable function imports can be executed as stored procedures.
Nov 28, 2012 06:18 AM|LINK
Hi,
It should work properly, as far as I see……