Joining to a temporary table or table valued parameter

Hi

I am using Lightspeed 4.0.1725.22176.

Is there any way under the SQL2008 provider to join to a temporary table or table valued parameter?

I am using a custom Lucene provider that sometimes returns around 300,000 documents. I then bulk-insert into a normal table and then inner join with the target table to perform sorting and paging of the result.

My problem is that this should be an ephemeral operation but by using a normal table I run into problems of lock-contention, maintenance (cleaning up after the search) and performance.

I have toyed with the idea of using N normal tables for the job, using one per search, then dropping them (or truncating) afterwards which would allow N concurrent searches without lock-contention or maintenance problems. This however, is essentially a temporary table. If I could use those, I would not have to worry about implementing this in code.

If you have any other ideas, I would appreciate that as well.

No, you cant work with either temp tables or TVP's when working with the query object and by extension a LINQ query.

If I understand correctly you are joining the target table just to align with the Id's on the search results and then you need to sort by a column on that table and then select a paged window from that sorted result? A temp table is definitely going to be the most sensible approach for this, so I would look at using a stored proc in conjunction with a TVP if thats an option.

Jeremy

In case anyone is interested in how I solved this, I used a code-first entity

[Table("#FullTextSearchResult")]
public partial class FullTextSearchResult : Entity<Guid>
{
    private float _score;

    public float Score
    {
        get { return Get(ref _score, "Score"); }
        set { Set(ref _score, value, "Score"); }
    }
}

and then built an extension method on my UnitOfWork class to ensure the table is created and to bulk insert the search results. After that I just used the standard LightSpeed Query object.