Improve overall user experience in using F# with expression-based ORMs

[bessgeor]

I'm trying to move the project I'm working on from C# towards F#.
It's mostly back-end and involves a lot of database querying which is being done via EntityFramework and Linq2DB, which seems a pretty common case for C# projects.

This issue is to keep track of issues related to F# usability in scenarios involving expression-based ORMs.

If this issue format seems inappropriate for the repo, feel free to close it.

Quotation -> Expression translation

• Nested Linq expression trees result in quotation related calls #2758 Nested Linq expression trees result in quotation related calls
• to_be_filed Translate TuplePat ident referencing to .PropertyGet ItemX expressions

Performance

• Reduce memory footprint and improve performance of expression-based ORM usage #10522 Reduce memory footprint and improve performance of expression-based ORM usage

QueryBuilder

• update VS project options cache when files are added/removed #1813 LEFT JOIN in F#
• Query builder with EF core can't return fsharp tuples (and sometimes new objects) using async methods #3782 Query builder with EF core can't return fsharp tuples (and sometimes new objects) using async methods
• leftOuterJoin is different from C# version, workaround doesn't work for double left join #6552 leftOuterJoin is different from C# version, workaround doesn't work for double left join

[dsyme]

This is an incredibly valuable overview.

It's been a long time since I looked at this area, it would be great to have community assistance here

[Szer]

I would like to add 5 cents here. More often than not, ORM experience is a blocker for F# introduction in a lot of companies

[dsyme]

@Szer Is it really the case that the specific bugs above (all related to LINQ queries) are blockers?

Or are you referring to the general ORM area - including the whole end to end experience (including docs, perf etc.) of other ORM techniques, including techniques that are non-LINQ?

I've not specifically heard that either are an absolute blocker before - though I can see each one is technically challenging in certain specific circumstances, and I'd love to see improvements here.

In commercial usage there are often many potential blockers as people look for reasons to proceeed/reject a technology. I just want to gather evidence here and make sure we're working on the right things, and that depends whether you're saying something general about ORM or specific about these issues.

And if the issues are the actual blockers, which is most important? A priority list would be helpful

thanks
don

[dsyme]

As an aside, on the whole it's really hard to judge just how much F# queries actually get used in practice. Many of the community write zero queries. Some commercial projects write hundreds (and perf is not critical, because it's experimental code). Some people use them in production performance-critical code.

Lots to ponder

[cartermp]

my guess is @Szer's comment is about using ORMs in general. It's an ugly reality of bogstandard software interacting with a sql database and there aren't really any good options when using F#. None ever feel particularly friendly towards expression-based functional programming, and especially EF (the one pushed by microsoft) is extremely complicated and full of little "magic" things that are C#-centric and even worse with F#. I would posit that programming against EF from F# is most definitely a "hard mode" kind of programming, and its ubiquity in the software world means nobody using F# on the server is going to have a good time in that kind of environment.

[Szer]

@cartermp totally agree.

My thought was about EF, which is the most prominent ORM in the market and MS is behind it.
To use it properly language has to have mutable properties, inheritance + protected, virtual properties, partial classes, implicit casts, implicit discards. And just be C# (if you want to codegen migrations)

F# experience with EF is so bad, that in our community we just recommend don't bother with EF at all and use Dapper or Linq2DB. And that advice is a blocker because people got used to EF so much

[roji]

Hi all, Shay here from the EF team.

Just wanted to say that better F# support is a recurring occasional conversation, and that we'd definitely interested in improving the F# experience for EF Core (subject to priorities of course). While it's true that we're C#-centric as a general rule (because that's what the vast majority of our users use), I think the team is quite open to improvements for supporting other languages (several such improvements for VB.NET have gone into the recent EF Core 5.0 release). One problem here is that nobody in the team is a real F# native (though @bricelam has already done some work), and so we definitely require help from the F# community to know exactly what is needed. Another thing to note, is that we haven't seen that much interest (in the form of issues being filed for better F# support), though that may well be a vicious cycle where people aren't even trying EF with F#.

More concretely:

• We're definitely open to support F#-specific expression tree constructs or method translations (is this the case with left joins mentioned above)? Obviously it would be ideal for F# to produce the same constructs as C# where possible, but we could take care of cases where there are differences.
• Better support for immutable types is something we will probably try to look at, at some point... Especially as the introduction of records into C# 9 is also creating a push towards better immutability there.

One additional important note... For people familiar with the older EF6, I'd recommend not assuming that EF Core works in the same way. There are certainly similarities between the two, but EF Core was rewritten from the ground up and is far more extensible in many ways.

Bottom line, please don't hesitate to engage with us by opening concrete issues on our repo!

[iTKerry]

I also want to add my five cents. As @Szer mentioned, I'm from another company that was blocked during the F# investigation as a potentially new language because of ORM. For us it is critical because we use RDBMS a lot. Yes, that is possible to use Dapper or another micro-ORMs but that is completely dissimilar experience than using EF or NHibernate.

[Lanayx]

@roji I can confirm the vicious cycle - almost every new person joining russian speaking fsharp community starts with ef (from previous csharp experience) as Microsoft default orm and after having bad times with it doesn't ever return to it or use at work even after gaining sufficient fsharp knowledge. Current situation is fsharp=microOrm, which is not that bad, but is a blocker for orm apologets. This issue is a great start to change the situation

[jkone27]

@roji @dsyme updates on this and on F# interop? maybe also some docs on how to use F# with entity framework today could help,
just dreaming but if SQLProvider could "plug" to entity framework model or evolve together with it, maybe it would make up something greater for DB first approach https://fsprojects.github.io/SQLProvider/ is EF team aware of this project?

[roji]

There unfortunately hasn't been much progress - on the EF side we've certainly been busy with other things, and have also barely seen engagement or concrete problems/issues from F# users.

Some sort of magical integration with SQLProvider is likely not going to be possible - it's unfortunately not feasible to cause two completely different database access tools to just work together.