I'm using sql executor to bring latest relevant banner titles with appversions <= of requested version My idea was to group by articul, get max supported appversion and return all rows appropriate to those pairs (articul, max version) Couldn't write a LINQ for it, so did it using sql executor by joining the table with itself. Would be very grateful for help to rewrite it back to LINQ
var response_banners = await _sqlExecutor.Clone()
.With(@$"select a.* from [dbo].[BannerData] as a join (")
.With(@$"select articul, max(AppVersionAndroid) as appversion")
.With(@$" from [dbo].[BannerData]")
.With(@$" where AppVersionAndroid is not null and isenabled=1 and AppVersionAndroid<=").InPar(request.AppVersion)
.With(@$" group by articul) as t on t.articul = a.articul and t.appversion = a.AppVersionAndroid order by sortindex")
.ExecuteReaderAsync(r => new BannerDataDTO()
{
Articul = r.ToString("Articul"),
Description = r.ToString("Description"),
...
}).ConfigureAwait(false);
example of data in table:
Id Articul Title AppVersionAndroid
1 AnimationBanner title1 2.0
2 test2 title2 1.5
3 AnimationBanner title3 1.1
4 NewAppVersionBanner title4 1.1
7 PaperPropositionBanner title5 1.1
11 PennyAccountBanner title6 1.1
13 DeliveryFreeBanner title7 1.1
