Dynamic query problem with LINQ to SQL

Question

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 1-Mar-11 21:49pm

Mastersev

Add a Solution

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.

2 solutions

Solution 2

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

Posted 2-Mar-11 14:42pm

Mastersev

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

JV9999 · Accepted Answer · 2011-03-01T21:58:00

Solution 1

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.

Posted 1-Mar-11 21:58pm

JV9999

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.