The argument against Entity Framework, and for micro-ORMs

[pauldotknopf]

https://pknopf.com/post/2019-09-22-the-argument-against-entity-framework-and-for-micro-orms/

[AndresLuga]

Thank you for not advocating building queries from strings :).

Change tracking downside of Entity Framework could have an easy enough workaround: use new DBContext for applying changes, then effects are more obvious.

What doesn't have an easy workaround: startup time of Entity Framework, which is quite noticeable in desktop apps.

Right now is not really a great time to look at EF bug list, or jump into EF, for that matter. The query translator got rewritten, but hasn't really stabilized, as the issue list rightfully indicates. Their test coverage for new/rewritten parts is also not great. "Please try again with nightly builds" has been the standard response for over a month now. Let's hope by the time 3.1 releases in November things have stabilized.

[lufthavn]

I stumbled into this article on reddit, and really liked it. I agree with a lot of what you're saying. 😊
However, the quote you've included in the "Change Tracking" paragraph feels like a straw man. I agree that it can be dangerous to throw around change tracking entities to every corner of your system, and thus lose sight of what actually happening in your system, but I feel like most people know this shouldn't be done, and that if you have this problem in your code base, it might be a code smell.
Currently I like to implement a command-handler approach in my system, where a command corresponds to a particular business concern, and has only one handler, where I centralize the preparation and execution of my business logic.
I agree that the change tracking gets in the way when all you need is to load read-only data for display. Not only that but if you're eager loading, and have only a couple of nested collections in your entities, the amount of data EF is querying the database for can get huge. We recently had to debug this problem where I work. A request for ~1000 entities could result in result sets of well over 100,000 lines. Surely this could be optimized in EF, but when I encounter something like this, I like to take a step back and evaluate the tool we're using.
If you're querying read-only data from the database, what exactly do you need an ORM for? I'd advocate for introducing a micro-ORM (Dapper is my current favorite, but I might check out ServiceStack.OrmLite soon 😉). Nobody says you can't use more than one data-access library in your solution.
This approach of using full-fledged ORMs to retrieve and save business entities in your command handlers, and using bare metal ADO.net or a micro-ORM to very efficiently query for read-only data goes very well with the architectural pattern of CQRS, and is why I recommend it a lot to developers who are dealing with large systems and complex domains.

Anyway, the blog post was great, and I generally I agree with you. 😊 Just don't write ORMs off right of the bat, and consider them as a tool as you would any other dependency in your system.
Hope this brain dump makes to sense anybody. 😅

[sharpninja]

This blog post is good up to a certain level of application complexity. Most large enterprise projects are going to be dealing with data sets with millions of rows of data, with billions of relational outcomes. Advocating eager loading in such a scenario is non-sense. EF allows you to cherry pick relationships to eager load when using lazy loading by default. If you are using a webserver and constantly eager loading large graphs then you are placing a huge burden on the server to load data that gets dumped in the garbage when the controller returns. If it's a desktop app, you may get away with it for a while, but eventually your app will be holding a couple of gigs of data in RAM and user experience will suffer. On mobile, you should be just as strict with memory usage as on a REST server.

EF was designed to work in all these scenarios and when used correctly, does so admirably. Enterprise code bases need consistency and reliability as their top concerns for maintainability. Designing a system under the assumption that only you or someone of your skill level will be maintaining code is both arrogant and dangerous.

[Grauenwolf]

There's other options for ORMs.

For example, Tortuga Chain (which I work on) using database reflection. Rather than just assuming the class exactly matches the table or doing everything using SQL string literals, it compares the table and class definitions at runtime. This dramatically reduces the boilerplate, especially when you don't want every column.

[Grauenwolf]

Another is SQL Alchemy which allows you to build complex SQL expressions using an object model. Unfortunately it is Python only at this time.

[Grauenwolf]

Regarding boilerplate, consider this line:

Consider this line:

dataSource.Update("dbo.Person", new { ID = personId, Name = "Another Name"}).Exceute();

Why can't all ORMs do this? Why do they usually require manually dealing with connections/contexts and an extra round trip to the database just to perform a simple update?

In my opinion, the only time I should see a using statement in my DB code is when I actually need a transaction. And that should only be needed if I'm updating multiple records.

[TehWardy]

Having read this .... many of the points here are exactly where i'm at right now ... the hidden technical debt, abstraction issues I can't solve, complexity with no explanation, poor performance in scenarios that often seem trivial on face value.

I'm currently looking in to an alternative to EF Core to resolve a ton of issues I have that the EF Core team seems to be either confused about, not interested in, or simply willing to pick at my description rather than focusing on the issue at hand.

The fact is, since EF6, each and every subsequent release has removed or broken functionality that my stack has depended on and i'm sick of swallowing that with the reasoning being "this is the cost of progress".

So here's where i'm at ...
I'm trying to find an alternative that as this blog post states should not be an issue because "there shouldn't be a problem that EF solves that another framework couldn't also solve".

So here's my core functionality requirements from the EF functionality that I currently use ...

• Mapping LINQ to SQL
• Mapping SQL query results to Entities
• Managing / migrating the DB (ideally without having to manually crank out SQL myself)
• Complex filtration when "nested questions" happen.

That last point appears to be the sticking point for most "micro-ORMs" .. the "micro" prefix usually means like with say Dapper that it does the SQL query to Entity mapping but won't do the bit before that to get from the LINQ expression tree to SQL.

Assuming this ORM can handle that I'm looking for examples ofdoing things like applying set filters so I can achieve something like ...

var results = Db.GetAll<BaseT>()
   .Include(t => t.Property)
       .ThenInclude(i => i.SubProperty)
   .ToArray();

... key things to note here that EF solves that I can't seem to find a solution to in other ORM's the include, and then the sub include are both filtered by the relationship but also on filter conditions applied to the table regardless of context in which that table is questioned.

This seems to be a feature missing in all but EF.

Does this ORM support this scenario?

[TehWardy]

This requires you to know all the possible combinations of questions that you might want to ask the api up front or manually wiring up a second model so that the traversal is possible with sub queries.
That's never a desired result when you have a db with potentially billions of rows to manage and you want a joned set of maybe 1,000 of them from the results of a single dynamically generated SQL query.

Consider putting an OrmLite managed DB behind an OData API where the questions are virtually limitless but every possible combination of scenarios has to be considered and handled.
With EF this is trivial as I can tell EF that the Set has a filter and then any time EF sees any portion of a linq query hitting a given table it applies the filter then the requested query to the SQL query.

People often forget about the complexity that EF is solving stating that something is a "micro-ORM" instead of a "full ORM" is seemingly just like declaring "this solves half your issue, now go find something else that solves the other half, but it's fast ok".

I've not yet found anything that can match this type of EF solved scenario that didn't require a crap ton of "work arounds" or "patching stuff together" and it's the one thing that keeps my solutions sat on it ... which is frustrating because I both hate it and have to use it at the same time as there is seemingly no alternative.

Other things of note ...

• For some reason Code generation is seen as bad
• Designer tooling focus is seen as bad

... all features that I often use to solve dynamic scenarios that would otherwise be unsolvable or for situations like I don't want to sit around writing boiler plate stuff like the SQL statement for building a table when I have a class that exactly matches it's structure.

Also, the current version of EF core will never return "some proxy sub class with injected decorated behavior", as that functionality was ripped out as part of the rebuild when EF6 became EF Core 1.0.

My current entity model, has a DbContext as you might expect with entity sets, none of which I pull from the Db in such a manner that I use things like Lazy loading, or proxies, or in any way require the resulting entities to be "attached" to the context, this is basically the same setup as is explained here.

My ideal ORM would allow me to do something like ...

using(var db = new factory.GetConnection("db name"))
{
       IQueryable<T> results = new Query<T>().Where(...).Select(...).Include(...).OrderBy();
}

... in this situation I would be constructing a simple ADO.Net connection to the Db and then telling the framework "build me a SQL query of Type T", large complex "models" seem to feel like overkill for me since the type metadata for the query you're building should tell you all you need to know.

I continue my composition on that to construct the full query, including notifying it (as per my previous comment) of what "sub sets" I want in the results, then performing a .ToArray() .ToList() or simply iterating over it would actually execute it.

The results would be disconnected from the DB (simple POCO's) and be appropriately secured unless I specifically asked the framework to track changes for me to make saving easier later.
If a calling user asks for "select * from Table" configuration of a secure where clause should be a key feature of any ORM "micro" or not.

My issue tends to boil down to the fact that all these ORM's claiming to be better than EF appear to be so on face value, but as soon as you start drilling in to the complex scenarios they fall short of features resulting in me having to extend or build tons of framework around the ORM.

So the article makes the comparison of OrmLite's 89k cloc to EF's 514k cloc in the chart at the begining but it's forgetting that there's a bunch of stuff in that extra 400k cloc that OrmLite can't do.

Unless i'm missing something?

I recently asked the Dapper guys about how implementing a Dapper based back end for OData might look I simply got a one liner "you'd have to write your own version of LINQ to SQL as Dapper doesn't do that" ...

So Dapper can execute a query, and map the results to an Object graph, but it can't build the query in the first place. That's half the work is it not?

In short

I'd love to see a "micro-ORM" implementation of an OData or GraphQL or similar API model as those sorts of API's really push the limits of what ORM's can do.

[pauldotknopf]

See this: Why not OData?

Also this: AutoQuery

[TehWardy]

I don't agree with the bulk of points in that Odata article, for example the supposed "tight coupling of internals" problem highlighted is not present in my stack but that's a far more complex and different discussion. I was using OData as an example mainly because of the complexity in scenarios that it exposes us to only, even Microsoft who pushes OData heavily states that best practice is to have N-Tier separation and promotes the use of both an API model and a DB model, the mapping for that is an entirely different issue though that's not worth discussion here.

The article seems to suggest that AutoQuery is an alternative to OData which it just isn't.
the fact is, as the article points out "the OData query-space can reference any table and any column that was exposed.", not quite true but the premise is ... you have a model (that model doesn't have to be the same as your data model, but you can build any question on any part of the API model,

Essentially all i'm asking is that an ORM should be able to handle exactly that, and when I ask it a question I should be able to tell it "I want my question asked in this business context" which for the bulk of queries in enterprise applications boils down to "based on what the user making the call has access to", which in my case is a small cut of every table in the DB.

If the answer it seems is to just avoid asking the question then it's not really answer is it?
This comes back to my Dapper point above, can Dapper really claim it's faster than EF when it only solves half the problem!

[mikependon]

Interesting topics, I really like to chip-in my ideas but only on ORM side. Hope to share more here soon.

As of writing this, I do not have any idea of AutoQuery and I am not as deep at OData.

key things to note here that EF solves that I can't seem to find a solution to in other ORM's the include, and then the sub include are both filtered by the relationship but also on filter conditions applied to the table regardless of context in which that table is questioned.

@TehWardy - I think, a micro-ORM can do solve all the things, whereas EF can't. The EF has abstracted most of the things for you, and that limits you to access the benefits of the underlying storage. It means, you have less control with EF over a what we called micro-ORMs.

Migration tool - People often forget about the complexity that EF is solving.

True, and not true. It is based on preference. I can say, EF did not solve any complex problem :) - that's why there are micro-ORMs which allows you to have more control. Of course, you have to write more.

Specifically to Migration Tool, we have developed a DevOps tool and had not used EF code-first as our preference is to not bound it to EF at all. In the end, it is much easy to maintain and solves the complexities of our releases :)

I've not yet found anything that can match this type of EF solved scenario that didn't require a crap ton of "work arounds" or "patching stuff together"

Do not generalize this, EF does not even have Batch and Bulk operations, 2nd layer cache, Trace, etc. And if you tend to do that, it requires you a lot of works just to make it work with EF. Also, you are bound to the models at all, and you can't do anything on your model but just to use it on a specific table.

My ideal ORM would allow me to do something like ...

I am interested to collaborate and share. I am also an author of one micro-ORM named RepoDb. Can you have a look?

It is a hybrid-ORM which will allow you to do the things of micro-ORMs and macro-ORMs. You will have a lot of benefits while using it, I can even explain and support on this.

You may experience a different test as it has been baked differently.

[TehWardy]

It's refreshing to have people to talk to about this stuff ... most devs want to avoid this type of problem as it's a minefield of pain whatever path you go down it seems, the trick is picking which mines you step on with some level of "good guess work of the future".

@mythz

I can see you clearly have some big issues with the M$ stack, mostly they are valid too if the stack is used as documented.
The way they document stuff is the "this should get you going" method, not the "this is probably how you should be using it" way ... the main reasoning there I guess is that they want as many people using the technology as possible which is true of any stack vendor.

You seem to be under the impression that OData is ...

1. Not type safe / lacking contract definition.
2. Tightly bound to a db model.
3. Specifically an M$ technology.
4. Over-complicated for complexity's sake.

To that I would say ...

1. The OData standard specifically requires an API model definition.
2. It doesn't have to be, that's a documentation issue.
3. It's open source, managed and owned by not M$ ... but M$ do have 1,000's of endpoints that use it (their entire cloud platform that they spent billions on uses it for it's entire API model).
4. That complexity caters for scenarios that from what I can tell are impossible in service stack without a lot of code or the loss of that type safety (as per our discussion on stackoverflow).

I've noticed that OData is referenced with regards to the v3 spec and WCF, that version is basically dead, the v4 spec and version runs entirely on .Net Core 3.1 and is actually more complete than it's partner version of EF (the soruce of my frustration right now).

Also what's this ...

it was found wanting with the industry quickly moving to simpler RESTful JSON APIs built with CoC frameworks and libraries.

... OData isn't dead, it IS restful and returns JSON by default and my entire point to you centered around my testing efforts on service stack are literally that CoC point (i have that already and don't want to give it up), service stack doesn't seem to do anything by convention it requires explicit definition literally everywhere.

When I say "I should be able to tell it "I want my question asked in this business context" ... i'm not joking, I run a transactional platform and the context of a question is important ... I see a billion euros a week worth of invoice data through the system and users getting back the wrong rowset is not an option, that is by design a complex question nto because M$ said so but simply because it has to be.

As for the "mis-appropriation of features" ... I don't use any of the extended features that didn't get ported to .Net core for that exact reason, I saw the headache coming and avoided it.
The one feature i'm struggling with is expansions when pulling entities from the DB with their children, other ORM's seem to be able to do this but it's pita by comparison and lacks flexibility.

I've had DBA's hand crank queries to answer some of the simpler questions and the way EF handles some of the scenarios actually beats that (it's rare but it happens).
The most complex of the queries i've hit run a 1MB select query and it comes back in incredibly short times, that's how complex the questions are.

That's a requirement imposed on me because of the nature of our DB not due to the framework imposing that, replacing EF with ORmLite or Dapper will not change that, it's been tested extensively.

[TehWardy]

@mikependon

Thanks for the feedback, I can totally see why people say what they say about EF, hell the grief I give M$ on occasion and the EF team is somewhat ridiculous at times because i'm trying to solve problems that simply shouldn't exist.

I'll happily take a look at your ORM :)

As of writing this, I do not have any idea of AutoQuery and I am not as deep at OData.

Good place to start, I know here on the Service Stack side OData is seen as some sort of anti-pattern due to the way that M$ documents and recommends using it, I definitely don't use OData as documented so usually don't hit the down sides (like tight binding to the DB structure).
I actually get a lot of flack from the M$ dev teams about my abuse of their tech, but my implementations are cleaner, faster, and more secure than their documented examples.

When discussing it here though, it pays to appreciate the complexity of questions that you can answer with the OData + EF stack WITHOUT having to specifically write any code at all beyond building the class that matches the table by default.

With other stacks like shown here with service stack I've had some interesting conversations with @mythz (sorry mate, I do like to ask the complex questions) on this area and the choice to jump boils down to a few key points for me ...

1. I should be able to implement what I want as a "base" and extend for specific cases where needed (and only where needed).
2. I shouldn't have to handle every possible use case of my API explicitly (because my API is consumed in situations I don't have any visibility of).
3. My clients build a solution using my framework so I can't "build the complete API they want" (meaning it needs to answer questions i haven't thought of)
4. I can't hand crank blocks of SQL (nor do i have any interest in that / time to do so)
5. It would be nice if it's free, (I can't test my code stack conversion to Service Stack without buying a licence which is frustrating).

@mythz I was going to ask you about point 5 actually ... I've converted a few thousand lines of code over to service stack but obvs because my model is more than 10 tables (or whatever the limit is) I can't test it, i'm actually seriously interested in at least spinning it up to see if that performance gain is really there (although I do have a query generation problem to solve) as ORMLite only solves some of the scenarios I have.

Some points of discussion had on stackoverflow ...

• https://stackoverflow.com/questions/60686237/servicestack-is-context-based-routing-specified-in-the-url-possible
• https://stackoverflow.com/questions/60678495/understanding-the-request-lifecycle-and-routing-mechanism-in-service-stack

The key thing here is that as the technical lead on my own stack I should be able to pick the pieces that work for me (EF admittedly doesn't give me that, it's all of that half million lines or nothing - ish) ... but then having picked my pieces I should be able to build solutions around them as needed.

When I talk about my ideal ORM, i currently don't think it exists but then i'm very picky.

Key features I would like to see in an ORM which would make building my own API easy are ...

• CRUD without a model (so much like OrmLite here or Dapper the ability to just grab a connection and do stuff with T's on it)
• Query generation, from either some form of string source or an expression tree.
• T based Filters on the DB (which is why i don't want to get involved with the SQL construction).
• The ability to point the ORM at a context class which defines the model and generates migrations for me (EF does this bit incredibly well).
• No forced patterns or architecture design.

That last point along side the lack of SQL generation from LINQ is where I feel both Dapper and OrmLite fall short, but this is why I think they are sold as "micro-ORM's" at least in part, they deal with ONLY the problem of talking SQL to SQL servers.
That's not a bad thing as it keeps the framework light, but maybe what's missing here is a LINQ to Sql abstraction (like the one in EF) but not tied to any ORM, one that's pluggable so consumers can override certain behaviour (probably on a tpye by type basis).

consider this sort of query example ...

// build the query
var query = new Query<T>()
    .Where(...)
    .Where(...)
    .Select(...)
       .Expand(...)
             .ThenExpand(...)
    .GroupBy(...)
    .OrderBy(...)
    .ToSql();

// then with Dapper I could do ...
var results = connection.Query<ResultType>(query).ToList();

... the key thing to note about this example is that I'm mapping questions presented as OData parameters in to this framework basically allowing the User to build the query they want the API to run, but not only that, the base set is filtered by a preconfigured filter for T based on the users access to rows in the DB, then when they expand in to the subsets those also have a filter applied to them, all of this is automatically injected in to the query.

Now I can see the response here ... "yeh you can do all that with x-ORM" ... you're right, I can ... but I don't want to hand crank all that functionality, EF already handles it for me, the only issue is that I have to take all of EF to get it.

If I could take the query building, as a feature and plug that in to any ORM then I'm free to choose to use OData on top of that if I so please.
If I take service stack I can't do this, I have to pre-think of all the possible queries the user might want to ask and build them in to my API layer providing a pairing of at least 1 DTO + a service method for each possible question.

For CRUD on a single OData endpoint I only require a single generic controller, with a filter on the DB table (a one of linq expression, and one liner) I can filter the table for any user that logs in by applying my own "app role logic or whatever" to the table and i'm done.

Assuming I follow a convention I would then have 1 controller / service and one context class representing my DB then the simple POCO that represents the table (all things I have with service stack) ... the key difference with the OData + Ef stack is that if I want a new endpoint I simply add a new POCO and i'm done, full CRUD implemented "by convention".

is this slower than a handcranked query for each CRUD operation on every possible endpoint query ... yup, do I care that it costs a few extra CPU cycles ... nope, servers are cheap to rent, cloud solutions architects and the dev teams to maintain complex codebases aren't.

[TehWardy]

@mythz i'm not ranting, sorry if you feel that way ... I thought this was a friendly discussion.

I'd also like to point out that i'm about 80% of the way through making the code work with Service Stack but i'm currently stuck on a few things (understanding this AutoQuery behaviour is actually one of them, so thanks for that). In addition to that i'm constantly sharing the information you give me internally to get further feedback from my team.

I'm not adverse to dropping the M$ stack entirely (again why i'm here), I just need to prove to the business that I can deliver the problem domain under the new stack without too much fallout and in a timely AND cost effective fashion (OData + EF is free after all which means something to a small business like us).

The fact that i'm using OData here is not out of some misguided loyalty to it or M$ it's more that it presents certain complex challenges to ORM solutions so acts as a good example of showing the worst cases that an ORM may need to face.
OData and GraphQL have a lot of common features in their design ethos and yet you don't seem as adverse to GraphQL.

Sure the M$ implementation of OData is horrendously bad in places but to conflate the implementation with the standard (which is what OData is) is out right wrong.
As you say i'm just throwing assertions around and not backing them up lets take @pauldotknopf 's blog post from earlier, in the first part of the article he straight up begins with a bunch of incorrect assertions without backing them up ...

There is virtually no contract. A service consumer has no idea how to use the service (for example, what are valid Command arguments, encoding expectations, and so on).

... it's wrong because they actively push the use of metadata based descriptions for the entire model that you're exposing and even go as far as defining the definition of that metadatas schema ...
http://docs.oasis-open.org/odata/odata/v4.0/odata-v4.0-part3-csdl.html

The interface errs on the side of being too liberal in what it will accept.

... that's just an assertion, the very thing you're accusing me of.

The contract does not provide enough information to consumers on how to use the service. If a consumer must read something other than the service’s signature to understand how to use the service, the factoring of the service should be reviewed.

OData is repeatedly comprared to WCF SOAP Services for some reason by both you and @pauldotknopf in his article (which I find odd as they have literally nothing in common) EXCEPT ... SOAP had a WSDL description, you could use the tools to generate your client code.
OData doesn't require tools, it has an XML based description, as stated above the schema for that is well documented and anything that can read XML can understand OData schemas and thus consume with strong typing the service.

I go a step further and expose metadata relevant to each endpoint on the endpoint itself to avoid the caller having to rely on a large blob of meta for which they want a small portion (IMO this should be the standard).

I could go on but my "opinions" (however documented and fact based they may be) regarding OData aren't wanted here.

Again .. The reason I pointed at OData was that it generates complex "real world questions" I have to build an API to answer.

Your examples are interesting and do solve the problem in the event that I handle the question or use AutoQuery to do this for me ... can AutoQuery do this with my own business logic in the middle something like this (taking the OrmLite example) ...

var q = db.From<Customer>()
        .Where(...)
    .Join<Customer, CustomerAddress>()
        .Where(...)
    .Join<Customer, Order>()
         .Where(...)
    .Where(x => x.CreatedDate >= new DateTime(2016,01,01))
    .And<CustomerAddress>(x => x.Country == "Australia");

var results = db.SelectMulti<Customer, CustomerAddress, Order>(q);

foreach (var tuple in results)
{
    Customer customer = tuple.Item1;
    CustomerAddress custAddress = tuple.Item2;
    Order custOrder = tuple.Item3;
}

... if I understand this correctly this is the equivilent of a query that returns an expanded subset of properties too.

• Does this support filtered joins "in this manner"?
• Can I configure AutoQuery / ORMLite to apply the nested Where clauses ANY time a table of that type is used anywhere in any query?

Essentially the reasoning here is that from the users information in the request (like an auth token for example), I have to filter the db down to the stuff they can see in every table then execute my question on what's left (standard multi-tenancy issue basically).

From there the logic is only as complex as the user question asked which it sounds like AutoQuery might be able to limit to a problem domain that's already coded for which is perfect!