10

I am building a .NET disconnected client-server application that uses Entity Framework 5 (EF5) to generate a SQL Server CE 4.0 database from POCOs. The application allows the user to perform a bulk copy of data from the network SQL Server into the client's SQL Server CE database. This is very (VERY) slow, due to the constraints and indexes created by EF5. Temporarily dropping the constraints and indexes will reduce the 30-minute wait to 1 minute or less.

Before starting the bulk copy, the application executes queries to drop the constraints and indexes from the SQL Server CE tables. However, the commands fail, because EF5 created constraint names include the table schema name, dot, and table name. The dot in the constraint name is causing the drop command to fail, due to a parsing issue.

For example, POCO Customer creates table dbo.Customer with the primary key constraint PK_dbo.Customer_Id. The database performs as expected.

However, upon executing non-query:

 ALTER TABLE Customer DROP CONSTRAINT PK_dbo.Customer;

SQL Server Compact ADO.NET Data Provider returns an error:

There was an error parsing the query.
[ Token line number = 1, Token line offset = 57, Token in error = . ]

Of course, using a secondary DataContext object that does not have foreign keys generate the database without the constraints, and then add them later works; but, that requires maintaining two DataContext objects and hopefully not forgetting to keep both updated. Therefore, I am looking for one of two solutions:

  1. Compose the DROP statement in such a way that the . character is parsed

  2. Prevent EF5 from using the . character in the constraint and index names

Thank you in advance for your help!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mike Christian
  • 1,526
  • 1
  • 15
  • 27

1 Answers1

15

Wrap that bad boy in a []. It tells the parser that everything inside is the key name. 

ALTER TABLE Customer DROP CONSTRAINT [PK_dbo.Customer];

Should run fine. Personally I just wrap every identifier in brackets to avoid this exact issue. So I would write this query like this.

ALTER TABLE [Customer] DROP CONSTRAINT [PK_dbo.Customer];

I think it's more readable that way because you can instantly see identifiers.

Marissa
  • 430
  • 3
  • 12