none
Crud and query for a Generic Database Schema RRS feed

  • Question

  •  

    I wonder if anyone would have any ideas on implementing the following requirement.

     

    I have a requirement  to map entities to a generic database schema which can be seen below. A consumer must be able to access, query and update the virtual schema, an example of this schema can be seen in the second schema below. (Consumer Properties would need to strongly typed )

     

    I have investigated the entity framework to perform this mapping but its is looking very doubtful this can be accomplished. I wonder if it would be better to use linq to sql and conversion layer maybe in the form of a query provider.

     

    Thanks in a advance and any help would be greatly appreciated.

     

    Regards,

     

    Paul

     

     

    Database schema

    ______________       ___________________

    |EntityDef   |       |EntityInstanceId |

    |------------|       |-----------------|

    |EntityDefId |<------|EntityInstanceId |

    |Name        |       |EntityDefId      |

    |____________|       |_________________|

          ^    ^                  ^    ^

          |    |                  |    |

     _____|____|__________      __|____|__________________

    |EntityPropertyDefId |     |EntityPropertyInstance   |

    |--------------------|     |-------------------------|

    |EntityPropertyDefId |<----|EntityPropertyInstanceId |

    |EntityDefId         |     |EntityInstanceId         |

    |Name                |     |EntityPropertyDefId      |

    |ValueEntityDefId    |     |Value                    |

    |____________________|     |ValueEntityInstanceId    |

                               |DataType                 |

                               |_________________________|

     

     

    Virtual (consumer) schema

               _______________

              |Derivative     |

              |---------------|

              |DerivativeId   |

              |ValidFrom(date)|

              |NameplateId    |

              |SeriesId       |

              |_______________|

               |            |

               |            |           

     _________\ /_        _\ /______________

    |Nameplate    |      |Series            |

    |--------     |      |------------------|

    |NamePlateId  |      |SeriesId          |

    |Name (string)|      |Decription(string)|

    |Price(double)|      |Name(string)      |

    |             |      |Standard(string)  |

    |_____________|      |__________________|

     

    Friday, April 11, 2008 10:16 AM

Answers

  • LINQ to SQL should be able to provide the database schema mapping which you could then combine into the virtual schema either by projecting the results from one to the other or if your virtual schema changes dynamically then you could build this using the code dom api and subsequently load the generated assembly into memory.

     

    Hope that helps,

     

    [)amien

     

    Wednesday, May 28, 2008 9:02 PM
    Moderator