CODEWITHSUNDEEP
linqentity-framework
dotnetnoob
dotnetnoob

Reputation: 11330

Performing linq search using substring - is there a better way?

I'm using Entity Framework and have started writing queries using linq.

I have a Store table where each store has a name field. One of the searches I want to do is by initial letter and I'm trying to find the best way of achieving it. By best I mean most efficient.

Most searches only look for one key, 'A', 'B', 'C' etc but one of the searches is different in that the group '0-9' will contain a list of keys, one for 0, one for 1 etc. So my starting point is a list of some kind.

I then need to say if a Store name starts with any key in the list, because a Store does not store the initial letter in the table.

There are 2 things I'm looking for help with. Firstly how to get the linq working as I've outlined. Secondly, any advice as to whether there this is the best/only approach to bringing back the data or whether there may be a better way.

Upvotes: 0

Views: 5190

Answers (3)

dotnetnoob
dotnetnoob

Reputation: 11330

I asked a more specific question on another thread.

Here is the answer:

Linq to entities - first letter of string between 2 keys

Upvotes: 0

Thom Smith
Thom Smith

Reputation: 14086

If the queries can differ, then for the letters:

stores.Where(s=>s.Name.First == c);

And for numerals:

stores.Where(char.IsDigit);

If they must be the same, then I suggest a character range:

stores.Where(s=> c1 <= s.Name.First && s.Name.First <= c2)

You can represent the ranges by Tuples if you want:

Tuple<char, char> range = Tuple.Create('A', 'A');
//Tuple<char, char> range = Tuple.Create('0', '9');
stores.Where(s=> range.Item1 <= s.Name.First && s.Name.First <= range.Item2)

EDIT: Using the function in the Entities Framework

Tuple<char, char> range = Tuple.Create('A', 'A');
//Tuple<char, char> range = Tuple.Create('0', '9');
stores.Where(s=> range.Item1 <= s.Name.Substring(0, 1) && s.Name.Substring(0, 1) <= range.Item2)

Upvotes: 1

david.s
david.s

Reputation: 11403

Your question is not very clear, but from what I understand you want to search for stores which begin with a key found in a list of keys. You can achieve that like this:

List<string> keys = new List<string>() { "A", "B", "M" };
var result = stores.Where(store => keys.Any(key => store.Name.StartsWith(key));

Upvotes: 6

Related Questions