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,rn ");
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,rn ");
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.
New contributor
keks01 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
5
SplitOn is splitting the single row data into multiple objects, based on a column names you give into it. Exception says it well what is happening.
The reason of the exception you is that you dont have the column CreatedBy in the sql query you posted and marked as correct.
Also, its the same reason why if you remove a logic with Administrator it works well, because- there is no CreatedBy column in your splitOn then, nor in your data that you are trying to split.
7