none
Defining Queries RRS feed

  • Question

  • I'm curious about the DefiningQuery going in the SSDL. The reason is because it seems to me that the store schema would be dependent on the store. It goes back to a question I have been asked a lot which is "who controls the schemas". I would have thought that the SSDL should be treated almost like a codegen. It is generated from the db schema and I have this idea that eventually we'll be able to refresh the SSDL when the db gets modified. Therefore if you start manually adding stuff into there, you won't have this opportunity.

     

    Is it not possible to define those in the CSDL?

     

    I don't imagine for a minute that you haven't had these discussions but would like to know what the reasoning was behind putting it into the ssdl.

     

    It makes sense to me that in the SSDL it lets the entities and the mapping layer interact with this query in the same way as any other db object. Putting it in the mapping or conceptual layer could disrupt the functionality between the other layers.  But I don't feel comfy about manually altering the SSDL. Can it, perhaps, be extended instead?

     

    Thanks.

    Saturday, July 7, 2007 1:55 AM

Answers

  • Actually, we can add mapping in the MSL through EntitySQL Views, but they key differentiator that makes the DefiningQuery feature so powerful is that it's done in terms of a native store query.  So whatever query you can execute in the store, you can represent as a (read-only) table in the SSDL, and the rest of the entity framework treats it just like any other table/view.  For example, with DefiningQuery, you can map multiple entities to the same table outside of a type hierarchy, or a single entity to multiple rows within a single table (I did a demo at TechEd where I mapped an "Activity" Entity to a Sharepoint schema in which the properties of the Activity were actually mapped to different rows within a single "universal" table according to a row ordinal).  The list goes on... In fact, every time I think I've found a mapping scenario that we don't support in Entity Framework 1.0, I find a way to do it using DefiningQuery...

     

    So yes, you should use MSL to do mapping between the store schema and the conceptual schema where possible in order to leverage the full power of client views for querying and updating, but DefiningQuery provides an ultimate escape hatch for cases where the mapping is too complex to define in MSL, even through an EntitySQL view.

    Monday, July 9, 2007 11:44 PM
    Moderator
  • This is Tim's response:

     

    The SSDL is a logical representation of what you want us to believe the shape of the store looks like. This indirection allows us to do a number of “tricks” in complex mapping scenarios.

    For example, consider that there is a view that you want that requires native SQL to express and you do not have rights to create a view in the underlying store. You can define an entityset and an entitytype (where the entitytype describes the desired shape) and then use the defining query to express the view external to the store. A practical example of this is how our tools infrastructure uses the defining query to allow database vendors to describe entitysets of their database catalog in terms of a common logical model (the information schema views). You could also see a world where the way that we were to integrate with frameworks that do external logical shaping of the underlying store (for example DSV’s in Reporting Services) is to have an SSDL that is isomorphic with their logical model by virtue of the defining queries.

    Monday, July 9, 2007 3:46 PM
  • EDM, and SSDL as part of it, is a specification language. It’s agnostic to the schema/model generation workflow – model-to-store, or store-to-model. It has to have the capability of specifying useful store-specific information either way.

     

    We provide the EdmGen tool for convenience but third parties may write their own tools. Such tools may allow tables to be shaped through native SQL views in SSDL during model generation.

     

    Lastly, entity-level programming is a newer paradigm compared to database programming. There are plenty of database that we try to elevate to entity-level. That’s why currently the workflow mainly is to generate a model from an existing database schema. In future, for newly incepted projects that probably won’t be the case. They may start with a model and use a tool to generate a persistence schema for that model in a specific store.

     

    Zlatko Michailov

    Program Manager, Data Programmability

    Microsoft Corp.

    Monday, July 9, 2007 5:30 PM
    Moderator

All replies

  • This is Tim's response:

     

    The SSDL is a logical representation of what you want us to believe the shape of the store looks like. This indirection allows us to do a number of “tricks” in complex mapping scenarios.

    For example, consider that there is a view that you want that requires native SQL to express and you do not have rights to create a view in the underlying store. You can define an entityset and an entitytype (where the entitytype describes the desired shape) and then use the defining query to express the view external to the store. A practical example of this is how our tools infrastructure uses the defining query to allow database vendors to describe entitysets of their database catalog in terms of a common logical model (the information schema views). You could also see a world where the way that we were to integrate with frameworks that do external logical shaping of the underlying store (for example DSV’s in Reporting Services) is to have an SSDL that is isomorphic with their logical model by virtue of the defining queries.

    Monday, July 9, 2007 3:46 PM
  • EDM, and SSDL as part of it, is a specification language. It’s agnostic to the schema/model generation workflow – model-to-store, or store-to-model. It has to have the capability of specifying useful store-specific information either way.

     

    We provide the EdmGen tool for convenience but third parties may write their own tools. Such tools may allow tables to be shaped through native SQL views in SSDL during model generation.

     

    Lastly, entity-level programming is a newer paradigm compared to database programming. There are plenty of database that we try to elevate to entity-level. That’s why currently the workflow mainly is to generate a model from an existing database schema. In future, for newly incepted projects that probably won’t be the case. They may start with a model and use a tool to generate a persistence schema for that model in a specific store.

     

    Zlatko Michailov

    Program Manager, Data Programmability

    Microsoft Corp.

    Monday, July 9, 2007 5:30 PM
    Moderator
  • Actually, we can add mapping in the MSL through EntitySQL Views, but they key differentiator that makes the DefiningQuery feature so powerful is that it's done in terms of a native store query.  So whatever query you can execute in the store, you can represent as a (read-only) table in the SSDL, and the rest of the entity framework treats it just like any other table/view.  For example, with DefiningQuery, you can map multiple entities to the same table outside of a type hierarchy, or a single entity to multiple rows within a single table (I did a demo at TechEd where I mapped an "Activity" Entity to a Sharepoint schema in which the properties of the Activity were actually mapped to different rows within a single "universal" table according to a row ordinal).  The list goes on... In fact, every time I think I've found a mapping scenario that we don't support in Entity Framework 1.0, I find a way to do it using DefiningQuery...

     

    So yes, you should use MSL to do mapping between the store schema and the conceptual schema where possible in order to leverage the full power of client views for querying and updating, but DefiningQuery provides an ultimate escape hatch for cases where the mapping is too complex to define in MSL, even through an EntitySQL view.

    Monday, July 9, 2007 11:44 PM
    Moderator