ASP.Net Bootgrid Integration (without sorting)

Question

I've been struggling for days now trying to implement jQuery Bootgrid with my ASP.Net application. So far this is what I have: (Order By Functionality isn't working yet, I'll tackle that later)

public JsonResult IndexJson(RequestData model)
{
    var result = (from x in db.ContactSet
        select new
        {
            x.AccountId,
            x.FirstName,
            x.LastName,
            x.FullName,
            x.JobTitle,
            x.ParentCustomerId,
            x.EMailAddress1,
            x.Telephone1,
            x.MobilePhone,
            x.Fax,
            x.GenderCode,
            x.BirthDate
        }); //? Gets all rows

    result = (from x in result
        where x.FirstName.Contains(model.searchPhrase)
            || x.LastName.Contains(model.searchPhrase)
        select x); //? Search Filter

    var totalRows = result.Count(); //? Sets totalRows (for ResponseData)

    if (model.rowCount == -1)
        model.rowCount = totalRows; //? In case View All Rows is selected by Bootgrid (for ResponseData)

    // TODO: Add Order By functionality

    var tResult = new ResponseData<object>()
    {
        current = model.current,
        rowCount = model.rowCount,
        rows = result.ToList(),
        total = totalRows
    }; //? Builds Json Response

    return Json(tResult, JsonRequestBehavior.AllowGet);
}

The problem with this code is I need to count the total number of records after the search functionality and I'm just not that skilled at using the LINQ Queries properly.

By the time I get to var totalRows = result.Count(); I get the following error:

System.NotSupportedException: 'The method 'Where' cannot follow the method 'Select' or is not supported. Try writing the query in terms of supported methods or call the 'AsEnumerable' or 'ToList' method before calling unsupported methods.'

Any idea what's wrong here?

Answer 1

I have been using bootgrid in difference situations, including implementing server-side paging and sorting asc, desc without any problem.

Try this:

//Let's assume this is your model....
public class RequestData
{
    public int RowCount { get; set; }
    public int Current { get; set; }
    public string Search { get; set; }
    public string SortBy { get; set; }
    public string SortDirection { get; set; }
    public int TotalItems { get; set; }
}

1.If you are not selecting all the columns of your DB table, create a DTO that will map your selected columns. Otherwise skip this part and replace anywhere you see ContactSetDTO with ContactSet

public class ContactSetDTO
{
    public string AccountId { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string FullName { get; set; }         
    public string JobTitle { get; set; }         
    public string ParentCustomerId { get; set; }
    public string EMailAddress1 { get; set; }
    public string Telephone1 { get; set; }
    public string MobilePhone { get; set; }
    public string Fax { get; set; }
    public string GenderCode { get; set; }
    public DateTime BirthDate { get; set; }
}

2.Assuming you are using SQL Server, you could use the following method to retrieve the count:

 public int getContactSetCount(string searchPhrase)
 {
    int ret = 0;
    try
    {
        string query = string.Empty;
        if (!string.IsNullOrWhiteSpace(searchPhrase))
        {
            // ********* Assuming your db table is also called ContactSet **********************
            query = @"SELECT COUNT(*) FROM ContactSet s WHERE s.FirstName LIKE '%' + @p0 + '%' OR s.LastName LIKE '%' + @p0 + '%')";
            ret = db.Database.SqlQuery<int>(query, new System.Data.SqlClient.SqlParameter(parameterName: "@p0", value: searchPhrase)).FirstOrDefault();
        }
        else
        {
            ret = db.ContactSet.Count();
        }
    }
    catch (Exception)
    {
         throw;
    }

    return ret;
}

3.And finally, your method would look like this:

public JsonResult IndexJson(RequestData model)
{
     var searchPhrase = model.Search;

     if (!string.IsNullOrWhiteSpace(searchPhrase))
     {
         //Notice that the select columns match the ContactSetDTO properties
         string query = @"SELECT TOP " + model.RowCount + " s.AccountId, s.FirstName, s.LastName, s.FullName, s.JobTitle, s.ParentCustomerId, s.EmailAddress1, s.Telephone1, s.MobilePhone, s.Fax, s.GenderCode, s.BirthDate FROM ContactSet s WHERE s.FirstName LIKE '%' + @p0 + '%' OR s.LastName LIKE '%' + @p0 + '%')";

   //Then, this should return a list of ContactSetDTO for you
    var result = db.Database.SqlQuery<ContactSetDTO>(query, new System.Data.SqlClient.SqlParameter(parameterName: "@p0", value: searchPhrase)).ToList();

     var totalRows = getContactSetCount(searchPhrase);

     var tResult = new { rows = result, rowCount = model.RowCount, total = totalRows, current = model.Current, searchPhrase = model.Search }
    };

    return Json(tResult, JsonRequestBehavior.AllowGet);
}

I hope the above will help.