Linq.Where-to-SQL on a text field comparing to a list of values

Question

Customer.text is a field in an T-SQL DB (that I do not control and thus may not alter) of type "text".

I'd like to do something like this:

List<string> compare = new List<string>();
compare.Add("one");
compare.Add("two");

var q = from t in customer
        where t.text.Contains( compare.First())
        select t;

this will work.

But now I'd like to do something like: (!NOT WORKING!)

var q = from t in customer
        where compare.Contains( t.text )
        select t;

How can I achieve this? Is it even possible?

EDIT: The problem is obviously not exactly clear: A text column in SQL cannot be queried using "=" but only with LIKE. Thus the compare.Contains( t.text ) will result in an error, as it is converted into a query using "=".

What I did not tell - I thought it is irrelevant - is, that I use LINQ-to-ORM (LLBLGen in this case). What I tried instead:

var q = from t in customer
        where compare.Any( x => t.text.Contains(x) )
        select t;

Now this did not work also. Currently I'm not at work, but the exception was something with a ConstantExpression not being convertable into a SetExpression.

I hope this gave some clarification.

EDIT2:

Joseph pointed this out to me: PredicateBuilder. It creates an Expression on a given ObjectType. Now my problem is, that my type is an anonymous type out of multiple joins. Is there an easy or elegant way to handle this?

Answer 1

Now I might be missing something, but your code looks like it should work. Did you include the namespaces at the top of the file?

using System.Linq;
using System.Linq.Expressions;

You could also rewrite it without the Linq2Sql syntax, like:

var q = customer.Where(c => compare.Contains(c.text));

Answer 2

You have to convert the text field to string

        var query = from t in dataContext.table
                    where compare.Contains(t.textField.ToString())
                    select t;

Answer 3

You could build your query using LinqKit's free predicate builder class. Here is a blog post which describes its use and has a link to the download site.

http://thecodeslinger.wordpress.com/2008/10/28/linqkit-predicatebuildert-goodness/

Below is a code sample from the post

    //First get a list of keywords that match the description entered.
                string[] parts = txtInclude.Text.Split(new[] {‘ ‘});
                string[] noparts = null;
                if(txtButNot.Text.Trim().Length > 0)
                    noparts = txtExclude.Text.Trim().Split(new[] {‘ ‘});

                var pred = PredicateBuilder.True<Pet>();
   //here is where you would loop through your compare object
                parts.ForEach(p => pred = pred.And(pl => pl.description.Contains(p)));
                if(noparts != null)
                    noparts.ForEach(p => pred = pred.And(pl => !pl.description.Contains(p)));

                var pets = from s in db.Pets.Where(pred)
                        select s;