Conditional Where Clause in EF Core-CodePudding

I'm trying to add a conditional where clause to a query depending on what model is asking for the associated Comments:

public List<Comments> GetCommentsByResource(string ResourceName, Guid ResourceID)
{
    var query = CommentsManager.GetQueryable();

    switch (ResourceName)
    {
        case "Bike":
            query.Where(x => x.BikeID == ResourceID);
            break;
        case "Skis":
            query.Where(x => x.SkiID == ResourceID);
            break;
        case "Helmet":
            query.Where(x => x.HelmetID == ResourceID);
            break;
        case "Scooter":
            query.Where(x => x.ScooterID == ResourceID);
            break;
    }

    return query.OrderByDescending(o => o.CreatedOn).ToList();
}

I can trace the execution and the switch statements are being correctly hit-- but when the query is executed, it isn't applying the where and the list returns all comments. What am I missing?

CodePudding user response：

You need to capture the result of the Where(...) and store it in a (new) variable.

public List<Comments> GetCommentsByResource(string ResourceName, Guid ResourceID)
{
    var query = CommentsManager.GetQueryable();

    switch (ResourceName)
    {
        case "Bike":
            query = query.Where(x => x.BikeID == ResourceID);
        //  ^^^^^^^
            break;
        case "Skis":
            query = query.Where(x => x.SkiID == ResourceID);
            break;
        case "Helmet":
            query = query.Where(x => x.HelmetID == ResourceID);
            break;
        case "Scooter":
            query = query.Where(x => x.ScooterID == ResourceID);
            break;
    }

    return query.OrderByDescending(o => o.CreatedOn).ToList();
}

You need to check if the result type of the where matches the result type of CommentsManager.GetQueryable();. You might explicitly declare the type instead of var query.

CodePudding user response：

Just set your query to the value of the conditional statement

public List<Comments> GetCommentsByResource(string ResourceName, Guid ResourceID)
{
var query = CommentsManager.GetQueryable();

switch (ResourceName)
{
    case "Bike":
        query = query.Where(x => x.BikeID == ResourceID);
        break;
    case "Skis":
        query = query.Where(x => x.SkiID == ResourceID);
        break;
    case "Helmet":
        query = query.Where(x => x.HelmetID == ResourceID);
        break;
    case "Scooter":
        query = query.Where(x => x.ScooterID == ResourceID);
        break;
}

return query.OrderByDescending(o => o.CreatedOn).ToList();

}

CodePudding user response：

The query have to be updated with the new condition, therefore query=query.Where(...) have to be used.

I also recommend you to use "if" instead of "switch" so that code is more extendable with the query with unlimited no. of conditions.

public List<Comments> GetCommentsByResource(int resourceNo, string resourceName)
{
    var query = CommentsManager.GetQueryable();

    if(resourceNo>-1)
       query = query.Where(x => x.ResourceID == resourceNo);
    if(resourceName=="A")
       query = query.Where(x => x.ResourceName == "A");
    .....

    return query.OrderByDescending(o => o.CreatedOn).ToList();
}