How do I migrate a class to a dotted table name in SQLite using Entity Framework's Code First approach?

[fabmkk]

Dear Guru,

With Entity Framework's Code-First approach, how do I migrate this class to a dotted table named 'Entity.Student' in SQLite3? Is this possible?

I am currently using dotConnect for SQLite Pro v5.1.80.0 with Entity Framework v6.0.2

I know that SQLite supports a dotted table name, for example:

Code: Select all

sqlite> CREATE TABLE [123abc](col);
sqlite> CREATE TABLE [123abc.txt](col);
sqlite> CREATE TABLE [123abc-ABC.txt](col);
sqlite> select tbl_name from sqlite_master;
123abc
123abc.txt
123abc-ABC.txt

The context,

Code: Select all

<DbConfigurationType(GetType(Devart.Data.SQLite.Entity.SQLiteEntityProviderServicesConfiguration))> _
Public Class SQLiteContext
	Inherits DbContext
	Public Sub New()
		MyBase.New("SQLiteDb")
	End Sub

	Public Property Students As DbSet(Of Student)
End Class

The class,

Code: Select all

Public Class Student
	Private _StudentId As Int32
	Public Property StudentId() As Int32
		Get
			Return _StudentId
		End Get
		Set(ByVal value As Int32)
			_StudentId = value
		End Set
	End Property

	Private _Name As String
	Public Property Name() As String
		Get
			Return _Name
		End Get
		Set(ByVal value As String)
			_Name = value
		End Set
	End Property
End Class

Then I tried defining the table name explicitly in the class,

Code: Select all

<Table("[Entity.Students]")> _
Public Class Student
...
End Class

After running 'add-migration SQLITE_add_students_table_test' command, this migration was generated,

Code: Select all

    Public Partial Class SQLITE_add_students_table_test
        Inherits DbMigration
    
        Public Overrides Sub Up()
            CreateTable(
                "dbo.[Entity.Students]",
                Function(c) New With
                    {
                        .StudentId = c.Int(nullable := False, identity := True),
                        .Name = c.String()
                    }) _
                .PrimaryKey(Function(t) t.StudentId)
            
        End Sub
        
        Public Overrides Sub Down()
            DropTable("dbo.[Entity.Students]")
        End Sub
    End Class

When I finally ran the 'update-database' command, this error came out in the Package Manager Console,

The multi-part identifier 'dbo.[Entity.Students]' could not be used. Use two-part identifier instead.

I also tried removing the 'dbo.' like this,

Code: Select all

	Partial Public Class SQLITE_add_students_table_test
		Inherits DbMigration

		Public Overrides Sub Up()
			CreateTable(
				"[Entity.Students]",
				...
				) _
				.PrimaryKey(Function(t) t.StudentId)

		End Sub

		Public Overrides Sub Down()
			DropTable("[Entity.Students]")
		End Sub
	End Class

But when I excitedly ran the 'update-database' again, this came out:

SQLite error
unknown database "[Entity"

Am I missing something here?

Thanks Guru,

[Shalex]

Thank you for your report. We have reproduced the issue and are investigating it. We will notify you about the result.

As a workaround, please use <Table("Entity_Students")> instead of <Table("[Entity.Students]")>.

[Shalex]

We have encountered the limitation of the EF Code-First Migrations engine when fixing the issue. Please use the described workaround.