How do I update DBContext after adding new table to SQL database?

Question

We added a new table to our SQL Server database called Occupation.

From the ASP.NET Web Form project, I can not seem to access it.

Imports System.Data.Entity

Public Class CCDBContext
    Inherits DbContext

Public Sub New()
    MyBase.New("name=CCDBContext")
End Sub

Public Property ClaimDetail_CSR() As DbSet(Of ClaimDetail_CSR)
Public Property CSR_Filter() As DbSet(Of CSR_Filter)
Public Property CSR_AnalysisCodes() As DbSet(Of CSR_AnalysisCodes)
Public Property Occupations() As DbSet(Of Occupation)

Protected Overrides Sub OnModelCreating(modelBuilder As System.Data.Entity.DbModelBuilder)
    MyBase.OnModelCreating(modelBuilder)
End Sub
End Class

The class for Occupation:

Public Class Occupation
Public Property ClassCode As String
Public Property Description As String

Public Property idOccupation As Integer
Public Property OccupationName As String
Public ReadOnly Property Occupation As String
    Get
        Dim pt1 As String = String.Format("{0}", ClassCode).Trim()
        Dim pt2 As String = String.Format("{0}", OccupationName).Trim()
        Dim pt3 As String = String.Format("{0}", idOccupation)
        Return String.Format("{0} - {1} - {2}", pt1, pt2, pt3)
    End Get
End Property
End Class

I need to link it into my Linq query:

dim query = (
From cd as ClaimDetail_CSR In ClaimQuery
Join oc As Occupation In db.Occupations On cd.idOccupation Equals oc.idOccupation
Group Join ac As CSR_AnalysisCodes In db.CSR_AnalysisCodes On cd.ClaimKey Equals ac.Claimkey Into MatchedClaim = Group
From mc In MatchedClaim.DefaultIfEmpty()
Select cd.ClientID, cd.ClaimKey, cd.Region, cd.Office, cd.Department, cd.ClaimNumber,

You can see where I want to join the new Occupation table above.

Whenever I run the code, however, I get this exception:

System.Data.Entity.Core.EntityCommandExecutionException: 'An error occurred while executing the command definition. See the inner exception for details.'

Inner Exception SqlException: Invalid object name 'dbo.Occupations'.[/quote]

From what I have gathered in my online searches, the suggested thing to do is to open the .edmx file, right-click somewhere in the empty space, and select 'update model from database'.

https://stackoverflow.com/q/28479785/27599767

However, my project does not have an .edmx file. It simply has the DBContext files.

How do I update my DBContext to include the new SQL table?

Accepted Answer

Hi @Joe Pool (Heartland),

Based on the description of your issue, I recommend that you can check the problem from the following two points:

Table name (Community members have mentioned): Occupation != Occupations .
ConnectionString, check the name of database you're accessing, for the property Initial Catalog or Database.

If the table name you define is inconsistent with the class name, you can implement the mapping in the following way:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity()
        .ToTable("TableName");
}

Or use Annotation on your class, something like this:

[Table("TableName")] 
public class ClassName
{     
	public int prop1 { get; set; }     
	public string prop2 { get; set; } 
}

Best regards,

Xudong Peng

If the answer is the right solution, please click "Accept Answer" and kindly upvote. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer

However, my project does not have an .edmx file. It simply has the DBContext files.

If you want to create an .edmx file and use Entity Data Model:

(1) Click [Add] => [New Item...] ,

enter image description here

(2) Show [Add New Item] dialog and select [ADO.NET Entity Data Model],

enter image description here

(3) Show [Entity Data Model Wizard], select [EF Designer from database] and proceed with creating the Entity Data Model (.edmx file).

enter image description here

If you want to use a context class (DBContext) and entity classes rather than the EDM, I suggest that you recreate them. To do so, select [Code First from database] and proceed with creating new context class (DBContext) and entity classes.

enter image description here

Share via

How do I update DBContext after adding new table to SQL database?

1 additional answer

Your answer