Subqueries in dapper no longer working

c# dapper

Question

I am using dapper, I just implemented the splitOn function to get a location object set in my AdListingSearchResult, but by doing so my subqueries (MainPhotoFileName and TotalPhotos) are now null and 0 respectively. Any idea what I am doing wrong here?

var data = GetConnection().Query<AdListingSearchResult, AdListingLocationSearchResult, AdListingSearchResult>(@"
SELECT TOP 10 a.AdListingID, a.Title, a.Details, a.CreateDateTime, l.LocationID, l.CountryID, l.USCity, l.USStateCode, l.IntlRegion, c.CountryName,  
(SELECT TOP 1 ap.Filename FROM tbAdListingPhotos ap WHERE ap.AdListingID = a.AdListingID) AS MainPhotoFileName, 
(SELECT COUNT (*) FROM tbAdListingPhotos ap WHERE ap.AdListingID = a.AdListingID) AS TotalPhotos
FROM tbAdListing a 
INNER JOIN tbLocation l ON (a.LocationID = l.LocationID) 
INNER JOIN tbEnumCountry c ON (l.CountryID = c.CountryID) 
WHERE a.Deleted = 0  ORDER BY a.CreateDateTime DESC                 
", (a, l) =>
 {
     a.Location = l;
     return a;
 },
 splitOn: "LocationId"
 ).AsQueryable();

return data;

Accepted Answer

I'm guessing these sub-queries are meant to map to properties on the AdListingSearchResult object. However, the row is partitioned horizontally according to the splitOn parameter, which means that AdListingSearchResult never sees those values.

You should be able to simply re-order the SQL:

SELECT TOP 10 a.AdListingID, a.Title, a.Details, a.CreateDateTime,
(SELECT TOP 1 ap.Filename FROM tbAdListingPhotos ap WHERE ap.AdListingID = a.AdListingID) AS MainPhotoFileName, 
(SELECT COUNT (*) FROM tbAdListingPhotos ap WHERE ap.AdListingID = a.AdListingID) AS TotalPhotos,
l.LocationID, l.CountryID, l.USCity, l.USStateCode, l.IntlRegion, c.CountryName
FROM tbAdListing a
...


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why