locked
dbcontext per schema / multiple dbcontext RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I have been using EF for one software project and liked it. then I decided to try for another which will be different scenario and I could not find a way to make use of EF.

    In a software I am planning to work on, i need to store data for many different projects. All projects are independent from each other. Each project will have exactly same database schema (same group of tables with exactly same table structures). All this data will be stored in one SQL server. 

    One approach would be to create one database and one dbcontext for each project. Then EF will work easily and nicely. However, SQL server only allows up to 32,767 databases. I hope to be able to support more projects because this software is expected to be used for years and years on many different project and different clients. So one database per project will quickly reach SQL database limit.  However, older projects can be archived and restored when needed. So this approach is still possible. I am not sure if there is any performance issue with 32767 live database though. 

    The second approach is to create one schema per project. Schema is just a regular object in SQL database so I can create as many as I want. However I did not find a way that EF can create dbContext per schema. Even EF can work with schemas, it will be incompatible with Oracle. So this approach seems not work either.

    Anybody can give me some advice?

    Thanks in advance.


    Wednesday, November 5, 2014 5:03 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hello curiousj,

    >>One approach would be to create one database and one dbcontext for each project

    For first approach, my suggestion is that you might need to increase your database server number and use the Cluster Service so that you could create 32,767*N(your server number) databases. However, I think this is not very relevant with Entity Framework.

    >> I am not sure if there is any performance issue with 32767 live database though.

    You could ask it to the SQL Server forum so that SQL Server experts would explain it for you.

    >> However I did not find a way that EF can create dbContext per schema.

    Entity Framework would pull all schema to the select dialog. If you use the second approach, you need to choose the needed one by hand:

    Regards,

    Fred.

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Fred Bao Friday, November 14, 2014 9:24 AM
    Wednesday, November 5, 2014 6:49 AM