Comment by tfigment | Hacker News Reader

Comment by tfigment | Hacker News Reader

Parent Full thread

I written a fair number of C# LOB apps and use LINQ quite a bit with mysql. I don't even want to talk about Java and some of its ORMs as its too painful to think about. I agree with the sentiment of the post but in compiled languages I really want an ORM to simplify unpacking result sets. LINQ is great when it works but joins sort of suck as well as calling in-built sql functions and it can some times generate highly unoptimized queries. I frequently pull in data via LINQ to SQL and then massage the data with LINQ for objects where I have better control of performance and operations.

Trying to write pure SQL leads to lots of manual unpacking of the result set which I generally dislike and is much harder to maintain and doesn't work well in practice compared to when LINQ actually works.

I think maybe what I've really learned is use scripting / loosely coupled type systems when working with SQL. In python, I usually just call sql directly rather than use sqlalchemy and its fine because of the loose typing and result set unpacking isn't terrible.

On C#, Dapper's useful, but not perfect, for letting you write your own queries and then making it easy to unpack the result sets.

Unfortunately, it relies on property setters for doing the unpacking, so it doesn't interact super well with your code if you like to avoid unnecessary mutability.

The only publicly-available lightweight ORM I know of that does a good job with that is the SQL type provider in F#.Data. That one is head-and-shoulders above any other option I've found for working with databases. It does require that you write your data access layer in F#, though, which may make it a hard one to sell at work.

Thanks. That actually doesn't look that bad. It would be nice to have anonymous type objects but I recognize the difficulty in that. This looks like a nice compromise. Now if they could also fix passing in arrays as part of a parameterized query "select x from y where z in ?" where ? is a collection of strings or integers it would be perfect but I think that is a driver/interface problem.

Edit: Looks like it actually does says it does support the enumerable but I'm questioning if it will do what I want. Will have to test out but I'm guessing there is a limit to the size of the array.

Dapper accepts anonymous objects for passing query parameters. It will return an expando if you don't specify the type of object you want back, too. That can be convenient, but I don't personally like having dynamic objects running too wild in my code so I end up manually mapping it to another type before returning anyway. (AutoMapper can help here.)

The IEnumerable support will generate (parameterized, I believe) inline arrays in the query. Not really my favorite, but it works.

The thing that I couldn't get over, and which ultimately led me to write my own micro-ORM at my last job, was the weak support for table-valued parameters. But I gather they've fixed that since then, so hopefully it's not a big deal anymore.

Dapper supports IN statements being passed an array:

SELECT x, y FROM A WHERE z IN @ids

Works if you pass in a parameters object containing a property called ids that is a list/array of some sort (see https://github.com/StackExchange/dapper-dot-net#parameterize...).

On SQL 2016 this uses STRING_SPLIT across an nvarchar(MAX) which makes it effectively unbounded. Earlier versions and other platforms have restrictions on the number of elements in the array/list.

Dapper will use a constructor with parameter names that match your query's columns if you need immutability.

unpack result sets into what?

Reply on news.ycombinator.com