Mapping POCO Class to Dynamically Created Tables

I have a script running every hour that stores log data in a logging database. Every month a new table is created, and the logging information from this month is stored in that table.

Every table that is created is identical and match a simple POCO class

class IISLog
{
  SystemRef,
  date,
  s-sitename,
  //etc
 }

I've only found a single way to access these tables using a code-first approach:

 var result = this.Database.SqlQuery<WebLog>("select * from " + table + "_" + month);

However, it appears that I lose the lazy loading in the process since SqlQuery returns an IEnumerable of the type.

Is th开发者_运维问答ere any way to let lazy loading prevail as well as allow the data context to track the elements? (Main priority on the first point).

---

You will lose everything by using SqlQuery - that is direct SQL execution without any EF features involved. It will just materialize objects for you.

There is no way to use EF with this type of database tables. EF demands that type can be mapped only to single table (or set of tables in case of inheritance or splitting). Entity cannot be mapped to multiple tables even if they are exactly same.

If you want to use EF you should look for solution on database side and map only single database view or table to your entity or use stored procedure to correctly select table which will be queried.

---

I think this is very possible. Of course I have not developed it but here is where I would start. First you can make two queries to find basic information regarding your database:

SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = 'MyTableName'

EDIT: From this you will have a list of tables and their respective attributes by cycling through. You may not need this step if you use convention over configuration.

From here you can create your custom objects at runtime using reflections TypeBuilder and FieldBuilder. An example would be http://mironabramson.com/blog/post/2008/06/Create-you-own-new-Type-and-use-it-on-run-time-%28C%29.aspx.

Next, use EF Code First with a custom DbContext and override OnModelCreating(...) to map your dynamic objects. Using the Fluent API you could loop through your dynamic types to create your reoccurring mapping pattern. However use caution to not perform this step with every instantiation. I think that might be the crux of your issue.

Never the less I'm very intrigued by your question and curious to what your end solution is.