SQLite Error 1: 'no such column: c.ClientId - looking at wrong table

Viewed 453

I have the following code:

Private Sub GetDropdowns()
        Using context As New ClientContext
            ClientTypeList = New ObservableCollection(Of ClientType)(context.ClientTypes.ToList())                
            CaseworkersList = New ObservableCollection(Of Caseworker)(context.Caseworkers.ToList())
        End Using
    End Sub

In this code, ClientTypeList pulls correctly. Currently there are no items in the database. CaseworkersList give the following error:

{"SQLite Error 1: 'no such column: c.ClientId'."}

When I comment out CaseworkersList, ClientTypeList continues to work as expected, returning an empty list.

When I comment out ClientTypeList, I continue to get the error for CaseworkersList.

I have confirmed that my context is creating the database and tables. The CaseworkersList does not have a column named ClientId, but the object/list it is trying to fill also does not have a property for this, so i don't understand why i am getting this error. It seems like it is looking at the wrong table for some reason.

My context looks like this:

Namespace Models
Public Class ClientContext
    Inherits DbContext

    Public Property Clients As DbSet(Of Client)
    Public Property Attorneys As DbSet(Of Attorney)
    Public Property Caseworkers As DbSet(Of Caseworker)
    Public Property IncomeSources As DbSet(Of IncomeSource)
    Public Property InsurancePolicies As DbSet(Of InsurancePolicy)
    Public Property ClientTypes As DbSet(Of ClientType)
    Public Property ClientAttorneys As DbSet(Of ClientAttorneys)
    Public Property ClientCaseworkers As DbSet(Of ClientCaseworkers)
    Public Property ClientIncomeSources As DbSet(Of ClientIncomeSources)
    Public Property ClientInsurancePolicies As DbSet(Of ClientInsurancePolicies)

    Public Sub Create()
        Using dbContext As ClientContext = New ClientContext()
            dbContext.Database.Migrate()
            dbContext.InitialMigration()
            dbContext.SaveChanges()
        End Using
    End Sub

    Protected Overrides Sub OnConfiguring(optionsBuilder As DbContextOptionsBuilder)
        Dim connectionStringBuilder = New SqliteConnectionStringBuilder With {.DataSource = "ClientRecords.sqlite"}
        Dim connectionString As String = connectionStringBuilder.ToString()
        Dim connection As SqliteConnection = New SqliteConnection(connectionString)
        optionsBuilder.UseSqlite(connection)
    End Sub


#Region "Migration Code"

    Private Sub InitialMigration()

        'Create tables that do not exist

        'Create Clients Table
        Me.Database.ExecuteSqlCommand("CREATE TABLE IF NOT EXISTS 'Clients' ('Id' GUID NOT NULL PRIMARY KEY, 'Name' TEXT, 'EstateNumber' TEXT,
                                                'ClientTypeId' GUID, 'Active' INTEGER, 'Residence' TEXT,
                                                'AddressLine1' TEXT, 'AddressLine2' TEXT, 'City' TEXT, 'State' TEXT, 'ZipCode' TEXT, 'County' TEXT, 'Phone' TEXT, 
                                                'Fax' TEXT, 'AcctCode' TEXT, 'BirthDate' DATE, 'Placement' DATE, 'Letters' DATE, 'LastAccounting' DATE, 
                                                'DateOfDeath' DATE, 'ClosureDate' DATE, 'Minor' INTEGER, 'Disabled' INTEGER, 'Deceased' INTEGER, 
                                                'PersonalRepresentative' INTEGER, 'OtherCheckFormat' INTEGER, 'BondCompany' TEXT, 'BondNumber' TEXT, 
                                                'BondAmount' REAL, 'SocialSecurityNumber' TEXT, 'MedicareClaimNumber' TEXT, 'MedicaidClaimNumber' TEXT, 
                                                'VaClaimNumber' TEXT, 'SsiEligibilityTest' INTEGER, 'MedicaidEligibilityTest' INTEGER,
                                                'PreferHome' TEXT, 'Cemetery' TEXT, 'BurialPlan' TEXT, FOREIGN KEY(ClientTypeId) REFERENCES ClientTypes(Id))")
        'Create Attorneys Table
        Me.Database.ExecuteSqlCommand("CREATE TABLE IF NOT EXISTS 'Attorneys' ('Id' GUID NOT NULL PRIMARY KEY, 'Name' TEXT,
                                                'PhoneNumber' TEXT, 'Email' TEXT)")
        'Create Caseworkers Table
        Me.Database.ExecuteSqlCommand("CREATE TABLE IF NOT EXISTS 'Caseworkers' ('Id' GUID NOT NULL PRIMARY KEY, 'Name' TEXT,
                                                'PhoneNumber' TEXT, 'Email' TEXT, 'Mileage' REAL, 'HourlyRate' REAL)")
        'Create IncomeSources Table
        Me.Database.ExecuteSqlCommand("CREATE TABLE IF NOT EXISTS 'IncomeSources' ('Id' GUID NOT NULL PRIMARY KEY, 'Name' TEXT)")

        'Create InsurancePolicies Table
        Me.Database.ExecuteSqlCommand("CREATE TABLE IF NOT EXISTS 'InsurancePolicies' ('Id' GUID NOT NULL PRIMARY KEY, 'Name' TEXT,
                                                'Number' TEXT)")
        'Create ClientTypes Table
        Me.Database.ExecuteSqlCommand("CREATE TABLE IF NOT EXISTS 'ClientTypes' ('Id' GUID NOT NULL PRIMARY KEY, 'Name' TEXT)")

        'Create ClientAttorneys Table
        Me.Database.ExecuteSqlCommand("CREATE TABLE IF NOT EXISTS 'ClientAttorneys' ('Id' GUID NOT NULL PRIMARY KEY, 'ClientId' GUID,
                                                'AttorneyId' GUID, 'StartDate' DATE, 'EndDate' DATE,
                                                FOREIGN KEY(ClientId) REFERENCES Clients(Id), FOREIGN KEY(AttorneyId) REFERENCES InsurancePolicies(Id))")
        'Create ClientCaseWorkers Table
        Me.Database.ExecuteSqlCommand("CREATE TABLE IF NOT EXISTS 'ClientCaseWorkers' ('Id' GUID NOT NULL PRIMARY KEY, 'ClientId' GUID,
                                                'CaseworkerId' GUID, 'StartDate' DATE, 'EndDate' DATE,
                                                FOREIGN KEY(ClientId) REFERENCES Clients(Id), FOREIGN KEY(CaseworkerId) REFERENCES Caseworkers(Id))")
        'Create ClientIncomeSources Table
        Me.Database.ExecuteSqlCommand("CREATE TABLE IF NOT EXISTS 'ClientIncomeSources' ('Id' GUID NOT NULL PRIMARY KEY, 'ClientId' GUID,
                                                'IncomeSourceId' GUID, 'StartDate' DATE, 'EndDate' DATE, 'Amount' TEXT, 'Period' INTEGER,
                                                FOREIGN KEY(ClientId) REFERENCES Clients(Id), FOREIGN KEY(IncomeSourceId) REFERENCES IncomeSources(Id))")
        'Create ClientInsurancePolicies Table
        Me.Database.ExecuteSqlCommand("CREATE TABLE IF NOT EXISTS 'ClientInsurancePolicies' ('Id' GUID NOT NULL PRIMARY KEY, 'ClientId' GUID,
                                                'InsurancePolicyId' GUID, 'StartDate' DATE, 'EndDate' DATE,
                                                FOREIGN KEY(ClientId) REFERENCES Clients(Id), FOREIGN KEY(InsurancePolicyId) REFERENCES InsurancePolicies(Id))")
    End Sub


#End Region
End Class
End Namespace

and my Caseworker model looks like this:

Namespace Models
Public Class Caseworker

    Public Property Id As String
    Public Property Name As String
    Public Property PhoneNumber As String
    Public Property Email As String
    Public Property Mileage As Double
    Public Property HourlyRate As Double

End Class
End Namespace
0 Answers
Related