CODEWITHSUNDEEP
c#postgresqldapper
Dave3of5
Dave3of5

Reputation: 730

Dapper array of custom types

Using dapper I'm trying to map a column that is an array of custom types but there seems to be no example of this anywhere. Here's the table definition:

CREATE TABLE public."Data"
(
    "Id" bigint NOT NULL DEFAULT nextval('"Data_Id_seq"'::regclass),
    "Value" real NOT NULL,
    "UDFs" "UDF"[],
    "Timestamp" timestamp with time zone NOT NULL,
    CONSTRAINT "Data_pkey" PRIMARY KEY ("Id")
)

Note the UDFs column here which is defined as follows:

CREATE TYPE public."UDF" AS
(
    "Name" text,
    "Value" text
);

Now I currently have my model set as such:

public class Data
{
    [Key]
    public long Id { get; set; }

    [Required]
    public float Value { get; set; }

    [Required]
    public DateTime Timestamp { get; set; }

    [Required]
    public UDF[] UDFs { get; set; }

}

And the UDF class:

public class UDF 
{
    public string Name {get;set;}
    public string Value {get;set;}
}

But I get the following exception:

Exception has occurred: CLR/System.InvalidOperationException
An exception of type 'System.InvalidOperationException' occurred in Dapper.dll but was not handled in user code: 'Error parsing column 2 (UDFs=2 - Single)'
   at Dapper.SqlMapper.ThrowDataException(Exception ex, Int32 index, IDataReader reader, Object value)
   at Dapper.SqlMapper.<QueryImpl>d__124`1.MoveNext()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
   at Dapper.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable`1 commandTimeout, Nullable`1 commandType)
   at backend.Repository.DataRepository.FindAll() in c:\Users\davidk\Desktop\ProjectSaturn\backend\Repository\DataRepository.cs:line 43
   at backend.Controllers.ValuesController.Get() in c:\Users\davidk\Desktop\ProjectSaturn\backend\Controllers\ValuesController.cs:line 26
   at Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker.<InvokeActionMethodAsync>d__27.MoveNext()

I have read that you need to use a custom type mapper but I can't really see any good examples of this. Does anyone know where I would find more info?

Oh the DB I'm using is postgres.

Upvotes: 3

Views: 2501

Answers (1)

Tony Bruni
Tony Bruni

Reputation: 101

Your UDFs column contains an array? I don't think Dapper can deal with array values in a single column. The more typical case would be to have a separate table for UDFs, then join them in the query.

public class UDF
{
    [Key]
    public long Id { get; set; }

    [Required]
    public string Name { get; set; }

    [Required]
    public string Value { get; set; }
}

public class Data
{
    [Key]
    public long Id { get; set; }

    [Required]
    public float Value { get; set; }

    [Required]
    public DateTime Timestamp { get; set; }

    [Required]
    public List<UDF> UDFs { get; set; }
}

string sql = @"SELECT
        d.Id,
        d.Value,
        d.Timestamp,
        u.name,
        u.value
FROM Data d
INNER JOIN UDFTable u ON d.Id = u.Id";

then you can do

QueryAsync<Data, UDF, Data>(
        sql,
        (data, udf, data) => {
            data.UDFs.Add(udf);
            return data;
        },
        new { },
        null, true, "Id", null, System.Data.CommandType.Text)

Upvotes: 3

Related Questions