LINQ to SQL query where a string StartsWith an element from a generic list

Question

I'm looking to update one of my queries as the requirements for the search has changed. Originally, the user was to enter a single SKU and a mfg. date range to search the product catalog. So this is what I used.

DateTime startDate = ...;
DateTime endDate = ...;
string prodSKU = TextSKU.Text.Trim();

var results = from c in db.Products
                where c.is_disabled == false 
                && c.dom >= startDate 
                && c.dom <= endDate 
                && c.sku.StartsWith(prodSKU)
                select c;

Now the requirement says that the user can enter a comma delimted list of SKUs into the textbox to search. What I'm stumped about is how to find all the products in the mfg. date range that begin with any of the SKUs in skuList (w/o using a fornext loop).

string prodSKU = TextSKU.Text.Trim();
List<string> skuList = prodSKU.Split(new char[] { ', ' }).ToList();

var results = from c in db.Products
                where c.is_disabled == false 
                && c.dom >= startDate 
                && c.dom <= endDate 
                // && c.sku.StartsWith(prodSKU)
                select c;

Any ideas would be greatly appreciated!

Rizzi

Answer 1

Something like

string prodSKU = TextSKU.Text.Trim(); 
List<string> skuList = prodSKU.Split(new char[] { ', ' }).ToList();  

var results = from c in db.Products                 
   where c.is_disabled ==false                  
   && c.dom >= startDate                  
   && c.dom <= endDate                  
   &&  skuList.Any(sl=>c.sku.StartsWith(sl))                 
      select c; 

Jericajericho

Good answer. Took me too long to come back and read comments, but I came to a similar solution. – Stagemanage

Answer 2

string prodSKU = TextSKU.Text.Trim();
List<string> skuList = prodSKU.Split(new char[] { ', ' }).ToList();

var results = from c in db.Products
                where c.is_disabled == false 
                && c.dom >= startDate 
                && c.dom <= endDate 
                &&  skuList.Contains(c.sku)
                select c;

EDIT: I'll put this here even though this question is already answered.

I achieved the same as the accepted solution, but using an extension method to aid readability:

public static class StringExtensions
{
    public static bool StartsWithAny(this string s, IEnumerable<string> items)
    {
        return items.Any(i => s.StartsWith(i));
    }
}

and then the solution is:

var results = from c in db.Products
            where c.is_disabled == false 
            && c.dom >= startDate 
            && c.dom <= endDate 
            && c.sku.StartsWithAny(skuList)
            select c;

Stagemanage

I noticed the missed And, but not the var name. Woops! I removed my answer as it was idential to this anways. – Beuthen

how does this address the .StartsWith part? – Rizzi

@Andy - do you mean users can type in a comma separated list of sku's, but you still want the StartsWith functionality? – Stagemanage

I would like the query to find products in the product table where the sku starts with any of the elements in skuList. – Rizzi