WHERE clause with optional dynamic clause

[Sulphy]

Hi all,

Apologies if this has already been addresssed else where in the forum. Does anyone know how to create a linq to sql statement which can include an optional parameter in the where clause?

In this example I want to be able to do the following:

1. Navigate to http://mysite/Home/Names

This would list all of the names in my database

2. Navigate to http://mysite/Home/Names/123

This would only return one name entry where the ID of the record is equal to 123.

I've got the below example working but need to figure out how to pass the optional parameter as and when needed. Currently if the ID is null it prevents any entry in the database being returned.

Code: Select all

   MyNames = (from c in TBLNAMEs                             
                           where c.FirstName == 1 && c.ID == id
                             orderby c.FirstName ascending
                             select c).Take(10).ToList();

[StanislavK]

You can prepare the query in the following way:

Code: Select all

var query = from c in TBLNames
            where c.FirstName == 1
            select c;
if (id != null)
  query = from c in MyNames
          where c.ID == id
          select c;
MyNames = query.Take(10).ToList();

As LINQ queries are deferred, no actual SQL is performed until you enumerate your collection.

[Sulphy]

Perfect! That did the trick, as always you've been brilliant! Thanks StanislavK!