4 ms·

I've started working on a .NET project that uses EntityFramework and due to some of the magic, I prefer just using raw SQL as well. I've used other ORM solution

by wsc981 2y ago

I've started working on a .NET project that uses EntityFramework and due to some of the magic, I prefer just using raw SQL as well. I've used other ORM solutions in the past as well and I am not a fan ... But the team chose EF due to it supposedly being easier to integrate with whatever database the customer might be using and that seems like valid reasoning.

djeiejejejej 2y ago
Yes I agree. That’s a valid argument, but there are other ways as well like SQLKata and other query builders. Depending on the complexity of the product I think writing standard SQL92 will get you far as well. I don’t get how an ORM will handle Postgres’ CTEs and windowing functions. Those are pretty basic and extremely useful features to me, but those require dropping to raw SQL. Each vendor has those useful particularities that you immediately lose by going ORM. So in practice you are already required to make your queries as bland as possible where GROUP BY and JOIN is about as complex as it will get. I’d say just do SQL92 work around the limitations - which is something you’ll have to do anyway, but now more directly - and all major vendors will work OOB.
- Izkata 2y ago
  > I don’t get how an ORM will handle Postgres’ CTEs and windowing functions. Those are pretty basic and extremely useful features to me, but those require dropping to raw SQL. Each vendor has those useful particularities that you immediately lose by going ORM. Django has supported windowing functions since 2.0, almost 7 years ago: https://docs.djangoproject.com/en/2.0/ref/models/expressions/#django.db.models.expressions.Window https://docs.djangoproject.com/en/2.0/ref/models/expressions... And people are experimenting with CTEs - this looks pretty natural to use, at least with the basic example, and it supports recursive CTEs even if the example looks clunky (though I think that's their choice of example rather than the actual recursive part making it look bad): https://dimagi.github.io/django-cte/ https://dimagi.github.io/django-cte/ (repo: https://github.com/dimagi/django-cte https://github.com/dimagi/django-cte)
neonsunset 2y ago
It's pretty straightforward, and LINQ method names map quite closely to SQL. If you are not a fan of it however, you can use queries directly with '.FromSql(...)' retaining the convenience of object mapping. Interpolated queries use string interpolation API to convert them to parametrized queries that are injection-safe under the hood. https://learn.microsoft.com/en-us/ef/core/querying/sql-queries https://learn.microsoft.com/en-us/ef/core/querying/sql-queri...

by wsc981 2y ago

djeiejejejej 2y ago

Yes I agree. That’s a valid argument, but there are other ways as well like SQLKata and other query builders. Depending on the complexity of the product I think writing standard SQL92 will get you far as well. I don’t get how an ORM will handle Postgres’ CTEs and windowing functions. Those are pretty basic and extremely useful features to me, but those require dropping to raw SQL. Each vendor has those useful particularities that you immediately lose by going ORM. So in practice you are already required to make your queries as bland as possible where GROUP BY and JOIN is about as complex as it will get. I’d say just do SQL92 work around the limitations - which is something you’ll have to do anyway, but now more directly - and all major vendors will work OOB.

Izkata 2y ago
> I don’t get how an ORM will handle Postgres’ CTEs and windowing functions. Those are pretty basic and extremely useful features to me, but those require dropping to raw SQL. Each vendor has those useful particularities that you immediately lose by going ORM. Django has supported windowing functions since 2.0, almost 7 years ago: https://docs.djangoproject.com/en/2.0/ref/models/expressions/#django.db.models.expressions.Window https://docs.djangoproject.com/en/2.0/ref/models/expressions... And people are experimenting with CTEs - this looks pretty natural to use, at least with the basic example, and it supports recursive CTEs even if the example looks clunky (though I think that's their choice of example rather than the actual recursive part making it look bad): https://dimagi.github.io/django-cte/ https://dimagi.github.io/django-cte/ (repo: https://github.com/dimagi/django-cte https://github.com/dimagi/django-cte)

neonsunset 2y ago

It's pretty straightforward, and LINQ method names map quite closely to SQL. If you are not a fan of it however, you can use queries directly with '.FromSql(...)' retaining the convenience of object mapping. Interpolated queries use string interpolation API to convert them to parametrized queries that are injection-safe under the hood. https://learn.microsoft.com/en-us/ef/core/querying/sql-queries https://learn.microsoft.com/en-us/ef/core/querying/sql-queri...