locked
Mapping Entity Hierarchy to multiple views RRS feed

  • Question

  • Hi All...

    I have the following scenario:

    We have several different applications, using several different databases, and my manager wants me to provide an unified way to access all the data.

    So my first thought was to create an unified view that peruse all tha databases the application use and create an entity out of it.

    OK. That was the easy part.

    Now comes the hard part.

    This base and abstract entity is, as obvious as it may sound, the Person class.

    For each system there will be a concrete SystemPerson class that will be mapped to the specific table and life will be good.

    However there's the catch. As is the inherited properties will be read from the master view, which will cause a major blow performance-wise.

    Just to mention the database size we have something around 300,000 persons' records on file, so the view would read 300,000+ records each time to get the information for a single person.

    How can I solve this mess I put myself into?

    Regards,

    Paulo Santos
    http://pjondevelopment.50webs.com
    PJ on Development
    Thursday, December 11, 2008 6:17 PM

Answers

  • Ok, now I understand, thank you. The Entity Framework does allow mapping inherited properties to separate tables, but the designer does not support this scenario. So, if you must, you can edit the edmx file directly to express your more complex mappings, with the caveat that the designer will no longer be able to visualize your model's mappings in the mapping editor.

    That said, why do you have a master table if queries to subclasses do not need to access that table? When would you actually query that table? What is in this table that you need? Perhaps a different modeling approach would work better here...
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, December 12, 2008 6:26 PM

All replies

  • Can you explain a bit more what exactly is going on? The above explanation isn't quite clear - do you have multiple different database servers that you are linking? Or do you have one single database with multiple person tables, with a new table that you have created for all the common properties, the "master view"?

    What exactly is the mess here? Can you walk us through an end to end scenario here?
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, December 11, 2008 6:27 PM
  • Hi,

    Yes, I must admit that I couldn't quite express myself on the opening post, anyways, I'll try to explain better.

    We have several distinct systems, I'll name three for brevity sake: ERP, the Faculty system, and the Alumni system.

    Each system has its own server and database.

    I'm in charge to create a consolidated system that will read the "Persons" table from each system, merging it into a "master view".

    That part was easy, I liked all three servers (via Linked Servers) and created a view that lists the common properties between all of them.

    Then I used this "master view" (let's call vwPeople which returns roughly 300,000+ people in out databases) to create the entity Person, which I want to make it an abstract class.

    After that I want to create the entities Employee, Professor and Student, each accessing its own direct table, but the modeling tool doesn't allow me to map the inherited properties to the tables.

    So, when accessing an inherited property the vwPeople is called, blowing my performance to smiterings...

    What can I do?

    Regards,

    Paulo Santos
    http://pjondevelopment.50webs.com
    PJ on Development
    Thursday, December 11, 2008 10:39 PM
  • Ok, now I understand, thank you. The Entity Framework does allow mapping inherited properties to separate tables, but the designer does not support this scenario. So, if you must, you can edit the edmx file directly to express your more complex mappings, with the caveat that the designer will no longer be able to visualize your model's mappings in the mapping editor.

    That said, why do you have a master table if queries to subclasses do not need to access that table? When would you actually query that table? What is in this table that you need? Perhaps a different modeling approach would work better here...
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Friday, December 12, 2008 6:26 PM