LINQ to SQL using dynamic tables

Question

I am developing application VS 2008, .NET 3.5 and I am trying to use LINQ To SQL. I drag & drop tables on the designer to generate the .dbml file.

The problem I have that I have some dynamic tables for search indexing.

I know the structure of table, only the application creates new tables like this:

Files_1_1, Files_1_2, ... Files_m_n

DataSearch_1_1, DataSearch_1_2, DataSearch_m_n

In this case, m and n are integers in the name of the table.

I statically define which columns are available but not the name of table, so I need a way to do this on the fly. Of course, this would also have to include associated tables.

I haven't been able to get good idea about it. I would also be satisfied with just being able to generate LINQ To SQL class for this tables.

Has anyone come across a solution to this problem? I have been looking through blog posts and forums for the past one days in vain. Any sample code is great for me.

Answer 1

Link to sql works with stored procedures and the designer will auto create a class for the return type. You could use dynamic sql in your sp and return linq to sql classes.

You could create a stored procedure like below:

CREATE PROCEDURE spGetFiles
(
    @TableName
)
AS

EXEC('SELECT * FROM " + @TableName)

Then in the Visual Studio O/R designer, select the SP from the server explorer and drag it into the designer window in the same way that you add tables. A method with the same name as your SP will be created on your data context class and a class called something like spGetFilesReturnType will be created (i may have got this naming slightly wrong but you get the idea). You then just call the datacontext method with the table name as a string parameter and collections of spGetFilesReturnType objects will be returned.