none
Does the Entity Framework Designer optimize the underlying table design when creating the DB?

    Question

  • Hello,

    I am new to the Entity Framework, and I'm trying to learn about the best practices for entity design.

    I am designing a database in the Entity Framework Designer, and I really want to be able to use the feature "Generate Database from model..."

    1.  Should I create entities with normalization in mind, or does the designer handle that automatically?  In other words, should my entities reflect what I presume the normalized table structure would look like?  

    1b.  Will the entity designer ever create more than one table per entity in SQL?

    2.  When I add an association between entities (one to many, for example) - do I need to create Properties for the foreign keys, or is that handled automatically?

    3.  And finally, is the Entity Framework compatible with SQL Azure?

    Thanks!

    Matt

    Wednesday, August 17, 2011 10:46 PM

Answers

  • Hi Matt:

    1. The designer will always create one table for an entity type. This is the 'table-per-type' mapping strategy. In this way, you can model your database tables in entities.

    1b. The default Generate Database from Model experience (what we call "Model First") will never create more than one table per entity.

    2. This is handled automatically whether you choose to expose foreign keys in the conceptual model or not.

    3. Yes, the generated SQL script should be compatible with Azure.

    As an aside, the Model First feature is very extensible; it is built on top of Windows Workflow and T4 templates. If you need more powerful database generation capabilities, I encourage you to take a look at the power pack: http://visualstudiogallery.msdn.microsoft.com/df3541c3-d833-4b65-b942-989e7ec74c87 which is built on top of this extensibility and can generate migration T-SQL (ALTER statements) as well as synchronizing a database project which users have found more useful if they want to model things in their database like triggers, indexes, etc. This power pack also has functionality for table-per-hierarchy mappings, where an entire inheritance hierarchy of entity types can represent a single table.

    Finally, we have an ongoing effort to integrate entity modeling in a much more fluid way with the new SSDT/"Juneau" database projects. We'll release this soon; here's a video of it from TechEd: http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/DEV314

    -adi


    Adi Unnithan | Software Design Engineer | SQL Server Developer Tools | Please mark the post as answered if it sufficiently answers your question


    Thursday, August 18, 2011 7:16 AM
  • 1) Yes, you need to think about db normalization when you create your data model. If you want an optimal data model in the database, it is generally a good idea to model your baseline database schema first and then generate an EF model in a db-first scenario. After that you can tweak and update it either in the EF designer or db-side (or both).

    1b) If you want conceptual model entities that combine several storage model entities (tables) then you need to handle that in the MSL (mapping) layer. If you want to use inheritance, EF supports three inheritance models (TPT, TPH, and TPC). Out-of-the-box the designer will generate TPT SSDL but with the help of third party tools you can use all three inheritance models in the EF designer. See this blog article for more info on the inheritance models supported, and my tool [that allow you to use all three within a single EF model]: http://huagati.blogspot.com/2010/10/mixing-inheritance-strategies-in-entity.html

    2) There is a setting in the designer that controls if you want scalar properties for FK members or not. If that is enabled, the scalar properties will be created in the conceptual model. If disabled, they will only exist in the storage layer.

    3) Yes.

     


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Visual Studio add-in with loads of new features for the Entity Framework and Linq-to-SQL designers
     huagati.com/L2SProfiler - Runtime SQL query profiler for Linq-to-SQL and Entity Framework v4
    Friday, August 19, 2011 2:02 AM

All replies

  • Hi Matt:

    1. The designer will always create one table for an entity type. This is the 'table-per-type' mapping strategy. In this way, you can model your database tables in entities.

    1b. The default Generate Database from Model experience (what we call "Model First") will never create more than one table per entity.

    2. This is handled automatically whether you choose to expose foreign keys in the conceptual model or not.

    3. Yes, the generated SQL script should be compatible with Azure.

    As an aside, the Model First feature is very extensible; it is built on top of Windows Workflow and T4 templates. If you need more powerful database generation capabilities, I encourage you to take a look at the power pack: http://visualstudiogallery.msdn.microsoft.com/df3541c3-d833-4b65-b942-989e7ec74c87 which is built on top of this extensibility and can generate migration T-SQL (ALTER statements) as well as synchronizing a database project which users have found more useful if they want to model things in their database like triggers, indexes, etc. This power pack also has functionality for table-per-hierarchy mappings, where an entire inheritance hierarchy of entity types can represent a single table.

    Finally, we have an ongoing effort to integrate entity modeling in a much more fluid way with the new SSDT/"Juneau" database projects. We'll release this soon; here's a video of it from TechEd: http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/DEV314

    -adi


    Adi Unnithan | Software Design Engineer | SQL Server Developer Tools | Please mark the post as answered if it sufficiently answers your question


    Thursday, August 18, 2011 7:16 AM
  • Hi Matt,

    Welcome!

    When you use  "Generate Database from model", the DDL will be generated according to your models and it is easy to understand the relationship between models and datatables. @Adi's reply is helpful.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, August 18, 2011 8:42 AM
  • 1) Yes, you need to think about db normalization when you create your data model. If you want an optimal data model in the database, it is generally a good idea to model your baseline database schema first and then generate an EF model in a db-first scenario. After that you can tweak and update it either in the EF designer or db-side (or both).

    1b) If you want conceptual model entities that combine several storage model entities (tables) then you need to handle that in the MSL (mapping) layer. If you want to use inheritance, EF supports three inheritance models (TPT, TPH, and TPC). Out-of-the-box the designer will generate TPT SSDL but with the help of third party tools you can use all three inheritance models in the EF designer. See this blog article for more info on the inheritance models supported, and my tool [that allow you to use all three within a single EF model]: http://huagati.blogspot.com/2010/10/mixing-inheritance-strategies-in-entity.html

    2) There is a setting in the designer that controls if you want scalar properties for FK members or not. If that is enabled, the scalar properties will be created in the conceptual model. If disabled, they will only exist in the storage layer.

    3) Yes.

     


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Visual Studio add-in with loads of new features for the Entity Framework and Linq-to-SQL designers
     huagati.com/L2SProfiler - Runtime SQL query profiler for Linq-to-SQL and Entity Framework v4
    Friday, August 19, 2011 2:02 AM