How can I use LINQ to return only rows where the first character of a column is within a range?

Question

I would like to use LINQ to limit the number of rows I return from my SQL Server database to just those rows where the primary key (WordId) first character is in the range A to E. Here is the way I am currently doing it using this LINQ:

var words = db.WordForm
            .Where(wf => string.Compare(wf.WordId.Substring(0, 1), "A") >= 0 && 
                         string.Compare(wf.WordId.Substring(0, 1), "E") <= 0)
            .ToListAsync();

Can someone tell me is this the best way to do this or is there another way using LINQ?

Possible duplicate of [Multiple string comparison with C#](http://stackoverflow.com/questions/6034503/multiple-string-comparison-with-c-sharp) — techspider, Apr 29 '16 at 14:28
How many rows are there in the WordForm? Is the use of a computed column not worth considering? — Jeroen Heier, Apr 29 '16 at 14:38
@MaxSorin - I had not considered use of lower case. This is a problem I think I will need to modify the code. Thank you — , Apr 29 '16 at 14:44
@JeroenHeier - There are about 1,000 - 2,000 rows in the table. Can you explain what you mean by a computed column — , Apr 29 '16 at 14:45
If there are many rows then selection at the database level on the basis of an additional "category" column would be very efficient but this is not the case. What you are looking for is a equivalent for the BETWEEN operator. See [this](http://stackoverflow.com/questions/12476686/what-is-equivalent-to-clause-between-for-comparasion-strings-in-linq-or-lambda) SO question. — Jeroen Heier, Apr 29 '16 at 15:31

Arturo Menchaca · Answer 1 · 2016-04-29T14:54:02.043

3

You can use SqlFunctions.Ascii, this function returns the ASCII code value of the left-most character of a character expression, so you can use it like this:

int asciiA = Encoding.ASCII.GetBytes("A")[0];
int asciiE = Encoding.ASCII.GetBytes("E")[0];

var words = db.WordForm.Where(wf => SqlFunctions.Ascii(wf.WordId.ToUpper()) >= asciiA &&
                                    SqlFunctions.Ascii(wf.WordId.ToUpper()) <= asciiE).ToListAsync();

edited Apr 29 '16 at 14:54

answered Apr 29 '16 at 14:44

Arturo Menchaca

15,783
1
29
53

Why is this better than the OP way? – Ivan Stoev Apr 29 '16 at 15:13

Fabjan · Answer 2 · 2016-04-29T15:58:28.250

Just tested different approaches, with HashSet<T> and without it. In case when we have < 20k records using of List<T>.Contains() method should be sufficient :

        var db = new List<MyClass>()
        {
            new MyClass() { WordId = "gds134" }, new MyClass() { WordId = "ads134" },
            new MyClass() { WordId = "Cds134" }, new MyClass() { WordId = "Hds134" },
            new MyClass() { WordId = "eds134" }, new MyClass() { WordId = "eds135" },
        };

        var lettersList = new List<char>() { 'a', 'b', 'c', 'd', 'e', 'A', 'B', 'C', 'D', 'E' };

        var result = db.Where(x => lettersList.Contains(x.WordId.First()));

        foreach(var item in result) Console.WriteLine(item.WordId);

Output:

ads134
Cds134
eds134
eds135

P.S. Another example (including simple performance diagnostics) :

http://rextester.com/KCNX61550

@Sophia updated link to rexter, check new link please – Fabjan Apr 29 '16 at 15:59 — Fabjan, Apr 29 '16 at 15:59

score 1 · Answer 3 · answered Apr 29 '16 at 15:45

Can someone tell me is this the best way to do this or is there another way using LINQ?

Someone surely can tell you. But it would be an opinion based on limited info. What you have will work and do its job. Are you using EF? Does that LINQ transform into an SQL query? You will certainly want to debug and see what SQL is being generated if this is the case.

How to debug the SQL:

private EntitiesContext _context;
private EntitiesContext EntitiesContext
        {
            get
            {
                if (_context == null)
                {
                    _context = new EntitiesContext();
                     _context.Database.Log = s => System.Diagnostics.Debug.WriteLine(s); //For debugging the SQL made by EF
                }
                return _context;
            }
            set { _context = value; }
        }

If you are working with an already created list then you might consider using a regex. This will translate very poorly, I suspect, if applied to an EF context.

score 0 · Answer 4 · answered Apr 29 '16 at 14:56

There is not a way of going "A thru E" in code, you will need to make a list or array of strings that represent the starting letters you want to check.

Take a look at the following example solution:

I've created the WordForm objects in-code; obviously yours come from your DB and you can do what you need with them.

The .StartsWith("whatever") Linq extension method is a nice comparison instead of using the .Substring(0,7) == "whatever".

public static void Main()
{
    var words = new List<WordForm>();
            words.Add(new WordForm { WordId = "Apple" });
    words.Add(new WordForm { WordId = "Banana" });
    words.Add(new WordForm { WordId = "Cake" });
    words.Add(new WordForm { WordId = "Date" });
    words.Add(new WordForm { WordId = "Egg" });

    var filterChars = new string[] { "C", "D" };
    var filtered = GetStartingWith(filterChars, words);

    foreach (var item in filtered)
    {
       Console.WriteLine(item.WordId);
    }

}

public static List<WordForm> GetStartingWith(string[] startingLetters, List<WordForm> collection)
{
    var returnList = new List<WordForm>();

    foreach (var wordForm in collection)
    {
        foreach (var startingLetter in startingLetters)
        {
            if (wordForm.WordId.StartsWith(startingLetter))
            {
                returnList.Add(wordForm);
            }
        }
    }

    return returnList;
}

public class WordForm
{
    public string WordId { get; set; }
    //... Plus all your other properties on WordForm...
}

This returns me

"Cake" and "Date" in console.

What the above does:

Give the method a list of letters to check against: in this case A, B, C, D, E; and the list to check in (your List<WordForm> collection)

It then loops through the WordForm objects and checks if it starts with any of the startingLetters you've given it.

If it matches a starting letter e.g. "Cake".StartsWith("C") (Cake starts with "C"), then add it to a list that will then be returned with the filtered objects in.

How can I use LINQ to return only rows where the first character of a column is within a range?

4 Answers4