I’m using postgresql tablefunc’s crosstab function that dynamically builds a table from data stored in the database. You pass in 2 sql queries to build the table, a ‘values’ query and a ‘fields’ query like this:
select * from crosstab($values_query, $fields_query) as $fields definition . See this postgresql doc for more info.
This makes that I cannot define static types for the crosstab query’s results, as the structure of the table built can and will change.
I thought I could work with the data returned by Dapper’s Query, as a
printfn "%A" displays the sequences making up the data:
Ok [seq [[id, 51565]; [name, firstname.lastname@example.org]; [website, http://www.hdfghdgfhdgf.net]; [email, Dryades]]; [[id, 51603]; [name, email@example.com]; [website, http://www.tzetreztzer.net]; [email, Orthrus]]; ...
However, trying the simply access the first element of the sequence with
Seq.head causes trouble and the query itself isn’t issued succesfully anymore. Here’s the code:
// sql query let selectStar = sprintf "select * from %s limit 10" info.Head.crosstabListQueryBody // this is succesful if the sequence isn't accessed, but fails if data in the returned sequence is accessed below let result = client.q (selectStar,new Map<string,obj>()) // next line prints error if sequence is accessed later in the code (in the match below) printfn "%A' result match result with // next line works fine and print Ok result as shown above |Ok ll -> ll |> List.iter (fun s -> s |> printfn "--> %A" ) // but the next line cause trouble earlier in the code, resutling in an Error e to match, with the error below //|Ok ll -> ll |> List.iter (fun s -> s |> Seq.head |> printfn "--> %A" ) |Error e -> printfn "Error in crosstab query"
The error displayed is
"System.ArgumentException: Invalid type owner for DynamicMethod. at System.Reflection.Emit.DynamicMethod.Init(String name, MethodAttributes attributes, CallingConventions callingConvention, Type returnType, Type signature, Type owner, Module m, Boolean skipVisibility, Boolean transparentMethod) at System.Reflection.Emit.DynamicMethod..ctor(String name, Type returnType, Type parameterTypes, Type owner, Boolean skipVisibility) at Dapper.SqlMapper.GetTypeDeserializerImpl(Type type, IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in C:\projects\dapper\Dapper\SqlMapper.cs:line 3083 at Dapper.SqlMapper.TypeDeserializerCache.GetReader(IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in C:\projects\dapper\Dapper\SqlMapper.TypeDeserializerCache.cs:line 153 at Dapper.SqlMapper.TypeDeserializerCache.GetReader(Type type, IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in C:\projects\dapper\Dapper\SqlMapper.TypeDeserializerCache.cs:line 50 at Dapper.SqlMapper.GetDeserializer(Type type, IDataReader reader, Int32 startBound, Int32 length, Boolean returnNullIfFirstMissing) in C:\projects\dapper\Dapper\SqlMapper.cs:line 1787 at Dapper.SqlMapper.QueryImpl[T](IDbConnection cnn, CommandDefinition command, Type effectiveType)+MoveNext() in C:\projects\dapper\Dapper\SqlMapper.cs:line 1092 at System.Collections.Generic.List`1.AddEnumerable(IEnumerable`1 enumerable) 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) in C:\projects\dapper\Dapper\SqlMapper.cs:line 723 at DB.DBClient.q[Result](String query, IDictionary`2 parameters) in /home/rb/gits/myowndb/app/lib/DB.fs:line 52"
and line 52 of file
let rows = connection.Query<'Result>(query, parameters)
I have multiple questions about this:
- how is it explained that the query fails due to a line later in the code, that was compiled succesfully?
- what do I have to do to make it work? Do I really have to define a type ? But which type exactly?
- Is there a better way to achieve what I’m trying to do?
Thanks in advance for your help