// read detection left joins query public async Task<List<DetectionResponse>> ReadDetection(DateTime fromDate, DateTime toDate , int cameraId) { DynamicParameters _params = new DynamicParameters(); _params.Add("@ToDate", toDate); _params.Add("@FromDate", fromDate); _params.Add("@CameraId", cameraId); string sql = " SELECT " + bods.bods.Tdetection + ".*, " + bods.bods.Tweather + ".*, " + bods.bods.Tcamera + ".* " + " FROM " + bods.bods.Tdetection + " LEFT JOIN " + bods.bods.Tweather + " ON " + bods.bods.Tweather.FWeatherId.Cn + " = " + bods.bods.Tdetection.FWeatherId.Cn + " LEFT JOIN " + bods.bods.Tcamera + " ON " + bods.bods.Tcamera.FCameraId.Cn + " = " + bods.bods.Tdetection.FCameraId.Cn + " WHERE " + bods.bods.Tdetection.FDate.Cn + " >=@FromDate" + " AND " + bods.bods.Tdetection.FDate.Cn + " <=@ToDate" + " AND " + bods.bods.Tdetection.FCameraId.Cn + " =@CameraId"; using (IDbConnection conn = DataLayer.NewConnection()) { // determine query response stracture return (await conn.QueryAsync<DetectionResponse>(sql, new[] { typeof(int), // 0 DetectionId typeof(int), // 1 CameraId typeof(string), // 2 ImagePath typeof(int), // 3 WeatherId typeof(DateTime), // 4 Date typeof(int), // 5 WeatherId typeof(string), // 6 WeatherlocationName typeof(double), // 7 Longitude typeof(double), // 8 Latitude typeof(DateTime), // 9 Date typeof(double), // 10 WindSpeed typeof(string), // 11 Description typeof(int), // 12 CameraId typeof(string), // 13 CameraLocationName typeof(double), // 14 Longitude typeof(double), // 15 Latitude typeof(bool), // 16 IsWorking typeof(DateTime) // 17 DateAdded }, objects => { // create object DetectionResponse detectionResponse = new DetectionResponse() { CameraId = objects[1] as int? ?? 0, ImagePath = objects[2] as string, Date = objects[4] as DateTime? ?? DateTime.UtcNow, WeatherLocationName = objects[6] as string, Longitude = objects[7] as double? ?? 0, Latitude = objects[8] as double? ?? 0, WindSpeed = objects[10] as double? ?? 0, Description = objects[11] as string, CameraLocationName = objects[13] as string }; return detectionResponse; } , _params , splitOn: $@"{bods.bods.Tdetection.FCameraId.OnlyColumnName}, {bods.bods.Tdetection.FImagePath.OnlyColumnName}, {bods.bods.Tdetection.FWeatherId.OnlyColumnName}, {bods.bods.Tdetection.FDate.OnlyColumnName}, {bods.bods.Tweather.FWeatherId.OnlyColumnName}, {bods.bods.Tweather.FLocationName.OnlyColumnName}, {bods.bods.Tweather.FLongitude.OnlyColumnName}, {bods.bods.Tweather.FLatitude.OnlyColumnName}, {bods.bods.Tweather.FDate.OnlyColumnName}, {bods.bods.Tweather.FWindSpeed.OnlyColumnName}, {bods.bods.Tweather.FDescription.OnlyColumnName}, {bods.bods.Tcamera.FCameraId.OnlyColumnName}, {bods.bods.Tcamera.FLocationName.OnlyColumnName}, {bods.bods.Tcamera.FLongitude.OnlyColumnName}, {bods.bods.Tcamera.FLatitude.OnlyColumnName}, {bods.bods.Tcamera.FIsWorking.OnlyColumnName}, {bods.bods.Tcamera.FDateAdded.OnlyColumnName}" )).ToList(); } }