ASP.NET 5, Entity Framework 7 & SQL Server 2014 - multiple dbContexts, SQL views

asp.net-core dbcontext entity-framework-core sql-server-2014 visual-studio-2015

Question

I have created a solution in Visual Studio 2015 consisting of a Web project (using the ASP.NET 5 Beta 6 Web Application Preview Template), and a Model project (using Entity Framework 7 Beta 6).

I got to the point where I could get data from a SQL Server database and display it on a page. Then, I wanted to try pulling data from two different databases, but ran into issues when trying to use more than one dbContext.

In Startup.cs, I add the EF service and add a dbContext, which works fine. But I'm not sure how to add more than one... the code below is how I attempted to add another, but the second AddDbContext just seems to overwrite the first.

I also ran into another issue somewhat related to this.. I was not able to get data from a SQL view. I've confirmed that the view does contain data, but when I write a WebAPI method to get data from that view, the response contains nothing. In past projects using EF6, I've had no problem modeling views and retrieving data from them.

To summarize my questions:

  1. How do you set up multiple dbContexts in an ASP.NET 5 web project using EF7?
  2. How do you model a SQL Server view in EF7?

Startup.cs:

public IConfiguration Configuration { get; set; }

public Startup(IHostingEnvironment env, IApplicationEnvironment appEnv)
{
    // Setup configuration sources.
    var builder = new ConfigurationBuilder(appEnv.ApplicationBasePath)
        .AddJsonFile("config.json")
        .AddEnvironmentVariables();
    Configuration = builder.Build();
}

// This method gets called by a runtime.
// Use this method to add services to the container
public void ConfigureServices(IServiceCollection services)
{
    // Register Entity Framework
    services.AddEntityFramework()
        .AddSqlServer()
        .AddDbContext<MyContextA>(options =>
            options.UseSqlServer(Configuration["Data:DefaultConnection:AConnectionString"])
        )
        .AddDbContext<MyContextB>(options =>
            options.UseSqlServer(Configuration["Data:DefaultConnection:BConnectionString"])
        );

    services.AddMvc();
}

config.json

{
  "Data": {
    "DefaultConnection": {
      "AConnectionString": "data source=xxxxxxx;initial catalog=xxxxxxxx;Uid=xxxxxxxxx;Pwd=xxxxxx;",
      "BConnectionString": "data source=xxxxxxx;initial catalog=xxxxxxxx;Uid=xxxxxxxxx;Pwd=xxxxxx;"
    }
  },
  "EntityFramework": {
    "MyContextA": {
      "ConnectionStringKey": "Data:DefaultConnection:AConnectionString"
    },
    "MyContextB": {
      "ConnectionStringKey": "Data:DefaultConnection:BConnectionString"
    }
  }
}

MyContextA.cs

public class MyContextA : DbContext
{
    public MyContextA (DbContextOptions options) : base(options) { }
    public DbSet<Product> Product { get; set; }
    public DbSet<Some_View> Some_View { get; set; }
}

MyContextB.cs

public class MyContextB : DbContext
{
    public MyContextB (DbContextOptions options) : base(options) { }
    public DbSet<Customer> Customer { get; set; }
}

Product.cs

public class Product
{
    [Key]
    public int ID { get; set; }
    public int Name { get; set; }
}

Some_View.cs

public class Some_View
{
    public int SomeField { get; set; }
    public int AnotherField { get; set; }
}

TestController.cs

[Route("api/[controller]")]
public class TestController : Controller
{
    private readonly MyContextA _dbContext;

    // Constructor
    public TestController(MyContextA dbContext)
    {
        _dbContext = dbContext;
    }

    // GET: api/values
    [HttpGet]
    public IEnumerable<Some_View> Get()
    {
        var data = _dbContext.Some_View;
        return data;
    }
}

I am able to get everything to work with one dbContext and using regular tables such as Product in the example above. But when I try to use the view I get no data, and when I use multiple contexts the second context overrides the first.

Accepted Answer

In case this helps anyone, this isn't an issue anymore with .NET Core and EF Core 1.0.0. The following is how I registered each dbcontext in my Web API Startup.cs.

Startup.cs

public void ConfigureServices(IServiceCollection services)
{
    // ...
    services.AddDbContext<Context1>(options => options.UseSqlServer(Configuration["ConnectionStrings:Connection1"]));
    services.AddDbContext<Context2>(options => options.UseSqlServer(Configuration["ConnectionStrings:Connection2"]));
    // ...
}

appsettings.DEVELOPMENT.json

{
  "ConnectionStrings": {
    "Connection1": "[connection string here]",
    "Connection2": "[connection string here]",
  }
}

Popular Answer

Try changing the constructors of your DbContexts to look like this:

public MyContextA (DbContextOptions<MyContextA> options) : base(options) { }
public MyContextB (DbContextOptions<MyContextB> options) : base(options) { }

Try calling ToList()

return data.ToList();

Each of these may be bugs you'll want to file on the EF and MVC repos.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why