LINQ to SQL Conditional where clause

Question

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:

if SiteTypeId = -1 (then this means show all so nothing is to be changed)
if SiteTypeId = 0 (then i.SiteTypeId == 0 needs to be added)
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)
            searchTextSite = "";

        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);
    }

mattmc3 · Accepted Answer · 2011-07-11 03:41:15Z

2

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.

answered Jul 11, 2011 at 3:41

mattmc3

18.6k8 gold badges88 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Dan J Over a year ago

+1 To power a flexible filtering UI, I ended up building a dynamic ExpressionFilter based around PredicateBuilder; it is a delicious technique. :)

Chandu · Accepted Answer · 2011-07-11 03:19:09Z

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);
}

NinjaNye · Accepted Answer · 2011-07-11 07:54:57Z

0

Simply add the following to your where clause

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

answered Jul 11, 2011 at 7:54

NinjaNye

7,1361 gold badge34 silver badges47 bronze badges

Collectives™ on Stack Overflow

LINQ to SQL Conditional where clause

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related