# How to use sequences returned by Dapper's Query? **URL:** \ **Category:** Uncategorized\ **Created:** [May 5, 2019, 6:23pm UTC](https://forums.fsharp.org/t/how-to-use-sequences-returned-by-dappers-query/782 "2019-05-05T18:23:39Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![rbauduin](https://yyz2.discourse-cdn.com/flex030/user_avatar/forums.fsharp.org/rbauduin/32/314_2.png) [@rbauduin](https://forums.fsharp.org/u/rbauduin)\ **Post date:** [May 5, 2019, 6:23pm UTC](https://forums.fsharp.org/t/how-to-use-sequences-returned-by-dappers-query/782/1 "2019-05-05T18:23:39Z")
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](https://www.postgresql.org/docs/9.2/tablefunc.html#AEN152432) 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: ```auto 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: ```auto // 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([])) // 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 ```auto "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 ```auto 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 ---
**Author:** ![FoggyFinder](https://yyz2.discourse-cdn.com/flex030/user_avatar/forums.fsharp.org/foggyfinder/32/136_2.png) [@FoggyFinder](https://forums.fsharp.org/u/FoggyFinder)\ **Post date:** [May 5, 2019, 8:51pm UTC](https://forums.fsharp.org/t/how-to-use-sequences-returned-by-dappers-query/782/2 "2019-05-05T20:51:10Z")
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>`. When it should only be `Query` 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>>` or maybe something less complex indeed ? ---
**Author:** ![rbauduin](https://yyz2.discourse-cdn.com/flex030/user_avatar/forums.fsharp.org/rbauduin/32/314_2.png) [@rbauduin](https://forums.fsharp.org/u/rbauduin)\ **Post date:** [May 6, 2019, 4:52pm UTC](https://forums.fsharp.org/t/how-to-use-sequences-returned-by-dappers-query/782/3 "2019-05-06T16:52:40Z")
thanks @FoggyFinder for your answer. The whole code is on gitlab: - [the line causing touble](https://gitlab.com/myowndb/myowndb/blob/explorations/lib/Program.fs#L241). - [client.q](https://gitlab.com/myowndb/myowndb/blob/explorations/lib/DB.fs#L50) 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<_>`): ```auto Ok [seq [[id, 51565]; [name, info@ylu.net]; [website, http://www.ylu.net]; [email, Dryades]]] ``` but ```auto 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! ---
**Author:** ![FoggyFinder](https://yyz2.discourse-cdn.com/flex030/user_avatar/forums.fsharp.org/foggyfinder/32/136_2.png) [@FoggyFinder](https://forums.fsharp.org/u/FoggyFinder)\ **Post date:** [May 6, 2019, 10:15pm UTC](https://forums.fsharp.org/t/how-to-use-sequences-returned-by-dappers-query/782/4 "2019-05-06T22:15:09Z")
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: ```auto member __.q<'Result> (query:string, parameters : IDictionary) : DBResult.DBResult<'Result> = try connection.Query<'Result>(query, parameters) |> Seq.toList |> Ok with e -> e.ToString() |> Error.fromStringg ``` `Query` 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` so your query returns `seq 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>` 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? ```auto 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](https://docs.microsoft.com/en-us/dotnet/api/system.data.datatable?view=netframework-4.8). I’d use it if there are no ways to describe your data better. ---
**Author:** ![rbauduin](https://yyz2.discourse-cdn.com/flex030/user_avatar/forums.fsharp.org/rbauduin/32/314_2.png) [@rbauduin](https://forums.fsharp.org/u/rbauduin)\ **Post date:** [May 7, 2019, 6:10pm UTC](https://forums.fsharp.org/t/how-to-use-sequences-returned-by-dappers-query/782/5 "2019-05-07T18:10:43Z")
thanks! I’ll try to use DataTable, seems to be exactly what I need indeed! ---
**Author:** ![rbauduin](https://yyz2.discourse-cdn.com/flex030/user_avatar/forums.fsharp.org/rbauduin/32/314_2.png) [@rbauduin](https://forums.fsharp.org/u/rbauduin)\ **Post date:** [May 11, 2019, 5:58pm UTC](https://forums.fsharp.org/t/how-to-use-sequences-returned-by-dappers-query/782/6 "2019-05-11T17:58:10Z")
Just to report I got it working by combining Dapper’s `connection.ExecuteReader(query, parameters)` and Datatable: ```auto let table = new DataTable() let reader = connection.ExecuteReader(query, parameters) table.Load reader ``` which lets me print data in tables like this: ```auto 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