5 ms·

> LINQ is the only “ORM” that is truly worth using I've been thinking a lot about ORMs and they almost usually fall flat at some level since they are not nearl

• scarface74 4y ago
  LINQ is a full query language you can left join, right join, inner join limits, etc. It even maps C# functions to Sql functions. If you can express it with SQL, you can express it with LINQ. Cross language will still be an issue. LINQ isn’t really an “ORM” it is a method to translate C# commands used to work over related collections to expression trees and those expression trees are interpreted/translated at runtime by a provider. You need support from the language/runtime to treat expression trees as a first class type. expression tree parser. It’s not for the faint of heart.
  • jolux 4y ago
    > If you can express it with SQL, you can express it with LINQ. Not if it uses CTEs or other advanced SQL features. It can do basic select, join, group, sure. But that's just scratching the surface of how powerful SQL is. As for requiring language support: in Elixir, Ecto.Query essentially implements a LINQ-like DSL as a library (albeit with macros). See https://hexdocs.pm/ecto/Ecto.Query.html https://hexdocs.pm/ecto/Ecto.Query.html
    • scarface74 4y ago
      From what I see a CTE is just an expression that you can reference. In C#, if you did something like this var seniorMales = from user in users where u.Age > 65 && u.Sex == “male” select u seniorMales is just an IQueryable<User> Later on you could say var gaMales = seniorMales.Where(s => s.State == “GA”) var floridaMale = seniorMales.Where(s => s.State ==“Florida”) All three of those are just expressions that have not hit the database yet. Then when you want to actually run the query and limit the number of rows, you can do var result = floridaMales.Limit(20).ToList() It would then create a query including a limit clause just as you would expect it to. Now if you do floridaMales.ToList().Limit(10) It would return all of the rows in the table to the client and it would be limited on the client side (don’t do that). Worse case, if LINQ can’t express in query syntax, a provider can add its own extensions in function syntax and the provider can still parse the expression tree.
      • jolux 4y ago
        Yes, I've been a professional C#/.NET developer before and understand LINQ and Entity Framework. The difference is that CTEs allow you to reference multiple tables at once and do inserts and updates on the intermediate result set. You can also use recursive CTEs to do hierarchical and graph-style queries, which you can't do with LINQ as far as I know. LINQ also can't do window functions. Like I said, it covers the basics of SQL that most applications need. But as soon as you need to go beyond that you'll be writing custom SQL.
        • dropofwill 4y ago
          Never used C#, but in Java land we have a similar dsl jooq, which supports everything i’ve ever needed, including window functions https://www.jooq.org/doc/latest/manual/sql-building/column-expressions/window-functions/ https://www.jooq.org/doc/latest/manual/sql-building/column-e...
          • jolux 4y ago
            Yeah, there's nothing else quite like jOOQ as far as I know. I think SQLAlchemy in Python is the only other library that even comes close.
          • matwood 4y ago
            jooq is amazing and one of the things I really miss any time I leave Java land.
            • lukaseder 4y ago
              Then don't leave
• mondaygreens 4y ago
  > I'm thinking the best approach if I had time/money would be to develop a better query language that is close enough to SQL for people to learn, but then also translates back down to SQL Have a look at EdgeQL, the query language that powers edgedb.