Visual Studio Entity Data Model Wizard Not Responding

1120 words

6 minutes

Visual Studio Entity Data Model Wizard Not Responding

2016-02-13

Entity Framework

/

SQL Server

/

TSQL

This post is for the issue that Entity Data Model Wizard becomes not responding. In Visual Studio 2015, when clicking the Finish button to create the entity data model from SQL Server 2014 SP! database, the wizard is frozen:

Then:

There are some solutions like deleting all data connections in Server explorer (by delete file %AppData%\Microsoft\VisualStudio\14.0\ServerExplorer\DefaultView.SEView), but didn’t work for Visual Studio 2015 + SQL Server 2014 SP1. After trouble shooting with SQL Server Profiler, it shows Visual Studio hangs because it of the following query (the code is formatted for readability):

1
SELECT
2
    [UnionAll1].[Ordinal] AS [C1],
3
    [Extent1].[CatalogName] AS [CatalogName],
4
    [Extent1].[SchemaName] AS [SchemaName],
5
    [Extent1].[Name] AS [Name],
6
    [UnionAll1].[Name] AS [C2],
7
    [UnionAll1].[IsNullable] AS [C3],
8
    [UnionAll1].[TypeName] AS [C4],
9
    [UnionAll1].[MaxLength] AS [C5],
10
    [UnionAll1].[Precision] AS [C6],
11
    [UnionAll1].[DateTimePrecision] AS [C7],
12
    [UnionAll1].[Scale] AS [C8],
13
    [UnionAll1].[IsIdentity] AS [C9],
14
    [UnionAll1].[IsStoreGenerated] AS [C10],
15
    CASE
16
        WHEN ([Project5].[C2] IS NULL) THEN CAST(0 AS bit)
17
        ELSE [Project5].[C2]
18
    END AS [C11]
19
FROM (
20
    SELECT
21
        QUOTENAME(TABLE_SCHEMA) + QUOTENAME(TABLE_NAME) [Id],
22
        TABLE_CATALOG [CatalogName],
23
        TABLE_SCHEMA [SchemaName],
24
        TABLE_NAME [Name]
25
    FROM INFORMATION_SCHEMA.TABLES
26
    WHERE TABLE_TYPE = 'BASE TABLE') AS [Extent1]
27
INNER JOIN (
28
    SELECT
29
        [Extent2].[Id] AS [Id],
30
        [Extent2].[Name] AS [Name],
31
        [Extent2].[Ordinal] AS [Ordinal],
32
        [Extent2].[IsNullable] AS [IsNullable],
33
        [Extent2].[TypeName] AS [TypeName],
34
        [Extent2].[MaxLength] AS [MaxLength],
35
        [Extent2].[Precision] AS [Precision],
36
        [Extent2].[DateTimePrecision] AS [DateTimePrecision],
37
        [Extent2].[Scale] AS [Scale],
38
        [Extent2].[IsIdentity] AS [IsIdentity],
39
        [Extent2].[IsStoreGenerated] AS [IsStoreGenerated],
40
        0 AS [C1],
41
        [Extent2].[ParentId] AS [ParentId]
42
    FROM (
43
        SELECT
44
            QUOTENAME(c.TABLE_SCHEMA) + QUOTENAME(c.TABLE_NAME) + QUOTENAME(c.COLUMN_NAME) [Id],
45
            QUOTENAME(c.TABLE_SCHEMA) + QUOTENAME(c.TABLE_NAME) [ParentId],
46
            c.COLUMN_NAME [Name],
47
            c.ORDINAL_POSITION [Ordinal],
48
            CAST(CASE c.IS_NULLABLE
49
                WHEN 'YES' THEN 1
50
                WHEN 'NO' THEN 0
51
                ELSE 0
52
            END AS bit) [IsNullable],
53
            CASE
54
                WHEN c.DATA_TYPE IN ('varchar', 'nvarchar', 'varbinary') AND
55
                    c.CHARACTER_MAXIMUM_LENGTH = -1 THEN c.DATA_TYPE + '(max)'
56
                ELSE c.DATA_TYPE
57
            END
58
            AS [TypeName],
59
            c.CHARACTER_MAXIMUM_LENGTH [MaxLength],
60
            CAST(c.NUMERIC_PRECISION AS integer) [Precision],
61
            CAST(c.DATETIME_PRECISION AS integer) [DateTimePrecision],
62
            CAST(c.NUMERIC_SCALE AS integer) [Scale],
63
            c.COLLATION_CATALOG [CollationCatalog],
64
            c.COLLATION_SCHEMA [CollationSchema],
65
            c.COLLATION_NAME [CollationName],
66
            c.CHARACTER_SET_CATALOG [CharacterSetCatalog],
67
            c.CHARACTER_SET_SCHEMA [CharacterSetSchema],
68
            c.CHARACTER_SET_NAME [CharacterSetName],
69
            CAST(0 AS bit) AS [IsMultiSet],
70
            CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') AS bit) AS [IsIdentity],
71
            CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed') | CASE
72
                WHEN c.DATA_TYPE = 'timestamp' THEN 1
73
                ELSE 0
74
            END AS bit) AS [IsStoreGenerated],
75
            c.COLUMN_DEFAULT AS [Default]
76
        FROM INFORMATION_SCHEMA.COLUMNS c
77
        INNER JOIN INFORMATION_SCHEMA.TABLES t
78
            ON c.TABLE_CATALOG = t.TABLE_CATALOG
79
            AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
80
            AND c.TABLE_NAME = t.TABLE_NAME
81
            AND t.TABLE_TYPE = 'BASE TABLE') AS [Extent2]
82
    UNION ALL
83
    SELECT
84
        [Extent3].[Id] AS [Id],
85
        [Extent3].[Name] AS [Name],
86
        [Extent3].[Ordinal] AS [Ordinal],
87
        [Extent3].[IsNullable] AS [IsNullable],
88
        [Extent3].[TypeName] AS [TypeName],
89
        [Extent3].[MaxLength] AS [MaxLength],
90
        [Extent3].[Precision] AS [Precision],
91
        [Extent3].[DateTimePrecision] AS [DateTimePrecision],
92
        [Extent3].[Scale] AS [Scale],
93
        [Extent3].[IsIdentity] AS [IsIdentity],
94
        [Extent3].[IsStoreGenerated] AS [IsStoreGenerated],
95
        6 AS [C1],
96
        [Extent3].[ParentId] AS [ParentId]
97
    FROM (
98
        SELECT
99
            QUOTENAME(c.TABLE_SCHEMA) + QUOTENAME(c.TABLE_NAME) + QUOTENAME(c.COLUMN_NAME) [Id],
100
            QUOTENAME(c.TABLE_SCHEMA) + QUOTENAME(c.TABLE_NAME) [ParentId],
101
            c.COLUMN_NAME [Name],
102
            c.ORDINAL_POSITION [Ordinal],
103
            CAST(CASE c.IS_NULLABLE
104
                WHEN 'YES' THEN 1
105
                WHEN 'NO' THEN 0
106
                ELSE 0
107
            END AS bit) [IsNullable],
108
            CASE
109
                WHEN c.DATA_TYPE IN ('varchar', 'nvarchar', 'varbinary') AND
110
                    c.CHARACTER_MAXIMUM_LENGTH = -1 THEN c.DATA_TYPE + '(max)'
111
                ELSE c.DATA_TYPE
112
            END
113
            AS [TypeName],
114
            c.CHARACTER_MAXIMUM_LENGTH [MaxLength],
115
            CAST(c.NUMERIC_PRECISION AS integer) [Precision],
116
            CAST(c.DATETIME_PRECISION AS integer) AS [DateTimePrecision],
117
            CAST(c.NUMERIC_SCALE AS integer) [Scale],
118
            c.COLLATION_CATALOG [CollationCatalog],
119
            c.COLLATION_SCHEMA [CollationSchema],
120
            c.COLLATION_NAME [CollationName],
121
            c.CHARACTER_SET_CATALOG [CharacterSetCatalog],
122
            c.CHARACTER_SET_SCHEMA [CharacterSetSchema],
123
            c.CHARACTER_SET_NAME [CharacterSetName],
124
            CAST(0 AS bit) AS [IsMultiSet],
125
            CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') AS bit) AS [IsIdentity],
126
            CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed') | CASE
127
                WHEN c.DATA_TYPE = 'timestamp' THEN 1
128
                ELSE 0
129
            END AS bit) AS [IsStoreGenerated],
130
            c.COLUMN_DEFAULT [Default]
131
        FROM INFORMATION_SCHEMA.COLUMNS c
132
        INNER JOIN INFORMATION_SCHEMA.VIEWS v
133
            ON c.TABLE_CATALOG = v.TABLE_CATALOG
134
            AND c.TABLE_SCHEMA = v.TABLE_SCHEMA
135
            AND c.TABLE_NAME = v.TABLE_NAME
136
        WHERE NOT (v.TABLE_SCHEMA = 'dbo'
137
        AND v.TABLE_NAME IN ('syssegments', 'sysconstraints')
138
        AND SUBSTRING(CAST(SERVERPROPERTY('productversion') AS varchar(20)), 1, 1) = 8)) AS [Extent3]) AS [UnionAll1]
139
    ON (0 = [UnionAll1].[C1])
140
    AND ([Extent1].[Id] = [UnionAll1].[ParentId])
141
LEFT OUTER JOIN (
142
    SELECT
143
        [UnionAll2].[Id] AS [C1],
144
        CAST(1 AS bit) AS [C2]
145

146
    FROM (
147
        SELECT
148
            QUOTENAME(tc.CONSTRAINT_SCHEMA) + QUOTENAME(tc.CONSTRAINT_NAME) [Id],
149
            QUOTENAME(tc.TABLE_SCHEMA) + QUOTENAME(tc.TABLE_NAME) [ParentId],
150
            tc.CONSTRAINT_NAME [Name],
151
            tc.CONSTRAINT_TYPE [ConstraintType],
152
            CAST(CASE tc.IS_DEFERRABLE
153
                WHEN 'NO' THEN 0
154
                ELSE 1
155
            END AS bit) [IsDeferrable],
156
            CAST(CASE tc.INITIALLY_DEFERRED
157
                WHEN 'NO' THEN 0
158
                ELSE 1
159
            END AS bit) [IsInitiallyDeferred]
160
        FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
161
        WHERE tc.TABLE_NAME IS NOT NULL) AS [Extent4]
162
    INNER JOIN (
163
        SELECT
164
            7 AS [C1],
165
            [Extent5].[ConstraintId] AS [ConstraintId],
166
            [Extent6].[Id] AS [Id]
167
        FROM (
168
            SELECT
169
                QUOTENAME(CONSTRAINT_SCHEMA) + QUOTENAME(CONSTRAINT_NAME) [ConstraintId],
170
                QUOTENAME(TABLE_SCHEMA) + QUOTENAME(TABLE_NAME) + QUOTENAME(COLUMN_NAME) [ColumnId]
171
            FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE) AS [Extent5]
172
        INNER JOIN (
173
            SELECT
174
                QUOTENAME(c.TABLE_SCHEMA) + QUOTENAME(c.TABLE_NAME) + QUOTENAME(c.COLUMN_NAME) [Id],
175
                QUOTENAME(c.TABLE_SCHEMA) + QUOTENAME(c.TABLE_NAME) [ParentId],
176
                c.COLUMN_NAME [Name],
177
                c.ORDINAL_POSITION [Ordinal],
178
                CAST(CASE c.IS_NULLABLE
179
                    WHEN 'YES' THEN 1
180
                    WHEN 'NO' THEN 0
181
                    ELSE 0
182
                END AS bit) [IsNullable],
183
                CASE
184
                    WHEN c.DATA_TYPE IN ('varchar', 'nvarchar', 'varbinary') AND
185
                        c.CHARACTER_MAXIMUM_LENGTH = -1 THEN c.DATA_TYPE + '(max)'
186
                    ELSE c.DATA_TYPE
187
                END
188
                AS [TypeName],
189
                c.CHARACTER_MAXIMUM_LENGTH [MaxLength],
190
                CAST(c.NUMERIC_PRECISION AS integer) [Precision],
191
                CAST(c.DATETIME_PRECISION AS integer) [DateTimePrecision],
192
                CAST(c.NUMERIC_SCALE AS integer) [Scale],
193
                c.COLLATION_CATALOG [CollationCatalog],
194
                c.COLLATION_SCHEMA [CollationSchema],
195
                c.COLLATION_NAME [CollationName],
196
                c.CHARACTER_SET_CATALOG [CharacterSetCatalog],
197
                c.CHARACTER_SET_SCHEMA [CharacterSetSchema],
198
                c.CHARACTER_SET_NAME [CharacterSetName],
199
                CAST(0 AS bit) AS [IsMultiSet],
200
                CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') AS bit) AS [IsIdentity],
201
                CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed') | CASE
202
                    WHEN c.DATA_TYPE = 'timestamp' THEN 1
203
                    ELSE 0
204
                END AS bit) AS [IsStoreGenerated],
205
                c.COLUMN_DEFAULT AS [Default]
206
            FROM INFORMATION_SCHEMA.COLUMNS c
207
            INNER JOIN INFORMATION_SCHEMA.TABLES t
208
                ON c.TABLE_CATALOG = t.TABLE_CATALOG
209
                AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
210
                AND c.TABLE_NAME = t.TABLE_NAME
211
                AND t.TABLE_TYPE = 'BASE TABLE') AS [Extent6]
212
            ON [Extent6].[Id] = [Extent5].[ColumnId]
213
        UNION ALL
214
        SELECT
215
            11 AS [C1],
216
            [Extent7].[ConstraintId] AS [ConstraintId],
217
            [Extent8].[Id] AS [Id]
218
        FROM (
219
            SELECT
220
                CAST(NULL AS nvarchar(1)) [ConstraintId],
221
                CAST(NULL AS nvarchar(max)) [ColumnId]
222
            WHERE 1 = 2) AS [Extent7]
223
        INNER JOIN (
224
            SELECT
225
                QUOTENAME(c.TABLE_SCHEMA) + QUOTENAME(c.TABLE_NAME) + QUOTENAME(c.COLUMN_NAME) [Id],
226
                QUOTENAME(c.TABLE_SCHEMA) + QUOTENAME(c.TABLE_NAME) [ParentId],
227
                c.COLUMN_NAME [Name],
228
                c.ORDINAL_POSITION [Ordinal],
229
                CAST(CASE c.IS_NULLABLE
230
                    WHEN 'YES' THEN 1
231
                    WHEN 'NO' THEN 0
232
                    ELSE 0
233
                END AS bit) [IsNullable],
234
                CASE
235
                    WHEN c.DATA_TYPE IN ('varchar', 'nvarchar', 'varbinary') AND
236
                        c.CHARACTER_MAXIMUM_LENGTH = -1 THEN c.DATA_TYPE + '(max)'
237
                    ELSE c.DATA_TYPE
238
                END
239
                AS [TypeName],
240
                c.CHARACTER_MAXIMUM_LENGTH [MaxLength],
241
                CAST(c.NUMERIC_PRECISION AS integer) [Precision],
242
                CAST(c.DATETIME_PRECISION AS integer) AS [DateTimePrecision],
243
                CAST(c.NUMERIC_SCALE AS integer) [Scale],
244
                c.COLLATION_CATALOG [CollationCatalog],
245
                c.COLLATION_SCHEMA [CollationSchema],
246
                c.COLLATION_NAME [CollationName],
247
                c.CHARACTER_SET_CATALOG [CharacterSetCatalog],
248
                c.CHARACTER_SET_SCHEMA [CharacterSetSchema],
249
                c.CHARACTER_SET_NAME [CharacterSetName],
250
                CAST(0 AS bit) AS [IsMultiSet],
251
                CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsIdentity') AS bit) AS [IsIdentity],
252
                CAST(COLUMNPROPERTY(OBJECT_ID(QUOTENAME(c.TABLE_SCHEMA) + '.' + QUOTENAME(c.TABLE_NAME)), c.COLUMN_NAME, 'IsComputed') | CASE
253
                    WHEN c.DATA_TYPE = 'timestamp' THEN 1
254
                    ELSE 0
255
                END AS bit) AS [IsStoreGenerated],
256
                c.COLUMN_DEFAULT [Default]
257
            FROM INFORMATION_SCHEMA.COLUMNS c
258
            INNER JOIN INFORMATION_SCHEMA.VIEWS v
259
                ON c.TABLE_CATALOG = v.TABLE_CATALOG
260
                AND c.TABLE_SCHEMA = v.TABLE_SCHEMA
261
                AND c.TABLE_NAME = v.TABLE_NAME
262
            WHERE NOT (v.TABLE_SCHEMA = 'dbo'
263
            AND v.TABLE_NAME IN ('syssegments', 'sysconstraints')
264
            AND SUBSTRING(CAST(SERVERPROPERTY('productversion') AS varchar(20)), 1, 1) = 8)) AS [Extent8]
265
            ON [Extent8].[Id] = [Extent7].[ColumnId]) AS [UnionAll2]
266
        ON (7 = [UnionAll2].[C1])
267
        AND ([Extent4].[Id] = [UnionAll2].[ConstraintId])
268
    WHERE [Extent4].[ConstraintType] = N'PRIMARY KEY') AS [Project5]
269
    ON [UnionAll1].[Id] = [Project5].[C1]
270
WHERE [Extent1].[Name] LIKE N'%'

This query executes for almost 7 minutes to retrieve 491 rows of schema info:

This is too long running. After trying around, the only working solution is to change SQL Server 2014 SP! database’s default compatibility mode (120) to SQL Server 2012 (110):

1
ALTER DATABASE [AdventureWorks2014]
2
SET COMPATIBILITY_LEVEL = 110;
3
GO
4

5
SELECT compatibility_level
6
FROM sys.databases WHERE name = N'AdventureWorks2014';
7
GO

Then above schema query executes in 8 seconds:

Disregarding this long running query execution as a regression, at least in Entity Model Wizard, the UI thread should not be used to execute the query, which causes Visual Studio frozen.

Visual Studio Entity Data Model Wizard Not Responding

https://dixin.github.io/posts/visual-studio-entity-data-model-wizard-not-responding/

Author

Dixin

Published at

2016-02-13

License

CC BY-NC-SA 4.0

Entity Framework and LINQ to Entities (10) Performance

Entity Framework and LINQ to Entities (8) Transactions