CODEWITHSUNDEEP
c#asp.net-mvclinq
John
John

Reputation: 1499

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

Upvotes: 1

Views: 3029

Answers (3)

NinjaNye
NinjaNye

Reputation: 7126

Simply add the following to your where clause

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

Upvotes: 0

mattmc3
mattmc3

Reputation: 18355

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.

Upvotes: 2

Chandu
Chandu

Reputation: 82943

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

Upvotes: 1

Related Questions