LINQ to SQL Conditional where clause

I have the following controller code that returns a Json list object to my view that draws a pie chart.

There are 4 input parameters and i have it working with 3 of them.

However, the fist parameter entitled 'SiteTypeId' needs to be included in the where.

My problem is how to include this neatly in the code, i'd like to avoid an override of the function.

The required additional logic is:

1. if SiteTypeId = -1 (then this means show all so nothing is to be changed)
2. if SiteTypeId = 0 (then i.SiteTypeId == 0 needs to be added)
3. if SiteTypeId = 1 (then i.SiteTypeId == 1 needs to be added)

If 2 and 3 above were all that was required it would be easy I guess. I'm thinking there must be a neat expression for this or a neat way of splitting the LINQ into 2 with a condition perhaps.

I'm new to LINQ - can anyone advise me, here is the controller code i need to modify:

    public JsonResult GetChartData_IncidentsBySiteStatus(string SiteTypeId, string searchTextSite, string StartDate, string EndDate)
    {
        if (searchTextSite == null)
            searchTextS开发者_运维问答ite = "";

        DateTime startDate = DateTime.Parse(StartDate);
        DateTime endDate = DateTime.Parse(EndDate);

        var qry = from s in _db.Sites   
                  join i in _db.Incidents on s.SiteId equals i.SiteId
                  where s.SiteDescription.Contains(searchTextSite)
                    && (i.Entered >= startDate && i.Entered <= endDate)
                  group s by s.SiteStatus.SiteStatusDescription + "[" + s.SiteTypeId.ToString() + "]"
                      into grp
                      select new
                      {
                          Site = grp.Key,
                          Count = grp.Count()
                      };

        return Json(qry.ToList()  , JsonRequestBehavior.AllowGet);
    }

Sounds like you could use LINQKit and its PredicateBuilder. You use it to build dynamic conditional WHERE clauses. It's also used in LinqPad, and it's free.

Try this:

public JsonResult GetChartData_IncidentsBySiteStatus(string SiteTypeId, string searchTextSite, string StartDate, string EndDate)
{
        if (searchTextSite == null)
                searchTextSite = "";

        DateTime startDate = DateTime.Parse(StartDate);
        DateTime endDate = DateTime.Parse(EndDate);

        var  incidentsQry = _db.Incidents;
        if(SiteTypeId > -1)
        {
            incidentsQry = incidentsQry.Where(a=>a.SiteTypeId == SiteTypeId);
        }

        var qry = from s in _db.Sites   
                            join i in incidentsQry  on s.SiteId equals i.SiteId
                            where s.SiteDescription.Contains(searchTextSite)
                                && (i.Entered >= startDate && i.Entered <= endDate)
                            group s by s.SiteStatus.SiteStatusDescription + "[" + s.SiteTypeId.ToString() + "]"
                                    into grp
                                    select new
                                    {
                                            Site = grp.Key,
                                            Count = grp.Count()
                                    };

        return Json(qry.ToList()  , JsonRequestBehavior.AllowGet);
}

Simply add the following to your where clause

(SiteTypeId == -1 || i.SiteTypeId == SiteTypeId)