HI guys,
I want to create a dynamic query for a simple reporting application. I have all these filters: StartDate, EndDate, StartTime, EndTime, NodeID, Call Channel ID, CallerID
and there are check boxes on the filters panel for the user so he can choose which of the filters should apply. Basically I want to have a single method for my report and pass empty strings if any particular filter is not selected.
But there is a problem with the WHERE syntax, it does not filter correctly.
So here is my method which I pass the values to:
public IQueryable GetGeneralReport(string StartDate, string EndDate, string StartTime, string EndTime, string NodeID, string CallChannel, string CallerID)
{
LINQTOIVRDataContext datacon = new LINQTOIVRDataContext(GetConnectionString());
var query = from c in datacon.tbl_Calls
where Convert.ToDateTime(c.CallDate) >= Convert.ToDateTime(StartDate) &&
Convert.ToDateTime(c.CallDate) <= Convert.ToDateTime(EndDate) &&
Convert.ToInt32(c.CallTime.ToString().Replace(":","")) >= Convert.ToInt32(StartTime) &&
Convert.ToInt32(c.CallTime.ToString().Replace(":", "")) <= Convert.ToInt32(EndTime)
select new { c.CallerID, c.CallChannel, c.CallDate, c.CallTime, c.NodeID };
if (NodeID != "")
{
query = from c in datacon.tbl_Calls
where c.NodeID == NodeID
select new { c.CallerID, c.CallChannel, c.CallDate, c.CallTime, c.NodeID };
}
if (CallChannel != "")
{
query = from c in datacon.tbl_Calls
where c.CallChannel == CallChannel
select new { c.CallerID, c.CallChannel, c.CallDate, c.CallTime, c.NodeID };
}
if (CallerID != "")
{
query = from c in datacon.tbl_Calls
where c.CallerID == CallerID
select new { c.CallerID, c.CallChannel, c.CallDate, c.CallTime, c.NodeID };
}
return query;
}