Click here to Skip to main content
15,901,035 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.
 
Share this answer
 
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;
            
        }
 
Share this answer
 

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