LINQ SQL query check if a obect field is not null

Question

Hi all,

I am trying write a SQL query that filters a gridview by the fields that are entered. There are four fields, title, firstname, surname and Company.Name.

The first three are fine as they are never null but the fourth can be null. The following LINQ Query works just fine:

var listofclients = from client in allcients
                    where client.Title.ToLower().Contains(titletxtbox.Text.Trim().ToLower())
                    where client.Firstname.ToLower().Contains(firstnametxtbox.Text.Trim().ToLower())
                    where client.Surname.ToLower().Contains(surnametxtbox.Text.Trim().ToLower())
                    orderby client.Name

But when I try and put a filter into it for the company I will get an error at runtime when the company is null

var listofclients = from client in allcients
                    where client.Title.ToLower().Contains(titletxtbox.Text.Trim().ToLower())
                    where client.Firstname.ToLower().Contains(firstnametxtbox.Text.Trim().ToLower())
                    where client.Surname.ToLower().Contains(surnametxtbox.Text.Trim().ToLower())
                    where client.Company.Name.ToLower().Contains(companynametxtbox.Text.Trim().ToLower())
                    orderby client.Name

What I would like to know, is there a way to build the query so that it will only filter when the client.Company field is not null.

Also am I vulnerable to SQL injection or the like when I pull directly from the textbox fields like this. I know in this case it is not connected to the DB but if it was could they do a drop. Or even if it is not connected to the db could they fiddle with the objects in the list?

Thanks

Jon Hawkins

Answer 1

var listofclients = from client in allcients
                    orderby client.Name
                    select client;

if (string.IsNullOrEmpty(titletxtbox.Text))
listofclients = listofclients.Where(l=>l.Title.Contains(titletxtbox.Text))

........

Something like this

Answer 2

1) Linq to Sql uses parameters in its queries, so it is not vulnerable to sql injection. HOWEVER, NEVER TRUST USER INPUT.

2) Linq doesn't provide free null checking, sorry. You can accomplish this with a simple extension method, tho, to keep your linq query trim and fit:

public static class StringExtensions
{
  public static bool ContainsEx(this string me, string other)
  {
    if(me == null || other == null) return false;
    // This is a better way of performing a case-insensitive Contains
    return me.IndexOf(other, 0, StringComparison.OrdinalIgnoreCase) != -1;
  }
}

Answer 3

I'm assuming you want all matching records where Company is null but filtered by name when the Company exists. The following should do that. Also, you needn't worry about SQL injection as LINQToSQL uses parameterized queries. You will have to worry about cleaning up any HTML that may be in the client controls if you intend to do inserts from them and display any of the values on the web to avoid XSS attacks.

var listofclients = from client in allcients
                    where client.Title.ToLower().Contains(titletxtbox.Text.Trim().ToLower())
                    where client.Firstname.ToLower().Contains(firstnametxtbox.Text.Trim().ToLower())
                    where client.Surname.ToLower().Contains(surnametxtbox.Text.Trim().ToLower())
                    where client.Company == null || client.Company.Name.ToLower().Contains(companynametxtbox.Text.Trim().ToLower())
                    orderby client.Name