Refer to a table in another database on the same server

Asked

Viewed 28 times

0

I’m making an app on C# and .NET Core (ASP.NET Core MVC) using the EntityFrameworkCore and in the Connection String access to a database, say DB1, and I can read the tables in this database programmatically.

The string Connection is:

Server=MY\\SQLEXPRESS;Initial Catalog=DB1;User ID=Me;
    Persist Security Info=True;Integrated Security=SSPI;

But on the same server I have other databases, say DB2. In my app I needed to access both databases. So the code of the Model is:

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace MyApp.Models
{
    // modelo das várias tabelas na DB1...

    [Table("tMyTable")]      // esta tabela está na DB2!
    public class MyModel
    {
        [Key]
        public int ID { get; set; }
        // ...
    }
}

The data access model is:

using System.Linq;
using Microsoft.EntityFrameworkCore;

public class appDataContext : DbContext
{
    private AppFeatures features { get; set; }

    private DbContextOptions<appDataContext> opt { get; set; }

    private DbSet<MyModel> tbl { get; set; }

    public appDataContext(
        DbContextOptions<appDataContext> options, 
        AppFeatures _features 
    )
        : base(options)
    {
        opt = options;
        features = _features;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseSqlServer(features.ConnectionString);

    private IEnumerable<MyModel> _fcRetrieveUser(int id)
        => tbl.Where(l => l.ID == id);

    // acesso a outras tabelas
}

Of course this code does not work, because the tMyTable is not pointed at the Connection String, how can I refer to this table in the correct database without creating another Connection String and using this skeleton, and without using pure SQL (Raw SQL)?

I’ve tried to put [Table("DB2.dbo.tMyTable")] or [Table("tMyTable", Schema="DB2.dbo")] but none worked.

Is there an attribute you can use to refer to the database DB2, as an example Database?

  • 2

    There is no way! Each ORM layer has its own connection from that the entities work according to that connection ... Create another Persistence layer and each takes care of your tables / entities. Why complicate?

  • It means I have to use raw SQL (like SELECT ... FROM DB2.dbo.tMyTable) or create two DbContext...

  • 1

    You have to create another Dbcontext! Each in its square.

  • Until today the only way I could work in this scenario was by abandoning EF and writing all the CRUD sql in the same hand. The bases being on the same server and the connection with a user who has access there all you would not need more than one context

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.