5 ms·
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 thi
by tfigment 10y ago
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.
- bunderbunder 10y agoOn 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.
- tfigment 10y agoThanks. 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.
- deleted 10y ago[deleted]
- deanward81 10y agoDapper 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#parameterized-queries 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.
- bunderbunder 10y agoDapper 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.
- deleted 10y ago[deleted]
- deanward81 10y agoDapper will use a constructor with parameter names that match your query's columns if you need immutability.
- intrasight 10y agounpack result sets into what?