Click here to Skip to main content
15,029,128 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
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;

}
Posted
Comments
[no name] 2-Mar-11 3:55am
   
What error it throws? have you find it using exception or not?
Mastersev 2-Mar-11 5:45am
   
there is no error, the multiple filters function wrong, sometimes I might have both or all of them with the IF statements.

It indeed won't filter correctly as soon as you use more then 1 filter criteria. Simply because you are constantly overriding the query variable with a new query variable instead of appending a filter to it.
   
Comments
Mastersev 2-Mar-11 5:44am
   
ok so how can I have multiple filters applied in one method?
Mastersev 2-Mar-11 20:43pm
   
thank you, you were right.
I found the solution.



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 c;
                if (NodeID != "")
                {
                    query = query.Where(c => c.NodeID == NodeID);
                }
                if (CallChannel != "")
                {
                    query = query.Where(c => c.CallChannel == CallChannel);
                }
                if (CallerID != "")
                {
                    query = query.Where(c => c.CallerID == CallerID);
                }
             var query2 = from c in query
                          join o in datacon.tbl_Nodes on c.NodeID equals o.NodeID 
                    select new { c.CallerID, c.CallChannel, c.CallDate, c.CallTime, c.NodeID, o.NodeName };
           
            return query2;
            
        }
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900