Editor() with conditional Where() [.NET MVC]

rdm

In my .NET MVC controller, I have a scenario with an Editor() with an optional Where() filter.

I have tested my code (shown below) and know precisely where the problem is. response.Where("Table1.CourseCompleted", bCompleted); You can see in the code comments what I have tried. Is there a correct approach in applying a conditional filter?

The overall ActionResult follows the pattern shown on ASP.NET MVC and Where Conditions.

I have not seen any specific examples for my type of scenario, so I tried an approach that I've used with Entity Framework: (1) define the response variable with the fields and where conditions that always apply, and then (2) apply where conditions only where optional functional parameters are used.

While I get no compile or runtime errors, the fact I get zero results when using the optional parameter tells me something is not working correctly. The database field is a bit data type (SQL Server) whereas the function parameter is a string ("Yes","No" or null), where null indicates that the optional parameter is not used.

What am I doing wrong? How can I correctly apply a where filter that should only be used on condition the function parameter is not null?

[AcceptVerbs(HttpVerbs.Get | HttpVerbs.Post)]
public ActionResult TestTableData(string completed)
{
    var settings = Properties.Settings.Default;
    var formData = HttpContext.Request.Form;
    
    // "completed" is a yes, no, or null string value   
    // translate "completed" to bool because the database field is bit          
    var bCompleted = completed == "Yes";

    /*
    * No results returned with each of these "bCompleted" definitions
    *     bCompleted as bit (true or false): var bCompleted = completed == "Yes";
    *     bCompleted as int (0 or 1): var bCompleted = completed == "Yes"?1:0;
    *     bCompleted as string ("true" or "false"): var bCompleted = completed == "Yes"?"true":"false";
    *     bCompleted as string ("0" or "1"): var bCompleted = completed == "Yes"?"1":"0";
    */
    
    using (var db = new Database(settings.DbType, settings.DbConnection))
    {
        var response = new Editor(db, "Table1", "Id")
            /* ... more fields */
            
            .LeftJoin("Table2", "Table2.Id","=","Table1.Table2Id");

        if (!string.IsNullOrEmpty(completed))
        {
            // The optional filter is applied here.
            response = response.Where("Table1.CourseCompleted", bCompleted);
        }

        return Json(response.Process(formData).Data(), JsonRequestBehavior.AllowGet);
    }

rdm

I figured out how to simply the where code by using this approach. I think the issue might be making the proper cast to SQL bit type.

I've tried casting as int (0 or 1), boolean (true or false), string ("0" or "1"), and string ("true" or "false").

The question is now how to correctly cast this SQL bit field so that the filter correctly applies.

.Where(q =>
     {
          if (string.IsNullOrEmpty(completed)) return;
          
          Debug.WriteLine($"Completed is not null: {bCompleted}");
          q.Where("FridayPlanning.CourseCompleted", bCompleted);
     })

rdm

I think I have the correct solution:

First, convert the "Yes" or "No" to "true" or "false".

var bCompleted = completed == "Yes" ? "true":"false";

Second (which I already had in place by now), use the lambda definition of where:

.Where(q =>
    {
        if (string.IsNullOrEmpty(completed)) return;
        q.Where("Table1.CourseCompleted", bCompleted);
    })

So going back to the code sample at the very top, it should look like this:

[AcceptVerbs(HttpVerbs.Get | HttpVerbs.Post)]
public ActionResult TestTableData(string completed)
{
    var settings = Properties.Settings.Default;
    var formData = HttpContext.Request.Form;

    var bCompleted = completed == "Yes"?"true":"false"; 
     
    using (var db = new Database(settings.DbType, settings.DbConnection))
    {
        var response = new Editor(db, "Table1", "Id")
            /* ... more fields */
             
            .LeftJoin("Table2", "Table2.Id","=","Table1.Table2Id")
            .Where(q =>
             {
                  if (string.IsNullOrEmpty(completed)) return;                 
                  
                  q.Where("Table1.CourseCompleted", bCompleted);
             });
 
        return Json(response.Process(formData).Data(), JsonRequestBehavior.AllowGet);
    }