Tuesday, May 3, 2011

SubSonic 2.1 SqlQuery multiple Where()s?

I'm building a SqlQuery to support an ad-hoc query screen.

I want something like this:

SqlQuery q = new Select().From<VwInstitutes>();
if (!string.IsNullOrEmpty(username))
{
    q.Where(VwInstitutes.Columns.AssignedUser).IsEqualTo(username); 
}

if (!string.IsNullOrEmpty(stage))
{
    q.Where(VwInstitutes.Columns.Stage).IsEqualTo(stage); 
}

My problem is--attaching multiple Where()s doesn't seem to work--is that correct?

So now I'm writing this, but it's pretty ugly.

if (!string.IsNullOrEmpty(username)) 
{ 
    if (q.HasWhere) q.And(VwInstitutes.Columns.AssignedUser).IsEqualTo(username); 
    else q.Where(VwInstitutes.Columns.AssignedUser).IsEqualTo(username); 
}

if (!string.IsNullOrEmpty(stage))
{
    if (q.HasWhere) q.And(VwInstitutes.Columns.Stage).IsEqualTo(stage); 
    else q.Where(VwInstitutes.Columns.Stage).IsEqualTo(stage);
}

Please tell me that there is a better idiom for this scenario. Thanks!

From stackoverflow
  • Start your query with a where that always evaluates to true.

    new Select().From().Where("1").IsEqualTo("1");
    

    then build the rest of the query with q.And. See this thread as well.

0 comments:

Post a Comment