.NET Core: IEnumerable parameters are not supported, pass an array or List instead

Question

I am trying to save JSON into my Postgres database from .NET Core.

This is my table :

This is my query:

string strCommandInsert = @"INSERT INTO  public.""City"" (""id"", ""externalId"", ""tags"", ""members"", ""geometry"") values (@id, @externalId, @tags, @members, @geometry);";

await using (var command = new NpgsqlCommand(strCommandInsert, conn))
{
    foreach (var customer in cities.elements)
    {
        var tags = JsonConvert.SerializeObject(customer.tags);
        command.Parameters.AddWithValue("@id", customer.id);
        command.Parameters.AddWithValue("@externalId", Guid.NewGuid());
        command.Parameters.AddWithValue("@tags", JObject.Parse(tags));

        int nRows = command.ExecuteNonQuery();

        await Console.Out.WriteLineAsync(String.Format("Number of rows inserted={0}", nRows));
    }
}

But I get this error:

Opening connection

Unhandled exception. System.NotSupportedException: IEnumerable parameters are not supported, pass an array or List instead

at Npgsql.Internal.TypeMapping.TypeMapper.g__ResolveLong|30_0(Type type)
at Npgsql.Internal.TypeMapping.TypeMapper.ResolveByClrType(Type type)
at Npgsql.Internal.TypeMapping.TypeMapper.g__ResolveLong|30_0(Type type)
at Npgsql.Internal.TypeMapping.TypeMapper.ResolveByClrType(Type type)
at Npgsql.Internal.TypeMapping.TypeMapper.g__ResolveLong|29_0(Object value, Type type)
at Npgsql.Internal.TypeMapping.TypeMapper.ResolveByValue(Object value)
at Npgsql.NpgsqlParameter.g__Resolve|96_0(TypeMapper typeMapper)
at Npgsql.NpgsqlParameter.ResolveHandler(TypeMapper typeMapper)
at Npgsql.NpgsqlParameter.Bind(TypeMapper typeMapper)
at Npgsql.NpgsqlParameterCollection.ProcessParameters(TypeMapper typeMapper, Boolean validateValues, CommandType commandType)
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior behavior, Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteNonQuery(Boolean async, CancellationToken cancellationToken)
at Npgsql.NpgsqlCommand.ExecuteNonQuery()
at EazyCityCA.Startup.Main(String[] args) in /Users/alt/Projects/MyProjects/eazy_city/EazyCityCore/EazyCityCA/Program.cs:line 104
at EazyCityCA.Startup.Main(String[] args) in /Users/alt/Projects/MyProjects/eazy_city/EazyCityCore/EazyCityCA/Program.cs:line 107
at EazyCityCA.Startup.Main(String[] args) in /Users/alt/Projects/MyProjects/eazy_city/EazyCityCore/EazyCityCA/Program.cs:line 109
at EazyCityCA.Startup.(String[] args)

This tags json:

score 1 · Accepted Answer · answered Apr 15 '23 at 15:45

1

If you want to pass a value through npgsql to a JSON column then you pass that value as a string, see https://www.npgsql.org/doc/types/basic.html?q=json

The error message is somewhat confusing as it just happens that JObject implements IEnumerable and most often the coding error is that someone wanted to pass multiple values, so an array or list type should have been passed, but in your case you want to use the string instead.

Npgsql has built-in support for System.Text.Json but requires a plugin for support for Newtonsoft's Json.net https://www.npgsql.org/doc/types/jsonnet.html?tabs=datasource which would allow you to use the JObject directly, but since you already have the string and are parsing it that seems a bit redundant.

answered Apr 15 '23 at 15:45

Pete Kirkham

48,893
5
92
171

If I want to save null value what I have to do ? I added `AddWithValue("@members", null);` but I got error ` Parameter '@members' must have either its NpgsqlDbType or its DataTypeName or its Value set ` – Cyrus the Great Apr 15 '23 at 15:58
@CyrustheGreat use DbNull.Value instead of C# null https://stackoverflow.com/questions/4958379/what-is-the-difference-between-null-and-system-dbnull-value – Pete Kirkham Apr 15 '23 at 15:59
I removed `JObject.Parse(tags)`and I added `AddWithValue("@tags", tags)`but I got error `column "tags" is of type json but expression is of type text`? – Cyrus the Great Apr 15 '23 at 16:07
1

@CyrustheGreat IIRC you need to use a cast in your SQL when passing the string, so `@tag :json` – Pete Kirkham Apr 15 '23 at 16:12
See https://www.postgresql.org/docs/15/datatype-json.html#JSON-KEYS-ELEMENTS Casting a string to json is a postgresql thing not a dot not one – Pete Kirkham Apr 15 '23 at 16:18

score 0 · Answer 2 · answered Apr 15 '23 at 15:54

0

You can convert it to string, which provvides valid JSON

command.Parameters.AddWithValue("@tags", JObject.Parse(tags).ToString());

answered Apr 15 '23 at 15:54

nbk

45,398
8
30
47

yes, but `tags` is JSON string – Cyrus the Great Apr 15 '23 at 15:58
that doesn't matter postgres will convert it automatically as long as it is valid jason, and this is what the tostring produces, did you try it – nbk Apr 15 '23 at 16:04

.NET Core: IEnumerable parameters are not supported, pass an array or List instead

2 Answers2