ServiceStack Ormlite SqlExpressionVisitor null check in Where extension

Question

I would like to write a method for querying table with one method by null cheking parameters using SqlExpressionVisitor of Ormlite Here is my method :

public static List<UserChatsDTO> GetUserChats(int startRow, int rowCount, DateTime? startDate, DateTime? endDate, string operatorName, short? rating, string visitorName)
        {
            using (IDbConnection db = DbFactory.OpenDbConnection())
            {
                SqlExpressionVisitor<UserChatsDTO> ev = OrmLiteConfig.DialectProvider.ExpressionVisitor<UserChatsDTO>();
                ev.Where(q => 
                    (startDate.HasValue && q.Dated >= startDate) && 
                    (endDate.HasValue && q.Dated <= endDate) &&
                    (!string.IsNullOrEmpty(operatorName) && q.TakenByUser.Contains(operatorName)) &&
                    (rating.HasValue && q.Rating == (short)rating) &&
                    (!string.IsNullOrEmpty(visitorName) && q.VisitorName.Contains(visitorName)));
                //ev.OrderBy();
                ev.Limit(startRow, rowCount);
                return db.Select<UserChatsDTO>(ev);
            }
        }

But Object reference not set to an instance of an object. NullReferenceException is thrown when i call ev.Where part.

Is there a bug here or i am missing something ? Thank you.

Answer 1

You can actually build up the ExpressionVisitor inside the Select method like so:

var chats = db.Select<UserChatsDTO>(q => q
    .Where(x => startDate.HasValue && x.Date >= startDate)
    .Where(x => endDate.HasValue && x.Date <= endDate)
    .Where(x => string.IsNullOrEmpty(operatorName) || x.TakeByUser.Contains(operatorName))
    .Where(x => rating.HasValue && x.Rating == (short)rating)
    .Where(x => string.IsNullOrEmpty(visitorName) || x.VisitorName.Contains(visitorName)
    .Limit(startRow, rowCount));

Answer 2

I know this question is 7 months old but I had a similar issue & this was the first question that came up when I searched. I wanted to add my working solution since Master Morality's didn't work for me.

Originally, I tried syntax roughly similar to mustafasturan's first attempt. I got the same NullReferenceException he did. Master Morality's answer did not help either...

I'm trying to build a search function that performs LIKE searches rather than exact match. There are multiple criteria on the request object which may or may not be null (for simplicity's sake we'll use an example with 2 criteria). Following Master Morality's suggestion, I tried this:

var searchResults = db.Select<Item>(q => q
        .Where(x => string.IsNullOrWhiteSpace(request.Criteria1) || x.Criteria1.Contains(request.Criteria1))
        .Where(x => string.IsNullOrWhiteSpace(request.Criteria2) || x.Criteria2.Contains(request.Criteria2))
    );

I still got a NullReferenceException. Seems like the && and || operators are not using short-circuit evaluation inside the lambda expression.

What I finally had to go with is this:

SqlExpressionVisitor<Item> ev = new ServiceStack.OrmLite.MySql.MySqlExpressionVisitor<Item>();

if (!String.IsNullOrWhiteSpace(request.Criteria1)) {
    ev.Where(q => q.Criteria1.Contains(request.Criteria1));
}
if (!String.IsNullOrWhiteSpace(request.Criteria2)) {
    ev.Where(q => q.Criteria2.Contains(request.Criteria2));
}

searchResults = db.Select<Item>(ev);

It doesn't feel very elegant, but it's the only thing I could find that works.