Why LINQ beats SQL

I do like the syntax also though.

The happy medium is a flexible ORM that automates the repetitive stuff but let's you call out to the higher level stuff...because if you don't have that your developers are going to end up building their own awful ORM-ish layer to automate the repetitive stuff anyway.

I don't think it's conceptually right to regard LINQ as a "SQL generator". I think it's better to think of both LINQ and SQL as just different database control languages.

(It might be the case that behind the scenes LINQ is implemented by generating SQL, but that's an implementation detail. Just like Haskell is implemented by generating C behind the scenes but it's more reasonable to think of Haskell as its own separate programming language than as a "C generator")

1. Compile time checking. I hate when I'm writing a straight SQL query in a string literal and misspell one of the column names, because everything will compile and unit tests will pass, and the mistake isn't caught until I run integration tests. I want a short edit-compile-debug cycle. jOOQ is a good example of something that helps here.

2. Arbitrary filters. Often I have to expose a REST API where the user can add an arbitrary number of filters to their query. You can't write SQL literals for these because there's a combinatorial explosion of possibilities. So you wind up dynamically generating SQL yourself by concatenating strings in the WHERE clause, hoping and praying you don't accidentally allow SQL injection. I want an ORM to deal with that shit for me.

Vlad Mihalcea, a Hibernate developer/evangelist, has been doing amazing work improving the project's documentation and providing educational material about how to use it properly without sacrificing performance[1]. I can't recommend his book High-Performance Java Persistence enough: https://leanpub.com/high-performance-java-persistence

For me, his key lesson is that ORMs can be incredibly useful if, and only if, your understanding of SQL and DB performance is good enough that you can use them in a DB-friendly way. It's really simple things like expressing relationships between your domain entities such that the resulting tables can be easily joined. You still get the productivity that comes from skipping loads of JDBC boilerplate, but you don't feel like there's a load of magic between you and the DB.

He also pushes the idea that you should use Hibernate in combination with other DB tools: Liquibase/Flyway for migrations, and JOOQ for optimised queries with lots of joins,or DB-specific work. By having a decent ORM, SQL builder, and migration tool, you can use be flexible depending on the situation. Complex, performance-insensitive business logic can use the domain model from the ORM, whereas API-backing queries can be expressed a single, tightly constrained JOOQ call, and the actual creation and maintenance of the schema can be done via pure SQL scripts that are managed by your migration tool.

[1] https://vladmihalcea.com/

Personally I like creating an API of stored procedures. Make users that can _only_ execute those sprocs with zero dynamic SQL in them. Users can only do what you tell them they can do via your sproc API.

Why don't you hire a DBA and have all major queries go through them if it's that important instead of asking developers to master yet another thing because you got burned in the past?

I'm all for learning database stuff, but you and everyone else are pulling developers in all different directions by simultaneously claiming "YOU NEED TO KNOW THIS SHIT" about <THING THAT HURT YOU PREVIOUSLY>.

- Prototype and use Linq first ( faster to develop and good enough queries in production)

- Static Typed LINQ reduces mistakes ( a domain object changes, an error appears in your code), definatly later on the road when changing the domain.

- Iterate and improve when i goes slow. Try to keep LINQ, but off course, SQL is faster.

For example, if you want the top N items ordered by f(x), and LINQ doesn't know how to map f(x) to a SQL Server built-in, it has to pull the entire set over the wire, and find the top N client-side. Obviously, if your table is huge, performance won't be good.

That can even affect more than performance. For example, SQL Server orders GUIDs differently than .NET, and SQL Server's decimal isn't quite a IEEE float (https://msdn.microsoft.com/en-us/library/bb738633(v=vs.110)....)

If it actually only ends up creating some complex sql behind the scenes to get those ten rows, how horribly convoluted did it become and wouldn't it be better to write a straight SQL query to do the same thing, even if it's a little more complicated than the pseudo code? I guess I prefer more control. If my SQL query turns out to be slow, then I can learn from that and fix it. If the generated query from LINQ is slow, I have no recourse over it.

It's a pretty bizarre situation.

    SELECT * FROM items
    ORDER BY key
    OFFSET 100 ROWS
    FETCH NEXT 10 ROWS ONLY

    select * from data where data.indexed_column > last_seen order by data.indexed_column desc limit 100

I'm rather dubious of this claim. This is kind of like claiming the reason we use Javascript is because of how good it is. The awkwardness of SQL is not because it is declarative, otherwise LINQ would have the same awkwardness because it is also declarative. I think the real reason SQL is because database vendors have not made an effort to add support for other languages.

It's a language that can barely express subqueries and query aliases.

Every ORM has shorthand syntax for expressing filters and order by statements that's way better than the SQL equivalents.

It's barely composable. Do you want to build a query dynamically based on a set of conditions. Tough luck, use a query builder that's prettier anyway.

Its syntax is not amenable to autocompletion. Join syntax is ugly on the eyes and hard to indent.

Seriously, let's kill the love for SQL. I'd rather read queries written in EF Codd's original relational algebra syntax (https://en.wikipedia.org/wiki/Relational_algebra) rather than embedding the abortion that is SQL in my code.

SQL is an ugly COBOLesque veneer over an elegant relational calculus core.

But the "new foundation" you speak of already exists, and has for decades. Competing query languages based on relational calculus are less ugly – QUEL, D, etc.

Sadly, those better looking query languages have failed in the marketplace–ugly SQL won.

Sadly, many IT professionals lack the intuition on this.

SQL is a highly sophisticated language since many years. Having done insane amounts of data prepping and validation for analytics, I will attest to this.

However, if I am writing software in .NET, I would trade away SQL for LINQ any day of the week. (Preferrably by means of some sort of intuitive DAL.)

I would like to see C#'s LINQ get an update. It seems to have been left to rot since Eric Meijer left the team. It doesn't even support the new values tuples where it's been fitted to every other part of the language:

    from (x,y) in Some((1,2))   // (x,y) will error
    select x + y;

Honestly, if you've seen what LINQ compiles to, it looks an awful lot like a Java 8 Stream.

Using Wikipedia's example of LINQ translation (https://en.wikipedia.org/wiki/Language_Integrated_Query#Lang...):

Written LINQ is:

    var results = from c in SomeCollection
                  where c.SomeProperty < 10
                  select new {c.SomeProperty, c.OtherProperty};

    var results =
         SomeCollection
            .Where(c => c.SomeProperty < 10)
            .Select(c => new {c.SomeProperty, c.OtherProperty});

    Stream<> results = someCollection.stream()
                                     .filter(c -> c.getSomeProperty() < 10)
                                     .map(c -> new AbstractMap.SimpleEntry<>(c.getSomeProperty(), c.getOtherProperty()));

I think Streams have more LINQ in them than most people think.

Disclaimer: it's been about a year since I've written Java 8, and this is off the top of my head (and the last time I used it, my employer's codebase had a class for pairs that was better than just SimpleEntry), so the code could be wrong.

Edit: and just for completeness... the same in Python generator expressions:

    results = ((c.some_property, c.other_property)
               for c in some_collection
               if c.some_property < 10)

The LINQ to SQL stuff annoys me, because while it works, it's a painful disaster to implement yourself against your own data stores. Believe me, I've tried.

Yet somehow, dirty, ugly, hard-to-write SQL is still here while ORMs keep coming and going. And I have yet to see one without an escape hatch allowing raw SQL.

I convert all objects that i import to a big SQL Query.

Something like

First query: UPDATE table SET active=0;

Second ( big ) query: UPDATE table SET properties=values IF ROWCOUNT =0 INSER INTO table(properties)VALUES(values)

Now i design most of my pages through 1 service/method with a lot of if's and else and re-use that method all the time

For example, a ecommerce-site i made has one function for querying the products, paging, searching, sorting by X, ...). Although, it get's rather complex lately, i wouldn't have it any other way. ( with complex, i mean i now also grouping them by category and category code, a category order number, a product order number, by product title ( varying by the current language), ...

And yes, it' seperated in my service layer ;) and no, i won't change it because this is the fastest way untill now.

Alternatively, you could use Extensions on IQueryable and IOrderedQueryable. Then you do something like db.Products.FilterBySearchTerm(searchTerm).OrderAllBy(sortPropertyName);

But i like ProductHelper.GetProducts(filterWithSettings,SortOnSettings,GroupBySettings); more, mostly because it's easier when i need to customize all the methods because of some "sudden" Change Request that changes all the internal workings :(

Also, if you are new to Linq To Sql. Don't forget DbFunctions / SqlFunctions for built-in SQL Functionality ( depending the EF version you are using). Eg. The LIKE operator has been added in the new EF.

Also, you can always using DataContext.Database.Sql() for using standard SQL.

=====

Pro Tip: DbContext is already a Repository Pattern if you are using DDD !

=====

PS. If you don't know LINQ. It generates a long SQL query and it's a bit slow, BUT it's very productive in development . Here's an example with both Generated SQL (commented between /* */ ) and Linq in one of my subbmitted issues on EF : https://github.com/NicoJuicy/EF-ContainsAndStartsWith/blob/m...

I like LINQ: Develop fast and improve when something executes slow :)

I haven't used entity framework or any newer tech beyond LINQ to SQL but I'd have to believe such a trivial feature is present in them.

Or maybe it was the look you have when you are SURE that someone is telling you an elaborate joke and suddenly realize that no punch line is coming.

LINQ: I'm looking at YOU :-/

Of course, if you don't know what you're doing, you can write some monstrous Linq queries that will be dog slow and exhibit all the worst n+1 behavior, pretty easily.

For example, that's Sequel querying DSL: http://sequel.jeremyevans.net/rdoc/files/doc/querying_rdoc.h...

And I think LINQ is "language integrated" because C#, like Java, has poor DSL building possibilities. So they're added ORM-specific syntax right into language.

You can always use DataContext.Database.SQL(query) ofc

Performance tuning LINQ is a science that I think escapes far too many people. Often it seemed to simply be mistakes that they wouldn't make in hand-written SQL, but they were writing LINQ too much like C# instead of enough like SQL.