在一个查询中使用多个DataContexts的Linq to SQL

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.

我有这个Linq到SQL查询序列，它基本上返回一个名为PROJECTS的表上的搜索。我正在利用延迟执行的优势来慢慢构建它。

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.

我编写了一些SQL函数，它们返回标量值和表值作为助手函数，因为LINQ不支持ISDATE()或全文搜索。它们与Projects表位于同一个.dbml文件中。

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.

我的问题是，如何在原始查询p中使用这些新的objdbcontext ?我还对如何将executequery()映射回原始查询感兴趣。

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.

我希望这是有意义的。我有一些类型的错误匹配错误，并试着用谷歌搜索了一段时间，但什么也找不到。

1 个解决方案

#1

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

既然您已经在context类上定义了所有的方法，那么对这三个都使用相同的实例:

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)

如果FullTextSearch不返回布尔值，则需要构建一个这样的表达式。就像它返回IList一样，您可以使用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.

尽管要记住，无论你用哪种方法，你尝试做的事情都会导致三次到数据库的往返。在这种情况下，最好手工编写SQL。LINQ to SQL不能，也不打算，100%替换SQL。

#1