[melolife]

There are three problems than an ORM should solve to some degree:

1. Type safety/refactorability

2. Composability (can I reuse my queries)

3. Expressivity (can I generate the queries I want)

Of these, EF only solves the first, which is far and away the easiest of the three.

Composability in EF is possible via expression tree splicing, however it requires such a degree of discipline and insight that I have never seen anyone do it, or even any discussion around it.

Like almost everything in the .NET ecosystem, EF makes a bunch of promises that are fantastic for hello world, but are disastrous as your project grows.

[5e92cb50239222b]

Do you have any specific examples of an ORM that ticks two (or all or three) of these?

[melolife]

It's a hard problem, NHibernate actually came a little bit closer with it's QueryOver API. The most glaring problem with the LINQ API is there is no way to OR two reusable conditions together because the only tool you have is Where.

To be clear you can get a long way by abusing EF's willingness to try and interpret arbitrarily complex expressions - it just provides zero tooling or guidance on how to build those expressions.

Or projections. It's possible to write a reusable projection for EF like so:

    class MyTableProjection {
      public static Expression<Func<MyTable, MyTableProjection>> Expression =>
        t => new MyTableProjection {
       Id = t.Id,
       Str = t.Str,
       Flag = t.Flag
     };
    
      public int Id { get; init; }
      public string Str { get; init; }
      public bool Flag { get; init; }
    }
    
    dbContext.MyTable
    .Where(...)
    .Select(MyProjection.Expression)
    .ToListAsync()
    

But I have never seen anyone do this because it breaks the promise that you magically won't have to write any code or abstractions.

[grouseway]

SQLalchemy is exceptionally good at all of these -- and I'm not even a power user so I don't know all the tricks.

Re: Composability

You can generate something like the WHERE clause of a query in a function and return it alone (rather than as a SELECT query) or even combine it with another WHERE.

e.g. In SA the "select" and "where" portions aren't tightly coupled.

Re: Expressivity

Right now you can easily build up ef.core queries by chaining IQueryable.Where, which is nice but you can only do that for selects and something like OR conditions are difficult to implement.

e.g. in SA you can just pass a list of predicates to the or_() function.

e.g. in SA you can build a WHERE clause and then pass the same clause to a select or a bulk operation.

[bayesian_horse]

For me, the Django ORM was always good enough, EF isn't. Lately, there is also support for type checking I think, but in general, Django is big on validating input data.