EF5 generates SQL Server CE constraints with a dot in the name

Question

I am creating a .NET disconnected.NET client application that uses Entity Framework 5 (EF5) to create a SQL Server CE 4.0 database from POCOs. The application allows the user to make a bulk copy of data from a networked SQL Server to a SQL Server CE client database. This is very (VERY) slow, due to the restrictions and indexes created by EF5. Temporarily reducing restrictions and indexes will reduce the 30-minute wait to 1 minute or less.

Before running a bulk copy, an application executes queries to remove constraints and indexes from SQL Server CE tables. However, the commands do not work, because EF5 creates constraint names, including the table schema name, dot, and table name. A dot in the constraint name causes the drop command to fail due to a parsing problem.

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

However, after executing the request:

  ALTER TABLE Customer DROP CONSTRAINT PK_dbo.Customer; 

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

An error occurred while parsing the request.
[Token line number = 1, token line offset = 57, token with error =. ]

Of course, using a secondary DataContext object that has no foreign keys generates a database without restrictions, and then adds them later; but this requires two DataContext objects to be supported, and hopefully remember to keep both updates. Therefore, I am looking for one of two solutions:

• Compose your DROP statement so that. character is parsed

• Prevent the use of EF5. symbol in constraint and index names

Thank you in advance for your help!

Answer 1

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

 ALTER TABLE Customer DROP CONSTRAINT [PK_dbo.Customer]; 

Should work fine. Personally, I simply put each identifier in brackets to avoid this exact problem. Therefore, I would write this query as follows.

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

I think this is more readable because you can see identifiers instantly.