4

I have a MSSQL DB with about 3300tables (don't ask why, that's Nav...). When I try to "Update Model from Database..." on my EDMX I am getting a timeout exception 99 times out of 100. So my question is, is there anyway to change the timeout? I have tried to set "Connect Timeout" in the connection string, but it doesn't change anything.

I also tried to create a SQL account with an access on only the tables I need (about 10) but the timeout in the wizard still occur.

01000001 01010011
  • 71
  • 1
  • 8
  • Do you REALLY need 3300 tables in the same EDMX file? Which SQL Server and EF versions and EF Tools versions do you use? – ErikEJ Jul 07 '15 at 08:23
  • I didn't say my EDMX had 3300 tables, I said the DB have 3300 tables, the timeout occur when the wizard try to get the list of the tables from the DB. So I am not even able to add a table to my EDMX. Sorry if it wasn't clear. – 01000001 01010011 Jul 08 '15 at 08:20
  • Ah, OK. No, connection timeout doesn't help. And command timeout can't be set in the connection string. Is it an option to switch to "code first" (but not really, i.e. no migrations)? You'd have to sync the EF model manually when the database changes. Usually that's not too complicated. (Also, native edmx support will disappear in EF 7). – Gert Arnold Jul 08 '15 at 08:50

2 Answers2

3

Someone at work just helped me with this.

  1. Run a SQL profiler and try to update your model again.

  2. Capture the SQL Query that Visual Studio trys to run. It should look something like this 

    SELECT 
[Project1].[C1] AS [C1], 
[Project1].[CatalogName] AS [CatalogName], 
[Project1].[SchemaName] AS [SchemaName], 
[Project1].[Name] AS [Name]
FROM ( SELECT 
    [Extent1].[CatalogName] AS [CatalogName], 
    [Extent1].[SchemaName] AS [SchemaName], 
    [Extent1].[Name] AS [Name], 
    1 AS [C1]
    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]
)  AS [Project1]
ORDER BY [Project1].[SchemaName] ASC, [Project1].[Name] ASC

  3. Then run the query in SS Managment Studio. When you run the query the results should be cached. When Visual Studio runs the query again the results should come back really quick and it won't time out.

user3236794
  • 578
  • 1
  • 6
  • 16
0

A small work around :

  • Generate an sql script to create only the tables you need from the DB
  • Create another DB and execute the sql script
  • Generate the EDMX from this new DB
  • Change the connection string of the EDMX to connect to the first DB
01000001 01010011
  • 71
  • 1
  • 8