Normally CTE results cannot be directly used in Entity framework as the CTE does not describe any column as PRIMARY.

As a work around this problem we can use Table Valued Function to make the result set useful for the Entity framework.

Following is a sample example,

CREATE FUNCTION [dbo].[fnGetCTEResultSet](@IfThereIsAnyParameter BIGINT)

RETURNS @DATA TABLE

            (

                        ID BIGINT PRIMARY KEY NOT NULL,

                        Name VARCHAR(100) NOT NULL

            )

 

AS

 

BEGIN

           

            WITH MYDATA(ID, Name)

            AS

            (

                        -- WRITE HERE THE CTE CODE

            )

 

            INSERT INTO

                        @DATA(ID, Name)

            SELECT

                         ID

                        ,Name

            FROM

                        MYDATA

           

            RETURN

 

END