Using Contains() on an IQueryable built with Dapper (with MVCContrib Grid)

1.9k views Asked by At

I've got the following ActionResult in a ASP.NET MVC 3 project:

public ActionResult Index(string searchWord, GridSortOptions gridSortOptions, int? page)
{
    var userListModel = new PagedViewModel<UserModel>
    {
        ViewData = ViewData,
        Query = conn.Query<UserModel>("select Id, Login, Firstname, Lastname from User").AsQueryable(),
        GridSortOptions = gridSortOptions,
        DefaultSortColumn = "Id",
        Page = page,
        PageSize = 20
    }
    .AddFilter("searchWord", searchWord, u => u.Login.Contains(searchWord) || u.Firstname.Contains(searchWord) || u.Lastname.Contains(searchWord))
    .Setup();

    return View(userListModel);
}
  • The GridSortOptions and PagedViewModel classes are from MVCContrib as the view is using the Grid from MVCContrib to display the data.
  • conn is a standard SQLConnection talking to a SQL server database.
  • conn.Query is a SQLConnection extension method provided by Dapper which I'm using to get the data.
  • The Query property being filled in the PagedViewModel is a IQueryable type

If searchWord is null all works fine, but as soon as you add a searchWord the MVCContrib Grid falls over with an 'Object reference not set to an instance of an object' error. It works if I remove the .Contains) from the AddFilter method like this:

.AddFilter("searchWord", searchWord, u => u.Login == searchWord)

Anyone know a way around this, or why I can't use Contains with the Dapper IQueryable?

Thanks

Edit:

Thanks to Sam's answer this fixes it:

.AddFilter("searchWord", searchWord, u => (!string.IsNullOrEmpty(u.Login) && u.Login.Contains(searchWord))
1

There are 1 answers

0
Sam Saffron On BEST ANSWER

This is not really a Dapper issue.

You have a collection of objects, some of them have null properties and you are trying to hit them up with a .Contains

var users = new UserModel[] { new UserModel(); } 
// Login is null 
users.AsQueryable().where(u => u.Login.Contains("bob")); // kaboom