I'm having a bit of the trouble with translating following stored procedure to into LINQ.
SELECT DISTINCT a0.[recid] AS 'Id'
,a0.[latitude] AS 'Latitude'
,a0.[longitude] AS 'Longitude'
,a0.[countryregionid] AS 'CountryCode'
,a0.[level_] as 'Level'
,case
when wht.[disinfection] is null
then 0
else wht.[disinfection]
end as 'Disinfection'
,case when wd1.[fromtime] is null then '' else CONVERT(varchar, DATEADD(ms, wd1.[fromtime] * 1000, 0), 108) end as 'MondayOpen'
,case when wd1.[totime] is null then '' else CONVERT(varchar, DATEADD(ms, wd1.[totime] * 1000, 0), 108) end as 'MondayClose'
,case when wd2.[fromtime] is null then '' else CONVERT(varchar, DATEADD(ms, wd2.[fromtime] * 1000, 0), 108) end as 'TuesdayOpen'
,case when wd2.[totime] is null then '' else CONVERT(varchar, DATEADD(ms, wd2.[totime] * 1000, 0), 108) end as 'TuesdayClose'
,case when wd3.[fromtime] is null then '' else CONVERT(varchar, DATEADD(ms, wd3.[fromtime] * 1000, 0), 108) end as 'WednesdayOpen'
,case when wd3.[totime] is null then '' else CONVERT(varchar, DATEADD(ms, wd3.[totime] * 1000, 0), 108) end as 'WednesdayClose'
,case when wd4.[fromtime] is null then '' else CONVERT(varchar, DATEADD(ms, wd4.[fromtime] * 1000, 0), 108) end as 'ThursdayOpen'
,case when wd4.[totime] is null then '' else CONVERT(varchar, DATEADD(ms, wd4.[totime] * 1000, 0), 108) end as 'ThursdayClose'
,case when wd5.[fromtime] is null then '' else CONVERT(varchar, DATEADD(ms, wd5.[fromtime] * 1000, 0), 108) end as 'FridayOpen'
,case when wd5.[totime] is null then '' else CONVERT(varchar, DATEADD(ms, wd5.[totime] * 1000, 0), 108) end as 'FridayClose'
,case when wd6.[fromtime] is null then '' else CONVERT(varchar, DATEADD(ms, wd6.[fromtime] * 1000, 0), 108) end as 'SaturdayOpen'
,case when wd6.[totime] is null then '' else CONVERT(varchar, DATEADD(ms, wd6.[totime] * 1000, 0), 108) end as 'SaturdayClose'
,case when wd7.[fromtime] is null then '' else CONVERT(varchar, DATEADD(ms, wd7.[fromtime] * 1000, 0), 108) end as 'SundayOpen'
,case when wd7.[totime] is null then '' else CONVERT(varchar, DATEADD(ms, wd7.[totime] * 1000, 0), 108) end as 'SundayClose'
FROM [dbo].[address] AS a0
LEFT JOIN GIR_WashhouseTable as wht on wht.[recid] = a0.[addrrecid]
OUTER APPLY(
SELECT TOP 1
awt0.[fromtime]
, awt0.[totime]
FROM [GIR_AddressWorkTime] as awt0
WHERE awt0.[dataareaid] = 'v22'
AND awt0.[active] = 1
AND awt0.[weekday] = 1
AND awt0.[refrecid] = a0.[recid]
) as wd1
OUTER APPLY(
SELECT TOP 1
awt0.[fromtime]
, awt0.[totime]
FROM [GIR_AddressWorkTime] as awt0
WHERE awt0.[dataareaid] = 'v22'
AND awt0.[active] = 1
AND awt0.[weekday] = 2
AND awt0.[refrecid] = a0.[recid]
) as wd2
OUTER APPLY(
SELECT TOP 1
awt0.[fromtime]
, awt0.[totime]
FROM [GIR_AddressWorkTime] as awt0
WHERE awt0.[dataareaid] = 'v22'
AND awt0.[active] = 1
AND awt0.[weekday] = 3
AND awt0.[refrecid] = a0.[recid]
) as wd3
OUTER APPLY(
SELECT TOP 1
awt0.[fromtime]
, awt0.[totime]
FROM [GIR_AddressWorkTime] as awt0
WHERE awt0.[dataareaid] = 'v22'
AND awt0.[active] = 1
AND awt0.[weekday] = 4
AND awt0.[refrecid] = a0.[recid]
) as wd4
OUTER APPLY(
SELECT TOP 1
awt0.[fromtime]
, awt0.[totime]
FROM [GIR_AddressWorkTime] as awt0
WHERE awt0.[dataareaid] = 'v22'
AND awt0.[active] = 1
AND awt0.[weekday] = 5
AND awt0.[refrecid] = a0.[recid]
) as wd5
OUTER APPLY(
SELECT TOP 1
awt0.[fromtime]
, awt0.[totime]
FROM [GIR_AddressWorkTime] as awt0
WHERE awt0.[dataareaid] = 'v22'
AND awt0.[active] = 1
AND awt0.[weekday] = 6
AND awt0.[refrecid] = a0.[recid]
) as wd6
OUTER APPLY(
SELECT TOP 1
awt0.[fromtime]
, awt0.[totime]
FROM [GIR_AddressWorkTime] as awt0
WHERE awt0.[dataareaid] = 'v22'
AND awt0.[active] = 1
AND awt0.[weekday] = 7
AND awt0.[refrecid] = a0.[recid]
) as wd7
WHERE a0.[dataareaid] = 'v22'
AND a0.[active] = 1
AND a0.[level_] <> 0
AND a0.[type] = 153
AND a0.[ADDRTABLEID] = 52411
AND a0.[countryregionid] IN
('AT', 'BE', 'BG', 'HR', 'CZ', 'EE', 'FR', 'DE', 'GR', 'HU', 'IE', 'IT', 'LV', 'LT', 'LU', 'NL', 'PL', 'PT', 'RO', 'SK', 'SI', 'ES', 'LI', 'MC', 'SM', 'CH', 'GB', 'DK') -- AIO-833
order by COUNTRYREGIONID
so the problem here is outer apply. I've researched ways to mimic outer apply via LINQ and seen that let keyword helps you do that. but in my case it was not useful. so I tried to do it like this
var query =
from address in _context.Address
join washHouse in _context.GirWashhousetable
on address.Addrrecid equals washHouse.Recid into whtGroup
from washHouse in whtGroup.DefaultIfEmpty()
join workTime in _context.GirAddressworktime
on address.Recid equals workTime.Refrecid into workTimeGroup
from workTime in workTimeGroup.DefaultIfEmpty()
where address.Dataareaid == "v22"
&& address.Active == 1
&& address.Level != 0
&& address.Type == 153
&& address.Addrtableid == 52411
&& _countryCodesForWashStations.Contains(address.Countryregionid)
&& workTime.Dataareaid == "v22"
&& workTime.Active == 1
let mondayWorkTime = workTimeGroup.FirstOrDefault(wt => wt.Weekday == 1)
// Repeat similar logic for other days...
select new WashingStationsResult
{
Id = address.Recid,
Latitude = address.Latitude,
Longitude = address.Longitude,
CountryCode = address.Countryregionid,
Level = address.Level,
Disinfection = washHouse.Disinfection,
MondayOpen = mondayWorkTime != null ? TimeSpan.FromSeconds(mondayWorkTime.Fromtime).ToString(@"hh\:mm\:ss") : string.Empty,
MondayClose = mondayWorkTime != null ? TimeSpan.FromSeconds(mondayWorkTime.Totime).ToString(@"hh\:mm\:ss") : string.Empty,
// Repeat similar logic for other days...
};
It works if there is only one let but if I repeat some for other days also Im getting the empty list as a response. Im not sure why. can someone suggest what could be the best way to solve this problem?
For OUTER APPLY you have to use different technique: Collection selector references outer in a non-where case
var query =
from address in _context.Address
join washHouse in _context.GirWashhousetable
on address.Addrrecid equals washHouse.Recid into whtGroup
from washHouse in whtGroup.DefaultIfEmpty()
from mondayWorkTime in _context.GirAddressworktime
.Where(workTime => address.Recid == workTime.Refrecid
&& workTime.Dataareaid == "v22"
&& workTime.Active == 1
)
.Take(1)
.DefaultIfEmpty()
// Repeat similar logic for other days...
where address.Dataareaid == "v22"
&& _countryCodesForWashStations.Contains(address.Countryregionid)
&& address.Active == 1
&& address.Level != 0
&& address.Type == 153
&& address.Addrtableid == 52411
select new WashingStationsResult
{
Id = address.Recid,
Latitude = address.Latitude,
Longitude = address.Longitude,
CountryCode = address.Countryregionid,
Level = address.Level,
Disinfection = washHouse.Disinfection,
MondayOpen = mondayWorkTime != null ? TimeSpan.FromSeconds(mondayWorkTime.Fromtime).ToString(@"hh\:mm\:ss") : string.Empty,
MondayClose = mondayWorkTime != null ? TimeSpan.FromSeconds(mondayWorkTime.Totime).ToString(@"hh\:mm\:ss") : string.Empty,
// Repeat similar logic for other days...
};
For simplyfying query, you can prepare recordset with predefined filters:
var workTimes = _context.GirAddressworktime
.Where(workTime =>
&& workTime.Dataareaid == "v22"
&& workTime.Active == 1
);
var query =
from address in _context.Address
join washHouse in _context.GirWashhousetable
on address.Addrrecid equals washHouse.Recid into whtGroup
from washHouse in whtGroup.DefaultIfEmpty()
from mondayWorkTime in workTimes
.Where(workTime => address.Recid == workTime.Refrecid && workTime.WeekDay == 1)
.Take(1)
.DefaultIfEmpty()
// Repeat similar logic for other days...
where address.Dataareaid == "v22"
&& _countryCodesForWashStations.Contains(address.Countryregionid)
&& address.Active == 1
&& address.Level != 0
&& address.Type == 153
&& address.Addrtableid == 52411
select new WashingStationsResult
{
Id = address.Recid,
Latitude = address.Latitude,
Longitude = address.Longitude,
CountryCode = address.Countryregionid,
Level = address.Level,
Disinfection = washHouse.Disinfection,
MondayOpen = mondayWorkTime != null ? TimeSpan.FromSeconds(mondayWorkTime.Fromtime).ToString(@"hh\:mm\:ss") : string.Empty,
MondayClose = mondayWorkTime != null ? TimeSpan.FromSeconds(mondayWorkTime.Totime).ToString(@"hh\:mm\:ss") : string.Empty,
// Repeat similar logic for other days...
};