WHERE clause with optional dynamic clause

WHERE clause with optional dynamic clause

Postby Sulphy » Mon 31 Dec 2012 14:44

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();
Sulphy
 
Posts: 34
Joined: Wed 12 Sep 2012 18:07

Re: WHERE clause with optional dynamic clause

Postby StanislavK » Wed 02 Jan 2013 15:46

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.
StanislavK
Devart Team
 
Posts: 1710
Joined: Thu 03 Dec 2009 10:48

Re: WHERE clause with optional dynamic clause

Postby Sulphy » Thu 10 Jan 2013 13:03

Perfect! That did the trick, as always you've been brilliant! Thanks StanislavK!
Sulphy
 
Posts: 34
Joined: Wed 12 Sep 2012 18:07


Return to LinqConnect (LINQ to SQL support)