Linq and Sql Null Parameters
When doing queries, sometimes the parameters are null. If you didn't know already, the way to handle null parameters in a select stored procedure is:
select * from Customers
where (@CustomerName is null or CustomerName = @CustomerName)
If the @CustomerName parameter is null when passed into the stored procedure, the first option hits, which means all the records are returned. Otherwise, actual processing on the name occurs. I first encountered this when looking at the ASP.NET profile stored procedures in the ASPNETDB.mdf file.
You can do something similar with LINQ. For instance, you can query as such:
var data = from c in this.Context.Customers
where (string.IsNullOrEmpty(customerName) || c.CustomerName == customerName)
select c;
Or, if you have a series of parameters that check certain parameter types, you can filter by:
var data = from c in this.Context.Customers
where (isCheckingForCustomerName && c.CustomerName == text)
|| (isCheckingForCity && c.City == text)
|| (isCheckingForState && c.State == text)
|| (isCheckingForAccountNumber && c.AccountNumber.ToString() == text)
select c;
So you have your options in LINQ to do what you want to do in SQL, without having to write a lot of if statements.