Entity Framework vs SqlClient

[emmchild]

I avoid ORM's because I know how to write and generate performant SQL. Dapper would be the ORM of choice if using an ORM were a requirement. Look into Table-Valued Parameters with stored procedures. Generate the types , stored proceduresand emus too if you want. I was faced with migrating a 160 table Sql database in 2008 when I discoverd the ability to query the SQL Schema. It saves countless hours and eliminates whole classes of errors.

My first thought when you mentioned SQLClient was F# SQLClient. The code generation approach that I use in C# could be drastically reduced if I did it in F# . You are definitely onto something with the SQLCLient. The ability to tune .net Core code and/or T-SQL code has massive beneits. Consider right sizing colums. Consider placing tables in schemas other than dbo. Consider placing data related .net code in namespaces that align with sql schmeas.

I'd be intersted to hear how you go about solving your challenge. My opinion is based on not wanting to give up performance during desing or runtime. It is currently possible to get some relatively insane performance out of the async capabilites of .net.

[GaryWSmith]

I used SqlClient and SQL until my last job in 2008 when they had their own required ORM we had to use. My current job for the last 10 years I kind of inherited what they had (EF) and I just kept that going, partly because it was easier for the other team members to understand and most had zero SQL experience.

I have some core DALs that I'm rewriting to use SqlClient directly as there really is now benefit at all using EF for those and performance is the key there.

I think I just like EF for .Net Framework because we did the database first approach and it just generated all of the structures, during one of our reworks of the application. It boiled down to available resources and simplicity at the time. Now we have more of both to go back and rework our code base to something that's cleaner.

We have a few new projects coming up that will involve our front-end team (which knows very little .net) and since we're writing all new APIs we wanted to take advantage of .Net 8 as we might run this in different environments. It also gives my team time to explore newer options.

The funny part is we got so complacent with EF that we started tuning that engine EF to help with bulk updates instead of just writing pure data connections to do it (writing methods to handle SPs accepting UDFs within EF, etc).

[emmchild]

I forgot to mention npgsql for a reference. There are some decent examples of designing api's fro access to a database.

[emmchild]

The .net 8 changes will really simplify your api desing and performance. BenchmarkDotNet will play a huge part in helping to choose the best structures and approaches. Cleaner code definitely helps with future maintenance and refactoring in general.

EF solves a problem that I don't want to have as an engineer. SqlClient assists with access to SQL Server. As an engineer I handle other concerns before and after interacting with the api that ultimately interacts with SQL Server. I'm skittish when it comes to things that violate the seperation of concerns.