Or better yet, don’t fall for the monotoolism trap and use both! I regularly use EF for inserting lots of hierarchy using one transaction, and dapper for fast view/materialized view reads. It’s an excellent combination.

Sure, and then there’s of course old school ADO.NET for bulk inserts.

One of the main projects I'm working on is using JSON for a more normalized usage of stored procedures with MS-SQL `method(tokenJSON, inputJSON, out outputJSON, out outerrJSON)`, so using Straight ADO.Net for most calls and responses. I'm not a fan of DB heavy logic, but it has made the API layer super thin and works pretty well.

I find writing raw SQL to be quite annoying for simple CRUD operations. It's just so easy to make stupid little syntax mistakes when writing SQL. And things like having to update all your queries if you add a column to your database are pretty annoying too...

ORM's like Eloquent (PHP) are super nice, because they let you lean on them completely for simply queries, and then give you layers of opt-outs. For example, inserting a row is just obj->save(). For slightly tricky selects you can use the whereRaw method to interpolate raw SQL into just a where clause of the query, or you can use DB::query() to write a full raw SQL query if you have some queries that are particularly complex.

I guess I could give Dapper a go though. SQL is... fine.

I think you misunderstand Entity Framework if you think it's worse than Eloquent. Eloquest is ok and certainly pretty reasonable for PHP. But EF and Linq are in another ballpark entirely -- it's extremely easy to use and even allows you to do type-safe queries.

Perhaps! I'm new to .Net and Entity Framework.

But from what I can see, EF is trying to provide an abstraction layer that pretends that I am just working with collections of objects, which I don't like at all because it makes it harder to control which queries actually get executed and when!

On the other hand, Eloquent provides an abstraction for generating queries, but this maps pretty closely to SQL, and it otherwise largely stays out of the way...

The type safety is nice, and of course you don't get that in PHP. But IMO that's more about the language than the library.

EF will only query exactly what you ask for and will execute the query only at the point you actually iterate or request an object. For changes, EF will only hit the database when you call SaveChanges() on the context. It's an abstraction that works with very few "leaks".

Eloquent also has object collections. Both frameworks have similar methods for filtering and querying. Eloquent is much more clunky (because PHP) and the underlying conceptual model is different (ActiveRecord style vs. DataMapper style). I certainly would use Eloquent for PHP projects but I definitely wish PHP could do something like EF.

Doctrine is closer (in terms of actually been a Data Mapper) than Eloquent so it may be more up your alley.

I mean, I get writing boilerplate SQL can be annoying, but honestly if you organize your code well then you end up making changes only in a few places (your repository class and wherever the new column actually gets used). By encapsulating your database in a set of repository classes you can avoid most headaches. Even if you don’t write your own SQL for most things, this is a really valuable pattern to follow in data-centric code of any kind.

Look at Dapper.Contrib - it's an extension lib for Dapper that adds a load of ORM-esque functions for basic CRUDing.

Yeah, also Dapper.Extensions/Mapper/SimpleCRUD etc.

There are a lot of add-ons for it that simplifies all the generics for you.

> I find writing raw SQL to be quite annoying for simple CRUD operations. It's just so easy to make stupid little syntax mistakes when writing SQL.

There are ways to mitigate this [1]. You write your queries in .sql files in Visual Studio, so you get intellisense and syntax highlighting, and you can even add these to your automated test suite to ensure they run correctly.

[1] https://github.com/naasking/Dapper.Compose

what is the benefit of "composing" queries via this library instead of just running them separately in a single connection? The "composed" queries are completely opaque to each other and are independent - they are not "composed", just "batched".

> what is the benefit of "composing" queries via this library instead of just running them separately in a single connection?

I think the link is pretty clear: it combines and batches queries while providing a type-safe interface for the returned results. It further provides intellisense and unit testing features that are tedious and error-prone to achieve in other ways.

Of course you can do this all by hand, or manually batch your queries to avoid round trips, but why would you want to?

Dapper is quite good middle ground.

The flexibility and performance of full SQL, without having to write all the mapping boilerplate by hand.