none
Unable to generate Entity Framework context code from the wizard for my custom ado.net provider RRS feed

  • Question

  • Hi,

    I am getting this error after selecting a table from ADO Entity Data Wizard and getting empty .EDMX file.

    ERROR:

    Column 'UnionAll2.Id' is either not in any table in the FROM list or appears within a join specification and is outside the scope of the join specification or appears in a HAVING clause and is not in the GROUP BY list. If this is a CREATE or ALTER TABLE  statement then 'UnionAll2.Id' is not a column in the target table.

    I have the generated SQL string from DBCommandTree below. (sorry it is quite large)

    Thanks in advance. Any help would be highly appreciated.

    Syed.

    SELECT 
    "Project14"."C11" AS "C1", 
    "Project14"."C5" AS "C2", 
    "Project14"."C6" AS "C3", 
    "Project14"."C4" AS "C4", 
    "Project14"."C2" AS "C5", 
    "Project14"."C8" AS "C6", 
    "Project14"."C9" AS "C7", 
    "Project14"."C7" AS "C8", 
    "Project14"."C3" AS "C9", 
    "Project14"."C1" AS "C10", 
    "Project14"."Name" AS "Name", 
    "Project14"."Id" AS "Id", 
    "Project14"."C10" AS "C11"
    FROM ( SELECT 
    	"Extent1"."Id" AS "Id", 
    	"Extent2"."Name" AS "Name", 
    	"Join5"."Ordinal" AS "C1", 
    	"Join5"."Name1" AS "C2", 
    	"Join5"."Name2" AS "C3", 
    	"UnionAll5"."Name" AS "C4", 
    	"UnionAll5"."CatalogName" AS "C5", 
    	"UnionAll5"."SchemaName" AS "C6", 
    	"UnionAll6"."Name" AS "C7", 
    	"UnionAll6"."CatalogName" AS "C8", 
    	"UnionAll6"."SchemaName" AS "C9", 
    	CASE WHEN ("Extent1"."DeleteRule" = 'CASCADE') THEN cast(1 as smallint) WHEN ("Extent1"."DeleteRule" <> 'CASCADE') THEN cast(0 as smallint) END AS "C10", 
    	1 AS "C11"
    	FROM     (
            SELECT
            rc.TABLE_SCHEMA || '.' || rc.CONSTRAINT_NAME  AS "Id"
            , 'NO ACTION'  AS "UpdateRule"
            , 'NO ACTION'  AS "DeleteRule"
            FROM
            TEMP.SCHEMAFOREIGNKEYS rc
          ) AS "Extent1"
    	INNER JOIN (
            SELECT
            t.TABLESCHEMANAME || '.' || c.CONSTRAINTNAME as "Id",
            t.TABLESCHEMANAME || '.' || t.TABLENAME as "ParentId",
            c.CONSTRAINTNAME as "Name",
            c.TYPE as "ConstraintType",
            0 as "IsDeferrable",
            0 as "IsInitiallyDeferred"
            FROM SYS.SYSCONSTRAINTS c JOIN SYS.SYSTABLES t ON t.TABLEID = c.TABLEID
            WHERE c.TYPE != 'C' AND t.TABLENAME IS NOT NULL
          ) AS "Extent2" ON "Extent1"."Id" = "Extent2"."Id"
    	INNER JOIN  (SELECT "UnionAll1"."Ordinal" AS "Ordinal", "UnionAll1"."C1" AS "C11", "UnionAll1"."ConstraintId" AS "ConstraintId", "UnionAll1"."C2" AS "C21", "UnionAll1"."FromColumnId" AS "FromColumnId", "UnionAll1"."C3" AS "C3", "UnionAll1"."ToColumnId" AS "ToColumnId", "UnionAll2"."C1" AS "C12", "UnionAll2"."Id" AS "Id1", "UnionAll3"."C1" AS "C13", "UnionAll3"."Id" AS "Id2", "UnionAll3"."Name" AS "Name1", "UnionAll3"."C2" AS "C22", "UnionAll3"."ParentId" AS "ParentId", "UnionAll4"."C1" AS "C14", "UnionAll4"."Id" AS "Id3", "UnionAll4"."Name" AS "Name2"
    		FROM     (SELECT 
    			"Extent3"."Ordinal" AS "Ordinal", 
    			0 AS "C1", 
    			"Extent3"."ConstraintId" AS "ConstraintId", 
    			6 AS "C2", 
    			"Extent3"."FromColumnId" AS "FromColumnId", 
    			6 AS "C3", 
    			"Extent3"."ToColumnId" AS "ToColumnId"
    			FROM (
            SELECT
            FC.TABLE_SCHEMA || '.' || FC.CONSTRAINT_NAME || '.' || CAST(FC.ORDINAL_POSITION AS CHAR(3)) AS "Id"
            ,   FC.PRIMARYKEY_TABLE_SCHEMA || '.' || FC.PRIMARYKEY_TABLE_NAME || '.' || FC.PRIMARYKEY_COLUMN_NAME AS  "ToColumnId"
            ,   FC.TABLE_SCHEMA || '.' || FC.TABLE_NAME || '.' || FC.COLUMN_NAME AS "FromColumnId"
            ,   FC.TABLE_SCHEMA || '.' || FC.CONSTRAINT_NAME AS "ConstraintId"
            ,   FC.ORDINAL_POSITION AS "Ordinal"
            FROM
            TEMP.SCHEMAFOREIGNKEYCOLUMNS FC
          ) AS "Extent3"
    		UNION ALL
    			SELECT 
    			"Extent4"."Ordinal" AS "Ordinal", 
    			9 AS "C1", 
    			"Extent4"."ConstraintId" AS "ConstraintId", 
    			10 AS "C2", 
    			"Extent4"."FromColumnId" AS "FromColumnId", 
    			10 AS "C3", 
    			"Extent4"."ToColumnId" AS "ToColumnId"
    			FROM (
            SELECT
            CAST(NULL as varchar(1))    as "Id",
            CAST(NULL as varchar(1)) as "ToColumnId",
            CAST(NULL as varchar(1)) as "FromColumnId",
            CAST(NULL as varchar(1))    as "ConstraintId",
            0  as "Ordinal"
            FROM SYS.MEMBERS
            WHERE 1=2
          ) AS "Extent4") AS "UnionAll1"
    		INNER JOIN  (SELECT 
    			0 AS "C1", 
    			"Extent5"."Id" AS "Id"
    			FROM  (
            SELECT
            rc.TABLE_SCHEMA || '.' || rc.CONSTRAINT_NAME  AS "Id"
            , 'NO ACTION'  AS "UpdateRule"
            , 'NO ACTION'  AS "DeleteRule"
            FROM
            TEMP.SCHEMAFOREIGNKEYS rc
          ) AS "Extent5"
    			INNER JOIN (
            SELECT
            t.TABLESCHEMANAME || '.' || c.CONSTRAINTNAME as "Id",
            t.TABLESCHEMANAME || '.' || t.TABLENAME as "ParentId",
            c.CONSTRAINTNAME as "Name",
            c.TYPE as "ConstraintType",
            0 as "IsDeferrable",
            0 as "IsInitiallyDeferred"
            FROM SYS.SYSCONSTRAINTS c JOIN SYS.SYSTABLES t ON t.TABLEID = c.TABLEID
            WHERE c.TYPE != 'C' AND t.TABLENAME IS NOT NULL
          ) AS "Extent6" ON "Extent5"."Id" = "Extent6"."Id"
    		UNION ALL
    			SELECT 
    			9 AS "C1", 
    			"Project4"."C1" AS "C2"
    			FROM ( SELECT 
    				CAST (NULL as char(1)) AS "C1"
    				FROM  ( SELECT cast(1 as smallint) AS X FROM SYSIBM.SYSDUMMY1 ) AS "SingleRowTable1"
    			)  AS "Project4"
    			WHERE cast(1 as smallint) = cast(0 as smallint)) AS "UnionAll2" ON ("UnionAll1"."C1" = "UnionAll2"."C1") AND ("UnionAll1"."ConstraintId" = "UnionAll2"."Id")
    		LEFT OUTER JOIN  (SELECT 
    			6 AS "C1", 
    			"Extent7"."Id" AS "Id", 
    			"Extent7"."Name" AS "Name", 
    			3 AS "C2", 
    			"Extent7"."ParentId" AS "ParentId"
    			FROM (
            SELECT
            c.TABLE_SCHEMA || '.' || c.TABLE_NAME || '.' || c.COLUMN_NAME as "Id"
            ,   c.TABLE_SCHEMA || '.' || c.TABLE_NAME as "ParentId"
            ,   c.COLUMN_NAME   as "Name"
            ,   c.ORDINAL_POSITION as "Ordinal"
            ,   CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END as "IsNullable"
            ,   c.EDM_TYPE as "TypeName"
            ,   c.COLUMN_SIZE as "MaxLength"
            ,   c.PRECISION_RADIX as "Precision"
            ,   0 as  "DateTimePrecision"
            ,   c.SCALE as "Scale"
            ,   CAST(NULL as varchar(1)) as  "CollationCatalog"
            ,   CAST(NULL as varchar(1)) as  "CollationSchema"
            ,   CAST(NULL as varchar(1)) as  "CollationName"
            ,   CAST(NULL as varchar(1)) as  "CharacterSetCatalog"
            ,   CAST(NULL as varchar(1)) as  "CharacterSetSchema"
            ,   CAST(NULL as varchar(1)) as  "CharacterSetName"
            ,   0 as "IsMultiSet"
            ,   CASE WHEN c.IS_AUTOINCREMENT = 'YES' THEN 1 ELSE 0 END as "IsIdentity"
            ,   CASE WHEN c.IS_AUTOINCREMENT = 'YES' THEN 1 ELSE 0 END as "IsStoreGenerated"
            ,   c.COLUMN_DEFAULT as "Default"
            FROM
            TEMP.SCHEMACOLUMNS c
            WHERE TABLE_SCHEMA != 'SYS'
          ) AS "Extent7"
    		UNION ALL
    			SELECT 
    			10 AS "C1", 
    			"Extent8"."Id" AS "Id", 
    			"Extent8"."Name" AS "Name", 
    			12 AS "C2", 
    			"Extent8"."ParentId" AS "ParentId"
    			FROM (
            SELECT
            c.TABLE_SCHEMA || '.' || c.TABLE_NAME || '.' || c.COLUMN_NAME as "Id"
            ,   c.TABLE_SCHEMA || '.' || c.TABLE_NAME as "ParentId"
            ,   c.COLUMN_NAME   as "Name"
            ,   c.ORDINAL_POSITION as "Ordinal"
            ,   CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END as "IsNullable"
            ,   c.EDM_TYPE as "TypeName"
            ,   c.COLUMN_SIZE as "MaxLength"
            ,   c.PRECISION_RADIX as "Precision"
            ,   0 as  "DateTimePrecision"
            ,   c.SCALE as "Scale"
            ,   CAST(NULL as varchar(1)) as  "CollationCatalog"
            ,   CAST(NULL as varchar(1)) as  "CollationSchema"
            ,   CAST(NULL as varchar(1)) as  "CollationName"
            ,   CAST(NULL as varchar(1)) as  "CharacterSetCatalog"
            ,   CAST(NULL as varchar(1)) as  "CharacterSetSchema"
            ,   CAST(NULL as varchar(1)) as  "CharacterSetName"
            ,   0 as "IsMultiSet"
            ,   CASE WHEN c.IS_AUTOINCREMENT = 'YES' THEN 1 ELSE 0 END as "IsIdentity"
            ,   CASE WHEN c.IS_AUTOINCREMENT = 'YES' THEN 1 ELSE 0 END as "IsStoreGenerated"
            ,   c.COLUMN_DEFAULT as "Default"
            FROM
            TEMP.SCHEMACOLUMNS c
            WHERE TABLE_SCHEMA != 'SYS'
          ) AS "Extent8") AS "UnionAll3" ON ("UnionAll1"."C3" = "UnionAll3"."C1") AND ("UnionAll1"."ToColumnId" = "UnionAll3"."Id")
    		LEFT OUTER JOIN  (SELECT 
    			6 AS "C1", 
    			"Extent9"."Id" AS "Id", 
    			"Extent9"."Name" AS "Name"
    			FROM (
            SELECT
            c.TABLE_SCHEMA || '.' || c.TABLE_NAME || '.' || c.COLUMN_NAME as "Id"
            ,   c.TABLE_SCHEMA || '.' || c.TABLE_NAME as "ParentId"
            ,   c.COLUMN_NAME   as "Name"
            ,   c.ORDINAL_POSITION as "Ordinal"
            ,   CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END as "IsNullable"
            ,   c.EDM_TYPE as "TypeName"
            ,   c.COLUMN_SIZE as "MaxLength"
            ,   c.PRECISION_RADIX as "Precision"
            ,   0 as  "DateTimePrecision"
            ,   c.SCALE as "Scale"
            ,   CAST(NULL as varchar(1)) as  "CollationCatalog"
            ,   CAST(NULL as varchar(1)) as  "CollationSchema"
            ,   CAST(NULL as varchar(1)) as  "CollationName"
            ,   CAST(NULL as varchar(1)) as  "CharacterSetCatalog"
            ,   CAST(NULL as varchar(1)) as  "CharacterSetSchema"
            ,   CAST(NULL as varchar(1)) as  "CharacterSetName"
            ,   0 as "IsMultiSet"
            ,   CASE WHEN c.IS_AUTOINCREMENT = 'YES' THEN 1 ELSE 0 END as "IsIdentity"
            ,   CASE WHEN c.IS_AUTOINCREMENT = 'YES' THEN 1 ELSE 0 END as "IsStoreGenerated"
            ,   c.COLUMN_DEFAULT as "Default"
            FROM
            TEMP.SCHEMACOLUMNS c
            WHERE TABLE_SCHEMA != 'SYS'
          ) AS "Extent9"
    		UNION ALL
    			SELECT 
    			10 AS "C1", 
    			"Extent10"."Id" AS "Id", 
    			"Extent10"."Name" AS "Name"
    			FROM (
            SELECT
            c.TABLE_SCHEMA || '.' || c.TABLE_NAME || '.' || c.COLUMN_NAME as "Id"
            ,   c.TABLE_SCHEMA || '.' || c.TABLE_NAME as "ParentId"
            ,   c.COLUMN_NAME   as "Name"
            ,   c.ORDINAL_POSITION as "Ordinal"
            ,   CASE WHEN c.IS_NULLABLE = 'YES' THEN 1 ELSE 0 END as "IsNullable"
            ,   c.EDM_TYPE as "TypeName"
            ,   c.COLUMN_SIZE as "MaxLength"
            ,   c.PRECISION_RADIX as "Precision"
            ,   0 as  "DateTimePrecision"
            ,   c.SCALE as "Scale"
            ,   CAST(NULL as varchar(1)) as  "CollationCatalog"
            ,   CAST(NULL as varchar(1)) as  "CollationSchema"
            ,   CAST(NULL as varchar(1)) as  "CollationName"
            ,   CAST(NULL as varchar(1)) as  "CharacterSetCatalog"
            ,   CAST(NULL as varchar(1)) as  "CharacterSetSchema"
            ,   CAST(NULL as varchar(1)) as  "CharacterSetName"
            ,   0 as "IsMultiSet"
            ,   CASE WHEN c.IS_AUTOINCREMENT = 'YES' THEN 1 ELSE 0 END as "IsIdentity"
            ,   CASE WHEN c.IS_AUTOINCREMENT = 'YES' THEN 1 ELSE 0 END as "IsStoreGenerated"
            ,   c.COLUMN_DEFAULT as "Default"
            FROM
            TEMP.SCHEMACOLUMNS c
            WHERE TABLE_SCHEMA != 'SYS'
          ) AS "Extent10") AS "UnionAll4" ON ("UnionAll1"."C2" = "UnionAll4"."C1") AND ("UnionAll1"."FromColumnId" = "UnionAll4"."Id") ) AS "Join5" ON ((0 = "Join5"."C11") AND ("Extent1"."Id" = "Join5"."ConstraintId")) AND ("Join5"."Id1" = "Extent1"."Id")
    	LEFT OUTER JOIN  (SELECT 
    		3 AS "C1", 
    		"Extent11"."Id" AS "Id", 
    		"Extent11"."Name" AS "Name", 
    		"Extent11"."CatalogName" AS "CatalogName", 
    		"Extent11"."SchemaName" AS "SchemaName"
    		FROM (
            SELECT
            TABLE_SCHEMA || '.' || TABLE_NAME as "Id"
            ,   TABLE_CATALOG as "CatalogName"
            ,   TABLE_SCHEMA as "SchemaName"
            ,   TABLE_NAME  as "Name"
            FROM
            TEMP.SCHEMATABLES
            WHERE
            TABLE_TYPE LIKE 'TABLE' AND TABLE_SCHEMA != 'TEMP'
          ) AS "Extent11"
    	UNION ALL
    		SELECT 
    		12 AS "C1", 
    		"Extent12"."Id" AS "Id", 
    		"Extent12"."Name" AS "Name", 
    		"Extent12"."CatalogName" AS "CatalogName", 
    		"Extent12"."SchemaName" AS "SchemaName"
    		FROM (
            SELECT
            t.TABLESCHEMANAME || '.' || t.TABLENAME as "Id",
            ''       as      "CatalogName",
            t.TABLESCHEMANAME as    "SchemaName",
            t.TABLENAME as       "Name",
            v.VIEWDEFINITION   as    "ViewDefinition",
            CAST (0 as smallint) as "IsUpdatable"
            FROM SYS.SYSTABLES t JOIN SYS.SYSVIEWS v ON t.TABLEID = v.TABLEID AND t.TABLETYPE = 'V' AND t.TABLESCHEMANAME != 'SYSSTAT'
            AND t.TABLESCHEMANAME != 'TEMP'
          ) AS "Extent12") AS "UnionAll5" ON ("Join5"."C22" = "UnionAll5"."C1") AND ("Join5"."ParentId" = "UnionAll5"."Id")
    	LEFT OUTER JOIN  (SELECT 
    		3 AS "C1", 
    		"Extent13"."Id" AS "Id", 
    		"Extent13"."Name" AS "Name", 
    		"Extent13"."CatalogName" AS "CatalogName", 
    		"Extent13"."SchemaName" AS "SchemaName"
    		FROM (
            SELECT
            TABLE_SCHEMA || '.' || TABLE_NAME as "Id"
            ,   TABLE_CATALOG as "CatalogName"
            ,   TABLE_SCHEMA as "SchemaName"
            ,   TABLE_NAME  as "Name"
            FROM
            TEMP.SCHEMATABLES
            WHERE
            TABLE_TYPE LIKE 'TABLE' AND TABLE_SCHEMA != 'TEMP'
          ) AS "Extent13"
    	UNION ALL
    		SELECT 
    		12 AS "C1", 
    		"Extent14"."Id" AS "Id", 
    		"Extent14"."Name" AS "Name", 
    		"Extent14"."CatalogName" AS "CatalogName", 
    		"Extent14"."SchemaName" AS "SchemaName"
    		FROM (
            SELECT
            t.TABLESCHEMANAME || '.' || t.TABLENAME as "Id",
            ''       as      "CatalogName",
            t.TABLESCHEMANAME as    "SchemaName",
            t.TABLENAME as       "Name",
            v.VIEWDEFINITION   as    "ViewDefinition",
            CAST (0 as smallint) as "IsUpdatable"
            FROM SYS.SYSTABLES t JOIN SYS.SYSVIEWS v ON t.TABLEID = v.TABLEID AND t.TABLETYPE = 'V' AND t.TABLESCHEMANAME != 'SYSSTAT'
            AND t.TABLESCHEMANAME != 'TEMP'
          ) AS "Extent14") AS "UnionAll6" ON (3 = "UnionAll6"."C1") AND ("Extent2"."ParentId" = "UnionAll6"."Id")
    	WHERE ((("UnionAll6"."CatalogName" LIKE @p0) AND ("UnionAll6"."SchemaName" LIKE @p1)) AND ("UnionAll6"."Name" LIKE @p2)) AND ((("UnionAll5"."CatalogName" LIKE @p3) AND ("UnionAll5"."SchemaName" LIKE @p4)) AND ("UnionAll5"."Name" LIKE @p5))
    )  AS "Project14"
    ORDER BY "Project14"."Name" ASC, "Project14"."Id" ASC, "Project14"."C1" ASC

    Tuesday, July 17, 2012 9:12 PM

All replies

  • Hi Syed Mahmud,

    Welcome to MSDN Forum.

    I have involved my workmates to research on this issue, it may need some time, thanks for your understanding.

    Best Regards


    Allen Li [MSFT]
    MSDN Community Support | Feedback to us

    Friday, July 20, 2012 6:02 AM
    Moderator