One of the C# language features that makes LINQ so powerful is expression tree support; this allows the lambda (i.e. x => x * 2) to be read as a structured object and turned into something else (such as sql).
This would be pretty useful in ES7 or whatever spec is coming down the road for javascript.
In practice, it ends up being too powerful. The expression tree stuff tends to mostly be useful when you're using the IQueryable<T> interface. If there's a way to use IQueryable<T> to much effect without breaking encapsulation and creating tight coupling, I haven't seen it.
When you're directly interacting with the database tight coupling is always going to happen, and trying to hide that only adds needless complexity.
The nice part about using Linq and IQueryable instead of e.g. query strings, is that the compiler (and intellisense) can check for some errors at compile-time instead of run-time. Just have to make sure your database model is 1:1 with the actual database.
After you have the data you can throw it into a non-database DTO to re-couple a bit and pass it between layers.
If you're looking for compile time type checking, I'd say you should go one further and just write your data access layer in F#. The SQL type provider gives you much more thorough compile-time error checking than anything I've seen in an ORM.
There are a couple different ways you can set it up to use the actual database to do this, thereby eliminating the extra work of having to maintain two different copies of your schema in two different languages, and making sure they're kept in sync.
People tend to drop type providers in F# for non-trivial programs, because the benefits need to be extreme in order to justify adding flakiness to the build. Type providers also have the problem of yielding errors to people who may not be in the best position to fix them. In a bigger team, it may be better to have one part of the program be yield errors at runtime, but still compile, so the team can keep on working until it is fixed. (The alternative of course being code generation, which is triggered intentionally.)
Disagree with this; because it very much depends on the program being written. From what I've seen they've become more popular with ironically the exception of the SQL ones for the reason you describe. As long as you can get the schema/example file locally on the source tree without the type provider needing to fetch it remotely it saves having to write up a lot of classes by hand. E.g with web UI calls just get the swagger Json and download it into your solution, for rest/json APIs just drop all the example documents into a folder and have the type provider reference that. Once the source data is local the flakiness mostly goes away and it just becomes a much quicker/less error prone way of expressing schema from a wide variety of sources. It's personally saved me a lot of time in development especially when interacting with many data sources at the same time - that's a lot of Dtos I would have to craft myself and I would in this case probably moved to a dynamic language a while ago for the work I do. A type provider that allows local schema would deal with your issue and then you would get type checked SQL queries at compile time without the direct db dependency and connection latency.
> A type provider that allows local schema would deal with your issue and then you would get type checked SQL queries at compile time without the direct db dependency and connection latency.
You might be interested in Rezoom.SQL [1], which is exactly the thing you describe.
As an aside, it also has unusually thorough documentation [2] by F# standards.
If you only use it that way, type providers are only slightly more convenient than doing what everyone already does for every other language: code generation.
See quicktype, for example, which supports a lot of languages.
This isn't built in to the editor, but can be run from the command line. A benefit is that it does not suffer from the other problems with type providers, like not supporting the full language, (e.g., records and discriminated unions in F#).
Having 10 lines of code for 10 different data sources and having a explorable Intellisense API for all of them is much nicer though than having 10 code generator + command line tools in my now required build pipeline, each one with a different syntax. Don't get me wrong - I will resort to code generation for some things if there isn't a type provider available but I always find more friction with this path and still doesn't give me all the benefits of type providers (e.g the SQL static type checking aspect). It works across different IDE's, don't need to introduce more build tooling, generated code is obvious vs a code generator, is as cross-platform as the underlying language/runtime is, etc.
Slightly more convenient for one source becomes very convenient as the amount of data your analyzing increases. It's not the be-all-end-all it was promised to be as a feature but it is easier than code generation IMO. If you can get rid of the disadvantages by keeping reference schema locally in its native form (e.g no flakiness you mention) then why not use it?
Fair points. I consider the lack of support for the good F# features like records and DU to be an extremely big downside. I think we agree on the facts, just weigh the costs and benefits differently.
Because IQueryable's don't always translate to working SQL. I've broken EF's IQueryable a few times in the past; NHibernate's one was really bad if I recall. The expression inside the IQueryable operator is checked for type safety but the ability to generate SQL from it isn't which happens at runtime when the LINQ is evaluated. The F# approach bypasses the expression -> SQL translation and directly checks the SQL at compile time if it valid against a database schema rather than the expression AST that C# LINQ does.
I disagree. You can and often should decouple from your database by calling only parameterized stored procedures. Your code only needs to know the name of the proc and the paeans to pass.
In non-trivial applications this is where you get the best performance bang for your buck. ORMs are convenient dev tools, but I don’t see them as real options for deploying real apps of any meaningful scale.
There is a world of difference between a Micro-ORM such as Dapper and a bloated mess of an ORM like Entity Framework. They are not even close to the same thing.
It's faster, in memory and multiple people can test simultaneously without contention issues. I've done the whole wrap sql tests in a transaction thing, it was slow and wrought with database locks.
The added advantage is that if you do it right. You not only can switch between different RDMSs you can switch to anything that has a Linq provider. I have a mix of SQL Server and Mongo and the clients don't know the difference they just pass in Expression like (e => e.Id && e.salary > 50000) and it gets translated at runtime to either MongoQuery or Sql. I've switch between the two and they never knew the difference - just update a nuget package.
At some point, you've got to have a layer that interacts with and is coupled to the database. Nothing is stopping you from creating abstractions above this. Why is this a problem?
If you subscribe to the idea that the bit of your app that is coupled to the database should be very thin, then abstractions that live between that layer and the database tend to be a useless piece of excess complication. In the best case.
I've found that those two extra steps in the middle typically add negative net value.
They don't really make the code more readable; LINQ is so close to basic SQL that I can't fathom how a developer who can learn to write LINQ queries wouldn't be able to grok SQL.
They cut you off from the full power of the database by limiting you to a sort of least common denominator version of the things you can do with real SQL (e.g., no PIVOT or MERGE if you're using mssql).
They make the code harder to properly understand, because it's not readily apparent what will execute on the database. Some expressions will compile to SQL, some will execute in code, and some will execute in code and also force a bunch of other stuff that might have been compilable to SQL to execute in code instead.
They will probably remove some boilerplate, but you can just as easily remove that boilerplate with a too like Dapper that doesn't come with all those other downsides.
A benefit is strong typing. A change in the model breaks the queries and the code doesn't compile. You change the database when you are using stored procedures or direct queries and your code still compiles fine.
> They make the code harder to properly understand, because it's not readily apparent what will execute on the database. Some expressions will compile to SQL, some will execute in code, and some will execute in code and also force a bunch of other stuff that might have been compilable to SQL to execute in code instead.
I find statements like this statement are the crux differentiator of whether or not you find IQueryable<T> to be a useful abstraction or not. If you think LINQ is some unfathomable black box that magically, and with little observable difference, spits out SQL and/or executes code then of course you are going to have a bad time.
In my time doing LINQ performance work, I've had some ideas on how to make the tooling better (ToList() considered harmful, there could be stronger highlighting of "monad transitions" especially IQueryable<T> to (accidental) IEnumerable<T> and/or (intentional and mistaken) IList<T>), but all the information is already there in the tooling for the most part. It's not a black box, you can query a lot of information with tooltips alone.
LINQ really shines in places where you've got one "query" that runs on multiple databases and multiple types of databases, with no code change but a connection string or three.
Those transitions between monads can be a headache to an inexperienced developer and a delightful tool in experienced hands. There is a huge benefit that the "same query" can run on database with IQueryable<T> and also in memory with IEnumerable<T>, are interesting ways to take advantage of that, reusing code that can work both with fresh data directly on the database and with cached data already in memory as if it were the same data. There are amazing strengths to that abstraction.
There is also no doubt that it all has a steep learning curve.
LINQ is not just for SQL. Say if your LINQ queries are but simply `where obj.prop == val`, you can have a Redis LINQ provider hot-swapped in.
Even without the use of other backends it may still be beneficial. The "abstraction layer" need not to be thin as you explained here. You have the power to reinterpret and optimize your query. This allows visitor patterns (and maybe Roslyn-style analyzers) to kick in in the symbolic form, not just strings (indeed there's SQL parser, but again why? why limit it to SQL?)
With Employee being a regular old undecorated POCO.
Then if you call it:
Find(e => e.id == 5)
The repository can be searching for data in a SQL Server database using Entity Framework or a Mongo database using the Mongo driver or even an in memory list for unit testing and the calling code doesn't care.
Yes the expression will be translated to the appropriate query (MongoQuery or SQL) without any coupling.
This would be pretty useful in ES7 or whatever spec is coming down the road for javascript.