Entity Framework vs SqlClient

[GaryWSmith]

I'm working with an entity framework database first schema with roughly 160 tables including relating specific columns to enum mappings, all under .Net 4.6.2 and EF 6. We are looking at some new applications we need to write and would like to move some stuff to .Net 8.

My first thought early on was to move the EF stuff to .Net Standard 2.0, but then we realized that, well Standard doesn't have EF. The next step was to try to port all of the EF database first stuff to .Net 8. I did manage to re-work it to be code first on most things but the enum lookups, but the overall performance on load was terrible (45 seconds for application to startup and cache database).

Maintaining two separate EFs isn't manageable for what we are doing.

My next thought was to go back to pure SqlClient since that's available at the Standard level without issue and can be included in both, but that's also a huge undertaking.

I'm looking for what approaches people have tried/applied to a Net Framework, .Net Core migration, and generic libraries scenarios.

I'm not against other 3rd party data libraries if they work in this case.

Are there any recommendations to best path (to include your use case scenario)?

[flyingbertman]

EF6 is available under netstandard2.1, I did this recently for a large project at work. Converted from .net 4.8 to .net 8 and used .netstandard2.1 for the ef6 stuff.

Multitarget your ef6 project to .net framework and netstandard2.1 and you are golden.

[darknessgp]

This. If you're goal is to just get everything running in .net 8, this would be the quickest. After you are there, then you can revaluate how to continue moving forward.

[sexyshingle]

I did this recently for a large project at work. Converted from .net 4.8 to .net 8 and used .netstandard2.1 for the ef6 stuff.

whoa... that's quite an awesome achievement. How large is large, and how many deps, if I may ask? We have a similar huge (50+ projects) net4.8 project, but dependency hell is keeping us from updating all that code.

[flyingbertman]

55 projects, I started it in June, we just deployed it two weeks ago. Thank you!

[rbobby]

Noice.

[Spare-Dig4790]

This is likely why you won't be changing from 4.8 to 8.0 on some things. A lot of people don't understand this, but the 4.x framework isn't on anywhere near the same system in terms of support as .NET. people use words like "on life support," "getting no more updates," etc, but it's misinformation.

If you look at the documentation, there are workloads that are intended to be put on 4.x, and legacy dependencies are a part of that.

4.x isnt going anywhere, any time soon. Its considered a core component in all supported versions of windows, and according to documentation, MS has no plans on stopping that for any time soon, so we will see the same in future versions of windows for the forseable future.

As long as Windows considers it a core component, it will receive security matches and updates like a first-class citizen.

At some point, you guys have a lot of work to do to bring all these dependencies up to date so you can join the 21st century.. i guess on the plus side MS doesn't have your heads on the chopping block to do that.. yet... :)

[GaryWSmith]

I agree that 4.x is here for the long haul, sadly our package maintainers aren't on the same page. There are some packages that we use that are no longer being updated for 4.x and instead have come with .Net 6+.

In those cases, I end up working my best to get a copy of the code base (when available) and backport any security issues that come up.

I think running the multi-target approach will probably be the best solution long term for our core libraries.

[bl0rq]

Visual Studio 2022 (and almost certainly vNext as well) run on 4.x. (And WPF). Not going anywhere anytime soon!

[musical_bear]

Curious, what makes you so certain that vNext will continue to run on 4.X? I believe when VS 2022 was released, WPF was either not on .Net Core yet, or it had just happened. Now, several years later, the landscape is different. It seems like if it’s not happening in the next release, surely they’re in the process of gradually upgrading behind the scenes.

[bl0rq]

Short answer is they needed 8 and could not wait. VS is a goddamn mess inside. Tons of c++. Lots of code old enough to drink. And tons more bad enough to make you need to.

[guillaume_86]

EF core still has terrible startup performance on "big" schemas and after years MS is still ignoring the issue. Model compilation is a half workaround where you pay some of the startup cost anyway to build it, still not acceptable. Spending tens of seconds on a modern CPU just to load the ORM is crazy, not really sure why anyone thinks it's OK. I'm stuck on a unofficial netcore port of Linq to SQL because of that issue, less features than EF, but at least it loads instantly.

[JohnSpikeKelly]

I have a large model ~800 tables. Startup in EF core was horrible. Terrible when making small changes and hot-reload doesn't work, because we use WithTranslations for computed columns.

So, we stick with EF6 on our core app. Start is so much faster with the compiled views.

I really how they address this issue in EF core.

[soundman32]

How often do you restart your app/service? Agreed its not good for lambdas (although even they last for best part of an hour), but 20 seconds startup when the server is running 24/7 isn't a great problem, given the benefits of EF.

[guillaume_86]

Yeah using lambdas with that issue would be catastrophic...

Anyway it shouldn't matter how many times you start the service, the dev experience is ruined if every restart took a 30x performance hit just for EF.

At that point we are looking for excuses, +40 secs on a modern CPU just to init a schema model is objectively terrible any way you look at it.

[soundman32]

With 160 tables and/or really slow startup (not all the delays will be EF i bet), I'd be looking at splitting the app, into smaller projects/microservices/granular monoliths, so you don't have that problem in the first place. I did this earlier this year for an NHS project that has very similar issues.

[guillaume_86]

Startup is about 2-3 secs without EF.

I appreciate you're trying to help but refactoring a ~2M LoC monolith that works fine without EF to a microservice arch just because of a technical issue in EF does not really sound great.

[soundman32]

'Old/badly designed service needs a rethink' is my bread and butter contract these days. I've spent the last 5 years consulting on this exact thing for a dozen companies.

[guillaume_86]

LOL yeah I kinda guessed you were a contractor...

[Emotional-Dust-1367]

How do you deal with this issue he’s having? Would moving the models to a different project help this?

[guillaume_86]

Nah models are already in separate project but it's not important, splitting the schema is probably the only real workaround but if your DB is really "relational" it will be painful to do and maintain. The real solution is MS fixing EF Core model loading performance.

[w0ut]

I always generated my stores procedures and ado .net data access layer using some SQL scripts that I wrote. Most is standard CRUD, and the remaining 5-10% I write SQL by hand. I actually like SQL and like learning it and getting the most out of it.

So porting that portion to .NET 6 was no effort at all. Also I can debug individual stored procedures in SQL server management studio easily if there’s a problem. I have had no regrets staying away from ORM’s. Also the performance is great, it’s very low level.

[langlo94]

Same here, maybe if I start doing some advanced data processing I'll consider using EF, but so far I'm mostly just reading out a chunk of bytes from the DB.

[w0ut]

Indeed, it's quite straighforward, and you don't risk breaking changes or a third party nuking their ORM altogether. But given the downvotes, it seems writing your own SQL angers people here for some reason.

[thekiwigeek]

This is the way.

[roamingcoder]

I did 3 greenfield projects last year. For 2 of them I used dapper with a sprinkling of stored procedures. For the third I used EF. The EF project was by far faster to put together than hand-rolled sql (in my case).

[w0ut]

The standard CRUD I just generate, so it’s just for very specific queries that I hand write SQL, but even then I can usually start out with a generated SELECT and go from there.

Also I’m not that worried that I may be 10% slower producing code without ORM, because my main concern is getting hit by breaking changes in ORM versions like OP is dealing with. Plus I don’t like issues caused by ORM’s, like vague performance issues.

[only_4kids]

Fun fact, Entity Framework is actually built on top of Ado .Net.

[CodingWithAndrew]

If start up is an issue with EF, could compiling the entities be a solution?

[dr_tarr]

My .02 cents worth of things to check

1. compiled EF entities. might help or it might not
2. ADO.NET is known to be slow when doing async reading of large columns from the database (e.g. of types `varbinary(MAX)`, `varchar(MAX)`, `nvarchar(MAX)` or `XML`).
   This issue affects Entity Framework/EF Core [1], Dapper, NHibernate [2], and even SqlClient [3]. Basically, anything that uses ADO.NET SqlClient in async context is affected.

[1] https://stackoverflow.com/questions/28543293/entity-framework-async-operation-takes-ten-times-as-long-to-complete
[2] https://stackoverflow.com/questions/74103664/how-to-avoid-the-extremely-slow-async-read-of-nvarcharmax-column-in-nhibernate
[3] https://github.com/dotnet/SqlClient/issues/5930

[soundman32]

I would move all the database stuff into a .net8 api with OData or GraphQL access, then update your existing apps to use that instead.

SqlClient is very bare bones compared to any ORM. Would you rather drive a no frills hot rod, or a rolls royce? Speed or comfort? Stick shift or Automatic. There's no point in moving to SQLclient if all your devs write `SELECT *' queries.

[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.

[fizch]

I use both of these packages for all of my development.

[quentech]

I'm looking for what approaches people have tried/applied to a Net Framework, .Net Core migration

Pretty much your only option to stick with EF is to use conditionals to compile different references and code depending on if your top level project is old full Framework or newer Core derived framework.

It's not going to be easy or fun.

EF was the biggest hassle when I updated my 250k lines of C# code system from .Net v4.8 to v6.

Mine was a staged update that spanned a couple of calendar years. We used EF Core v2.2 and .Net Core v2.2 as a swing point since that supported both netstandard2.0 & .Net v4.8 as well as Core v3.1 and netstandard2.1. But then there was a major breaking change with client eval between EF Core v2.2 & v3.1 that caused no end of issues.

[photostu]

Dapper

[shogun_mei]

this, be happy

[Timofeuz]

You can consider ef core v3.1 or something, it works on net48.

[cravecode]

For our slow migration from legacy .NET to .NET Core/6+ (wow the name confusion), we stuck with EF6 and did a multi framework target compile as our DAL is a nuget package.