What kind of ORM should we use in the project?

MVP Consensus
,
1

Since the technology stack on the wiki states that the ORM for this project is still undecided I figured its time to decide an ORM.

These are the most viable options:

Entity framework

https://github.com/dotnet/efcore
EF is a framework that lets you query the database with an IQueryable approach and execute updates on the database using a unit of work pattern. All queries are executed via linq. There is mostly no SQL to write (though it does permit you to write it).

Pros

  • Widely known and used
  • Supported by MS and tested over the last 10 years or so
  • Has great support for migrations and the audit tables we are making
  • Has easy support and integration with most libraries
  • Supports most major databases including Postgre
  • Heavily extensible to a similar fashion as how .net core is

Cons

  • A pretty big overhead. Sometimes double than the rest. See the table at https://github.com/StackExchange/Dapper
  • If we want to use some libraries and they don’t have support for EF we won’t be able to use them.
  • If we decide at some point that EF doesn’t suit us for some reason at some point then we are stuck and it’ll be extremely difficult to change - hence its a framework, not a library.

Dapper

https://github.com/StackExchange/Dapper
Dapper is a library which its main job is to map database tables to classes.
Made by Marc Gravell (:heart:) and the SE team

Pros

  • Pretty fast
  • No limitations in libraries, can write SQL freely.
  • Has a few libraries that can help with some basic functionalities like Dapper.Contrib or you can make your own pretty easily.

Cons

  • Needs a lot more configuration and architectural planning
  • Need to write your own migrations - ie fluent migrator or some other kind of library
  • This is my opinion but in big projects it eventually leads to writing your own full ORM using dapper as a base.

Linq2db

https://github.com/linq2db/linq2db
Somewhat a combination of dapper and EF. You write linq and it converts it to sql queries. Maps the results to classes.

Pros

  • Fastest
  • Simple to use and understand
  • (Isn’t related to MS or SE)

Cons

  • Same cons as Dapper.

Considerations

To iterate here the most important things to consider:

  • Migration
  • Automatic actions such as adding the update_date/update_by etc should be done by the backend with the help of the ORM.
  • Having an actual proper design pattern (ie unit of work) in your ORM helps to build the bigger architure in many ways.
  • Speed.

Omitted ORMS

NHibernate - No experience in this.
No ORM - I don’t see a point when you can just Dapper.

3 Likes
2

I would suggest Entity Framework Core. I don’t think any of the cons listed are actually true, though…Dapper is faster than EF Core in some places but I don’t think it’s that much of a bottleneck, to be honest.

If the code is written decoupled and abstracted, the project will not necessarily be tied to EF Core. As far limitation of libraries, EF Core is for the database…it should not significantly impact other choices on what libraries to use.

2 Likes
3

Having no experience at all with an ORM, what are the advantages over writing SQL by hand?

Also, is an ORM purely a build tool, or does it involve a library that is included in the code? If the latter, are all the options compatible with the APGL?

4

@celtschk - writing SQL by hand becomes hard to maintain and verbose. You end up maintaining tons of strings with no type safety in your code or you have tied yourself to another hard to maintain story of thousands of stored procedures.

Essentially, the ORM abstracts what database you’re using and allows you to write code that generates the required SQL for you. It makes programming easier to do and to maintain at the potential cost of slightly less performance…that said, it’s usually not the ORM that causes performance issues. Most programmers are also not SQL experts.

ORMs sometimes have build tools to help generate new tables and such, but the main purpose is as a library included in code.

4 Likes
5

There are a number of advantages, though they vary by ORM, by source language, by DB and of course the utility of the advantages varies quite a bit depending on the complexity of the application:

  • Hide details of different DB types. Not likely to matter much for us, but if, for example, someone wants to use Codidact on MySQL instead of PostgreSQL, an ORM that supports both DBs would make it a trivial configuration change.
  • Help prevent SQL injection and other vulnerabilities by making sure all SQL code is structured properly.
  • Make coding of complex queries much simpler, particularly multi-table joins
  • Eliminate a lot of possible typographical errors that can make SQL queries hard to write & maintain and some logic errors too.

I used to (and still do on many existing projects) write straight SQL code. But I have been spoiled by ORMs now and, particularly for a multi-developer and long-term project the benefits are pretty clear.

3 Likes
6

A primary example of where it might be affected is the authorization library.
Maybe you’ll be forced to use .net Identity with all of its tables. Another example would be OData. Some libraries do use EF to convert what is needed into SQL Queries.

A quick edit: I do agree we should go with EF Core

4 Likes
7

After reading messages on discord (staring with this message and down) and here in this thread I feel like whenever people say “ORM” they also automatically mean “Code First”. Hence, I want to make a clear statement that ORM does not inherently imply Code First. You can use ORM and still have to write all the SQL by hand.

So, with that in mind, do we want to do Code First or generate POCOs from database using ORM?

So far it looks like all of the people who are in favor of ORM are also in favor of Code First.

I don’t care if we write SQL by hand or do Code First and I don’t care which ORM we use. I am only familiar with EF (Database First approach), but I’ll pick up Dapper/NHibernate/whatever if I need to.

1 Like
8

@Alex You just threw out some buzzwords & acronyms I’m not familiar with. Let me put it the way I think of ORMs:

  • No SQL written by hand for normal application code. (One-time maintenance is a different story - might be faster/easier to use SQL directly than to write & debug a script. But there should not be any SQL in the application code except possibly in a “new instance/database creation” script.)
  • Ideally database creation and migrations handled via scripts - might be special script that analyzes the before/after database structure, might be a function called on a class to “update the DB to match the class”, whatever. But except for the occasional glitches (been there…) routine database structure changes should not require hand-written SQL.
  • ORM should hide as many reasonable details of the DB as possible - e.g., constraints relating to FKs should be automagically be created. Creating a field that is an ENUM of sorts should figure out how to make that work with the DB.
3 Likes
9

One of the major cons for EF is that it’s impossible to do true unit tests, and that propagates to the rest of your DI services that attempt to use the DB context. MS’s advice is for the test to set up a in-memory database to run against. Also, EF doesn’t use interfaces so mocking is nearly impossible.

I’m against EF for this one very big reason, and I’d go with Dapper (though I understand and semi-agree with the criticisms of writing your own SQL in-code in a large application)

10

Since you build your own per project DbContext you can assign an interface to it and then mock it in the DI. There are libraries that help mock queryables too.
Although I would suggest having a factory for that instead of directly injecting DbContext.
Basically you can achieve satisfactory unit tests is what I am saying.

3 Likes
11

There are 3 ORM modeling approaches: Model First, Database First and Code First.

What you just described is what I mean by Code First approach. You write code first and it generates database tables. It appears that in dicussions here and on discord ORM has been synonymous with Code First, which was confusing to me, so I wanted to point that difference out.

At all the places I worked at a Database First approach was always used, where you write SQL first, then reverse-engineer database tables into C# classes.

The point I was trying to make is that if we choose to write SQL by hand it does not mean that we cannot use an ORM, because we can still utilize the Database First approach. I hope that makes better sense.

Edit: so, the question I still have is whether we want to design database in code using C# or by writing SQL by hand (and then updating C# classes (the .edmx file))?

5 Likes
12

Concerning abstracting the ORM:
I tried abstracting EF before and it didn’t work out well for me. Same for nHibernate, and that already has interfaces for everything. For example, my issues in nHibernate were mainly with the ending of queries. All the LINQ worked fine, but in the end you would have to call FirstOrDefaultAsync (or something similar), which was part of NHib again… which would have made actually using it a mess. As in less, not more, readable.

If somebody could show me a set of interfaces that work for abstracting EF as well as nHib and/or other’s, I’d be quite delighted with that, TBH.

There is also the argument an ORM is already an abstraction of the DB, having another layer of abstraction is a bit overkill. In general, if you choose a solid ORM, I agree with that now. How often do you really want to change the whole DB? It might happen at some point, if you’re really really unhappy with your current choice. But that’s what the ORM already makes possible. For the ORM itself, if you’re using a solid one, there shouldn’t be any need to switch it out.

Concerning EF
I had a (verbal) discussion with Marc about this and, having more experience with nHib, I suggested that EF might be better because of the built-in migration support (it would basically void those DB schema discussions because EF will just do that for you). He said that there are a lot of things in EF people are unhappy with, for example that they still don’t do many-to-many relations. I didn’t believe it at first, but yes, seriously, you have to do that yourself with EF, by faking it with another model and FKs.

Not a deal breaker per se, but he made it sound like there were several such issues. I don’t really know about those though, and he had already talked with a bunch of backend devs, so it might be a good idea to wait for him to pitch in on this discussion.

Concerning which ORM to use
For completeness, I want to add an option I suggested earlier:
Use nHibernate for writes and Dapper for reads. (where I’m not 100% on nHib)

I haven’t used that myself yet, but I read about it being “the best of both worlds”, and that makes a lot of sense: Dapper can squeeze out performance for reads (which will outnumber writes by a lot), while nHib (or another “full” ORM) keeps constructing models as easy as possible.

4 Likes
13

I also like Code First approach assuming we are starting with a blank slate and our application is the only application that connects to this database.

1 Like
14

Okay everyone, it appears, that we’ll start development really soon/started already.

Reading through these posts, I conclude, that the majority opinion is for EF core. Am I wrong with that impression?

To allow us to start soon, you only have 24h to dispute this, unlike the common 72h.

5 Likes
15

Code first migrations to an existing database? Info here and here, perhaps? .

Also EF core doesn’t use an edmx diagram. It’s all code-first. I too like the idea of an initial schema developed at database level and then scripted. @luap42 Either way, happy with EF core as the ORM

16

I have used EF Core fairly intensively, and it’s pretty easy to use, and if you are familiar with its limitations, it’s fast too. For example, try to avoid stuff like context.Foo.Where(w => inMemoryList.Contains(w))–it has to send the whole list to the DB to filter, or pull the whole DB table into memory to filter. It also has the benefit of being able to pass IQueryable<T>s around so you can do chain-filtering based on different call stacks, or based on different flags passed into a method. In Dapper, these typically end up needing to be custom queries per path in my (TBH, limited) experience.

1 Like
17

Did not know that as I never used the Core version of EF. Thanks.

18

This matches my understanding of the tech as well.

19

You can still start with a database and use EF core scaffolding commands to create the db context and model classes: https://docs.microsoft.com/en-us/ef/core/managing-schemas/scaffolding

1 Like
20

There’s now a PR that just goes ahead with EF.
https://github.com/codidact/core/pull/12

I don’t see much dispute here, has this decision be finalized somewhere else?

2 Likes