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