EF-Core how to access schema tables using DbContext

In this article, we’re going to look at how to access tables stored in an existing Database schema using Entity Framework Core DbContext. Let’s create a simple DbContext class using C# and access the tables in a Schema.

First, create a console application and add a class called AppContext.cs into it.

Then simply inherit DbContext class. This will give you a code error. To get rid of this error, open Nuget package manager and search Entity Framework Core and install the package.

Then override the following method. You can find out more details about DbContext by pressing F12 on you keyboard.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
 {
      optionsBuilder.UseSqlServer(connStr);
 }

The moment you add this code into your class, it will trigger another code error under UseSqlServer method. To avoid this error go back to Nuget Package manager, find Microsoft.EntityFrameworkCore.SqlServer and install this package

Now create your first table reference inside the DBContext class as follows;

public DbSet<Account> Account { get; set; }

Note: If you don’t use the exact name of the table as property name above, you will encounter runtime error saying Invalid object name Account.

Now your AppContext class will look as follows;

public class AppContext:DbContext
    {
        private const string connStr = @"Data Source=DESKTOP-UV9IG5D\SQLEXPRESS01;Initial Catalog=Finance;Integrated Security=True;Application Name=Finance";

        public DbSet<Account> Account { get; set; }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(connStr);
        }        
    }

Since you have not created the Account class yet, you may see a code error. Let’s create the Account class as follows;

public class Account
{
   public int AccountID { get; set; }
   public string AccountNumber { get; set; }
   public string AccountName { get; set; }
   public Account() { }
}

Now change the console app main method as follows and query the Database.

using(var DB= new AppContext())
 {
    var account = DB.Account.Where(x => x.AccountID == 1).FirstOrDefault();
    Console.WriteLine($"Account number : {account.AccountNumber} And Account Name : {account.AccountName}");
 }

Next, run your code with appropriate connection string pointing to a Database containing a table called Account in it, and you can read records from it. It is simple as that.

This will work because, account table is with the default schema dbo. But when you do real world development, we tend to keep tables in different schemas for clarity.

Let’s login to Database and change the schema of the account table. You can do that using following sql command;

ALTER SCHEMA Account TRANSFER dbo.Account

Run the application again. You will get an error “Invalid object name ‘Account'”.

This is an error that we may need more attention. Error stack trace also not that informative. The only option is to use the SQL Trace to check the sql statement generated by the LINQ query. This is the best way to tackle this error.

SQL trace shows the query generated by the LINQ query as follows;

SELECT TOP(1) [a].[AccountID], [a].[AccountName], [a].[AccountNumber]
FROM [Account] AS [a]
WHERE [a].[AccountID] = 1

As we can see, it is obvious that there is no reference to the schema passed from the DbContext. So, how do we fix this?

Configure EF Core DBContext read Schema details

EF- Core provides a method called OnModelCreating(). This method accepts a parameter called ModelBuilder. ModelBuilder provides a function called HasDefaultSchema(), which accepts the schema name as a string. This will get executed while DbContext is building its model when you run the linq query. Using ModelBuilder parameter we can tell the DbContext which schema the account table belongs to. Add the following code into DbContext;

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
   modelBuilder.HasDefaultSchema("Account").Entity<Account>();
}

Run the code again and the App will fetch account details from the DB. This will solve query existing database table in a schema using Entity Framework Core.

Also, if you check the SQL trace window, you will notice the correct sql statement getting generated by the linq query.

SELECT TOP(1) [a].[AccountID], [a].[AccountName], [a].[AccountNumber]
FROM [Account].[Account] AS [a]
WHERE [a].[AccountID] = 1

How to use data annotations set schema details

Another way of doing the same thing without overriding OnModelCreating() method is to decorate your POCO class with data annotations Table attribute. You can change you account class as follows;

[Table("Account",Schema ="Account")]
public class Account
{
   public int AccountID { get; set; }
   public string AccountNumber { get; set; }
   public string AccountName { get; set; }
   public Account() { }
}

You can download source code here