CODEWITHSUNDEEP
c#linqlinq-to-sql
Niklas
Niklas

Reputation: 13145

Retrieve all values that partly contains values from a list

I have a table in my database called Citites. I want to retrieve all cities whose name contain any of the values from the strings list.

List<string> strings = new List<string>(new string[] {"burg", "wood", "town"} );

I tried this but it will only match the exact value from the strings list. I need to find values that contain e.g town, like cape town and townsend

List<City> cities = db.Cities.Where(c => strings.Contains(c.name));

EDIT
I'm using LINQ to SQL and Any() doesn't seem to be supported here:

Local sequence cannot be used in LINQ to SQL implementations of query operators except the Contains operator.

Upvotes: 0

Views: 196

Answers (7)

Jamez
Jamez

Reputation: 1597

Do you have the ability to call a stored proc or sql? - you could use SQL fulltextsearch, especially if you're searching multiple terms. It'd probably be a lot quicker than doing string comparisons in SQL.

http://technet.microsoft.com/en-us/library/ms142583.aspx

You could create your search terms by doing string.Join(" ", strings)

Upvotes: 0

Shreya
Shreya

Reputation: 204

var cities = from c in db.Cities.AsEnumerable()
             from s in strings
             where c.name.ToLower().Contains(s.ToLower())
             select c.name;

Upvotes: 0

Dhanasekar Murugesan
Dhanasekar Murugesan

Reputation: 3239

If you find it with a lot of loops, try using FUNC<> which will be better (in performance). I have a sample for that :

            List<string> _lookup = new List<string>() { "dE", "SE","yu" };
            IEnumerable<string> _src = new List<string> { "DER","SER","YUR" };

            Func<string, List<string>, bool> test = (i,lookup) =>
            {
                bool ispassed = false;

                foreach (string lkstring in lookup)
                {
                    ispassed = i.Contains(lkstring, StringComparison.OrdinalIgnoreCase);

                    if (ispassed) break;
                }

                return ispassed;

            };


            var passedCities = _src.Where(i => test(i, _lookup));

Upvotes: 0

Knaģis
Knaģis

Reputation: 21485

Since you mention that your LINQ provider does not support .Any() in this context, here is a much more complicated code that builds the query expression dynamically.

var strings = new [] { "burg", "wood", "town" };
// just some sample data
var cities = new[] { new City("Capetown"), new City("Hamburg"), new City("New York"), new City("Farwood") };

var param = Expression.Parameter(typeof(City));
var cityName = Expression.PropertyOrField(param, "Name"); // change the property name
Expression condition = Expression.Constant(false);
foreach (var s in strings)
{
    var expr = Expression.Call(cityName, "Contains", Type.EmptyTypes, Expression.Constant(s));
    condition = Expression.OrElse(condition, expr);
}

// you can apply the .Where call to any query. In the debugger view you can see that
// the actual expression applied is just a bunch of OR statements.
var query = cities.AsQueryable().Where(Expression.Lambda<Func<City, bool>>(condition, param));
var results = query.ToList();

// the class used in the test
private class City
{
    public City(string name) { this.Name = name; }
    public string Name;
}

But note that since you mentioned in other comments that the strings collection is rather large, you should really look into building a stored procedure and pass the values as XML parameter to that procedure (then load the XML as table and join it in the query) because this approach of building the query will probably soon run into some sort of "query has too many operands" exception.

Upvotes: 1

nestedloop
nestedloop

Reputation: 2646

You need to check if the City name contains any of the string in the list, not the other way around:

protected bool ContainsSubstring(string cityName, List<string> strings)
{
 foreach(string subString in strings)
 {
  if (cityName.Contains(subString)) return true;
 }
 return false;
}

...

List<City> cities = db.Cities.Where(c => this.ContainsSubstring(c.name, strings));

Upvotes: 0

Knaģis
Knaģis

Reputation: 21485

This will do what you need, assuming your LINQ provider supports it - since you did not mention what are you using, we can't test it.

List<City> cities = db.Cities.Where(c => strings.Any(s => c.name.Contains(s)));

In detail: for a single value (like Capetown) you would write

strings.Any(s => "Capetown".Contains(s))

Then you just apply this expression inside your current Where condition as shown in the initial code example.

Upvotes: 3

Tim Schmelter
Tim Schmelter

Reputation: 460208

I'm not sure if it is supported by your LINQ-provider, but at least in LINQ-To-Objects this works:

List<City> cities = db.Cities.Where(c => strings.Any(s=> c.Name.Contains(s)));

Upvotes: 0

Related Questions