CODEWITHSUNDEEP
c#linqentity-framework-4
user290043
user290043

Reputation:

How to write Linq depending if a value is provided or not

I am trying to write a LINQ statement with some optional where clauses. This is for a search. The user can select a specific site to search or search against all sites:

var query =
    _db.STEWARDSHIP
       .OrderBy(r => r.SITE.SITE_NAME)
       .Where(r => r.SITE_ID == SiteId)
       .Where(r => r.VISIT_TYPE_VAL.VISIT_TYPE_ID == VisitTypeId)
       .Select(r => new
       {
           id = r.STEWARDSHIP_ID,
           name = r.SITE.SITE_NAME,
           visit_type = r.VISIT_TYPE_VAL.VISIT_TYPE_DESC,
           visit_date = r.VISIT_DATE
       });
return query;

So when the method gets SiteId = 14, for instance, no problem. However, when it gets SiteId = null, then that where clause should not be considered.

Thanks Eric

Upvotes: 0

Views: 81

Answers (4)

Austin Salonen
Austin Salonen

Reputation: 50225

I'm stealing a trick from TSQL. Just check for the null value as well.

...
.Where(r => SiteID == null || r.SITE_ID == SiteID)
...

The SQL example is this:

WHERE (SITE_ID = @given OR @given IS NULL)  --return matches or all

Though if that value is mutable and you want the value at the time the query was built, try this instead:

var localSiteID = SiteID;

...
.Where(r => localSiteID == null || r.SITE_ID == SiteID)
...

Upvotes: 1

Glory Raj
Glory Raj

Reputation: 17701

you can use where clause in one statement ..like this ..

  .Where(r => SiteID == null || r.SITE_ID == SiteID)

Upvotes: 1

Jon Skeet
Jon Skeet

Reputation: 1500525

That's easy:

var query = _db.STEWARDSHIP.OrderBy(r => r.SITE.SITE_NAME);

if (SiteId != null)
{
    query = query.Where(r => r.SITE_ID == SiteId);
}
query = query.Where(r => r.SITE.SITE_TYPE_VAL.SITE_TYPE_ID == SiteTypeId)
             .Select(r => new
              {
                  id = r.STEWARDSHIP_ID,
                  name = r.SITE.SITE_NAME,
                  visit_type = r.VISIT_TYPE_VAL.VISIT_TYPE_DESC,
                  visit_date = r.VISIT_DATE
              });
return query;

This works because queries compose nicely - and they really only represent queries; it's only when you try to fetch data from them that the query is actually executed.

Upvotes: 5

GWLlosa
GWLlosa

Reputation: 24403

Can't you just edit the where clause to something like 

 .Where(r=>SiteId == null || r.SiteId == SiteId)

Upvotes: 1

Related Questions