How to create a LINQ To SQL datacontext for large DB using SQLMetal when it generates a 12MB DataContext.cs file?

Question

My problem is this:

We have a very large Legacy DB with many SPROCs, Views & Tables.

The Designer is a "NO GO" b/c of the size.

I've configured SQL Metal to build the data context, but the resulting code file is so big (12MB) visual studio 2008 will not open it.

If SQLMetal would generate a new file for each class type (Table, View, SPROC_Result), I would be okay.

But as it works currently, I'm hosed.

Ideas??

Answer 1

You could do this:

1. Run SQLMetal.exe to create the .dbml file.
2. Create a little script or application to parse the .dbml file (which is XML) into 3 components: tables, stored procedures and views
3. Run SQLMetal.exe on each of the new .dbml files to create 3 separate .cs files.

In your filter phase (step 2) you could also create other arbitrary divisions of your .dbml file based on other requirements. Perhaps filter out some unnecessary tables or views. Or break the tables into logical components with separate DataContexts.

Answer 2

Just determined that Resharper was crashing VisualStudio on the very large DataContext.cs file. But Keltex's suggestion is a very good one.

Answer 3

Turns out there are a number of ways to do this:

1) Build your own (1 week for me : DBML God) http://southernbits.com/index.php/2009/05/16/dbml-hell-legacy-db-sqlmetal-is-painful-when-migrating-from-sql-designer/

2) Use an existing tool (this may work but I have NOT fully explored yet) http://www.plinqo.com/

3) Be wary of differences between SQLMetal & SQL Designer. See blog: http://southernbits.com/index.php/2009/05/25/unfortunate-differences-between-linq-sqlmetal-and-sqldesigner/

In the end, this took me 1 week of exploration (where I failed to discover plinqo). Simple solutions did not present themselves. I took 1.5 weeks to build a tool (DBMLGod) and another 1 week to refactor / compile code given differences between SQLMetal and SQLDesigner). Testing time is TBD...

If you do NOT have a large code base that was built using SQL Designer, you probably won't have as many issues.

I think all large, unkempt databases (use lots of complex sprocs & have lots of objects) will be painful.

The SQLGeneration time for our databases were 15 minutes on QuadCore 4GB development boxes (ouch).