Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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!

like image 341
Andy Evans Avatar asked Feb 11 '11 15:02

Andy Evans


2 Answers

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; 
like image 189
Richard Friend Avatar answered Nov 15 '22 16:11

Richard Friend


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;
like image 37
Jamiec Avatar answered Nov 15 '22 14:11

Jamiec