none
LINQ to SQL: best practices when having numerous(150-200) tables RRS feed

  • Question

  • Hi,

    I am relatively new to the LINQ to SQL. The problem I am facing is how to take advantage of LINQ, its database Designer, automatic code generation and very convenient cross-objects referencing(accessing data of the referenced tables as data members of an initialized LINQ-mapped object) when having around 150-200 distinct tables. Typical solution would be to split the tables among several databases / schemas.But cross-database references(FKs, joins) are impossible, AFAIU.

    Is there any way to get more clarity in the DBML diagram that combines 150-200 tables - by implementing some sort of separation / "grouping" conceptually similar tables?


    Thank you!
    Tuesday, October 21, 2008 10:50 AM

Answers

  • In theory, you could use two digrams, and address the cross-diagram joins as follows:

     

    (a) temporarily make the joins in one of the diagrams (I.e. introduce a table from one of the diagrams into the other, so it makes the joins there)

    (b) Find the generated code that implements the properties that reperesent the joins

    (c) Copy that code into partial classes of your own (so it belongs to the same classes as what you copied it out of, but it is in "your" half of the partial class)

    (d) remove your temporary addition to the diagram.

     

    Now, you should have code that is functionally identical to putting it all one one diagram, but you have split it into two.

     

    Three caveats:

    (1) you'll need to be real careful about what happens in the generated constructors.  You'll need to find the stuff that is put there to do the relationships in question (the ones that will cross diagrams) and move it into the OnCreated() method in the partial class.

    (2) you'll need a good understanding of exactly what generated code you need to copy.  I'd suggest that you make a copy of the generated code before step (a) above; and then use a file comparsion tool to diff it against the generated code after step (a). That will make sure that you can see everything that gets generated.

    (3) I haven't tried this myself, at least, not yet.

     

    John

     

    Tuesday, October 21, 2008 10:12 PM

All replies

  • In theory, you could use two digrams, and address the cross-diagram joins as follows:

     

    (a) temporarily make the joins in one of the diagrams (I.e. introduce a table from one of the diagrams into the other, so it makes the joins there)

    (b) Find the generated code that implements the properties that reperesent the joins

    (c) Copy that code into partial classes of your own (so it belongs to the same classes as what you copied it out of, but it is in "your" half of the partial class)

    (d) remove your temporary addition to the diagram.

     

    Now, you should have code that is functionally identical to putting it all one one diagram, but you have split it into two.

     

    Three caveats:

    (1) you'll need to be real careful about what happens in the generated constructors.  You'll need to find the stuff that is put there to do the relationships in question (the ones that will cross diagrams) and move it into the OnCreated() method in the partial class.

    (2) you'll need a good understanding of exactly what generated code you need to copy.  I'd suggest that you make a copy of the generated code before step (a) above; and then use a file comparsion tool to diff it against the generated code after step (a). That will make sure that you can see everything that gets generated.

    (3) I haven't tried this myself, at least, not yet.

     

    John

     

    Tuesday, October 21, 2008 10:12 PM
  • Thank you!
    I have tried that, and it did work!

    Just as you said, the constructors' logic had to be moved into another partial OnCreated() method. There should be the only  DataContext that should define referenced "System.Data.Linq.Table"-s.
    Surprisingly, having associations defined, like:
        [Association(Name = "User_Address", Storage = "_Addresses", OtherKey = "UserId")]
    makes VisualStudio 2008 neither angry, nor changes anything. So, having associations defined that way is not necessary.

    However, unless some automation script could be written, that seems quite an overhead to me, especially, if DB model changes from time to time. I managed to make it work for 2-3 tables, but in case of dozens of tables that could be quite a headache..
    Thursday, October 23, 2008 9:20 AM
  • Hi Jenny,

     

    Glad it worked.  I've been meaning to try it myself for a while now.

     

    I didn't quite understand what you meant by 'There should be the only  DataContext that should define referenced "System.Data.Linq.Table"-s.'  Can you elaborate?

     

    Also, what was the issue with Association attributes being unecessary?  I would not imagine that you could actually remove anything - i.e. I tihnk you will need everything that the designer generates.  (Unless I get round to finishing this, which would let you dispense with all the designer-generated code ;-)

    I agree that it would be a headache to do for lots of tables.  I guess my original suggestion was dependent on the assumption that you could partion up your data model in such a way that there were two or three main areas, with relatively few links between areas.  It would only be those cross-area links that would require manual attention.

     

    John

     

    Friday, October 24, 2008 9:14 PM

  • > I didn't quite understand what you meant by
    > 'There should be the only  DataContext that should define
    > referenced "System.Data.Linq.Table"-s.'  Can you elaborate?

    Well, that might be just specific to my case. My goal was to have just 1 DataContext object (to perform all operations on it like "CreateDatabase()" / access all mapped tables via classes), for simplicity reasons: not to think which context to access when referencing certain table. So, I have created a partial implementation of just 1 of the "X.designer.cs" DataContexts, and defined the "reference" to the tables in another "cluster" (DataContext):


            public System.Data.Linq.Table<Address> Addresses
            {
                get
                {
                    return this.GetTable<Address>();
                }
            }

    >Also, what was the issue with Association attributes being unecessary?
    No idea. I did not copy them first and it just started to work. Maybe they are needed just for visualizing purposes, which is not the case there are separate "clusters".

    > with relatively few links between areas
    yes, doable, but it is not something you want to do twice.. Smile
    I would rather write some script to half-automate it.
    Monday, October 27, 2008 9:08 AM