Entity Framework Code First使用者的福音 --- EF Power Tool使用記之二（問題探究）
上次為大家介紹EF Power Tool之后，不少朋友在使用的時候碰到了一些問題曾像我提問。我自己以及同事在使用這個工具時，其實也碰到了一些問題。今天我將和大家一起分享其中2個問題以及相應的原因。
1. EF Power Tool幫助我們生成Code First POCO class時為何只生成了部分代碼，甚至所使用的程序集都沒有導入？
在輸入了相應的數(shù)據(jù)庫Named Instance和登錄信息后，EF Power Tool開始為我們創(chuàng)建相應的class。此時使用SQL Server Profiler同時觀察SQL Server在本地的Named Instance和SQL Express Instance，我們會發(fā)現(xiàn)一系列數(shù)據(jù)庫訪問在Named Instance上執(zhí)行了。
1) 首先是這樣一個查詢。以上篇文章中的Parent和Child類為例子，會返回這些用戶級別表字段的數(shù)據(jù)。其中每個字段分別表示
C1: 每個字段在表中的序號
CatalogName: 數(shù)據(jù)庫名
SchemaName：表的schema名
Name：表名
C2：字段名
C3：是否為NULL
C4：字段類型
C5：字段最大長度
C6：字段精度
C7：DateTime的精度
C8：字段刻度
C9：是否為Identity
C10：是否為數(shù)據(jù)庫自動生成
C11：是否為主鍵
SELECT
[Project6].[C2] AS [C1],
[Project6].[CatalogName] AS [CatalogName],
[Project6].[SchemaName] AS [SchemaName],
[Project6].[Name] AS [Name],
[Project6].[C1] AS [C2],
[Project6].[C3] AS [C3],
[Project6].[C4] AS [C4],
[Project6].[C5] AS [C5],
[Project6].[C6] AS [C6],
[Project6].[C7] AS [C7],
[Project6].[C8] AS [C8],
[Project6].[C9] AS [C9],
[Project6].[C10] AS [C10],
[Project6].[C11] AS [C11]
FROM ( SELECT
[Extent1].[CatalogName] AS [CatalogName],
[Extent1].[SchemaName] AS [SchemaName],
[Extent1].[Name] AS [Name],
[UnionAll1].[Name] AS [C1],
[UnionAll1].[Ordinal] AS [C2],
[UnionAll1].[IsNullable] AS [C3],
[UnionAll1].[TypeName] AS [C4],
[UnionAll1].[MaxLength] AS [C5],
[UnionAll1].[Precision] AS [C6],
[UnionAll1].[DateTimePrecision] AS [C7],
[UnionAll1].[Scale] AS [C8],
[UnionAll1].[IsIdentity] AS [C9],
[UnionAll1].[IsStoreGenerated] AS [C10],
CASE WHEN ([Project5].[C2] IS NULL) THEN cast(0 as bit) ELSE [Project5].[C2] END AS [C11]
FROM (
SELECT
quotename(TABLE_SCHEMA) + quotename(TABLE_NAME) [Id]
, TABLE_CATALOG [CatalogName]
, TABLE_SCHEMA [SchemaName]
, TABLE_NAME [Name]
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE'
) AS [Extent1]
INNER JOIN (SELECT
[Extent2].[Id] AS [Id],
[Extent2].[Name] AS [Name],
[Extent2].[Ordinal] AS [Ordinal],
[Extent2].[IsNullable] AS [IsNullable],
[Extent2].[TypeName] AS [TypeName],
[Extent2].[MaxLength] AS [MaxLength],
[Extent2].[Precision] AS [Precision],
[Extent2].[DateTimePrecision] AS [DateTimePrecision],
[Extent2].[Scale] AS [Scale],
[Extent2].[IsIdentity] AS [IsIdentity],
[Extent2].[IsStoreGenerated] AS [IsStoreGenerated],
0 AS [C1],
[Extent2].[ParentId] AS [ParentId]
FROM (
SELECT
quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) [Id]
, quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) [ParentId]
, c.COLUMN_NAME [Name]
, c.ORDINAL_POSITION [Ordinal]
, CAST( CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsNullable]
, CASE
WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
c.CHARACTER_MAXIMUM_LENGTH = -1 THEN
c.DATA_TYPE + '(max)'
ELSE
c.DATA_TYPE
END
as [TypeName]
, c.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(c.NUMERIC_PRECISION as integer) [Precision]
, CAST(c.DATETIME_PRECISION as integer)[DateTimePrecision]
, CAST(c.NUMERIC_SCALE as integer) [Scale]
, c.COLLATION_CATALOG [CollationCatalog]
, c.COLLATION_SCHEMA [CollationSchema]
, c.COLLATION_NAME [CollationName]
, c.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, c.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, c.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as [IsIdentity]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as [IsStoreGenerated]
, c.COLUMN_DEFAULT as [Default]
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
INFORMATION_SCHEMA.TABLES t ON
c.TABLE_CATALOG = t.TABLE_CATALOG AND
c.TABLE_SCHEMA = t.TABLE_SCHEMA AND
c.TABLE_NAME = t.TABLE_NAME AND
t.TABLE_TYPE = 'BASE TABLE'
) AS [Extent2]
UNION ALL
SELECT
[Extent3].[Id] AS [Id],
[Extent3].[Name] AS [Name],
[Extent3].[Ordinal] AS [Ordinal],
[Extent3].[IsNullable] AS [IsNullable],
[Extent3].[TypeName] AS [TypeName],
[Extent3].[MaxLength] AS [MaxLength],
[Extent3].[Precision] AS [Precision],
[Extent3].[DateTimePrecision] AS [DateTimePrecision],
[Extent3].[Scale] AS [Scale],
[Extent3].[IsIdentity] AS [IsIdentity],
[Extent3].[IsStoreGenerated] AS [IsStoreGenerated],
6 AS [C1],
[Extent3].[ParentId] AS [ParentId]
FROM (
SELECT
quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) [Id]
, quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) [ParentId]
, c.COLUMN_NAME [Name]
, c.ORDINAL_POSITION [Ordinal]
, CAST( CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsNullable]
, CASE
WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
c.CHARACTER_MAXIMUM_LENGTH = -1 THEN
c.DATA_TYPE + '(max)'
ELSE
c.DATA_TYPE
END
as [TypeName]
, c.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(c.NUMERIC_PRECISION as integer) [Precision]
, CAST(c.DATETIME_PRECISION as integer) as [DateTimePrecision]
, CAST(c.NUMERIC_SCALE as integer) [Scale]
, c.COLLATION_CATALOG [CollationCatalog]
, c.COLLATION_SCHEMA [CollationSchema]
, c.COLLATION_NAME [CollationName]
, c.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, c.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, c.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as [IsIdentity]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as [IsStoreGenerated]
, c.COLUMN_DEFAULT [Default]
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
INFORMATION_SCHEMA.VIEWS v ON
c.TABLE_CATALOG = v.TABLE_CATALOG AND
c.TABLE_SCHEMA = v.TABLE_SCHEMA AND
c.TABLE_NAME = v.TABLE_NAME
WHERE
NOT (v.TABLE_SCHEMA = 'dbo'
AND v.TABLE_NAME in('syssegments', 'sysconstraints')
AND SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar(20)),1,1) = 8)
) AS [Extent3]) AS [UnionAll1] ON (0 = [UnionAll1].[C1]) AND ([Extent1].[Id] = [UnionAll1].[ParentId])
LEFT OUTER JOIN (SELECT
[UnionAll2].[Id] AS [C1],
cast(1 as bit) AS [C2]
FROM (
SELECT
quotename(tc.CONSTRAINT_SCHEMA) + quotename(tc.CONSTRAINT_NAME) [Id]
, quotename(tc.TABLE_SCHEMA) + quotename(tc.TABLE_NAME) [ParentId]
, tc.CONSTRAINT_NAME [Name]
, tc.CONSTRAINT_TYPE [ConstraintType]
, CAST(CASE tc.IS_DEFERRABLE WHEN 'NO' THEN 0 ELSE 1 END as bit) [IsDeferrable]
, CAST(CASE tc.INITIALLY_DEFERRED WHEN 'NO' THEN 0 ELSE 1 END as bit) [IsInitiallyDeferred]
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
WHERE tc.TABLE_NAME IS NOT NULL
) AS [Extent4]
INNER JOIN (SELECT
7 AS [C1],
[Extent5].[ConstraintId] AS [ConstraintId],
[Extent6].[Id] AS [Id]
FROM (
SELECT
quotename(CONSTRAINT_SCHEMA) + quotename(CONSTRAINT_NAME) [ConstraintId]
, quotename(TABLE_SCHEMA) + quotename(TABLE_NAME) + quotename(COLUMN_NAME) [ColumnId]
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
) AS [Extent5]
INNER JOIN (
SELECT
quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) [Id]
, quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) [ParentId]
, c.COLUMN_NAME [Name]
, c.ORDINAL_POSITION [Ordinal]
, CAST( CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsNullable]
, CASE
WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
c.CHARACTER_MAXIMUM_LENGTH = -1 THEN
c.DATA_TYPE + '(max)'
ELSE
c.DATA_TYPE
END
as [TypeName]
, c.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(c.NUMERIC_PRECISION as integer) [Precision]
, CAST(c.DATETIME_PRECISION as integer)[DateTimePrecision]
, CAST(c.NUMERIC_SCALE as integer) [Scale]
, c.COLLATION_CATALOG [CollationCatalog]
, c.COLLATION_SCHEMA [CollationSchema]
, c.COLLATION_NAME [CollationName]
, c.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, c.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, c.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as [IsIdentity]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as [IsStoreGenerated]
, c.COLUMN_DEFAULT as [Default]
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
INFORMATION_SCHEMA.TABLES t ON
c.TABLE_CATALOG = t.TABLE_CATALOG AND
c.TABLE_SCHEMA = t.TABLE_SCHEMA AND
c.TABLE_NAME = t.TABLE_NAME AND
t.TABLE_TYPE = 'BASE TABLE'
) AS [Extent6] ON [Extent6].[Id] = [Extent5].[ColumnId]
UNION ALL
SELECT
11 AS [C1],
[Extent7].[ConstraintId] AS [ConstraintId],
[Extent8].[Id] AS [Id]
FROM (
SELECT
CAST(NULL as nvarchar(1)) [ConstraintId]
, CAST(NULL as nvarchar(max)) [ColumnId]
WHERE 1=2
) AS [Extent7]
INNER JOIN (
SELECT
quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) [Id]
, quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) [ParentId]
, c.COLUMN_NAME [Name]
, c.ORDINAL_POSITION [Ordinal]
, CAST( CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsNullable]
, CASE
WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
c.CHARACTER_MAXIMUM_LENGTH = -1 THEN
c.DATA_TYPE + '(max)'
ELSE
c.DATA_TYPE
END
as [TypeName]
, c.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(c.NUMERIC_PRECISION as integer) [Precision]
, CAST(c.DATETIME_PRECISION as integer) as [DateTimePrecision]
, CAST(c.NUMERIC_SCALE as integer) [Scale]
, c.COLLATION_CATALOG [CollationCatalog]
, c.COLLATION_SCHEMA [CollationSchema]
, c.COLLATION_NAME [CollationName]
, c.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, c.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, c.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as [IsIdentity]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as [IsStoreGenerated]
, c.COLUMN_DEFAULT [Default]
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
INFORMATION_SCHEMA.VIEWS v ON
c.TABLE_CATALOG = v.TABLE_CATALOG AND
c.TABLE_SCHEMA = v.TABLE_SCHEMA AND
c.TABLE_NAME = v.TABLE_NAME
WHERE
NOT (v.TABLE_SCHEMA = 'dbo'
AND v.TABLE_NAME in('syssegments', 'sysconstraints')
AND SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar(20)),1,1) = 8)
) AS [Extent8] ON [Extent8].[Id] = [Extent7].[ColumnId]) AS [UnionAll2] ON (7 = [UnionAll2].[C1]) AND ([Extent4].[Id] = [UnionAll2].[ConstraintId])
WHERE [Extent4].[ConstraintType] = 'PRIMARY KEY' ) AS [Project5] ON [UnionAll1].[Id] = [Project5].[C1]
) AS [Project6]
ORDER BY [Project6].[SchemaName] ASC, [Project6].[Name] ASC, [Project6].[C2] ASC
2) 然后這個查詢返回系統(tǒng)級表的字段信息。
SELECT
[Project6].[C2] AS [C1],
[Project6].[CatalogName] AS [CatalogName],
[Project6].[SchemaName] AS [SchemaName],
[Project6].[Name] AS [Name],
[Project6].[C1] AS [C2],
[Project6].[C3] AS [C3],
[Project6].[C4] AS [C4],
[Project6].[C5] AS [C5],
[Project6].[C6] AS [C6],
[Project6].[C7] AS [C7],
[Project6].[C8] AS [C8],
[Project6].[C9] AS [C9],
[Project6].[C10] AS [C10],
[Project6].[C11] AS [C11]
FROM ( SELECT
[Extent1].[CatalogName] AS [CatalogName],
[Extent1].[SchemaName] AS [SchemaName],
[Extent1].[Name] AS [Name],
[UnionAll1].[Name] AS [C1],
[UnionAll1].[Ordinal] AS [C2],
[UnionAll1].[IsNullable] AS [C3],
[UnionAll1].[TypeName] AS [C4],
[UnionAll1].[MaxLength] AS [C5],
[UnionAll1].[Precision] AS [C6],
[UnionAll1].[DateTimePrecision] AS [C7],
[UnionAll1].[Scale] AS [C8],
[UnionAll1].[IsIdentity] AS [C9],
[UnionAll1].[IsStoreGenerated] AS [C10],
CASE WHEN ([Project5].[C2] IS NULL) THEN cast(0 as bit) ELSE [Project5].[C2] END AS [C11]
FROM (
SELECT
quotename(TABLE_SCHEMA) + quotename(TABLE_NAME) [Id]
, TABLE_CATALOG [CatalogName]
, TABLE_SCHEMA [SchemaName]
, TABLE_NAME [Name]
, VIEW_DEFINITION [ViewDefinition]
, CAST( CASE IS_UPDATABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsUpdatable]
FROM
INFORMATION_SCHEMA.VIEWS
WHERE
NOT (TABLE_SCHEMA = 'dbo'
AND TABLE_NAME in('syssegments', 'sysconstraints')
AND SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar(20)),1,1) = 8)
) AS [Extent1]
INNER JOIN (SELECT
[Extent2].[Id] AS [Id],
[Extent2].[Name] AS [Name],
[Extent2].[Ordinal] AS [Ordinal],
[Extent2].[IsNullable] AS [IsNullable],
[Extent2].[TypeName] AS [TypeName],
[Extent2].[MaxLength] AS [MaxLength],
[Extent2].[Precision] AS [Precision],
[Extent2].[DateTimePrecision] AS [DateTimePrecision],
[Extent2].[Scale] AS [Scale],
[Extent2].[IsIdentity] AS [IsIdentity],
[Extent2].[IsStoreGenerated] AS [IsStoreGenerated],
4 AS [C1],
[Extent2].[ParentId] AS [ParentId]
FROM (
SELECT
quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) [Id]
, quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) [ParentId]
, c.COLUMN_NAME [Name]
, c.ORDINAL_POSITION [Ordinal]
, CAST( CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsNullable]
, CASE
WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
c.CHARACTER_MAXIMUM_LENGTH = -1 THEN
c.DATA_TYPE + '(max)'
ELSE
c.DATA_TYPE
END
as [TypeName]
, c.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(c.NUMERIC_PRECISION as integer) [Precision]
, CAST(c.DATETIME_PRECISION as integer)[DateTimePrecision]
, CAST(c.NUMERIC_SCALE as integer) [Scale]
, c.COLLATION_CATALOG [CollationCatalog]
, c.COLLATION_SCHEMA [CollationSchema]
, c.COLLATION_NAME [CollationName]
, c.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, c.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, c.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as [IsIdentity]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as [IsStoreGenerated]
, c.COLUMN_DEFAULT as [Default]
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
INFORMATION_SCHEMA.TABLES t ON
c.TABLE_CATALOG = t.TABLE_CATALOG AND
c.TABLE_SCHEMA = t.TABLE_SCHEMA AND
c.TABLE_NAME = t.TABLE_NAME AND
t.TABLE_TYPE = 'BASE TABLE'
) AS [Extent2]
UNION ALL
SELECT
[Extent3].[Id] AS [Id],
[Extent3].[Name] AS [Name],
[Extent3].[Ordinal] AS [Ordinal],
[Extent3].[IsNullable] AS [IsNullable],
[Extent3].[TypeName] AS [TypeName],
[Extent3].[MaxLength] AS [MaxLength],
[Extent3].[Precision] AS [Precision],
[Extent3].[DateTimePrecision] AS [DateTimePrecision],
[Extent3].[Scale] AS [Scale],
[Extent3].[IsIdentity] AS [IsIdentity],
[Extent3].[IsStoreGenerated] AS [IsStoreGenerated],
0 AS [C1],
[Extent3].[ParentId] AS [ParentId]
FROM (
SELECT
quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) [Id]
, quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) [ParentId]
, c.COLUMN_NAME [Name]
, c.ORDINAL_POSITION [Ordinal]
, CAST( CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsNullable]
, CASE
WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
c.CHARACTER_MAXIMUM_LENGTH = -1 THEN
c.DATA_TYPE + '(max)'
ELSE
c.DATA_TYPE
END
as [TypeName]
, c.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(c.NUMERIC_PRECISION as integer) [Precision]
, CAST(c.DATETIME_PRECISION as integer) as [DateTimePrecision]
, CAST(c.NUMERIC_SCALE as integer) [Scale]
, c.COLLATION_CATALOG [CollationCatalog]
, c.COLLATION_SCHEMA [CollationSchema]
, c.COLLATION_NAME [CollationName]
, c.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, c.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, c.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as [IsIdentity]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as [IsStoreGenerated]
, c.COLUMN_DEFAULT [Default]
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
INFORMATION_SCHEMA.VIEWS v ON
c.TABLE_CATALOG = v.TABLE_CATALOG AND
c.TABLE_SCHEMA = v.TABLE_SCHEMA AND
c.TABLE_NAME = v.TABLE_NAME
WHERE
NOT (v.TABLE_SCHEMA = 'dbo'
AND v.TABLE_NAME in('syssegments', 'sysconstraints')
AND SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar(20)),1,1) = 8)
) AS [Extent3]) AS [UnionAll1] ON (0 = [UnionAll1].[C1]) AND ([Extent1].[Id] = [UnionAll1].[ParentId])
LEFT OUTER JOIN (SELECT
[UnionAll2].[Id] AS [C1],
cast(1 as bit) AS [C2]
FROM (
SELECT
CAST(NULL as nvarchar(1)) [Id]
, CAST(NULL as nvarchar(256)) [ParentId]
, CAST(NULL as nvarchar(256)) [Name]
, CAST(NULL as nvarchar(256)) [ConstraintType]
, CAST(0 as bit) [IsDeferrable]
, CAST(0 as bit) [IsInitiallyDeferred]
, CAST(NULL as nvarchar(max)) [Expression]
, CAST(NULL as nvarchar(11)) [UpdateRule]
, CAST(NULL as nvarchar(11)) [DeleteRule]
WHERE 1=2
) AS [Extent4]
INNER JOIN (SELECT
10 AS [C1],
[Extent5].[ConstraintId] AS [ConstraintId],
[Extent6].[Id] AS [Id]
FROM (
SELECT
quotename(CONSTRAINT_SCHEMA) + quotename(CONSTRAINT_NAME) [ConstraintId]
, quotename(TABLE_SCHEMA) + quotename(TABLE_NAME) + quotename(COLUMN_NAME) [ColumnId]
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
) AS [Extent5]
INNER JOIN (
SELECT
quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) [Id]
, quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) [ParentId]
, c.COLUMN_NAME [Name]
, c.ORDINAL_POSITION [Ordinal]
, CAST( CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsNullable]
, CASE
WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
c.CHARACTER_MAXIMUM_LENGTH = -1 THEN
c.DATA_TYPE + '(max)'
ELSE
c.DATA_TYPE
END
as [TypeName]
, c.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(c.NUMERIC_PRECISION as integer) [Precision]
, CAST(c.DATETIME_PRECISION as integer)[DateTimePrecision]
, CAST(c.NUMERIC_SCALE as integer) [Scale]
, c.COLLATION_CATALOG [CollationCatalog]
, c.COLLATION_SCHEMA [CollationSchema]
, c.COLLATION_NAME [CollationName]
, c.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, c.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, c.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as [IsIdentity]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as [IsStoreGenerated]
, c.COLUMN_DEFAULT as [Default]
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
INFORMATION_SCHEMA.TABLES t ON
c.TABLE_CATALOG = t.TABLE_CATALOG AND
c.TABLE_SCHEMA = t.TABLE_SCHEMA AND
c.TABLE_NAME = t.TABLE_NAME AND
t.TABLE_TYPE = 'BASE TABLE'
) AS [Extent6] ON [Extent6].[Id] = [Extent5].[ColumnId]
UNION ALL
SELECT
7 AS [C1],
[Extent7].[ConstraintId] AS [ConstraintId],
[Extent8].[Id] AS [Id]
FROM (
SELECT
CAST(NULL as nvarchar(1)) [ConstraintId]
, CAST(NULL as nvarchar(max)) [ColumnId]
WHERE 1=2
) AS [Extent7]
INNER JOIN (
SELECT
quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) [Id]
, quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) [ParentId]
, c.COLUMN_NAME [Name]
, c.ORDINAL_POSITION [Ordinal]
, CAST( CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsNullable]
, CASE
WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
c.CHARACTER_MAXIMUM_LENGTH = -1 THEN
c.DATA_TYPE + '(max)'
ELSE
c.DATA_TYPE
END
as [TypeName]
, c.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(c.NUMERIC_PRECISION as integer) [Precision]
, CAST(c.DATETIME_PRECISION as integer) as [DateTimePrecision]
, CAST(c.NUMERIC_SCALE as integer) [Scale]
, c.COLLATION_CATALOG [CollationCatalog]
, c.COLLATION_SCHEMA [CollationSchema]
, c.COLLATION_NAME [CollationName]
, c.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, c.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, c.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as [IsIdentity]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as [IsStoreGenerated]
, c.COLUMN_DEFAULT [Default]
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
INFORMATION_SCHEMA.VIEWS v ON
c.TABLE_CATALOG = v.TABLE_CATALOG AND
c.TABLE_SCHEMA = v.TABLE_SCHEMA AND
c.TABLE_NAME = v.TABLE_NAME
WHERE
NOT (v.TABLE_SCHEMA = 'dbo'
AND v.TABLE_NAME in('syssegments', 'sysconstraints')
AND SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar(20)),1,1) = 8)
) AS [Extent8] ON [Extent8].[Id] = [Extent7].[ColumnId]) AS [UnionAll2] ON (7 = [UnionAll2].[C1]) AND ([Extent4].[Id] = [UnionAll2].[ConstraintId])
WHERE [Extent4].[ConstraintType] = 'PRIMARY KEY' ) AS [Project5] ON [UnionAll1].[Id] = [Project5].[C1]
) AS [Project6]
ORDER BY [Project6].[SchemaName] ASC, [Project6].[Name] ASC, [Project6].[C2] ASC
3) 此查詢返回數(shù)據(jù)庫中所有的外鍵關(guān)系。以Parent和Child為例子，返回這樣一個表格：
SELECT
[Project11].[C1] AS [C1],
[Project11].[C5] AS [C2],
[Project11].[C6] AS [C3],
[Project11].[C4] AS [C4],
[Project11].[C2] AS [C5],
[Project11].[C8] AS [C6],
[Project11].[C9] AS [C7],
[Project11].[C7] AS [C8],
[Project11].[C3] AS [C9],
[Project11].[Name] AS [Name],
[Project11].[Id] AS [Id],
[Project11].[C10] AS [C10]
FROM ( SELECT
[Extent1].[Id] AS [Id],
[Extent2].[Name] AS [Name],
[Join5].[Ordinal] AS [C1],
[Join5].[Name1] AS [C2],
[Join5].[Name2] AS [C3],
[UnionAll4].[Name] AS [C4],
[UnionAll4].[CatalogName] AS [C5],
[UnionAll4].[SchemaName] AS [C6],
[UnionAll5].[Name] AS [C7],
[UnionAll5].[CatalogName] AS [C8],
[UnionAll5].[SchemaName] AS [C9],
CASE WHEN ([Extent1].[DeleteRule] = 'CASCADE') THEN cast(1 as bit) WHEN ([Extent1].[DeleteRule] <> 'CASCADE') THEN cast(0 as bit) END AS [C10]
FROM (
SELECT
quotename(rc.CONSTRAINT_SCHEMA) + quotename(rc.CONSTRAINT_NAME) [Id]
, CAST(rc.UPDATE_RULE as nvarchar(11)) [UpdateRule]
, CAST(rc.DELETE_RULE as nvarchar(11)) [DeleteRule]
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
) AS [Extent1]
INNER JOIN (
SELECT
quotename(tc.CONSTRAINT_SCHEMA) + quotename(tc.CONSTRAINT_NAME) [Id]
, quotename(tc.TABLE_SCHEMA) + quotename(tc.TABLE_NAME) [ParentId]
, tc.CONSTRAINT_NAME [Name]
, tc.CONSTRAINT_TYPE [ConstraintType]
, CAST(CASE tc.IS_DEFERRABLE WHEN 'NO' THEN 0 ELSE 1 END as bit) [IsDeferrable]
, CAST(CASE tc.INITIALLY_DEFERRED WHEN 'NO' THEN 0 ELSE 1 END as bit) [IsInitiallyDeferred]
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
WHERE tc.TABLE_NAME 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], [Join2].[Id1], [UnionAll2].[Name] AS [Name1], [UnionAll2].[C2] AS [C21], [UnionAll2].[ParentId] AS [ParentId1], [UnionAll3].[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
quotename(FC.CONSTRAINT_SCHEMA) + quotename(FC.CONSTRAINT_NAME) + quotename(cast(FC.ORDINAL_POSITION as nvarchar(30))) [Id]
, quotename(PC.TABLE_SCHEMA) + quotename(PC.TABLE_NAME) + quotename(PC.COLUMN_NAME) [ToColumnId]
, quotename(FC.TABLE_SCHEMA) + quotename(FC.TABLE_NAME) + quotename(FC.COLUMN_NAME) [FromColumnId]
, quotename(FC.CONSTRAINT_SCHEMA) + quotename(FC.CONSTRAINT_NAME) [ConstraintId]
, FC.ORDINAL_POSITION [Ordinal]
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS PC /* PRIMARY KEY COLS*/
ON RC.UNIQUE_CONSTRAINT_SCHEMA = PC.CONSTRAINT_SCHEMA
AND RC.UNIQUE_CONSTRAINT_NAME = PC.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS FC /* FOREIGN KEY COLS*/
ON RC.CONSTRAINT_SCHEMA = FC.CONSTRAINT_SCHEMA
AND RC.CONSTRAINT_NAME = FC.CONSTRAINT_NAME
AND PC.ORDINAL_POSITION = FC.ORDINAL_POSITION
) 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 nvarchar(1)) [Id]
, CAST(NULL as nvarchar(max)) [ToColumnId]
, CAST(NULL as nvarchar(max)) [FromColumnId]
, CAST(NULL as nvarchar(1)) [ConstraintId]
, 0 [Ordinal]
WHERE 1=2
) AS [Extent4]) AS [UnionAll1]
INNER JOIN (SELECT [Extent5].[Id] AS [Id1]
FROM (
SELECT
quotename(rc.CONSTRAINT_SCHEMA) + quotename(rc.CONSTRAINT_NAME) [Id]
, CAST(rc.UPDATE_RULE as nvarchar(11)) [UpdateRule]
, CAST(rc.DELETE_RULE as nvarchar(11)) [DeleteRule]
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
) AS [Extent5]
INNER JOIN (
SELECT
quotename(tc.CONSTRAINT_SCHEMA) + quotename(tc.CONSTRAINT_NAME) [Id]
, quotename(tc.TABLE_SCHEMA) + quotename(tc.TABLE_NAME) [ParentId]
, tc.CONSTRAINT_NAME [Name]
, tc.CONSTRAINT_TYPE [ConstraintType]
, CAST(CASE tc.IS_DEFERRABLE WHEN 'NO' THEN 0 ELSE 1 END as bit) [IsDeferrable]
, CAST(CASE tc.INITIALLY_DEFERRED WHEN 'NO' THEN 0 ELSE 1 END as bit) [IsInitiallyDeferred]
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
WHERE tc.TABLE_NAME IS NOT NULL
) AS [Extent6] ON [Extent5].[Id] = [Extent6].[Id] ) AS [Join2] ON ([UnionAll1].[C1] = 0) AND ([UnionAll1].[ConstraintId] = [Join2].[Id1])
LEFT OUTER JOIN (SELECT
6 AS [C1],
[Extent7].[Id] AS [Id],
[Extent7].[Name] AS [Name],
3 AS [C2],
[Extent7].[ParentId] AS [ParentId]
FROM (
SELECT
quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) [Id]
, quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) [ParentId]
, c.COLUMN_NAME [Name]
, c.ORDINAL_POSITION [Ordinal]
, CAST( CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsNullable]
, CASE
WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
c.CHARACTER_MAXIMUM_LENGTH = -1 THEN
c.DATA_TYPE + '(max)'
ELSE
c.DATA_TYPE
END
as [TypeName]
, c.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(c.NUMERIC_PRECISION as integer) [Precision]
, CAST(c.DATETIME_PRECISION as integer)[DateTimePrecision]
, CAST(c.NUMERIC_SCALE as integer) [Scale]
, c.COLLATION_CATALOG [CollationCatalog]
, c.COLLATION_SCHEMA [CollationSchema]
, c.COLLATION_NAME [CollationName]
, c.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, c.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, c.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as [IsIdentity]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as [IsStoreGenerated]
, c.COLUMN_DEFAULT as [Default]
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
INFORMATION_SCHEMA.TABLES t ON
c.TABLE_CATALOG = t.TABLE_CATALOG AND
c.TABLE_SCHEMA = t.TABLE_SCHEMA AND
c.TABLE_NAME = t.TABLE_NAME AND
t.TABLE_TYPE = 'BASE TABLE'
) 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
quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) [Id]
, quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) [ParentId]
, c.COLUMN_NAME [Name]
, c.ORDINAL_POSITION [Ordinal]
, CAST( CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsNullable]
, CASE
WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
c.CHARACTER_MAXIMUM_LENGTH = -1 THEN
c.DATA_TYPE + '(max)'
ELSE
c.DATA_TYPE
END
as [TypeName]
, c.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(c.NUMERIC_PRECISION as integer) [Precision]
, CAST(c.DATETIME_PRECISION as integer) as [DateTimePrecision]
, CAST(c.NUMERIC_SCALE as integer) [Scale]
, c.COLLATION_CATALOG [CollationCatalog]
, c.COLLATION_SCHEMA [CollationSchema]
, c.COLLATION_NAME [CollationName]
, c.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, c.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, c.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as [IsIdentity]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as [IsStoreGenerated]
, c.COLUMN_DEFAULT [Default]
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
INFORMATION_SCHEMA.VIEWS v ON
c.TABLE_CATALOG = v.TABLE_CATALOG AND
c.TABLE_SCHEMA = v.TABLE_SCHEMA AND
c.TABLE_NAME = v.TABLE_NAME
WHERE
NOT (v.TABLE_SCHEMA = 'dbo'
AND v.TABLE_NAME in('syssegments', 'sysconstraints')
AND SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar(20)),1,1) = 8)
) AS [Extent8]) AS [UnionAll2] ON ([UnionAll1].[C3] = [UnionAll2].[C1]) AND ([UnionAll1].[ToColumnId] = [UnionAll2].[Id])
LEFT OUTER JOIN (SELECT
6 AS [C1],
[Extent9].[Id] AS [Id],
[Extent9].[Name] AS [Name]
FROM (
SELECT
quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) [Id]
, quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) [ParentId]
, c.COLUMN_NAME [Name]
, c.ORDINAL_POSITION [Ordinal]
, CAST( CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsNullable]
, CASE
WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
c.CHARACTER_MAXIMUM_LENGTH = -1 THEN
c.DATA_TYPE + '(max)'
ELSE
c.DATA_TYPE
END
as [TypeName]
, c.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(c.NUMERIC_PRECISION as integer) [Precision]
, CAST(c.DATETIME_PRECISION as integer)[DateTimePrecision]
, CAST(c.NUMERIC_SCALE as integer) [Scale]
, c.COLLATION_CATALOG [CollationCatalog]
, c.COLLATION_SCHEMA [CollationSchema]
, c.COLLATION_NAME [CollationName]
, c.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, c.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, c.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as [IsIdentity]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as [IsStoreGenerated]
, c.COLUMN_DEFAULT as [Default]
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
INFORMATION_SCHEMA.TABLES t ON
c.TABLE_CATALOG = t.TABLE_CATALOG AND
c.TABLE_SCHEMA = t.TABLE_SCHEMA AND
c.TABLE_NAME = t.TABLE_NAME AND
t.TABLE_TYPE = 'BASE TABLE'
) AS [Extent9]
UNION ALL
SELECT
10 AS [C1],
[Extent10].[Id] AS [Id],
[Extent10].[Name] AS [Name]
FROM (
SELECT
quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) + quotename(c.COLUMN_NAME) [Id]
, quotename(c.TABLE_SCHEMA) + quotename(c.TABLE_NAME) [ParentId]
, c.COLUMN_NAME [Name]
, c.ORDINAL_POSITION [Ordinal]
, CAST( CASE c.IS_NULLABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsNullable]
, CASE
WHEN c.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
c.CHARACTER_MAXIMUM_LENGTH = -1 THEN
c.DATA_TYPE + '(max)'
ELSE
c.DATA_TYPE
END
as [TypeName]
, c.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(c.NUMERIC_PRECISION as integer) [Precision]
, CAST(c.DATETIME_PRECISION as integer) as [DateTimePrecision]
, CAST(c.NUMERIC_SCALE as integer) [Scale]
, c.COLLATION_CATALOG [CollationCatalog]
, c.COLLATION_SCHEMA [CollationSchema]
, c.COLLATION_NAME [CollationName]
, c.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, c.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, c.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity' ) as bit) as [IsIdentity]
, CAST(columnproperty( object_id(quotename(c.TABLE_SCHEMA) + '.' + quotename(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed' ) | CASE WHEN c.DATA_TYPE = 'timestamp' THEN 1 ELSE 0 END as bit) as [IsStoreGenerated]
, c.COLUMN_DEFAULT [Default]
FROM
INFORMATION_SCHEMA.COLUMNS c
INNER JOIN
INFORMATION_SCHEMA.VIEWS v ON
c.TABLE_CATALOG = v.TABLE_CATALOG AND
c.TABLE_SCHEMA = v.TABLE_SCHEMA AND
c.TABLE_NAME = v.TABLE_NAME
WHERE
NOT (v.TABLE_SCHEMA = 'dbo'
AND v.TABLE_NAME in('syssegments', 'sysconstraints')
AND SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar(20)),1,1) = 8)
) AS [Extent10]) AS [UnionAll3] ON ([UnionAll1].[C2] = [UnionAll3].[C1]) AND ([UnionAll1].[FromColumnId] = [UnionAll3].[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
quotename(TABLE_SCHEMA) + quotename(TABLE_NAME) [Id]
, TABLE_CATALOG [CatalogName]
, TABLE_SCHEMA [SchemaName]
, TABLE_NAME [Name]
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE'
) 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
quotename(TABLE_SCHEMA) + quotename(TABLE_NAME) [Id]
, TABLE_CATALOG [CatalogName]
, TABLE_SCHEMA [SchemaName]
, TABLE_NAME [Name]
, VIEW_DEFINITION [ViewDefinition]
, CAST( CASE IS_UPDATABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsUpdatable]
FROM
INFORMATION_SCHEMA.VIEWS
WHERE
NOT (TABLE_SCHEMA = 'dbo'
AND TABLE_NAME in('syssegments', 'sysconstraints')
AND SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar(20)),1,1) = 8)
) AS [Extent12]) AS [UnionAll4] ON ([Join5].[C21] = [UnionAll4].[C1]) AND ([Join5].[ParentId1] = [UnionAll4].[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
quotename(TABLE_SCHEMA) + quotename(TABLE_NAME) [Id]
, TABLE_CATALOG [CatalogName]
, TABLE_SCHEMA [SchemaName]
, TABLE_NAME [Name]
FROM
INFORMATION_SCHEMA.TABLES
WHERE
TABLE_TYPE = 'BASE TABLE'
) 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
quotename(TABLE_SCHEMA) + quotename(TABLE_NAME) [Id]
, TABLE_CATALOG [CatalogName]
, TABLE_SCHEMA [SchemaName]
, TABLE_NAME [Name]
, VIEW_DEFINITION [ViewDefinition]
, CAST( CASE IS_UPDATABLE WHEN 'YES' THEN 1 WHEN 'NO' THEN 0 ELSE 0 END as bit) [IsUpdatable]
FROM
INFORMATION_SCHEMA.VIEWS
WHERE
NOT (TABLE_SCHEMA = 'dbo'
AND TABLE_NAME in('syssegments', 'sysconstraints')
AND SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar(20)),1,1) = 8)
) AS [Extent14]) AS [UnionAll5] ON (3 = [UnionAll5].[C1]) AND ([Extent2].[ParentId] = [UnionAll5].[Id])
) AS [Project11]
ORDER BY [Project11].[Name] ASC, [Project11].[Id] ASC, [Project11].[C1] ASC
4) 最后這個查詢返回數(shù)據(jù)庫中存儲過程和函數(shù)的相關(guān)信息。
SELECT
[Project7].[C12] AS [C1],
[Project7].[C1] AS [C2],
[Project7].[C2] AS [C3],
[Project7].[C3] AS [C4],
[Project7].[C4] AS [C5],
[Project7].[C5] AS [C6],
[Project7].[C6] AS [C7],
[Project7].[C7] AS [C8],
[Project7].[C8] AS [C9],
[Project7].[C9] AS [C10],
[Project7].[C10] AS [C11]
FROM ( SELECT
[UnionAll3].[SchemaName] AS [C1],
[UnionAll3].[Name] AS [C2],
[UnionAll3].[ReturnTypeName] AS [C3],
[UnionAll3].[IsAggregate] AS [C4],
[UnionAll3].[C1] AS [C5],
[UnionAll3].[IsBuiltIn] AS [C6],
[UnionAll3].[IsNiladic] AS [C7],
[UnionAll3].[C2] AS [C8],
[UnionAll3].[C3] AS [C9],
[UnionAll3].[C4] AS [C10],
[UnionAll3].[C5] AS [C11],
1 AS [C12]
FROM (SELECT
[Extent1].[SchemaName] AS [SchemaName],
[Extent1].[Name] AS [Name],
[Extent1].[ReturnTypeName] AS [ReturnTypeName],
[Extent1].[IsAggregate] AS [IsAggregate],
cast(1 as bit) AS [C1],
[Extent1].[IsBuiltIn] AS [IsBuiltIn],
[Extent1].[IsNiladic] AS [IsNiladic],
[UnionAll1].[Name] AS [C2],
[UnionAll1].[TypeName] AS [C3],
[UnionAll1].[Mode] AS [C4],
[UnionAll1].[Ordinal] AS [C5]
FROM (
SELECT
quotename(SPECIFIC_SCHEMA) + quotename(SPECIFIC_NAME) [Id]
, SPECIFIC_CATALOG [CatalogName]
, SPECIFIC_SCHEMA [SchemaName]
, SPECIFIC_NAME [Name]
, CASE
WHEN DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
CHARACTER_MAXIMUM_LENGTH = -1 THEN
DATA_TYPE + '(max)'
ELSE
DATA_TYPE
END [ReturnTypeName]
, CHARACTER_MAXIMUM_LENGTH [ReturnMaxLength]
, CAST(NUMERIC_PRECISION as integer) [ReturnPrecision]
, CAST(DATETIME_PRECISION as integer) [ReturnDateTimePrecision]
, CAST(NUMERIC_SCALE as integer) [ReturnScale]
, COLLATION_CATALOG [ReturnCollationCatalog]
, COLLATION_SCHEMA [ReturnCollationSchema]
, COLLATION_NAME [ReturnCollationName]
, CHARACTER_SET_CATALOG [ReturnCharacterSetCatalog]
, CHARACTER_SET_SCHEMA [ReturnCharacterSetSchema]
, CHARACTER_SET_NAME [ReturnCharacterSetName]
, CAST(0 as bit) as [ReturnIsMultiSet]
, CAST(0 as bit) as [IsAggregate]
, CAST(0 as bit) as [IsBuiltIn]
, CAST(0 as bit) as [IsNiladic]
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
NOT (ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_NAME LIKE 'dt[_]%'
AND SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar(20)),1,1) = 8)
AND (DATA_TYPE != 'TABLE' OR DATA_TYPE is null)
AND ROUTINE_TYPE = 'FUNCTION'
) AS [Extent1]
LEFT OUTER JOIN (SELECT
[Extent2].[Name] AS [Name],
[Extent2].[Ordinal] AS [Ordinal],
[Extent2].[TypeName] AS [TypeName],
[Extent2].[Mode] AS [Mode],
0 AS [C1],
[Extent2].[ParentId] AS [ParentId]
FROM (
SELECT
quotename(f.SPECIFIC_SCHEMA) + quotename(f.SPECIFIC_NAME) + quotename(f.PARAMETER_NAME) [Id]
, quotename(f.SPECIFIC_SCHEMA) + quotename(f.SPECIFIC_NAME) [ParentId]
, CASE -- trim off the @ symbol
WHEN f.PARAMETER_NAME is null THEN NULL
ELSE SUBSTRING(f.PARAMETER_NAME, 2, LEN(f.PARAMETER_NAME) )
END [Name]
, f.ORDINAL_POSITION [Ordinal]
, CASE
WHEN f.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
f.CHARACTER_MAXIMUM_LENGTH = -1 THEN
f.DATA_TYPE + '(max)'
ELSE
f.DATA_TYPE
END [TypeName]
, f.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(f.NUMERIC_PRECISION as integer) [Precision]
, CAST(f.DATETIME_PRECISION as integer) as [DateTimePrecision]
, CAST(f.NUMERIC_SCALE as integer) [Scale]
, f.COLLATION_CATALOG [CollationCatalog]
, f.COLLATION_SCHEMA [CollationSchema]
, f.COLLATION_NAME [CollationName]
, f.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, f.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, f.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, f.PARAMETER_MODE [Mode]
, CAST(NULL as nvarchar(max)) [Default]
FROM
INFORMATION_SCHEMA.PARAMETERS f
INNER JOIN INFORMATION_SCHEMA.ROUTINES r ON
f.SPECIFIC_SCHEMA = r.SPECIFIC_SCHEMA AND
f.SPECIFIC_NAME = r.SPECIFIC_NAME AND
r.ROUTINE_TYPE = 'FUNCTION'
WHERE
f.IS_RESULT = 'NO'
) AS [Extent2]
UNION ALL
SELECT
[Extent3].[Name] AS [Name],
[Extent3].[Ordinal] AS [Ordinal],
[Extent3].[TypeName] AS [TypeName],
[Extent3].[Mode] AS [Mode],
6 AS [C1],
[Extent3].[ParentId] AS [ParentId]
FROM (
SELECT
quotename(p.SPECIFIC_SCHEMA) + quotename(p.SPECIFIC_NAME) + quotename(p.PARAMETER_NAME) [Id]
, quotename(p.SPECIFIC_SCHEMA) + quotename(p.SPECIFIC_NAME) [ParentId]
, CASE -- trim off the @ symbol
WHEN p.PARAMETER_NAME is null THEN NULL
ELSE SUBSTRING(p.PARAMETER_NAME, 2, LEN(p.PARAMETER_NAME) )
END [Name]
, p.ORDINAL_POSITION [Ordinal]
, CASE
WHEN p.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
p.CHARACTER_MAXIMUM_LENGTH = -1 THEN
p.DATA_TYPE + '(max)'
ELSE
p.DATA_TYPE
END [TypeName]
, p.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(p.NUMERIC_PRECISION as integer) [Precision]
, CAST(p.DATETIME_PRECISION as integer) as [DateTimePrecision]
, CAST(p.NUMERIC_SCALE as integer) [Scale]
, p.COLLATION_CATALOG [CollationCatalog]
, p.COLLATION_SCHEMA [CollationSchema]
, p.COLLATION_NAME [CollationName]
, p.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, p.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, p.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, p.PARAMETER_MODE [Mode]
, CAST(NULL as nvarchar(max)) [Default]
FROM
INFORMATION_SCHEMA.PARAMETERS p
INNER JOIN INFORMATION_SCHEMA.ROUTINES r ON
p.SPECIFIC_SCHEMA = r.SPECIFIC_SCHEMA AND
p.SPECIFIC_NAME = r.SPECIFIC_NAME AND
r.ROUTINE_TYPE = 'PROCEDURE'
WHERE
p.IS_RESULT = 'NO'
) AS [Extent3]) AS [UnionAll1] ON (0 = [UnionAll1].[C1]) AND ([Extent1].[Id] = [UnionAll1].[ParentId])
UNION ALL
SELECT
[Extent4].[SchemaName] AS [SchemaName],
[Extent4].[Name] AS [Name],
CAST(NULL AS varchar(1)) AS [C1],
cast(0 as bit) AS [C2],
cast(0 as bit) AS [C3],
cast(0 as bit) AS [C4],
cast(0 as bit) AS [C5],
[UnionAll2].[Name] AS [C6],
[UnionAll2].[TypeName] AS [C7],
[UnionAll2].[Mode] AS [C8],
[UnionAll2].[Ordinal] AS [C9]
FROM (
SELECT
quotename(SPECIFIC_SCHEMA) + quotename(SPECIFIC_NAME) [Id]
, SPECIFIC_CATALOG [CatalogName]
, SPECIFIC_SCHEMA [SchemaName]
, SPECIFIC_NAME [Name]
FROM
INFORMATION_SCHEMA.ROUTINES
WHERE
NOT (ROUTINE_SCHEMA = 'dbo'
AND ROUTINE_NAME LIKE 'dt[_]%'
AND SUBSTRING(CAST(SERVERPROPERTY('productversion') as varchar(20)),1,1) = 8)
AND (DATA_TYPE != 'TABLE' OR DATA_TYPE is null)
AND ROUTINE_TYPE = 'PROCEDURE'
) AS [Extent4]
LEFT OUTER JOIN (SELECT
[Extent5].[Name] AS [Name],
[Extent5].[Ordinal] AS [Ordinal],
[Extent5].[TypeName] AS [TypeName],
[Extent5].[Mode] AS [Mode],
0 AS [C1],
[Extent5].[ParentId] AS [ParentId]
FROM (
SELECT
quotename(f.SPECIFIC_SCHEMA) + quotename(f.SPECIFIC_NAME) + quotename(f.PARAMETER_NAME) [Id]
, quotename(f.SPECIFIC_SCHEMA) + quotename(f.SPECIFIC_NAME) [ParentId]
, CASE -- trim off the @ symbol
WHEN f.PARAMETER_NAME is null THEN NULL
ELSE SUBSTRING(f.PARAMETER_NAME, 2, LEN(f.PARAMETER_NAME) )
END [Name]
, f.ORDINAL_POSITION [Ordinal]
, CASE
WHEN f.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
f.CHARACTER_MAXIMUM_LENGTH = -1 THEN
f.DATA_TYPE + '(max)'
ELSE
f.DATA_TYPE
END [TypeName]
, f.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(f.NUMERIC_PRECISION as integer) [Precision]
, CAST(f.DATETIME_PRECISION as integer) as [DateTimePrecision]
, CAST(f.NUMERIC_SCALE as integer) [Scale]
, f.COLLATION_CATALOG [CollationCatalog]
, f.COLLATION_SCHEMA [CollationSchema]
, f.COLLATION_NAME [CollationName]
, f.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, f.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, f.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, f.PARAMETER_MODE [Mode]
, CAST(NULL as nvarchar(max)) [Default]
FROM
INFORMATION_SCHEMA.PARAMETERS f
INNER JOIN INFORMATION_SCHEMA.ROUTINES r ON
f.SPECIFIC_SCHEMA = r.SPECIFIC_SCHEMA AND
f.SPECIFIC_NAME = r.SPECIFIC_NAME AND
r.ROUTINE_TYPE = 'FUNCTION'
WHERE
f.IS_RESULT = 'NO'
) AS [Extent5]
UNION ALL
SELECT
[Extent6].[Name] AS [Name],
[Extent6].[Ordinal] AS [Ordinal],
[Extent6].[TypeName] AS [TypeName],
[Extent6].[Mode] AS [Mode],
6 AS [C1],
[Extent6].[ParentId] AS [ParentId]
FROM (
SELECT
quotename(p.SPECIFIC_SCHEMA) + quotename(p.SPECIFIC_NAME) + quotename(p.PARAMETER_NAME) [Id]
, quotename(p.SPECIFIC_SCHEMA) + quotename(p.SPECIFIC_NAME) [ParentId]
, CASE -- trim off the @ symbol
WHEN p.PARAMETER_NAME is null THEN NULL
ELSE SUBSTRING(p.PARAMETER_NAME, 2, LEN(p.PARAMETER_NAME) )
END [Name]
, p.ORDINAL_POSITION [Ordinal]
, CASE
WHEN p.DATA_TYPE in ('varchar', 'nvarchar', 'varbinary') and
p.CHARACTER_MAXIMUM_LENGTH = -1 THEN
p.DATA_TYPE + '(max)'
ELSE
p.DATA_TYPE
END [TypeName]
, p.CHARACTER_MAXIMUM_LENGTH [MaxLength]
, CAST(p.NUMERIC_PRECISION as integer) [Precision]
, CAST(p.DATETIME_PRECISION as integer) as [DateTimePrecision]
, CAST(p.NUMERIC_SCALE as integer) [Scale]
, p.COLLATION_CATALOG [CollationCatalog]
, p.COLLATION_SCHEMA [CollationSchema]
, p.COLLATION_NAME [CollationName]
, p.CHARACTER_SET_CATALOG [CharacterSetCatalog]
, p.CHARACTER_SET_SCHEMA [CharacterSetSchema]
, p.CHARACTER_SET_NAME [CharacterSetName]
, CAST(0 as bit) as [IsMultiSet]
, p.PARAMETER_MODE [Mode]
, CAST(NULL as nvarchar(max)) [Default]
FROM
INFORMATION_SCHEMA.PARAMETERS p
INNER JOIN INFORMATION_SCHEMA.ROUTINES r ON
p.SPECIFIC_SCHEMA = r.SPECIFIC_SCHEMA AND
p.SPECIFIC_NAME = r.SPECIFIC_NAME AND
r.ROUTINE_TYPE = 'PROCEDURE'
WHERE
p.IS_RESULT = 'NO'
) AS [Extent6]) AS [UnionAll2] ON (6 = [UnionAll2].[C1]) AND ([Extent4].[Id] = [UnionAll2].[ParentId])) AS [UnionAll3]
) AS [Project7]
ORDER BY [Project7].[C1] ASC, [Project7].[C2] ASC, [Project7].[C11] ASC
通過以上四個查詢所得到的信息，EF Power Tool便可以地生成對應的class。具體的實現(xiàn)就比較復雜了，今天就不詳細探究。（之后會為大家奉上）。
看到這里大家是否想到這樣一個問題？如果我們訪問數(shù)據(jù)庫所使用的用戶沒有執(zhí)行這些查詢的權(quán)限會怎么樣呢？這樣生成的代碼自然會很不完全。許多代碼只生成了一部分，有些命名空間和程序集都沒有被自動導入。這就產(chǎn)生了類似上一篇文章中，用戶@Zero0420所碰到的問題：“為什么我老是生成的時候，DbModelBuilder 找不到命名空間？”
經(jīng)過一番研究與試驗后，我們使用的用戶對于所要操作的數(shù)據(jù)庫必須要符合以下權(quán)限中的一個：db_datareader, db_datawriter, db_ddladmin, db_owner以及db_securityadmin。有關(guān)這些權(quán)限更詳細的說明，請參看：http://msdn.microsoft.com/en-us/library/ms189121(v=SQL.105).aspx。
2. EF Power Tool在生成Entity Data Model (.edmx和.XML)、Entity Data Model DDL以及生成Generated View的代碼時為何會報ProviderIncompatibleException的錯誤？
接下來我們討論另一個EF Power Tool的問題。在使用它為我們生成Entity Data Model (.edmx和.XML)、Entity Data Model DDL以及生成Generated View的代碼時，
我的一個同事以及其他的一些用戶碰到了這個錯誤信息：System.Data.ProviderIncompatibleException: The provider did not return a ProviderManifestToken string。從這個錯誤我們并不能得到太多有用的信息，所以我們就進一步查看了Visual Studio Output Window中更詳細的錯誤信息：
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)。熟悉SQL Server的用戶肯定對這個錯誤信息不陌生。似乎我們的用戶對相應的SQL Server數(shù)據(jù)庫訪問不了或權(quán)限受限？ 這怎么可能呢？之前Code First POCO class都生成得很好啊。我們此時又用SQL Server Management Studio和Visual Studio連接了相應的數(shù)據(jù)庫，發(fā)現(xiàn)運行都很正常。太奇怪了。。。
public class EFToolContext : DbContext
{
public EFToolContext() :
base(@"Data Source=.\;Initial Catalog=EFTool;Integrated Security=True;Pooling=False")
{
}
static EFToolContext()
{
Database.SetInitializer<EFToolContext>(null);
}
public DbSet<Child> Children { get; set; }
public DbSet<Parent> Parents { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Conventions.Remove<IncludeMetadataConvention>();
modelBuilder.Configurations.Add(new ChildMap());
modelBuilder.Configurations.Add(new ParentMap());
}
}
相同的情況在我自己的機器上就能運行正常。難道是同事項目里的App.config中的connection string有問題？我們又重新檢查了一下，發(fā)現(xiàn)connection string沒有問題。另一位同事@Alan_chen此時對相應的DbContext class做了很小的修改，將connection string直接寫入了class中：
此時，EF Power Tool能運行正常。果然是哪里傳遞connection string出了問題。之后我發(fā)現(xiàn)同事的機器上并不存在.\SQLExpress實例，這個發(fā)現(xiàn)頓時讓我茅塞頓開。難道EF Power Tool會去嘗試訪問.\SQLExpress嗎？用SQL Server Profiler觀察我機器上的.\SQLExpress實例后，發(fā)現(xiàn)果然如此。EF Power Tool會嘗試去連接.\SQLExpress但是并不會做任何數(shù)據(jù)查詢，而真正的Named Instance則無需訪問，因為所有數(shù)據(jù)庫端得信息已經(jīng)可以從DbContext，POCO以及mapping class中得到。
為什么要去訪問.\SQLExpress實例？在我看來，這是EF Power Tool代碼中的一個缺陷。為此，我也在詢問有關(guān)的產(chǎn)品組。不過EF Power Tool也才CTP1，有些瑕疵也在所難免吧。
如需轉(zhuǎn)發(fā)，請注明出處，謝謝！ http://www.rzrgm.cn/LingzhiSun/archive/2011/06/13/EFPowerTool_2.html
浙公網(wǎng)安備 33010602011771號