LINQ SQL query check if a object field is not null

Question

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

tvanfosson · Accepted Answer · 2009-04-13 11:59:26Z

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

omoto · Accepted Answer · 2009-04-13 11:41:55Z

3

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

answered Apr 13, 2009 at 11:41

omoto

1,2201 gold badge17 silver badges24 bronze badges

3 Comments

sipsorcery Over a year ago

That will do the filtering on the retrieved dataset though. I imagine Jon wants the Company Name to be included as part of the SQL select.

Julien N Over a year ago

@sipwiz : No. As long as there is no ToList() or equivalent called on the "from...select", the object listofclient is an IQueryable (so no data retrieved at this point). Which means that the request is still not executed so the "where" clause will be performed BEFORE retrieving the data.

omoto Over a year ago

Yeah exactly only when you call ToList or similar method real sql invocation happens.

score 1 · Accepted Answer · 2009-04-13 11:53:16Z

1

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

edited Apr 13, 2009 at 11:53

answered Apr 13, 2009 at 11:44

user1228

1 Comment

user1228 Over a year ago

dangit! I hate editing code to make it better, only to introduce a bug!

Collectives™ on Stack Overflow

LINQ SQL query check if a object field is not null

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related