Error: System.ArgumentException: Multi-map error: splitOn column 'Id' was not found - please ensure your splitOn parameter is set and in the correct order (Parameter 'splitOn').
It happens when i use check IdentityRoles on Administrator role. If you remove all the checks and all the logic about this, then everything starts to work. See examples below.
Code of GetTransportationRequestQueryHandler:
internal sealed class GetTransportationRequestQueryHandler
: IQueryHandler<GetTransportationRequestQuery, IReadOnlyList<GetTransportationRequestResponse>>
{
private readonly ISqlConnectionFactory _sqlConnectionFactory;
public GetTransportationRequestQueryHandler(ISqlConnectionFactory sqlConnectionFactory)
{
_sqlConnectionFactory = sqlConnectionFactory;
}
public async Task<Result<IReadOnlyList<GetTransportationRequestResponse>>> Handle(
GetTransportationRequestQuery request, CancellationToken cancellationToken)
{
string sqlString =
"""
SELECT
a.id AS Id,
a.created_by AS CreatedBy,
a.created_on_utc AS CreatedOnUtc,
a.last_modified_date_on_utc AS LastModifiedDateOnUtc,
a.last_modified_by AS LastModifiedBy,
a.type_of_request AS TypeOfRequest,
a.customs_clearance AS CustomsClearance,
a.delivery_conditions AS DeliveryConditions,
a.cargo_readiness_period AS CargoReadinessPeriod,
a.deadline_for_submitting_commercial_offer AS DeadlineForSubmittingCommercialOffer,
a.type_of_cargo AS TypeOfCargo,
a.type_of_packaging AS TypeOfPackaging,
a.container_type AS ContainerType,
a.places_in_container AS PlacesInContainer,
a.loading_in_one_container_net AS LoadingInOneContainerNeT,
a.number_of_containers AS NumberOfContainers,
a.weight_of_one_place AS WeightOfOnePlace,
a.loading_per_container_gross_ton AS LoadingPerContainerGrossTon,
a.port_of_shipment AS PortOfShipment,
a.transshipment_port AS TransshipmentPort,
a.place_of_delivery AS PlaceOfDelivery,
a.cargo_pickup_address AS CargoPickupAddress,
a.note AS Note,
a.is_visible AS IsVisible,
a.index AS Index
FROM transportation_request AS a
""";
if (!request.IdentityUser.IdentityRoles.Contains("Administrator"))
{
var firstIndexOfWord = IndexOfFirstLetter(sqlString, "a.created_by");
var lastIndexOfWord = IndexOfLastLetter(sqlString, "LastModifiedBy,\r\n ");
sqlString = sqlString.Remove(firstIndexOfWord, lastIndexOfWord - firstIndexOfWord);
}
using var connection = _sqlConnectionFactory.CreateConnection();
var transportationRequestResponse = await connection.QueryAsync<GetTransportationRequestResponse>(
sqlString,
new[]
{
typeof(Guid),
request.IdentityUser.IdentityRoles.Contains("Administrator") ? null : typeof(Audit),
typeof(TypeOfRequest),
typeof(LogisticsProcess),
typeof(CargoCharacteristics),
typeof(CharacteristicsOfTheContainer),
typeof(LogisticsHubs),
typeof(string),
typeof(bool),
},
objects =>
{
Guid id = (Guid)objects[0];
Audit? audit = (Audit)objects[1];
TypeOfRequest typeOfRequest = (TypeOfRequest)objects[2];
LogisticsProcess logisticsProcess = (LogisticsProcess)objects[3];
CargoCharacteristics cargoCharacteristics = (CargoCharacteristics)objects[4];
CharacteristicsOfTheContainer characteristicsOfTheContainer = (CharacteristicsOfTheContainer)objects[5];
LogisticsHubs logisticsHubs = (LogisticsHubs)objects[6];
string note = (string)objects[7];
bool isVisible = (bool)objects[8];
GetTransportationRequestResponse getTransportationRequestResponse = new(
id,
audit,
typeOfRequest,
logisticsProcess,
cargoCharacteristics,
characteristicsOfTheContainer,
logisticsHubs,
note,
isVisible);
return getTransportationRequestResponse;
},
splitOn: $"Id{(request.IdentityUser.IdentityRoles.Contains("Administrator") ? ",CreatedBy," : ",")}TypeOfRequest,CustomsClearance,TypeOfCargo,ContainerType,PortOfShipment,Note,IsVisible");
return transportationRequestResponse.ToList();
}`
Code of IndexOfFirstLetter method:
int IndexOfFirstLetter(string str, string word)
{
var stringCharArray = str.ToCharArray();
var wordCharArray = word.ToCharArray();
int wordLength = wordCharArray.Length;
int wordIndex = 0;
int coincidenceCounter = 0;
int tmpValueForWordIndex = 0;
int counterForWhile = 0;
for (int i = 0; i < stringCharArray.Length; i++)
{
while (counterForWhile < wordLength)
{
if (stringCharArray[i] == wordCharArray[counterForWhile])
{
tmpValueForWordIndex = i;
++coincidenceCounter;
counterForWhile++;
if (coincidenceCounter == wordLength)
{
wordIndex = tmpValueForWordIndex - (wordLength - 1);
}
break;
}
if (stringCharArray[i] != wordCharArray[counterForWhile])
{
coincidenceCounter = 0;
counterForWhile = 0;
break;
}
}
}
return wordIndex;
}
Code of IndexOfLastLettermethod:
int IndexOfLastLetter(string str, string word)
{
var stringCharArray = str.ToCharArray();
var wordCharArray = word.ToCharArray();
int wordLength = wordCharArray.Length;
int wordIndex = 0;
int coincidenceCounter = 0;
int tmpValueForWordIndex = 0;
int counterForWhile = 0;
for (int i = 0; i < stringCharArray.Length; i++)
{
while (counterForWhile < wordLength)
{
if (stringCharArray[i] == wordCharArray[counterForWhile])
{
tmpValueForWordIndex = i;
++coincidenceCounter;
counterForWhile++;
if (coincidenceCounter == wordLength)
{
wordIndex = tmpValueForWordIndex;
}
break;
}
if (stringCharArray[i] != wordCharArray[counterForWhile])
{
coincidenceCounter = 0;
counterForWhile = 0;
break;
}
}
}
return wordIndex;
}
This piece of code removes part of the sql line if there is no administrator role.
if (!request.IdentityUser.IdentityRoles.Contains("Administrator"))
{
var firstIndexOfWord = IndexOfFirstLetter(sqlString, "a.created_by");
var lastIndexOfWord = IndexOfLastLetter(sqlString, "LastModifiedBy,\r\n ");
sqlString = sqlString.Remove(firstIndexOfWord, lastIndexOfWord - firstIndexOfWord);
}
After deleting the sql line looks like this and this sql query is correct:
SELECT
a.id AS Id,
a.type_of_request AS TypeOfRequest,
a.customs_clearance AS CustomsClearance,
a.delivery_conditions AS DeliveryConditions,
a.cargo_readiness_period AS CargoReadinessPeriod,
a.deadline_for_submitting_commercial_offer AS DeadlineForSubmittingCommercialOffer,
a.type_of_cargo AS TypeOfCargo,
a.type_of_packaging AS TypeOfPackaging,
a.container_type AS ContainerType,
a.places_in_container AS PlacesInContainer,
a.loading_in_one_container_net AS LoadingInOneContainerNeT,
a.number_of_containers AS NumberOfContainers,
a.weight_of_one_place AS WeightOfOnePlace,
a.loading_per_container_gross_ton AS LoadingPerContainerGrossTon,
a.port_of_shipment AS PortOfShipment,
a.transshipment_port AS TransshipmentPort,
a.place_of_delivery AS PlaceOfDelivery,
a.cargo_pickup_address AS CargoPickupAddress,
a.note AS Note,
a.is_visible AS IsVisible,
a.index AS Index
FROM transportation_request AS a
{
"records": [
{
"id": "872e32ac-cbe7-4d7d-8365-b6b1b4505968",
"typeofrequest": 1,
"customsclearance": "string",
"deliveryconditions": "string",
"cargoreadinessperiod": "2024-07-16",
"deadlineforsubmittingcommercialoffer": "2024-07-16",
"typeofcargo": "string",
"typeofpackaging": "string",
"containertype": "string",
"placesincontainer": 123,
"loadinginonecontainernet": 23,
"numberofcontainers": 14,
"weightofoneplace": 2,
"loadingpercontainergrosston": 12,
"portofshipment": "string",
"transshipmentport": "string",
"placeofdelivery": "string",
"cargopickupaddress": "string",
"note": "string",
"isvisible": "0",
"index": 1
},
{
"id": "d9e12d31-f719-48f0-a29c-d64ea54d0af8",
"typeofrequest": 1,
"customsclearance": "string",
"deliveryconditions": "string",
"cargoreadinessperiod": "2024-07-16",
"deadlineforsubmittingcommercialoffer": "2024-07-16",
"typeofcargo": "string",
"typeofpackaging": "string",
"containertype": "string",
"placesincontainer": 123,
"loadinginonecontainernet": 23,
"numberofcontainers": 14,
"weightofoneplace": 2,
"loadingpercontainergrosston": 12,
"portofshipment": "string",
"transshipmentport": "string",
"placeofdelivery": "string",
"cargopickupaddress": "string",
"note": "string",
"isvisible": "0",
"index": 2
},
{
"id": "e42d9993-3410-4a41-9ee9-7a1ce5791776",
"typeofrequest": 1,
"customsclearance": "string",
"deliveryconditions": "string",
"cargoreadinessperiod": "2024-07-16",
"deadlineforsubmittingcommercialoffer": "2024-07-16",
"typeofcargo": "string",
"typeofpackaging": "string",
"containertype": "string",
"placesincontainer": 123,
"loadinginonecontainernet": 23,
"numberofcontainers": 14,
"weightofoneplace": 2,
"loadingpercontainergrosston": 12,
"portofshipment": "string",
"transshipmentport": "string",
"placeofdelivery": "string",
"cargopickupaddress": "string",
"note": "string",
"isvisible": "0",
"index": 4
},
{
"id": "39cbdfff-79d7-41d6-938d-10071552fd1b",
"typeofrequest": 1,
"customsclearance": "string",
"deliveryconditions": "string",
"cargoreadinessperiod": "2024-07-16",
"deadlineforsubmittingcommercialoffer": "2024-07-16",
"typeofcargo": "string",
"typeofpackaging": "string",
"containertype": "string",
"placesincontainer": 123,
"loadinginonecontainernet": 23,
"numberofcontainers": 14,
"weightofoneplace": 2,
"loadingpercontainergrosston": 12,
"portofshipment": "string",
"transshipmentport": "string",
"placeofdelivery": "string",
"cargopickupaddress": "string",
"note": "string",
"isvisible": "0",
"index": 5
},
{
"id": "6b1285f5-e554-458d-9d23-f3180538a548",
"typeofrequest": 1,
"customsclearance": "string",
"deliveryconditions": "string",
"cargoreadinessperiod": "2024-07-16",
"deadlineforsubmittingcommercialoffer": "2024-07-16",
"typeofcargo": "string",
"typeofpackaging": "string",
"containertype": "string",
"placesincontainer": 123,
"loadinginonecontainernet": 23,
"numberofcontainers": 14,
"weightofoneplace": 2,
"loadingpercontainergrosston": 12,
"portofshipment": "string",
"transshipmentport": "string",
"placeofdelivery": "string",
"cargopickupaddress": "string",
"note": "string",
"isvisible": "0",
"index": 6
},
{
"id": "c5945a77-5e7f-476a-9645-fa860de0bd46",
"typeofrequest": 1,
"customsclearance": "string",
"deliveryconditions": "string",
"cargoreadinessperiod": "2024-07-16",
"deadlineforsubmittingcommercialoffer": "2024-07-16",
"typeofcargo": "string",
"typeofpackaging": "string",
"containertype": "string",
"placesincontainer": 123,
"loadinginonecontainernet": 23,
"numberofcontainers": 14,
"weightofoneplace": 2,
"loadingpercontainergrosston": 12,
"portofshipment": "string",
"transshipmentport": "string",
"placeofdelivery": "string",
"cargopickupaddress": "string",
"note": "string",
"isvisible": "0",
"index": 7
},
{
"id": "4697e6d1-64cc-4df0-b2f8-60666e3cd462",
"typeofrequest": 1,
"customsclearance": "string",
"deliveryconditions": "string",
"cargoreadinessperiod": "2024-07-16",
"deadlineforsubmittingcommercialoffer": "2024-07-16",
"typeofcargo": "string",
"typeofpackaging": "string",
"containertype": "string",
"placesincontainer": 123,
"loadinginonecontainernet": 23,
"numberofcontainers": 14,
"weightofoneplace": 2,
"loadingpercontainergrosston": 12,
"portofshipment": "string",
"transshipmentport": "string",
"placeofdelivery": "string",
"cargopickupaddress": "string",
"note": "string",
"isvisible": "0",
"index": 8
},
{
"id": "11132681-ad4e-4b48-90a0-5d48cffd9580",
"typeofrequest": 1,
"customsclearance": "string",
"deliveryconditions": "string",
"cargoreadinessperiod": "2024-07-16",
"deadlineforsubmittingcommercialoffer": "2024-07-16",
"typeofcargo": "string",
"typeofpackaging": "string",
"containertype": "string",
"placesincontainer": 123,
"loadinginonecontainernet": 23,
"numberofcontainers": 14,
"weightofoneplace": 2,
"loadingpercontainergrosston": 12,
"portofshipment": "string",
"transshipmentport": "string",
"placeofdelivery": "string",
"cargopickupaddress": "string",
"note": "string",
"isvisible": "0",
"index": 9
}
]
}
As I already said, if you remove the logic of checking for the administrator role, then everything will work. Then i get response like this:
{
"id": "872e32ac-cbe7-4d7d-8365-b6b1b4505968",
"audit": {
"createdBy": "0b429df5-bc7f-4291-ae6f-d928c7df6368",
"createdOnUtc": "2024-07-19T07:14:44.711343Z",
"lastModifiedDateOnUtc": null,
"lastModifiedBy": null
},
"typeOfRequest": 1,
"logisticsProcess": {
"customsClearance": "string",
"deliveryConditions": "string",
"cargoReadinessPeriod": "2024-07-16",
"deadlineForSubmittingCommercialOffer": "2024-07-16"
},
"cargoCharacteristics": {
"typeOfCargo": "string",
"typeOfPackaging": "string"
},
"characteristicsOfTheContainer": {
"containerType": "string",
"placesInContainer": 123,
"loadingInOneContainerNet": 23,
"numberOfContainers": 14,
"weightOfOnePlace": 2,
"loadingPerContainerGrossTon": 12
},
"logisticsHubs": {
"portOfShipment": "string",
"transshipmentPort": "string",
"placeOfDelivery": "string",
"cargoPickupAddress": "string"
},
"note": "string",
"isVisible": false
},
Please help me understand what I'm doing wrong
I looked at many topics on this issue and did not find an answer.
The problem was in Type[] array:
new[]
{
typeof(Guid),
typeof(Audit),
typeof(TypeOfRequest),
typeof(LogisticsProcess),
typeof(CargoCharacteristics),
typeof(CharacteristicsOfTheContainer),
typeof(LogisticsHubs),
typeof(string),
typeof(bool),
}
If the role is not an administrator, then I delete a piece of sql line that requests CreatedBy, CreatedOnUtc, LastModifiedDateOnUtc, LastModifiedBy, in other words, the Audit object. Accordingly, the sql query returns data without Audit data, but Type[] has typeof(Audit) and it needs to be filled out. And there’s nothing to fill out.
new[]
{
typeof(Guid),
typeof(Audit),
....
}
This is what caused the error. Refactored code below:
public async Task<Result<IReadOnlyList<GetTransportationRequestResponse>>> Handle(
GetTransportationRequestQuery request, CancellationToken cancellationToken)
{
var isRoleAdministrator = request.IdentityUser.IdentityRoles.Contains("Administrator");
var sqlString =
"""
SELECT
a.id AS Id,
a.created_by AS CreatedBy,
a.created_on_utc AS CreatedOnUtc,
a.last_modified_date_on_utc AS LastModifiedDateOnUtc,
a.last_modified_by AS LastModifiedBy,
a.type_of_request AS TypeOfRequest,
a.customs_clearance AS CustomsClearance,
a.delivery_conditions AS DeliveryConditions,
a.cargo_readiness_period AS CargoReadinessPeriod,
a.deadline_for_submitting_commercial_offer AS DeadlineForSubmittingCommercialOffer,
a.type_of_cargo AS TypeOfCargo,
a.type_of_packaging AS TypeOfPackaging,
a.container_type AS ContainerType,
a.places_in_container AS PlacesInContainer,
a.loading_in_one_container_net AS LoadingInOneContainerNeT,
a.number_of_containers AS NumberOfContainers,
a.weight_of_one_place AS WeightOfOnePlace,
a.loading_per_container_gross_ton AS LoadingPerContainerGrossTon,
a.port_of_shipment AS PortOfShipment,
a.transshipment_port AS TransshipmentPort,
a.place_of_delivery AS PlaceOfDelivery,
a.cargo_pickup_address AS CargoPickupAddress,
a.note AS Note,
a.is_visible AS IsVisible,
a.index AS Index
FROM transportation_request AS a
""";
List<Type> getTransportationRequestResponseTypes =
[
typeof(Guid),
typeof(Audit),
typeof(TypeOfRequest),
typeof(LogisticsProcess),
typeof(CargoCharacteristics),
typeof(CharacteristicsOfTheContainer),
typeof(LogisticsHubs),
typeof(string),
typeof(bool)
];
if (!isRoleAdministrator)
{
var firstIndexOfWord = IndexOfFirstLetter(sqlString, "a.created_by");
var lastIndexOfWord = IndexOfLastLetter(sqlString, "LastModifiedBy,\r\n ");
sqlString = sqlString.Remove(firstIndexOfWord, lastIndexOfWord - firstIndexOfWord);
getTransportationRequestResponseTypes.RemoveAt(1); //1 is an index of Audit type
}
using var connection = _sqlConnectionFactory.CreateConnection();
var transportationRequestResponse = await connection.QueryAsync<GetTransportationRequestResponse>(
sqlString,
getTransportationRequestResponseTypes.ToArray(),
objects =>
{
Guid id = (Guid)objects[0];
Audit? audit = isRoleAdministrator ? (Audit)objects[1] : null;
TypeOfRequest typeOfRequest = (TypeOfRequest)objects[^7];
LogisticsProcess logisticsProcess = (LogisticsProcess)objects[^6];
CargoCharacteristics cargoCharacteristics = (CargoCharacteristics)objects[^5];
CharacteristicsOfTheContainer characteristicsOfTheContainer = (CharacteristicsOfTheContainer)objects[^4];
LogisticsHubs logisticsHubs = (LogisticsHubs)objects[^3];
string note = (string)objects[^2];
bool isVisible = (bool)objects[^1];
GetTransportationRequestResponse getTransportationRequestResponse = new(
id,
audit,
typeOfRequest,
logisticsProcess,
cargoCharacteristics,
characteristicsOfTheContainer,
logisticsHubs,
note,
isVisible);
return getTransportationRequestResponse;
},
splitOn: $"Id{(isRoleAdministrator ? ",CreatedBy," : ",")}TypeOfRequest,CustomsClearance,TypeOfCargo,ContainerType,PortOfShipment,Note,IsVisible");
return transportationRequestResponse.ToList();
}
I simply made a List and filled it based on isRoleAdministrator and filled it with the necessary types.