Hello,
I use LINQ to dynamically generate a query based on user input. I found a very effective pattern to be a base query and set of additional where clauses based on user-defined filters. However, I'm running into the following problem.
In the code
IQueryable<EmailEntity> q;
if (!string.IsNullOrWhiteSpace(r.Keywords))
{
q = from m in x.Email
join s in x.TVFSearchEmails(r.Keywords) on m.Id equals s.Id
select m;
}
else
{
q = from m in x.Email
select m;
}
if (!string.IsNullOrEmpty(r.SenderContains))
q = q.Where(z => SQLFunctions.ContainsSearch(z.Sender, r.SenderContains));
//dozen more filter checks of the form q = q.Where() go here
If Keywords is blank, generated SQL has this form:
select *
from dbo.FNSearchEmails('keyword') s
join Email e on s.Id = e.Id
where contains(e.Sender, 'xyz')
However, if Keywords is not blank, generated SQL looks like this and it fails with error "Cannot use a CONTAINS or FREETEXT predicate on column 'Sender' because it is not full-text indexed." because my mapped contains function gets bound to a sub-query column
select *
from
(
select e.Id, e.Sender
from dbo.FNSearchEmails('keyword') s
join Email e on s.Id = e.Id
) sq
where contains(sq.Sender, 'xyz') --this does not work
Is there a way around this other than repeating the full query twice - one with a join to TVFSearchEmails and one without a join? The reason I wanted to break up the query into 2 chunks is so I don't have to repeat all the where clauses twice.
Thank you.
