Dapper Mapping не работает должным образом. Использование myql, основного веб-API asp.net ⇐ MySql

[Anonymous]

Я получаю таблицу ProductTransfer вместе со связанными продуктами. Хотя общее количество записей = 1200, мой API возвращает только три записи.
Вот мои модели

Код: Выделить всё

public class ProductTransferDto
{
public int Id { get; set; }
public int BusinessId { get; set; }
public ProductTransferType ProductTransferType { get; set; }
public ProductTransferStatus ProductTransferStatus { get; set; }
public ProductTransferAprroved ProductTransferAprroved { get; set; }
public List
 Products { get; set; } = [];
public string? RegisterBy { get; set; } = null;
public string? BranchName { get; set; } = null;
public int? BranchId { get; set; }
public DateTime TransferedOn { get; set; }
public decimal? StockValue { get; set; } = 0;
public int? ItemCount { get; set; } = 0;
public DateTime ApprovedOn { get; set; }
public string? ReceivedById { get; set; } = null;
public DateTime ReceivedOn { get; set; }
public string? Note { get; set; } = null;
}

public class ProductTransferDetailDto
{
public int Id { get; set; }
public int ProductTransferId { get; set; }
public int? ProductId { get; set; } = null;
public string? Name { get; set; } = null;
public string? Code { get; set; } = null;
public decimal SellingPrice { get; set; } = 0;
public decimal BuyingPrice { get; set; } = 0;
public decimal WholeSalePrice { get; set; } = 0;
public decimal Quantity { get; set; } = 0;
public string? ProfitMargin { get; set; } = null;
}

Вот мой метод получения данных из базы данных MySQL с помощью Dapper.

Код: Выделить всё

public async Task
> GetProductTransersByAdmin(SearchParameters searchParameters, int ProductTransferType)
{
try
{
using var connection = _dapperContext.CreateConnection();

var parameters = new DynamicParameters();
parameters.Add("@ProductTransferType", ProductTransferType);
parameters.Add("@StartDate", searchParameters.StartDate);
parameters.Add("@EndDate", searchParameters.EndDate);
parameters.Add("@BranchId", searchParameters.BranchId >  0 ? searchParameters.BranchId : null);
parameters.Add("@Skip", (searchParameters.PageNumber - 1) * searchParameters.PageSize);
parameters.Add("@Take", searchParameters.PageSize);

var SqlTotal = @"
SELECT COUNT(*)
FROM ProductTransfers PT
WHERE PT.ProductTransferType = @ProductTransferType
AND PT.TransferedOn BETWEEN @StartDate AND @EndDate
AND (@BranchId IS NULL OR PT.BranchId = @BranchId);
";

var SqlList = @"

SELECT
PT.Id,
PT.BusinessId,
PT.TransferedOn,
PT.ProductTransferType,
PT.ProductTransferStatus,
PT.ProductTransferAprroved,
PT.StockValue,
PT.ItemCount,
PT.ApprovedOn,
PT.ReceivedOn,
PT.Note,

B.Name AS BranchName,
B.Id AS BranchId,
R.Name AS RegisterBy,

PTP.Id,
PTP.ProductTransferId,
PTP.ProductId,
PTP.Name,
PTP.Code,
PTP.SellingPrice,
PTP.BuyingPrice,
PTP.WholesalePrice,
PTP.Quantity,
PTP.ProfitMargin

FROM producttransfers PT
LEFT JOIN branches B ON B.Id = PT.BranchId
LEFT JOIN registration R ON R.Id = PT.RegisterId
LEFT JOIN producttransferdetails PTP ON PTP.ProductTransferId = PT.Id
WHERE PT.ProductTransferType = @ProductTransferType
AND PT.TransferedOn BETWEEN @StartDate AND @EndDate
AND (@BranchId IS NULL OR PT.BranchId = @BranchId)
ORDER BY PT.Id DESC
LIMIT @Take OFFSET @Skip;
";

// 1) Read count
var total = await connection.ExecuteScalarAsync(SqlTotal, parameters);

// 2) Read full result set with children (multi-mapping)
var lookup = new Dictionary();

var transfers = await connection.QueryAsync(SqlList,
(pt, ptp) =>
{
if (!lookup.TryGetValue(pt.Id, out var entry))
{
entry = pt;

lookup.Add(entry.Id, entry);

}

entry.Products.Add(ptp);

return entry;
}, parameters,
splitOn: "Id"
);

var TransferList = transfers.Distinct().ToList();

Console.WriteLine("Total Transfers Count: " + total); // 1200

Console.WriteLine("Total Transfers: " + TransferList.Count); // only 3

return PagedList.ToPagedList(
TransferList,
total,
searchParameters.PageNumber,
searchParameters.PageSize
);
}
catch (MySqlException ex)
{

_logger.LogError(ex, "MySQL Database error occurred Fetching ProductTransfers");
throw new Exception("The database operation timed out.", ex);
}
catch (TimeoutException ex)
{

throw new Exception("The database operation timed out Fetching ProductTransfers.", ex);
}
catch (InvalidOperationException ex)
{

throw new Exception("Invalid database operation or data mapping error Fetching ProductTransfers.", ex);
}

}

Что я мог упустить?. Я просмотрел изящную документацию, но не смог найти решения.


Подробнее здесь: https://stackoverflow.com/questions/798 ... re-web-api