1

Have a project where I have my app which has it's database and models, but I need to connect to another database now that I need to import from. I have found many articles that show how to set up the second database, but it is not clear how to use it with out creating a model. I will state that I have been researching this for a bit now and can't seem to find a coherent answer.

Here are some points

  • The second database is only read only
  • the data from the read only context is split on a few tables with in the app
  • a wish is to do a mapping to have the fastest import as possible
  • It is not that a model for the read only is forbidden, it is just that it is best if it is dynamically typed, or created
  • It seem my project now has 3 other DBs to pull info from just as added information

Main Question Is there an appropriate workflow that will allow for the import from the second database into the app's database when the second database is read only?

Here is what I have so far

Startup.cs

public class Startup
{
    public void ConfigureServices(IServiceCollection services)
    {
        services.AddDbContext<ApplicationDbContext>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection")));

        services.AddDbContext<FDWDbContext>(options =>
            options.UseSqlServer(Configuration.GetConnectionString("FDWConnection")));

        services.AddIdentity<ApplicationUser, IdentityRole>(options =>
        {
            options.Password.RequireDigit = true;
            options.Password.RequireLowercase = true;
            options.Password.RequireNonAlphanumeric = true;
            options.Password.RequireUppercase = true;
            options.Password.RequiredLength = 24;
        })
            .AddEntityFrameworkStores<ApplicationDbContext>()
            .AddDefaultTokenProviders();

    }
}

DBContext.cs

public class FDWDbContext : DbContext
{
    public FDWDbContext(DbContextOptions<FDWDbContext> options)
    : base(options)
    {
       // the second database
    }
}

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>
{
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options)
        : base(options)
    {
       // the app's database
    }

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);
    }

    public DbSet<ApplicationUser> user { get; set; }

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

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

Ideas I have come across

Create on the fly The trouble here is that I don't find much information on this topic. So thoughts I have had is to maybe build a reference table and use something like this to map and cache (DBs don't normally change quickly) and then "create" a model like that. How to get Column name and corresponding Database Type from DbContext in Entity Framework Core is where i got the thought from.

Seems like over kill.

reverse engineer it Another thought was to just suck it up, literally and have models for that.

Create Entity Framework model based on an existing database in ASP.NET Core

But here is the issues that i see, first organizationally, what if that DB has a few hundred tables? I would have a mess of model files. I know of apps that have a few K of tables. That would see to just swamp out the real app and just.. well also seems like over kill.

Community
  • 1
  • 1
Quantum
  • 1,456
  • 3
  • 26
  • 54
  • You you just need to import the data from the "read-only" database into your EF database? No other interaction? How often does this need to happen? – Sam Axe Apr 20 '17 at 22:52
  • correct, it is just an import from the "read-only" in to my EF db. It is not a straight across table to table, it is does need to do checks in my DB to decide on update or insert. This happens once a day. – Quantum Apr 21 '17 at 03:13
  • Id just use vanilla ADO.NET. This isnt really a situation where ef adds any value. – Sam Axe Apr 21 '17 at 05:18
  • fair enough, why I'm asking the question. I guess I wasn't sure if there was anything that would be missed out if didn't use the ef there. Wanted to keep things as flexible as possible. Did you want to put up your answer so I can accept it? – Quantum Apr 21 '17 at 14:58

1 Answers1

0

Entity Framework is not well suited to this scenario.

My recommendation would be to use plain vanilla ADO.NET.

Sam Axe
  • 33,313
  • 9
  • 55
  • 89
  • FOR NOTE: "It is important to note that .NET Core does not have DataSet, DataTable and related objects anymore. But we have all of core features like Connection, Command, Paramter, DataReader and other related objects." from http://stackoverflow.com/questions/38510740/is-ado-net-in-net-core-possible as to what is missing when by passing EF I guess – Quantum Apr 26 '17 at 20:34