How to use sequences returned by Dapper's Query?

1

Hi,

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, info@ggsdgsdgs.net]; [website, http://www.hdfghdgfhdgf.net];
      [email, Dryades]];
     [[id, 51603]; [name, info@treztezrtez.net]; [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 DB.fs is

       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

2

Can you please provide minimal example? It’s a bit hard to understand where the problem is now.

For example, here in your code the parameters argument is empty map (probably in your real code there are some parameters there). It’s really distract one attention.

Also, function that raises exception - client.q is hidden from view.

The quick searching gives Q on SO with similar description.

and one of answers is:

I ran into this error when Query was strongly typed to a collection, like so: Query<IEnumerable<string>>. When it should only be Query<string>

There wasn’t such a problem there but it maybe a reason here.

So, what is your type T ('Result) here? Is it really something like
list<seq<list<...>>> or maybe something less complex indeed ?

3

thanks @FoggyFinder for your answer.
The whole code is on gitlab:

The query I want to issue at this stage has no parameter, that’s why I pass an empty map.

I had seen this thread on SO, but I hope it doesn’t apply to my situation, as I don’t have a type defined for the rows returned. I tried specifying the type string, but then it doesn’t return this (obtained with a client.q<_>):

Ok
  [seq
     [[id, 51565]; [name, info@ylu.net]; [website, http://www.ylu.net];
      [email, Dryades]]]

but

Ok ["51565"]

I currently don’t see what I can do and I hope you or someone else on the forum can suggest a way forward. Thanks!

4

Okay, it’s much clearer now.

When you don’t specify any types they will inferred for you by compiler. It this case your type is goes as basic obj. And this is a problem. Inside obj one could hidden anything. So when you change using of the result you change everything.

Let’s look at the q function:

member __.q<'Result> (query:string, parameters : IDictionary<string,obj>) : DBResult.DBResult<'Result> =
    try 
        connection.Query<'Result>(query, parameters) |> Seq.toList |> Ok
    with 
        e -> e.ToString() |> Error.fromStringg

Query<T> returns IEnumerable<'T>.

In the first case type T is obj and there is no problem here.

In the second case type T is seq<obj> so your query returns seq <obj> list.

Why it raises such exception - I don’t know. Maybe I’ll try to find out this later. I’m even unsure that Dapper can map result to IEnumerable<IEnumerable<_>> yet. But this is doesn’t matter here. Your case is the same as at the Q from SO. You just use Dapper in a wrong way.

From what you describe it seems like you want to get a result as simple dynamic table depending on query. For this query expected result is like below, right?

id       name                 website                       email
51565  info@ggsdgsdgs.net     http://www.hdfghdgfhdgf.net   Dryades
51603  info@treztezrtez.net   http://www.tzetreztzer.net    Orthrus

And there is a class DataTable. I’d use it if there are no ways to describe your data better.

5

thanks! I’ll try to use DataTable, seems to be exactly what I need indeed!

6

Just to report I got it working by combining Dapper’s connection.ExecuteReader(query, parameters) and Datatable:

let table = new DataTable()
let reader = connection.ExecuteReader(query, parameters)
table.Load reader

which lets me print data in tables like this:

    for c in table.Columns do  
        printf "%s\t" c.ColumnName
    printfn ""

    for r in table.Rows do
        for c in table.Columns do  
            printf "%A\t" (r.Item(c))
        printfn ""

I edited the example code and hope I didn’t screw up in the process, but this gives an idea of the code if others would need it in the future

1 Like