In creating a SQL select statement to query some records from a table, it is pretty common to include WHERE clause - where you are limiting your selections by criterias specified.
Here is an example:
SELECT * FROM Products WHERE CategoryID = 5The SQL statement above will return all Product records that are in Category 5.
Now, sometimes we want to use the condition only if it meets certain condition (like if the condition value is not empty or null).
So you SQL statement may look like this (assuming this is wrapped in a stored procedure) - where you want the stored procedure to return all Products if Category is not filtered:
ALTER PROCEDURE [dbo].[GetProducts] @CategoryID int = NULL AS IF (@CategoryID IS NULL) BEGIN SELECT * FROM dbo.Products END ELSE BEGIN SELECT * FROM dbo.Products WHERE CategoryID = @CategoryID ENDLooks rather long isn't it? In LINQ this is a bit shorter, but still quite repetitive:
var data; if (categoryId == 0) data = from row in db.Productselse data = from row in db.Products where CategoryID == categoryIdSo how can we short-cut this into shorter statements that is elegant and still do the job well?
ALTER PROCEDURE [dbo].[GetProducts] @CategoryID int = NULL AS SELECT * FROM dbo.Products WHERE (@CategoryID is NULL OR @CategoryID = 0) OR CategoryID = @CategoryIDIn LINQ:
var data = from row in db.Products where (CategoryID == categoryId || categoryId == 0)By including the possibility of null or zero (or any default condition) in the WHERE clause means that the condition will be computed as well to produce the results. Got it?