CODEWITHSUNDEEP
linq
Jon
Jon

Reputation: 15200

LINQ SQL query check if a object field is not null

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

Upvotes: 8

Views: 15640

Answers (3)

tvanfosson
tvanfosson

Reputation: 532765

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

Upvotes: 5

user1228
user1228

Reputation:

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;
  }
}

Upvotes: 1

omoto
omoto

Reputation: 1220

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

Upvotes: 3

Related Questions