Using contains() in LINQ to SQL

A simpler and more correct solution (then leppie's):

public IQueryable<Part> SearchForParts(string[] query)
{
    var q = db.Parts.AsQueryable(); 

    foreach (string qs in query)
    {
        q = q.Where(x => x.partName.Contains(qs));
    }

    return q;
}

This will work as long as partName is a string (or an SQL equivalent of a string).

The important thing to note is partName.Contains(qs) is different than query.Contains(partName).
With partName.Contains(qs), partName is searched for any occurrence of qs. The resulting SQL would be equivalent (where <qs> is the value of qs):

select * from Parts where partName like '%<qs>%';

Also of note are StartsWith and EndsWith which are similar to Contains but look for the string in the specific location.

query.Contains(partName) is the same as a SQL in command. The resulting SQL would be equivalent to (where <query0> is the value of query[0], <query1> is the value of query[1], and <queryN> is the last value in the query array):

select * from Parts where partName in ( <query0>, <query1>, ..., <queryN> );

Update:
It is also important to note that leppie's answer does not escape the wildcard characters before adding them to the like statement. This is not an issue with the Contains solution since Linq will escape the query before sending it. An escaped version of the SqlMethods.Like solution would be:

public IQueryable<Part> SearchForParts(string[] query)
{
    var q = db.Parts.AsQueryable(); 

    foreach (var qs in query)
    {
        string escaped_bs = qs.Replace("/", "//"),
            escaped_us = escaped_bs.Replace("_", "/_"),
            escaped_p = escaped_us.Replace("%", "/%"),
            escaped_br = escaped_p.Replace("[", "/["),
            likestr = string.Format("%{0}%", escaped_br);

        q = q.Where(x => SqlMethods.Like(x.partName, likestr, '/'));
    }

    return q;
}

You don't have to worry about ' since Linq will escape that for you.

You could try:

public IQueryable<Part> SearchForParts(string[] query)
{
    return from part in db.Parts
           where query.All(term => part.partName.Contains(term))
           select part;
}

However, I'm not sure if LINQ to SQL will be able to transform it into T-SQL. Another option would be:

public IQueryable<Part> SearchForParts(string[] query)
{
    var result = from part in db.Parts
                 select part;

    foreach(var term in query)
    {
        result = from part in result
                 where part.partName.Contains(term)
                 select part;
    }

    return result;
}

It's not as pretty, but it should work. You'll get a query with a lot of ANDs in the where clause.

You can write it as this

var result = db.Parts.Where(p => query.All(q => p.partName.Contains(q)));

Using the NinjaNye.SearchExtension nuget package allows you to perform this search with ease:

string[] terms = new[]{"search", "term", "collection"};
var result = db.Parts.Search(terms, p => p.PartName);

You could also search multiple string properties

var result = db.Parts.Search(terms, p => p.PartName, p.PartDescription);

Or perform a RankedSearch which returns IQueryable<IRanked<T>> which simply includes a property which shows how many times the search terms appeared:

//Perform search and rank results by the most hits
var result = db.Parts.RankedSearch(terms, p => p.PartName, p.PartDescription)
                     .OrderByDescending(r = r.Hits);

There is a more extensive guide on the projects GitHub page: https://github.com/ninjanye/SearchExtensions

Hope this helps future visitors

I feel this is somewhat simple and working for me:

string[] product = products.Split(','); 
using (var context = new ProjectTrackerEntities()) 
{ var result = from part in context.DBAudits where product.Contains(part.TableName) select part; }