I have a table called passenger policy that looks like this:

    public Guid HotelId { get; set; }
    
    public int FromAge { get; set; }
    public int ToAge { get; set; }
    public PassengerType PassengerType { get; set; }

and it has 3 rows for each HotelId key.

I have another table called search that looks like this

 public class Search : BaseEntity
 {
     public DateTime Date { get; set; }

     public Guid CountryId { get; set; }
     public Guid ProvinceId { get; set; }
     public Guid CityId { get; set; }
     public Guid HotelId { get; set; }
     public Guid VendorHotelRoomId { get; set; }
     public int StandardCapacity { get; set; }
     public int ExtraCapacity { get; set; }

     public int MaxInfantAge { get; set; }
     public int MaxChild1Age { get; set; }
     public int MaxChild2Age { get; set; }

     public double BasePrice { get; set; }
     public double ExtraAdultPrice { get; set; }
     public double ExtraInfantPrice { get; set; }
     public double ExtraChild1Price { get; set; }
     public double ExtraChild2Price { get; set; }
}

I want to write a query in T-SQL (SQL Server) to get hotels based on date field, standard capacity and extra capacity.

The extra capacity has 3 possible values:

1. infant
2. child 1
3. child 2

• (fetched from passenger type table)

I write it like this in EF Core

var searchOnAllVendors hotelContext.Search
                                   .Where(c => c.Date >= fromDate 
                                               && c.Date <= toDate 
                                               && c.CityId == cityId
                                               && c.ExtraCapacity >= adultCount)
                                   .AsEnumerable();

foreach (var item in searchOnAllVendors)
{
    foreach (var ag in request.Passengers.ChildrensAges)
    {
        if (ag <= item.MaxInfantAge && ag < item.MaxChild1Age && ag < item.MaxChild2Age)
            infant  ;

        if (ag > item.MaxInfantAge && ag <= item.MaxChild1Age)
            child1Count  ;

        if (ag > item.MaxChild1Age && ag <= item.MaxChild2Age)
            child2Count  ; 
                    
        if (ag > item.MaxChild1Age && ag <= item.MaxChild2Age)
            extraAdult  ;
    }

    if (item.MaxInfantAge >= infant && item.MaxChild1Age >= child1Count && item.MaxChild2Age >= child2Count)
    {
        var adulPrice = extraAdult * item.ExtraAdultPrice;
        var infantPrice = infant * item.ExtraInfantPrice;
        var child1Price = child1Count * item.ExtraChild1Price;
        var child2Price = child1Count * item.ExtraChild2Price;

        var finalPrice = adulPrice   infantPrice   child1Price   child2Price   item.BasePrice;

        searches.Add(new Search_Response
                         {
                             CityId = item.CityId,
                             CountryId = item.CountryId,
                             HotelId = item.HotelId,
                             ProvinceId = item.ProvinceId,
                             VendorHotelRoomId = item.VendorHotelRoomId,
                             Price = finalPrice
                         });
    }
}

CodePudding user response：

after couple days and try a few things i find a way to have best performance in T-SQL ... 1. Get count of extra passernger types in scalar-valued functions like this :

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetChildRule](@ages as nvarchar(max),@min as int,@max as int)
RETURNS int
AS
BEGIN
declare @count int

select @count=count(*) from STRING_SPLIT(@ages,',')
where value > @min and value <= @max
RETURN @count
END

...

and use it in stored procedure like a field as below :

select * , GetChildRule('1,2,3',mymin,mymax) from Search where date between date1 and date2

and call it in EF CORE:

Context.Set<YourModelWithAllOfFiledYouReturnInSP>()
.FromSqlRaw($"EXEC  [dbo].[StaySearch] @extraAges = N'{ages}', 
@checkInDate = N'{fromDate}', @checkOutDate = N'{toDate}',
@destinationId = '{destinationId}',
@countrySearch = '{countrysearch}',@adultCount={adultCount}");