none
Custom DataContext Generator RRS feed

  • Question

  • The application I am working on (a legacy app that we're rewriting part of in .NET) allows power users to add columns to the database.  We are using LINQ to SQL for the data access layer, but in order to support these dynamic fields, we need to dynamically generate the data entity classes and data context.

     

    We would also like our class and property names to be Pascal case, and the database uses uppercase and underscores.  We have our own data dictionary that supports additional customization and business rules (e.g. power users can define ranges for numeric database columns), and we're considering adding a "friendly" field name and table name that we would code against.  Currently, we're importing database tables into the LINQ to SQL designer and renaming the properties so the classes get generated the way we want.  But this is tedious, especially if new columns are added; i.e. we can't just delete the table in the designer and reimport from the database since all our renaming work is lost.

     

    So given this, we are considering writing our own data entity class and data context generator which would be used at design time for our developers, and runtime for the custom fields.

     

    The question is, where do we start?  I am fairly comfortable with the Db.designer.cs code that is generated by the designer from the Db.dbml file, but there are quite a few things to take care of (e.g. property change notification, pluralization, associations, etc.) and we'd rather not reinvent the wheel for all of this.  Is it possible to leverage the code in SqlMetal or the LINQ to SQL designer to do the generation?  If not, I guess we need to roll up our sleeves and use the CodeDom to generate the classes per the specs, but I wanted to be sure before we go down this path.

     

    Thanks for any guidance.

     

    -Larry

     

    Thursday, November 20, 2008 7:55 PM

Answers

  •  Lawrence Parker wrote:

    The power user customization has been a big part of this product's history, so that's what we have as a given.

     

    I'm looking for a way to generate entity classes mainly because we will be renaming the properties of classes corresponding to the database columns.  I need an automated way to do this -- we can't just import the database tables and rename in the LINQ to SQL designer.  And the data context will need to be generated because the table name properties will be different as well.

     

    For example, say we have a database table called INV_CATEGORY, with columns called ID and NAME.  SqlMetal (or the LINQ to SQL designer) will give us a data context with an INV_CATEGORies property, and an INV_CATEGORY type with ID and NAME properties.  That makes for pretty ugly code.  We would prefer to code against an InventoryCategories table property, with a type called InventoryCategory having properties called CategoryId and Name.  We will maintain the mapping of renamed tables and columns in our data dictionary application, and we'd like to generate a data context and entity classes ourselves based on this.

     

    But what's the best way to generate all of this code?  I'm hoping to leverage SqlMetal and/or the LINQ to SQL designer code, but we can hand roll everything ourselves if need be.  I just want to ensure that's the best route before we start writing code to do this.

     

    I'm still not 100% sure I understand the requirements. The designer and SQLMetal is only available design-time. If you need renaming etc at design-time, I have an add-in that can do that for you: www.huagati.com/dbmltools

     

    However, if you need runtime-generated entity classes then my add-in won't be of any use to you. (Although some internal classes from the add-in maybe would be useful). Instead, if you need runtime-generated classes my advice would be to use CodeDom to generate classes that inherit from your L2S-generated base classes and then adds members for the user-defined extra columns.

     

    The datacontext is unaware of tables - it retrieves this information from the class attributes so you can generate new classes with CodeDom and use them with an existing DataContext at runtime.

    Friday, November 21, 2008 3:25 PM
    Answerer

All replies

  • You don't need to generate a new datacontext - it is enough to generate new entity classes with the mapping attributes. You can use that with an existing datacontext. If all they do is add new columns to your tables, your generated classes can even inherit the existing entity classes.

     

    That said, is it really necessary for the "power users" to modify tables? You could always create a separate table structure that allow "custom field" / "user defined fields" to be defined per entity and then loaded separately.

    Friday, November 21, 2008 12:01 PM
    Answerer
  • The power user customization has been a big part of this product's history, so that's what we have as a given.

     

    I'm looking for a way to generate entity classes mainly because we will be renaming the properties of classes corresponding to the database columns.  I need an automated way to do this -- we can't just import the database tables and rename in the LINQ to SQL designer.  And the data context will need to be generated because the table name properties will be different as well.

     

    For example, say we have a database table called INV_CATEGORY, with columns called ID and NAME.  SqlMetal (or the LINQ to SQL designer) will give us a data context with an INV_CATEGORies property, and an INV_CATEGORY type with ID and NAME properties.  That makes for pretty ugly code.  We would prefer to code against an InventoryCategories table property, with a type called InventoryCategory having properties called CategoryId and Name.  We will maintain the mapping of renamed tables and columns in our data dictionary application, and we'd like to generate a data context and entity classes ourselves based on this.

     

    But what's the best way to generate all of this code?  I'm hoping to leverage SqlMetal and/or the LINQ to SQL designer code, but we can hand roll everything ourselves if need be.  I just want to ensure that's the best route before we start writing code to do this.

     

    -Larry

     

    Friday, November 21, 2008 12:57 PM
  •  Lawrence Parker wrote:

    The power user customization has been a big part of this product's history, so that's what we have as a given.

     

    I'm looking for a way to generate entity classes mainly because we will be renaming the properties of classes corresponding to the database columns.  I need an automated way to do this -- we can't just import the database tables and rename in the LINQ to SQL designer.  And the data context will need to be generated because the table name properties will be different as well.

     

    For example, say we have a database table called INV_CATEGORY, with columns called ID and NAME.  SqlMetal (or the LINQ to SQL designer) will give us a data context with an INV_CATEGORies property, and an INV_CATEGORY type with ID and NAME properties.  That makes for pretty ugly code.  We would prefer to code against an InventoryCategories table property, with a type called InventoryCategory having properties called CategoryId and Name.  We will maintain the mapping of renamed tables and columns in our data dictionary application, and we'd like to generate a data context and entity classes ourselves based on this.

     

    But what's the best way to generate all of this code?  I'm hoping to leverage SqlMetal and/or the LINQ to SQL designer code, but we can hand roll everything ourselves if need be.  I just want to ensure that's the best route before we start writing code to do this.

     

    I'm still not 100% sure I understand the requirements. The designer and SQLMetal is only available design-time. If you need renaming etc at design-time, I have an add-in that can do that for you: www.huagati.com/dbmltools

     

    However, if you need runtime-generated entity classes then my add-in won't be of any use to you. (Although some internal classes from the add-in maybe would be useful). Instead, if you need runtime-generated classes my advice would be to use CodeDom to generate classes that inherit from your L2S-generated base classes and then adds members for the user-defined extra columns.

     

    The datacontext is unaware of tables - it retrieves this information from the class attributes so you can generate new classes with CodeDom and use them with an existing DataContext at runtime.

    Friday, November 21, 2008 3:25 PM
    Answerer
  • We actually need design-time generation for the renamed properties, and also runtime generation for the custom columns.  But we don't need the designer diagram -- just the generated designer.cs file.

     

    OK, I will proceed with the CodeDom approach.  I found a project on CodePlex (http://www.codeplex.com/ULinqGen) that has some good examples.

     

    Thanks for the guidance!

     

    -Larry

     

     

    Friday, November 21, 2008 3:47 PM