Temporary tables in Linq -- Anyone see a problem with this?

Answer 1

I don't see a problem with using temporary tables to solve your problem. As far as mixing SqlCommands and LINQ, you are absolutely correct about the hazard factor. It's so easy to execute your SQL statements using a DataContext, I wouldn't even worry about the SqlCommand:

private string _ConnectionString = "<your connection string>";

public void CreateTempTable()
{
    using (MyDBDataContext dc = new MyDBDataContext(_ConnectionString))
    {
        dc.ExecuteCommand("create table #temptab (recno int primary key not null)");
    }
}

public void DropTempTable()
{
    using (MyDBDataContext dc = new MyDBDataContext(_ConnectionString))
    {
        dc.ExecuteCommand("DROP TABLE #TEMPTAB");
    }
}

public void YourMethod()
{
    CreateTempTable();

    using (MyDBDataContext dc = new MyDBDataContext(_ConnectionString))
    {
        ...
        ... do whatever you want (within reason)
        ...
    }

    DropTempTable();
}

Answer 2

As a "general-purpose solution", what if you code is run in more than one threads/apps? I think big-list solution is always related to the problem domain. It's better to use a regular table for the problem you are working on.

I once created a "generic" list table in database. The table was created with three columns: int, uniqueidentifier and varchar, along with other columns to manage each list. I was thinking: "it ought to be enough to handle many cases". But soon I received a task that requires a join be performed with a list on three integers. After that, I never tried to create "generic" list table again.

Also, it's better to create a SP to insert multiple items into the list table in each database call. You can easily insert ~2000 items in less than 2 db round trips. Of cause, depending on what you are doing, performance may do not matter.

EDIT: forgot it is a temporary table and temporary table is per connection, so my previous argument on multi-threads was not proper. But still, it is not a general solution, for enforcing the fixed schema.

Answer 3

We have a similar situation, and while this works, the issue becomes that you aren't really dealing with Queryables, so you cannot easily use this "with" LINQ. This isn't a solution that works with method chains.

Our final solution was just to throw what we want in a stored procedure, and write selects in that procedure against the temp tables when we want those values. It is a compromise, but both are workarounds. At least with the stored proc the designer will generate the calling code for you, and you have a black boxed implementation so if you need to do further tuning you can do so strictly within the procedure, without a recompile.

In a perfect world, there will be some future support for writing Linq2Sql statements that allow you to dicate the use of temp tables within your queries, avoid the nasty sql IN statement for complex scenarios like this one.

Answer 4

Would the solution offered by Neil actually work? If its a temporary table, and each of the methods is creating and disposing its own data context, I dont think the temporary table would still be there after the connection was dropped.

Even if it was there, I think this would be an area where you are assuming some functionality of how queries and connections end up being rendered, and thats ome of the big issues with linq to sql - you just dont know what might happen downt he track as the engineers come up with better ways of doing things.

I'd do it in a stored proc. You can always return the result set into a pre-defined table if you wish.