get the names starts with numbers or special characters in linq to sql

Question

I need to get the list of names that starts with special characters or numbers in the linq to sql query for my asp.net mvc(C#) application.

I have tried like this (Which may not be efficient):

public List<User> GetUsersStartingWithNonCharacter()
{
   List<User> _dbUsers = this.GetAllUsers();
   return _dbUsers.Where(p => ((p.FirstName != null && p.FirstName != string.Empty && !char.IsLetter(p.FirstName.ToLower()[0])) || (p.FirstName == null || p.FirstName == string.Empty))).ToList();
}

public List<Users> GetAllUsers()
{
    return (from u in _context.pu_Users
            where !u.Is_Deleted
            select new User
            {
                UserId = u.User_Id,
                Email = u.Email_Address,
                FirstName = u.First_Name,
                LastName = u.Last_Name
            }).ToList();
}

Can anyone suggest the most efficient way to do this in linq to sql?

Answer 1

How do you know if it isn't already efficient? Use somekind of profiler tool, like SQL Server Profiler if you're using MSSQL, that way you can trace your call against the database and see the actual SQL. Of course you can only debug the code to see the generated SQL but it's easier with a profiler tool and you'll see how long time the query takes.

EDIT: I see one part in how you can make it more efficient:

public List<User> GetUsersStartingWithNonCharacter()
{
   List<User> _dbUsers = this.GetAllUsers();
   return _dbUsers.Where(p => ((p.FirstName != null && p.FirstName != string.Empty && !char.IsLetter(p.FirstName.ToLower()[0])) || (p.FirstName == null || p.FirstName == string.Empty))).ToList();
}

public IQueryable<Users> GetAllUsers()
{
    return from u in _context.pu_Users
            where !u.Is_Deleted
            select new User
            {
                UserId = u.User_Id,
                Email = u.Email_Address,
                FirstName = u.First_Name,
                LastName = u.Last_Name
            };
}

Changing your GetAllUsersto return IQueryable will delay the query to execute until you've applied your filters. This might affect some other aspects of your design but you should consider it since that change might make your where clause run in the database instead of in the code which will result in less data traffic between your application and database. Again, use a profiler to see the difference :).

Answer 2

I'll use Regular Expression in this scenerio

Here is my sample code

 return _dbUsers.Where(p=>p.FirstName!=String.Empty)
                . Where(p => Regex.Match(p.Firstname[0].ToString(), "[a-zA-Z]").Success).ToList();

Answer 3

I suspect all rows will be retrieved and filted in your application due to the condition:

char.IsLetter(p.FirstName.ToLower()[0])

(Using a regular expression like suggested in another answer will also pull in all rows, and filter them on the client.)

It is possible to check characters in a string with the PATINDEX function, but it's seems only to be available for LINQ via the Entity framework.

You could write a stored procedure using PATINDEX directly to check for the first character to optimize your query. Sample queries can be found at http://www.databasejournal.com/features/mssql/article.php/3071531/Using-SQL-Servers-CHARINDEX-and-PATINDEX.htm.

Sometimes LINQ to whatever will not yield the most optimized solution, but that's just life. In most cases it will give clearer code, but special cases might require work arounds in order to use special operators of the underlying system.