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
?
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?
– novic
It means I have to use raw SQL (like
SELECT ... FROM DB2.dbo.tMyTable
) or create twoDbContext
...– JDias
You have to create another Dbcontext! Each in its square.
– novic
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
– Leandro Angelo