LINQ to SQL, SQL Compact and the |DataDirectory| macro
As I previously mentioned having the LINQ DataContext create the database if it doesn't exist yet is a cool feature but it isn't perfect.
The problem I just ran into was while using the |DataDirectory| macro, something the SqlCeConnection understands and will replace with the directory specified. Well it turns out the LINQ DataContext isn't that smart when checking if the database exists. Apparently it checks if the database file exists without expanding the |DataDirectory| so it never finds the database. The result is a SqlCeException with the message: "File already exists. Try using a different database name. [ File name = C:\Projects\ConsoleApplication1\ConsoleApplication1\bin\Debug\Data2.sdf ]"
Actually creating the database the first time works just fine and all other actions like querying or updating are fine to.
To reproduce the error create a console app and add a LINQ to SQL DBML file. Just add a small class with a property to server as table.
Add the following code:
' Cleanup any previous files
Catch ex As Exception
For i As Integer = 1 To 2
Sub CreateDatabaseIfNeeded(ByVal connection As String)
Dim context As New DataClasses1DataContext(connection)
If Not context.DatabaseExists() Then
And run the application.
Want to see this fixed? The vote for it here.