Tuesday, October 1, 2013

LINQ - SQL: validate null parameter and apply in where clause

I had the query

 var results =
                        from cs in skydeskDB.Cases
                        where
                        cs.AgentId == AgentId
                        && cs.ClosedAt >= iniDate
                        && cs.ClosedAt < endDate                        
                        select cs;

I wanted to avoid the agentid filter when the agentid filter were null, like

                  var results =
                        from cs in skydeskDB.Cases
                        where                        
                        cs.ClosedAt >= iniDate
                        && cs.ClosedAt < endDate                        
                        select cs;

and, when  agentid filter were different from null apply the agentid filter, like

var results =
                        from cs in skydeskDB.Cases
                        where
                        cs.AgentId == AgentId
                        && cs.ClosedAt >= iniDate
                        && cs.ClosedAt < endDate                        
                        select cs;

The solution is adding the condition (AgentId == null || cs.AgentId == AgentId):

var results =
                        from cs in skydeskDB.Cases
                        where
                        (AgentId == null || cs.AgentId == AgentId)
                        && cs.ClosedAt >= iniDate
                        && cs.ClosedAt < endDate                        

                        select cs;

now I can do both things with one single query:
 - avoid the agentid filter when the agentid filter were null
 - apply agentid filter when the agentid filter were different from null

Resource:
http://stackoverflow.com/questions/9505189/dynamically-generate-linq-queries

No comments: