LINQ to SQL query to determine if a value starts with a numeric

Question

I have a project where I request users by the first letter:

repository.GetAll().Where(q => q.BrukerIdent.StartsWith(letter.ToString())).ToList(); 

.. where repository.GetAll() returns IQueryable<Bruker> , BrukerIdent is the string containing the username, and letter is the char value. This works fine except that I also want to get users that start with numbers. And I do not want to sort by individual numbers.

My mind yells at StartsWith("\d") , but as far as I find out, it doesn't work that way. I also thought about doing a 10-position OR sentence, but it will look like spaghetti, and I'm not sure about the effectiveness.

Is there a “right” way to do it this way?

Answer 1

 repository.GetAll().Where(q => Char.IsNumber(q.BrukerIdent[0])) 

MSDN

---

 var numbers = Enumerable .Range(0, 10) .Select(i => i.ToString(CultureInfo.InvariantCulture)); // var numbers = new[] { 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 ); // var numbers = HashSet<int> { ... }; var q = from b in repository.GetAll() where numbers.Contains(b.BrukerIdent.FirstOrDefault())) //[0] select b; 

Answer 2

If this is for LINQ-to-SQL, you can use the SqlMethods.Like method:

 var result = repository .GetAll() .Where(q => SqlMethods.Like(q.BrukerIdent, "[0-9]%")) .ToList(); 

Answer 3

I suspect that the modified @abatishchev answer will help here, but if you are doing this lot, or this is an important request, I highly recommend refactoring. For instance:

 create table Test ( id int not null identity(1,1) primary key clustered, name nvarchar(20) not null, firstChar as (case when LEN(name) = 0 then null else SUBSTRING(name,1,1) end) persisted ) go create nonclustered index Test_firstChar on Test (firstChar) 

Now I can execute a very efficient first character match by simply checking firstChar . This can be an integer if numerical values ​​are especially important.

Answer 4

This may not be readable, but the following will probably work:

 q.BrukerIdent >= "0" && q.BrukerIdent < ":" 

Maybe there are some exotic sorting sequences where this will fail, but I expect this to work in most situations.