The Problem Solver

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:

Module Module1
    Sub Main()
        Try
            ' Cleanup any previous files
            My.Computer.FileSystem.DeleteFile("Data1.sdf")
            My.Computer.FileSystem.DeleteFile("Data2.sdf")
        Catch ex As Exception
        End Try

        For i As Integer = 1 To 2
            CreateDatabaseIfNeeded("Data Source=Data1.sdf")
            CreateDatabaseIfNeeded("Data Source=|DataDirectory|\Data2.sdf")
        Next
    End Sub

    Sub CreateDatabaseIfNeeded(ByVal connection As String)
        Dim context As New DataClasses1DataContext(connection)
        If Not context.DatabaseExists() Then
            Console.WriteLine("Creating database")
            context.CreateDatabase()
        Else
            Console.WriteLine("Database exists")
        End If
    End Sub
End Module

And run the application.

Want to see this fixed? The vote for it here.

Enjoy!

www.TheProblemSolver.nl
http://wiki.WindowsWorkflowFoundation.eu