Linq to SQL using multiple DataContexts in a single query.

Question

I have this Linq to SQL query sequence that is basically returning a search on a table called PROJECTS. and I'm taking advantage of the deferred execution to slowly build it up.

var query = from p in objDBContext.PROJECTs
where (p.PROVIDER_ID == cwForm.productForm) 
select p; 

query = from p in query
where p.SubmittedDate >= cwForm.beginDateForm
select p;

I wrote a few SQL functions that return scalar values and table values as helper function because LINQ doesn't support ISDATE() or full text search. they are in the same .dbml file as the Projects table.

So now I have:

var dateQuery = from d in objDBContext.ISDATE   
select d;
//returns a bool

var ftsQuery = from f in objDBContext.FullTextSearch
select f;
//returns a valued-table with the primary keys of hits with fulltextsearch

My question is, how do I use these new objDBContexts on the original query p? I'm also interested in figuring out how to map an executequery() back into the original query.

Something like:

query = from p in query
        from d in dateQuery
        from f in ftsQuery
where d.ISDATE(p.Field1) &&  f.FullContextSearch(searchString)    

    select p;

I hope that makes sense. I've got a few types mismatched errors and have tried googling for a while but couldn't find anything.

Answer 1

Since you have defined all your methods on the context class, use the same instance for all three:

var query = from p in objDBContext.Projects where
    p.PROVIDER_ID == cwForm.productForm 
    && objDBContext.ISDATE(p.Field1)
    && objDBContext.FullTextSearch(searchString)
        //assuming FullTextSearch returns boolean
    select p

if FullTextSearch doesn't return boolean, you need to build an expression that does. Like if it returns IList, you could do objDBContext.FullTextSearch(searchString).Contains(p)

Though keep in mind what you're trying to do will cause three round-trips to the database no matter which way you cut it. It might be better just to hand-craft your SQL in this case. LINQ to SQL cannot, and is not intended to, replace SQL 100% of the time.