How to write a If condition inside a Where clause in ef core-CodePudding

I am working in a asp.net core web API with EF Core version 5.0.

I wrote a query with a if condition.

if (incomingFacilities.Count() > 0)

then I need to filter the facilities those are in the incomingFacilities list.

If (incomingFacilities.Count() < 0) I do not need to filter. I have to get only 6 distinct facility list.

But write below code. The issue is I need to repeat same code again and again.

So I thing It is better to the if condition inside

.Where(x => (incomingFacilities.Count() > 0 ) ? 
            (incomingFacilities.Contains(x.FacilityCode)) :
            (x.FacilityCode > 0 )),

Here I did the First part correctly, (ie: If incomingFacilities.Count() > 0 then do filter.

But the second part is if incomingFacilities.Count() < 0 then there's no need to filter, just send the list of facilities (only no of 6 facilities those are distinct)

// this code is correct, but same code repeated 
if (incomingFacilities.Count() > 0)
{
    var hotels = await _context.Hotels
        .Where(i => (i.DestinationCode == request.Destination))
        .Select(i => new HotelListHotelVm
        {
            // removed some
            HotelFacilities =  i.Facilities.Select(x => new HotelListHotelVm.HotelListFacilityVm {
            Id = x.Id,
            FacilityGroupCode = x.FacilityGroupCode,
            HotelFacilityGroupDescription = x.FacilityGroup.Description,
            FacilityCode = x.FacilityCode
        })
        .Where(x => (incomingFacilities.Count() > 0 ) ? (incomingFacilities.Contains(x.FacilityCode)) : (x.FacilityCode > 0 )),
        })
        // rest
        ;
}
else
{
    var hotels = await _context
        .Hotels
        .Where(i => (i.DestinationCode == request.Destination))
        .Select(i => new HotelListHotelVm
        {
            // removed some
            HotelFacilities = i.Facilities.ToList().Distinct().Take(6)           
                .Select(x => new HotelListHotelVm.HotelListFacilityVm {          
                    Id = x.Id,                                                      
                    FacilityGroupCode = x.FacilityGroupCode,                                                     
                    HotelFacilityGroupDescription = x.FacilityGroup.Description,          
                    FacilityCode = x.FacilityCode                                           
                })
        })
        // rest
        ;
}

.Where(x =>(incomingFacilities.Count() > 0 ) ? (incomingFacilities.Contains(x.FacilityCode)) : (x.FacilityCode > 0 )), Here,

I did If incomingFacilities.Count() > 0 then, use where clause.

But I don't know how to say, If not incomingFacilities.Count() > 0 then do a list of facilities with distinct value and take only 6. Like this HotelFacilities = i.Facilities.ToList().Distinct().Take(6)

    .Select(x => new HotelListHotelVm.HotelListFacilityVm {          
        Id = x.Id,                                                      
        FacilityGroupCode = x.FacilityGroupCode,                                                     
        HotelFacilityGroupDescription = x.FacilityGroup.Description,          
        FacilityCode = x.FacilityCode                                           
    })

CodePudding user response：

EF Core query translator is smart enough to evaluate expressions which use pure client side arguments and remove filter expressions which are known to be true in advance, like true || some_expr.

So all you need is to write your filter as follows:

.Where(x => !incomingFacilities.Any() || incomingFacilities.Contains(x.FacilityCode))

This will produce two different SQL queries - one with filter and one without depending of whether the parameter incomingFacilities contains elements or not.

CodePudding user response：

Good morning,

if I understand you correctly you want to get a List<HotelListHotelVm>, but the content changes if there are elements in incomingFacilities.

First I would get rid of the .Count() extension here and use .Any() instead. This is for performance reasons, as .Count() would only stop once you have iterated over the whole list. .Any() instead stops once it has found one element in your incomingFacilities and returns the respective bool if there is anything in your list, or not. If you don't need the count of items of the incomingFacilities list and only check if there is anything in it .Any() is the better choice (like in your code). If you really need the count value, then use .Count().

Based on your comments, try the following (again, untested). First initialize a variable which holds your hotels as a list (or an array, or whatever). Then you check if you have any incomingFacilities. If so, you get the values from the database and compare it against the facility code. After that you filter your list for all elements which indeed HAVE HotelFacilities. If there are some values in it, hotels is not empty.

In the next step you look into the hotels list. If you have values in it, you don't have to retrieve anything from the database, if not - you get the first 6 elements from the database.

If incomingFacilities is empty from the beginning, so is hotels and therefore the code will retrieve 6 elements from the database.

I hope I understood it correctly now.

List<HotelListHotelView> hotels = new List<HotelListHotelView>(); 

if (incomingFacilities.Any())
{
    hotels = await _context.Hotels
                           .Where(i => (i.DestinationCode == request.Destination))
                           .Select(i => new HotelListHotelVm
                           {
                               HotelFacilities =  i.Facilities.Where(x => incomingFacilities.Contains(x.FacilityCode))
                                                              .Select(x => new HotelListHotelVm.HotelListFacilityVm 
                                                                                   {
                                                                                       Id = x.Id,
                                                                                       FacilityGroupCode = x.FacilityGroupCode,
                                                                                       HotelFacilityGroupDescription = x.FacilityGroup.Description,
                                                                                       FacilityCode = x.FacilityCode
                                                                                   })
                           }).Where(h => h.HotelFacilities.Any())
                             .ToList();   
}

if(!hotels.Any())
{             
    hotels = await _context.Hotels
                           .Where(i => (i.DestinationCode == request.Destination))
                           .Select(i => new HotelListHotelVm
                           {
                                   HotelFacilities = i.Facilities.Distinct()
                                                                 .Take(6)           
                                                                 .Select(x => new HotelListHotelVm.HotelListFacilityVm 
                                                                     {          
                                                                         Id = x.Id,                                                      
                                                                         FacilityGroupCode = x.FacilityGroupCode,                                                     
                                                                         HotelFacilityGroupDescription = x.FacilityGroup.Description,          
                                                                         FacilityCode = x.FacilityCode                                           
                                                                     })
                           }).ToList();
}