locked
Entity Framework and Dynamic Tables RRS feed

  • Question

  • Hi,

    I'm new to EF and am currently reviewing it to see if I can use if for my database. The problem with my db is that some tables are created dynamically, through the user's interaction with the client UI. These dynamically created tables have some fixed columns that are always there, plus new columns that are added through the user's interaction. The user's interaction will dictate the name of the column, and the data type too.

    Here's an illustration. I may have tables called Data_1, Data_2, Data_3 and so forth. All of the tables have at least 10 columns that are the same throughout. BUT, each table can then have additional columns that can vary throughout. So Data_1 may have 11 columns, Data_2 could have 20 columns and Data_3 may have 16 columns. If I know that 10 columns are the same, can I have an Entity object on the client?

    I didn't write the db and can't change it so any solution has to be provided at the client end. I know ultimately I can just just straight forward ADO.NET and just bring back a datatable etc. But a lot of the tables are constant in terms of columns and allow using the EF to produce Entity objects at the client and ideally I wanted to have an entity object for all tables.

    I was wondering if it's possible to have a entity object that has a dictionary. Here I could hold the dynamic column names and their values? Or any other approaches?

    Any help greatly appreciated! Thanks..

    Friday, March 23, 2012 4:44 PM

Answers

  • Hi rockshire;

    Entity Framework and as well as Linq to SQL are both a strongly typed database query language. In order to map entities / classes in your application to the table in a database they need to have the same schema you can't map a class in your application to part of a table that is just not supported. The only option I can see is using ADO.Net functions to retrieve the tables and and finding what columns are contained in the resulting DataTable object.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Allen_MSDN Tuesday, March 27, 2012 2:21 AM
    Friday, March 23, 2012 5:44 PM

All replies

  • Hi rockshire;

    Entity Framework and as well as Linq to SQL are both a strongly typed database query language. In order to map entities / classes in your application to the table in a database they need to have the same schema you can't map a class in your application to part of a table that is just not supported. The only option I can see is using ADO.Net functions to retrieve the tables and and finding what columns are contained in the resulting DataTable object.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".

    • Marked as answer by Allen_MSDN Tuesday, March 27, 2012 2:21 AM
    Friday, March 23, 2012 5:44 PM
  • Hi rockshire,

    Did you find a workaround for the above problem? I am also new to EF and also in the same situation as you are.

    Please help. thanks

    Monday, August 5, 2013 10:09 AM