C # linq to sql - dynamic table selection

Question

I have the following scenario: there is a database that generates a new logTable every year. It began in 2001 and now has 11 tables. All of them have the same structure, therefore the same fields, indices, pk, etc.

I have several classes, called managers, which, as the name says, manage each operation in this database. For each individual table, I have a manager, with the exception of this logTable, which has only one manager.

I read and tried a lot of different things, for example, using ITable to dynamically retrieve tables or an interface that implements all my tables. Unfortunately, I am losing strongly typed properties, and with this I cannot do any searches or updates or anything else since I cannot use logTable.Where(q=> q.ID == paramId) .

Given that these tables have the same structure, a query that searches for magazines from 2010 can be accurate, which searches for magazines from 2011 onwards.

I only ask about this because I would not want to rewrite the same code for each table, since they are equal in structure.

EDIT

I am using Linq for SQL as my ORM. And these tables use all database operations, and not only.

Answer 1

As long as each of your queries returns the same form, you can use ExecuteQuery <Log> (an instance of "Select columns from the log" +). Just keep in mind that ExecuteQuery is one case where LINQ to SQL allows SQL Injection. I discuss how to parameterize ExecuteQuery at http://www.thinqlinq.com/Post.aspx/Title/Does-LINQ-to-SQL-eliminate-the-possibility-of-SQL-Injection .

Answer 2

Consider placing all the logs in one table and partitioning to maintain performance. If this is not possible, you can create a view that combines all the log tables and uses it when selecting the log data. That way, when you added a new log table, you simply update the view to include the new table.

EDIT In addition to the last comment:

It looks like you need a new database administrator if he does not allow the creation of new SPs. Yes, I think it is possible to define an ILogTable interface and then make it the log table classes to implement it, but that will not allow you to do GetTable<ILogTable>() . You should have some kind of DAL class with the method that created the join request, for example.

 public IEnumerable<ILogTable> GetLogs() { var Log2010 = from log in DBContext.2010Logs select (ILogTable)log; var Log2011 = from log in DBContext.2011Logs select (ILogTable)log; return Log2010.Concat(Log2011); } 

The above code is completely untested and can greatly fail; -)

Edited that @ AS-CII is happy; -)

Answer 3

You might want to check out the Codeplex Fluent Linq to SQL project . I have never used it, but I am familiar with the ideas of using similar matching methods in EF4. You can create one object and dynamically convert it to different tables using syntax, for example:

 public class LogMapping : Mapping<Log> { public LogMapping(int year) { Named("Logs" + year); //Column mappings... } }