Filtering search results on column on a different table

Question

I am a novice at c# and MVC. I have seen a few similar questions to my problem, but nothing that has helped me address this problem yet. I'm looking for some more specific guidance.

I am trying to filter search results using a specific column in a related table. I am displaying a list of Shifts. Each shift has StoreNum as a foreign key. StoreNum is the primary key for the Stores table. The Stores table contains, among other things, a column called Area. I want the user to be able to click a checkbox and have the results filter on Area.

Here is my method in my controller (I have omitted some code that currently works and seems unrelated):

        [HttpGet]
    public ActionResult OpenShiftList(DateTime? searchStartDate = null, DateTime? searchEndDate = null, DateTime? searchStartTime = null, DateTime? searchEndTime = null, Boolean? searchStore = null, Boolean? searchArea = false, Boolean? searchDistrict = false)
    {
        var thisStore = User.StoreNum();
        var data =
                    from s in db.Shifts
                   select s;
            if (searchDistrict == true)
        {
            data = data.Where(s => db.Stores.Select(x => x.District.Where(x.StoreNum == thisStore)));
        }

        data = data.Where(s => s.IsCovered.Equals(false));
         return View(data.ToList());
    }

I am being given the error "Argument 2: cannot convert 'bool' to 'System.Func<char, bool>'

For reference, here is the HTML in my view that relates to this:

        <div class="col-md-2 well">
        <div class="form-group">
            @using (Html.BeginForm("OpenShiftList", "Shifts", FormMethod.Get))
            {
                <p>
                    Date @Html.TextBox("searchStartDate", "", new { @class = "date-picker" }) to @Html.TextBox("searchEndDate", "", new { @class = "date-picker" })
                </p><p>
                Start Time @Html.TextBox("searchStartTime", "", new { @class = "timepicker" })
                </p><p>
                    End Time @Html.TextBox("searchEndTime", "", new { @class = "timepicker" })
                </p><p>
                    My Store @Html.CheckBox("searchStore")
                </p><p>
                    My District @Html.CheckBox("searchDistrict")
            </p><p>
                    My Area     @Html.CheckBox("searchArea")
            </p><p>
                    <input type="submit" value="Search" class="btn btn-primary btn-md" />
                </p>
                }
    </div>
</div>
    </div>

Is there a way to achieve this filtering goal with the way I currently have things set up? Do I need to use a viewmodel instead?

EDIT Here is the class for Shift

public int ShiftID { get; set; }
    [DisplayFormat(DataFormatString = "{0:MM/dd/yyyy}", ApplyFormatInEditMode =true)]
    public System.DateTime Date { get; set; }
    [DisplayFormat(DataFormatString = "{0:HH:mm tt}", ApplyFormatInEditMode = true)]
    [DataType(DataType.Time)]
    [Display(Name="Start Time")]
    public System.DateTime StartTime { get; set; }
    [DisplayFormat(DataFormatString = "{0:HH:mm tt}", ApplyFormatInEditMode = true)]
    [DataType(DataType.Time)]
    [Display(Name ="End Time")]
    public System.DateTime EndTime { get; set; }
    [Display(Name ="Store Number")]
    public string StoreNum { get; set; }
    public string Id { get; set; }
    [Display(Name ="Covered")]
    public bool IsCovered { get; set; }

And here is the class for Store:

    [Display(Name="Store Number")]
    public string StoreNum { get; set; }
    [Display(Name = "Store Name")]
    public string StoreName { get; set; }
    [Display(Name = "Store Address")]
    public string StreetAddr { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string District { get; set; }
    public string Area { get; set; }

Thanks for any guidance and feedback on this and any other part of my code... it helps me learn!

Answer 1

I think you want to join Shift and Store first on StoreNum to get each store's shifts. You can build your where clause before the join.

Because you're querying against a database we'll need to build Expressions.

Expression<Func<StoreShift, bool>> hasStoreNum = (s) => s.Shift.StoreNum == storeNum;
Expression<Func<StoreShift, bool>> hasArea = (s) => s.Shift.Area == s.Store.Area;
Expression<Func<StoreShift, bool>> hasDistrict = (s) => s.Shift.District == s.Store.District;

var predicates = new List<Expression<Func<StoreShift, bool>>>();
predicates.Add(hasStoreNum);

if (searchArea)
{
    predicates.Add(hasArea);
}
if (searchDistrict)
{
    predicates.Add(hasDistrict);
}

var query = from shift in db.Shifts
            join store in db.Stores on shift.StoreNum equals store.StoreNum
            select new StoreShift { Store = store, Shift = shift };

var result = query.WhereAny(predicates).Select(s => s.Shift);

You'll need some helper classes

public class StoreShift
{
    public Store Store { get; set; }
    public Shift Shift { get; set; }
}

I got the implementation for the classes below here

public static class QueryableExtensions
{
    public static IQueryable<T> WhereAny<T>(this IQueryable<T> source, IEnumerable<Expression<Func<T, bool>>> predicates)
    {
        if (predicates == null || !predicates.Any()) return source;

        var predicate = predicates.Aggregate((a, b) => Expression.Lambda<Func<T, bool>>(
            Expression.Or(a.Body, b.Body.ReplaceParameter(b.Parameters[0], a.Parameters[0])),
            a.Parameters[0]));
        return source.Where(predicate);
    }
}
public static class ExpressionUtils
{
    public static Expression ReplaceParameter(this Expression expression, ParameterExpression source, Expression target)
    {
        return new ParameterReplacer { Source = source, Target = target }.Visit(expression);
    }

    class ParameterReplacer : ExpressionVisitor
    {
        public ParameterExpression Source;
        public Expression Target;
        protected override Expression VisitParameter(ParameterExpression node)
        {
            return node == Source ? Target : base.VisitParameter(node);
        }
    }
}

This was the simplest Expression builder implementation I could find. There are also NuGet libraries that help you build Expressions dynamically.

Answer 2

I managed to solve this issue using Jasen's idea of performing a join.

            var GetArea = from shift in db.Shifts
                       join store in db.Stores on shift.StoreNum equals store.StoreNum
                       where store.StoreNum == thisStore
                       select store.Area;
            var thisArea = GetArea.First();

This allowed me to store the Area for the user's Store. Then...

            if (searchArea == true)
        {
            data = from shift in db.Shifts
                               join store in db.Stores on shift.StoreNum equals store.StoreNum
                               where store.Area.Contains(thisArea)
                               select shift;
        }

This allowed me to filter for Area. I'm sure there is a more elegant solution than mine, but this works.