CODEWITHSUNDEEP
c#.netpostgresqlentity-framework-corenpgsql
Glory Raj
Glory Raj

Reputation: 17701

trying to pass list of guids to table

I have table where one of column(SelectedIds) datatype is uuid array and i am passing the same guid's using RAW sql insert statement like as below

foreach(var designHubProjectSpaceType in designHubProjectSpaceTypes)
{
    var warningJson = JsonConvert.SerializeObject(designHubProjectSpaceType.Warnings).Replace("'", "''", StringComparison.Ordinal);
    var initialObjectJson  = JsonConvert.SerializeObject(designHubProjectSpaceType.InitialObject).Replace("'", "''", StringComparison.Ordinal);
    var patchJson = JsonConvert.SerializeObject(designHubProjectSpaceType.Patches).Replace("'", "''", StringComparison.Ordinal);

    migrationBuilder.Sql($@"INSERT INTO ""DesignHubProjectSpaceTypes""(""Id"", ""ProjectNumber"", ""ExcludedIds"", ""SelectedIds"", ""IsBasedOnMaster"", ""Warnings"", ""InitialObject"", ""Patches"") "
                       + $@"VALUES ('{designHubProjectSpaceType.Id}', '{designHubProjectSpaceType.ProjectNumber}', [], '{designHubProjectSpaceType.SelectedIds.Select(a => a).ToList()}', '{designHubProjectSpaceType.IsBasedOnMaster}', 
                         '{warningJson}', '{initialObjectJson}', '{patchJson}' )");
}

and here designHubProjectSpaceType.SelectedIds.Select(a => a).ToList() are having list of guid's and that is what i am trying to pass into SelectedIds column. But when i tried to see the sql string that is passed, I noticed that in place of those guids system.Collections.Generic.List[System.Guid] is passed like as shown in below image

enter image description here

and these are guid's that designHubProjectSpaceType.SelectedIds is holding and i don't know where i am doing wrong here.

enter image description here

Could any one please let me know any idea why list of guids are passed as type, I am using postgreSQL as DB and using EFCore. I have put the above code in migration file.

below is the table structure

enter image description here

getting an error with this '{string.Join(',', selectedIds)}' enter image description here

Upvotes: 0

Views: 1982

Answers (1)

Saar
Saar

Reputation: 269

The problem of the code is that it is converting a list to a string by calling ToString() implicitly by string interpolation. Checkout this example:

Guid[] selectedIds = new Guid[]{
    Guid.NewGuid(),
    Guid.NewGuid()
};

string error = $@"'{selectedIds.Select(a => a).ToList()}'";
Console.WriteLine($"Error: {error}");

// Tweak the following string interpolations according to the target SQL syntax you needed.

// Join string with single quotes
string correct = $@"'{{{string.Join(',', selectedIds)}}}'";
Console.WriteLine($"Format 1: {correct}");

// Putting `{}` over uuid, and adding the list into a `[]`:
string correct2 = $@"'[{string.Join(',', selectedIds.Select(id => "{" + id + "}"))}]'";
Console.WriteLine($"Format 2: {correct2}");

Outputs:

Error: 'System.Collections.Generic.List`1[System.Guid]'
Format 1: '{5e4b44c2-f087-44db-98b0-95da1ae20ed7,1153d836-baee-4ba4-85f1-6f0c08934135}'
Format 2: '[{5e4b44c2-f087-44db-98b0-95da1ae20ed7},{1153d836-baee-4ba4-85f1-6f0c08934135}]'

On the side, it might be easier if you output the sql you are sending to console or logging for debugging.

Upvotes: 1

Related Questions