3

Our database has about 500 tables we'd like to use in our EF model. Of those I'd be happy to start with 50 or fewer just to get our feet wet after working in plain ADO.net for years.

The problem is, our SQL server contains many thousands of other tables that exist in our database that have been created through the years and many that are dynamically generated. Believe it or not:

select count(*) from INFORMATION_SCHEMA.TABLES
73261

So that's a lot of tables. I have found that pretty much every tool I've tried to design, build or template EF models or entities either hangs or does not return a list of tables. Even SQL Server Object Explorer in VS2012 won't list the tables and instead shows the Tables folder with a little "x" over the icon. So I can't even select a subset of tables.

What options do I have for using EF? Is there a template where I can explicitly define the tables that I want to use entities for? Even with 50 tables, I don't want to hand code each one in an empty EDMX.

powlette
  • 1,800
  • 20
  • 41
  • 1
    Can you create smaller databases with subsets of tables just for design purposes? But on the other hand: isn't this a time to reconsider the data "model"? This number of tables is really outrageous. It must be possible to merge vast numbers of tables in a normalized model (where table names promote to columns). – Gert Arnold Jan 15 '13 at 12:31
  • I'm curious: does SQL Server hold up to such a large amount of schema objects? Did you notice problems? – usr Jan 15 '13 at 13:20
  • @usr: sql server supports up to 2 billion tables I believe. We have noticed no slow down or anything. An added benefit is that most of these table correspond to a particular product and each table's schema is generated dynamically. When the product is done, the entire table can be deleted/archived. Also locks in one product's table don't impact any other. This design has served us well for 12 years now. – powlette Jan 15 '13 at 15:49

2 Answers2

2

Using a Database / Code First approach and avoiding connecting Visual Studio to the database at all (i.e. don't create an edmx, or connect with server explorer) would allow you to do this easily. It does not give you any of the Model First advantages, but I think it sounds like your project would be better served with a Database / Code First approach anyway as:

  1. You have an existing Model, and are not looking to push changes from your EDMX to the DB
  2. You are looking to implement this on a subset of your database

This link has a good summation ( Code-first vs Model/Database-first ) with the caveat that in you case a Database/Code First approach does not have you pushing changes from code to the Database, so the last two bullets under code first apply less, and yours is a Database/Code First hybrid.

With 70k tables I think that any GUI is going to be tricky. When I am saying Database / Code First, I am trying to convey that you are not using the code to create / define and update your Database. Someone may be able to answer this more succinctly / accurately?

Community
  • 1
  • 1
Matthew
  • 9,851
  • 4
  • 46
  • 77
  • Is there a t4 template for this where I can list the tables I care about and have it generate the entity classes for me? The ones I've found are so huge and complex and use SMO so they basically hang for many minutes before scripting one simple table. – powlette Jan 15 '13 at 22:47
  • I would have to leave that for someone else to hopefully comment as I have never tried it and have never dealt with using an ORM / modelling tool on a DB with so many tables. (Although I have come across a few DB's with similar number of auto-gen tables). I would add that hand-creating the Entity POCO's for 50+ objects, while mind-numbing, would not really be all that much work. If you eventually aim to do thousands and want DB changes to auto-propagate then you are definitely going to need to address some process (as your are requesting) for scripting the object generation. – Matthew Jan 15 '13 at 23:27
0

I now this is an old question. But for those who land here on a google search. The only tool I have found that actually works with thousands of tables is The Sharp Factory.

It is an ORM. Pretty simple to use. So if you are looking for an ORM that can work with a large number of tables and does not require you to write "POCOS" or "Mappings" or SQL then this is the tool.

You can find it here: The Sharp Factory

Jonathan Alfaro
  • 4,013
  • 3
  • 29
  • 32