How to use Entity Framework on multiple databases with asp.net mvc c#?

Question

I am new to Entity Framework with ASP.NET MVC and C#. I need to create the small demo for crud operation using Entity Framework and in the backend, I have used SQL Server. I have to create 2 databases:

1. Employee
2. EmployeeUsers

In database Employee I have one table, Sectors.

Here below I have shown my table data.

ID | Sector_name | UserId
1     Private       1
2     Business      2

In database EmployeeUsers I have also one table, users.

This is the sample data for users:

ID | FName | LName
1    ABC     P
2    XYZ     x

Now using Entity Framework, I need to work with 2 databases but how can do that I have no idea anyone know then please let me known.

This is my expected o/p:

ID | SectorName | UserFname
 1    Private       ABC
 2    Business      XYZ

Answer 1

What you can do is, use two entity framework database contexts for each database with their database connection string.

Creating the database context depends on the entity framework development model you choose. (Code first or Database first)

Database first:

Create edmx file for both databases. If you want to know how to create it, check out this Microsoft Link

Code first

the model for Employee database

public class Sector
{
   public int ID {get; set;}
   public string Sector_name {get; set;}
   public int UserId {get; set;}
   // you can't use Users class for navigation purpose because Users is in different context
}

So for Employee database you can use this database context

public partial class EmployeeContext : DbContext
{
    public EmployeeContext()
        : base("name=EmployeeDatabaseConnectionString")
    {
    }
    
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        throw new UnintentionalCodeFirstException();
    }
    
    public virtual DbSet<Sector> Sectors { get; set; }
}

the model for EmployeeUser database

public class User
{
   public int ID {get; set;}
   public string FName {get; set;}
   public string LName {get; set;}
}

for EmployeeUser database, use the following database context

public partial class EmployeeUserContext : DbContext
{
    public EmployeeUserContext()
        : base("name=EmployeeUserDatabaseConnectionString")
    {
    }
    
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        throw new UnintentionalCodeFirstException();
    }
    
    public virtual DbSet<User> Users { get; set; }
}

The tricky thing in code first is, you have to handle the migration for both database contexts. If you want to know how to do that, check out this SO answer.

Now you can do anything using the Database context. For your question on the comment, on how you can show a list of the sector table with the username....... I don't see a UserName column on the Users table.

Assuming that UserName is FName and assuming sector's user id are in sync with users table id.

The answer to your question could be:

Since both tables are in a different context, and Entity framework context does not support cross-database queries; you have to write two LINQ queries.

example:

UserName: ABC

EmployeeContext employeeContext = new EmployeeContext();
EmployeeUserContext employeeUserContext = new EmployeeUserContext();

// get the user
var user = employeeUserContext.Users.FirstOrDefault(u => u.FName == "ABC");

// get sector list
var sectors = employeeContext.Sectors.Where(s => s.UserId == user.Id);

UPDATED:

var employeeContext = new EmployeeContext();
var employeeUserContext = new EmployeeUsersContext();
            
var users = employeeUserContext.Users.ToList();
var sectors = employeeContext.Sectors.ToList();

var yourTask = from user in users
               join sector in sectors on user.ID equals sector.UserId
               select new {sector.ID, sector.SectionName, user.FName};

Answer 2

If you have two databases then create two edmx. This will provide you with two connection strings in your app.config which contains the name to the context classes to use. Notice that using statements are using two different contexts.

public class DataAccess
{
    public int GetUserIdByUserName(string username)
    {
        using (var context = new EmployeeUsersEntities())
        {
            var user = context.Users.First(d => d.Name == username);
            return user.Id;
        }
    }

    public string GetSectorByUserId(int id)
    {
        using (var context = new EmployeeEntities())
        {
            var sector = context.Sectors.First(d => d.UserId == id);
            return sector.Sector_name;
        }
    }
}

If you have one database containing the two tables, then create one edmx and you will have one connection string and one context class.