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

Answer 1

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