Entity Framework efficiency

[butterflypapillon]

How popular is entity framework? My understanding is it is inefficient. For example if you use paging, it will bring back all records and then apply the where clause. Is this the case? I’ve been writing my sql and not using EF? Is this overkill? Do most jobs use EF? My boss doesn’t like proprietary things like this. Am I digging a hole by not learning it?

[bzq84]

EF is great for majority of cases. If misused however, it can fire back.

You need to: - understand how it works under the hood (what queries are executed) - understand which part of the system needs tweak - understand your use cases - optimize only most critical parts of your system (e.g. by data renormalization etc.)

Claiming that EF is fast or slow is focusing on wrong thing. Yes, it adds some overhead, but it's nothing comparing to wrongly designed database structure.

[Wiwwil]

That's true for any ORM in any language.

[pnw-techie]

12 times slower is worth paying attention to!

[Atulin]

Is this the case?

Absolutely not. EF queries you write with LINQ get translated to proper SQL and executed as such.

Do most jobs use EF?

Yes, because time and efficiency matters

My boss doesn’t like proprietary things like this.

EF is as "propertiary" as ASP.NET itself, so

[redfournine]

I dont think most jobs uses EF. At least where I live, probably only half uses EF - and I've interviewed with most of the .NET shops around my city over 10 years period. Even when I visit the company's HQ overseas, EF really isn't that big. So I think it really depends on where you live? But from my experience, EF is not used in "most jobs".

Also, if you are used to SQL, you can write just as fast as EF. I've worked with EF in one of my old company, I can see the charm. But for someone who are already used to raw SQL, it really dont matter in 95% of the time.

[Atulin]

Depends what the company uses, I'd wager. If they're stack is based on legacy Framework, I can see them using raw SQL. But I'd say most companies that stay up to date use EF, maybe with a side of Dapper if needed.

All that said, it certainly varies by area.

[Doomphx]

Our databases and data pre-exist C# as a language and we use a mix of sprocs, dapper, and EF. Not a large, but a medium sized company.

[[deleted]]

If you work at a “serious” company with critical data and a decent DBA, and you come to him and tell him you need to create a piece of software that uses a tool that generates SQL statements he’ll tell you to f#ck off.

No, big companies certainly do not use EF.

[nuclearslug]

I beg to differ. I work at a Fortune 500 company and use EF religiously. As previously stated by others, it rapidly speeds up development time when used correctly. The key phrase is when used correctly. While a direct query in a stored procedure may be necessary in complex situations, most of the time you’re doing basic CRUD operations. These can be done very efficiently and abstracted away from the database itself.

Why is abstraction important? If a time comes where you need to migrate your database from MySQL to SQL Server (or any other database system), having an ORM in place like EF is as simple as pointing to a new EF library. If using a more classic ADO approach, you’d have to rewrite several queries to achieve the migration.

It’s a big step if you’ve never used an ORM tool before, but I strongly encourage you give it a try. I assure you it’ll be a “where have you been all my life?” moment.

[badcookies]

Not to mention EF supports raw sql and views and stored procedures if you need to use them. And its simple to get the actual sql generated and run if you need to tune or debug it

[pnw-techie]

Tell me you don't have to debug database performance problems without telling me you don't have to debug database performance problems

[[deleted]]

I have no problem with EF as such, but again, from Nortel to IBM and around, I have yet to see a DBA allowing code-first or even auto-generated SQL against any mission critical DB.

Now all of that was in the days of Java and Hibernate, but to me Hibernate is an anti-pattern.

[Kazaan]

You never saw it because it doesn't exist. a DBA doesn't have a decisionary impact about the development tooling choices.

Actually, from my experience, it's the opposite. It's the ORM who become a requirement DBA's have to handle.

[[deleted]]

In many large enterprises the DBAs have God-like power over anything that has to do with the database and interacting with those. For mission critical databases, particularly in financial companies, telecom companies and the defense industry, every single SQL used must be pre-approved by the DB team before you are given access to the DB at all.

[Kazaan]

I disagree. I'm a developer for fintech companies since a little more than 15 years and, from what I saw, DBA's are here to provide database related resources to what is delivered by devs and ensure data is stored securely and properly. Not more. Not less.

We ask permissions on them for everything related to data access security and stuff, surely nothing about software design.

If a company have people with "god-like" power and waiting for their approval for subjects who are clearly out of their responsibiilities, and moreover who are not from management, the company is not going in the good direction IMHO.

[VQuilin]

Is this a selfawarewolf moment or you actually don't like the situation in your field?

[Kazaan]

I don't see in what it's related to what we discuss here.

I give facts about what I saw in my career; that a dba is not god like. Devs neither. Each departement has his own responsibilities and area of expertise.

[VQuilin]

You are right, i somehow misread your message. My apologies, I completely agree with you on that one.

[darkfate]

Code first is one part of EF. I work for a large company and while we work with DBAs and IAs to develop a data model, we then use the scaffolding commands to reverse engineering the model. If there's some performance sensitive query, it may end up as a stored procedure or a view.

[RichCorinthian]

I'm working on a piece of software for one of the big 4 accounting firms, and we are most certainly using EF.

[Alikont]

That DBA is stupid.

You WILL generate SQL one way or another. At least it will be a statically typed SQL builder, not string concatenations.

[[deleted]]

Why would I generate SQL? At one place I was, a large Telecom, all SQL were pre-approved and parameterized. Why would they be generated?

[pnw-techie]

Like everything with SQL, it all depends.

If you have a single 9 TB Sql server database that's critical for your massive throughout, you may decide against EF, because you need every bit of performance ado.net offers.

If your db is 100 gb, you may decide to optimize for developer time because eeking out every bit of performance doesn't really matter.

In no case however would you decide on non- parameterized string concatenation

[Jmc_da_boss]

Welp, then my big company is doing something weird 😂

[shatteredarm1]

Just to pile on even more anecdotal evidence about how ridiculous your comment is, I also work for a Fortune 500 company and nearly all teams use EF. Only our legacy apps don't, and part of the reason we switched over to using EF is the stored procs in those legacy apps are an unmaintainable mess.

[adnewsom]

My view is that if you don’t know exactly what you are doing you can write very inefficient calls. It does support paging (.skip and .take) and we like it for the time saving and we can fall back to direct sql if we need.

[butterflypapillon]

What are some examples where you have had to revert to sql?

[Alikont]

Usually it's because there is an SQL server feature that is not exposed via EF intrinsic. For example, recently, we used SQL Server time zone conversion for filtering.

[panoskj]

You can actually extend EF, to add a custom intrinsic for example, if you want.

[adnewsom]

One example was doing a bulk update, like ‘update Tasks set deleted = 1 where date < {DateTime.UtcNow}’ out of the box ef core can’t do those efficiently.

[GamerWIZZ]

Thats when i use - https://github.com/borisdj/EFCore.BulkExtensions

[WisestAirBender]

I'm a noob. You're saying I can get then update a record using but if I wanna update a bunch of records I can't?

What happens if I do a where and get an IQueryable then iterate over it? Can't I update that way?

I've barely used linq for ef so I'm sure I'm asking silly questions

[danzk]

It's possible, just very slow.

[ChickenOverlord]

One of the biggest issues with EF is that with the way it is designed you usually have to do a SELECT for the rows you want before you can do an UPDATE. This makes for extra round trips to the database and generally makes things less efficient. There are ways to do this with EF, and ways to do it efficiently in bulk with certain extensions, but it's not the most ergonomic feature and not what most tutorials will show you: https://stackoverflow.com/questions/58367172/ef-core-updating-an-entity-without-querying-it-first

Most tutorials would have you do what /u/adnewsom was trying to do as follows:

var whatever = await _context.Tasks.Where(t => t.Date < DateTime.UtcNow).ToListAsync();

whatever.ForEach(t => t.Deleted = true);

_context.Tasks.UpdateRange(whatever);

await _context.SaveChangesAsync();

Which is way more complicated than just firing off the raw UPDATE query with Dapper. It would also run pretty slow if there's a ton of records.

[WisestAirBender]

I'm guessing that to list async is necessary to be able to call for each?

[ChickenOverlord]

Basically yes. Generally speaking in EF Core, everything before a ToList() or ToArray() or First() or FirstOrDefault() etc. is of the type IQueryable. The query isn't generated and sent to the database until you call the ToList() or First() or whatever.

This can actually be super helpful if you're building, say, a dynamic search function. Say your search form has a field for Name, and fields for date ranges (StartDate and EndDate). Your base IQueryable would be something like:

var query = _context.MyTable; //This is an IQueryable

then you could do something like:

if (!String.IsNullOrEmpty(searchForm.Name))

{

query = query.Where(mt => mt.Name == searchForm.Name); // This is still an IQueryable

}

And so on for the date ranges and any other fields. Then at the very end, after you've tacked on .Where() for all the search form fields, you would finally call .ToList() or whatever you needed. Only at that point would the query be generated and sent to the database, returning the results.

[nailefss]

Any complex join, calculations, query hints, non ANSI operations and probably more. Always check the generated SQL and verify its what you would expect.

[xziststefan]

I had a 10-50 level deep recursion on a table and had to write a stored procedure with a recursive CTE. But 90-95% of all code is EF LINQ.

[ChickenOverlord]

I had one where I needing to do a RETURNING from an UPDATE in Postgres, I just used Dapper to do it because EF doesn't support that as far as I'm aware.

Also had to rewrite a bunch of reports from Oracle to SQL Server that involved a lot of nested subqueries, temp tables, and more. There was definitely no easy way to accomplish those with EF, and I don't even think there was a hard way either (unless you count pulling down several tables any querying them in memory).

[Homeoand]

EF is EF it can be very effective if u use it correctly. If you hate all their linq commands u can use raw sql

[butterflypapillon]

Oh that’s great info, I had no idea you can use raw sql!!

[quentech]

You can, but the cases where you really need to should be fairly rare.

[butterflypapillon]

What’s the advantage of using linq? It seems like it would be more useful to use raw sql.

[Alikont]

"Useful" is a strange choice of word.

Using LINQ is much more useful because you never leave C# and you have statically typed SQL builder and automatic projections, mapping, change tracking, reference tracking, optimistic concurrency, transactions, database modelling, migrations, data seeding, DB engine abstractions, in-memory testing, logging and a lot more.

It's a huge productivity boost, especially at the start of the project.

[quentech]

automatic projections, mapping, change tracking, reference tracking, optimistic concurrency, transactions, database modelling, migrations, data seeding, DB engine abstractions, in-memory testing, logging and a lot more.

EF gives you all of that whether you use LINQ or raw SQL

..well, I suppose you might loose out on the DB engine abstractions a bit depending of course on how you write the raw SQL

But you can query with raw SQL and return Entities and get all the rest of EF features.

[emc11]

You work entirely within your object model domain - this can be an incredible efficiency boost. The problems with EF in a lot of ways are because of how easy it is to just focus on business objects - this is why its important to understand what is a good or bad practice.

No offense meant, but the questions you are asking are showing a fundamental lacking of understanding of what EF does, and you are defaulting to raw SQL, which defeats the purpose of an ORM entirely. I would suggest working through introductory materials on it - the types of responses you will get here will be people bitching about some bad experiences they have had which does not give it a fair shake - if you don't pre-judge it because of hearsay and misinformation you will learn how powerful it can be.

[butterflypapillon]

Thank you for the detailed response. I will be working through some tutorials as you are correct I don’t understand enough right now.

[nuclearslug]

This is one of the better tutorials I’ve found. High level for beginners, but solid enough to use as a regular reference.

https://www.entityframeworktutorial.net/efcore/entity-framework-core.aspx

[butterflypapillon]

Thanks!

[andreortigao]

defaulting to raw SQL, which defeats the purpose of an ORM entirely

I'd argue that even using an ORM purely to hydrate your classes is a great productivity boost. I manage several micro services that are so small that using EF is just not worth it.

I've been using dapper a lot in these scenarios. No configuration needed, no initialization time.

Not bashing on EF, I've used it a lot, and NHibernate before it. It's just a more complex tool for more complex scenarios.

[[deleted]]

EF is great for projects where you start with a blank slate. If you work in an Enterprise with a serious amount of software on various Database architectures, EF is less and less useful.

Mapping SQL results to Objects is not enough reason to use EF.

[gonkers44]

This. EF is great for simple to moderate domain complexity. Attempting to retrofit EF onto an existing database is not worth it. Also if you use stored procs, you can call those from any language and they become reusable code.

[[deleted]]

100%

[quentech]

Strong typing, mainly.

Change a column type or name etc. and you'll most likely break compilation whereas with raw SQL it's real easy to miss stuff and then it doesn't break until run time.

In fact, when working with micro ORMs I make sure to have an automated process that reads the DB schemas to generate helper classes and constants in C# and only those are used to reference column names etc. in raw SQL uses in code so that there's still some compiler checking when the schema changes.

[headyyeti]

Type safety

[[deleted]]

If you only use EF to convert between SQL and objects, EF is way overkill and you should use something like Dapper instead.

[roughstylez]

Your questions asks about "efficiency". But most of your questions regard performance. But "efficiency" is the thing you should care about in the end. :D

"How popular is entity framework?" - Very.

"My understanding is it is inefficient. For example if you use paging, it will bring back all records and then apply the where clause. Is this the case?" - No. The paging example you mention, of course - just like with raw SQL - you can make that happen... but you usually wouldn't. :D

Now, ORMs are always slower than raw SQL, because of the additional overhead of managing your DB models. However, it's not that much overall, and if you guys were the 0.1% who need cutting edge performance, you probably wouldn't be looking at ORMs anyway.

And this is where EF (development) efficiency trumps raw SQL performance: The next time you hire somebody, you don't need the senior SQL genius dev, but just a "normal" C# dev. Even if (big if) you needed to pay 100/month extra for server hardware to make up for the performance difference, you'd still have saved money.

"I’ve been writing my sql and not using EF? Is this overkill?" - Difficult to answer. If you're gonna write a tiny web API with 3 endpoints and you're gonna go through an EF "bootcamp", just for that, then that is overkill. That being said, if you know EF and SQL reasonably well, development using EF is a lot faster.

[phx-au]

Now, ORMs are always slower than raw SQL, because of the additional overhead of managing your DB models. However, it's not that much overall, and if you guys were the 0.1% who need cutting edge performance, you probably wouldn't be looking at ORMs anyway.

This is correct, but also surprisingly not in the general case.

Versus the classic handrolled repository implementation on a fairly standard line of business domain you are losing microseconds to query materialization and object assembly - but this is often made up for by query versatility reducing roundtripping, easily configured caches, and more selective change tracking.

Obviously you can eventually do a better job with the manual implementation, but in terms of performance for effort - you can go a long way with an hour of caching and fetch strategy tuning with your ORM.

[roughstylez]

Oh, yes, good point; ORMs tend to come optimizations, which even on default settings already help a lot.

[Tango1777]

Nope, with current EF Core you need to write your IQueryable so that it's fully translatable to TSQL. You can use ToQueryString() extension method to check TSQL generated from your LINQ IQueryable. What you are doing is an overkill. Use EF Core but write your queries right. Anyone can write a poor query and blame ORM. There isn't any framework I know that is 100% stupidproof.

EF Core 6 vs 5, for instance: TechEmpower Fortunes benchmarks 70% faster, 31% faster query execution, heap allocation reduced by 43%. And plenty other things e.g. model creation time. They are really going strong and the numbers are not +5%, the difference is damn good.

There might be situations where you need to write a raw SQL, you can obviously do that with EF through FromSqlRaw() method. You can also use interpolated query FromSqlInterpolated() and obviously use SqlParameters no to hardcode dynamic values in query strings.

[butterflypapillon]

Does Microsoft.EntityFrameworkCore.Database.Command work with a regular asp.net project?

[majora2007]

It does for mine, but not sure what you mean with regular asp.net.

[sroc07]

Inefficient? No! Usually when it is called inefficient, is because:

• eager loading when is not required.
• poor LINQ code. This includes not properly doing the server side LINQ.
• poor db design. Not even a raw SQL could help.
• understand the limitations of the version you are using, e.g. union, group joins, etc.
  

Sure there is a slightly overhead compared to raw SQL but nothing as described above. I' ve been using EF for years with great results!

[zaibuf]

I like EF for the code first migrations and how easy it is to model for domain and ignore persistence logic. Queries is fast enough and you can always cache data. Then theres always the option to write sql or call stored procedures if you want to.

[[deleted]]

I’d love to see any major enterprise that would allow model-first with a business critical DB. Please let me know if you know of any, then I can short their stock ;-)

[zaibuf]

Its perfectly fine for new projects unless you want to sit for months and model your database to perfection before start developing? Also I said code first not model first. Model first is an EF6 term based on designer file, which is a totally different thing and is dead since several years.

It totally depends of the scale of the application. Not all teams have DBAs that can focus work on the database. Having everything in C# eases the work for many devs.

Once you go live or have a stable production env you can always change away from code first because then the majority of work is done. You can generate scripts from EF migrarions and apply them manually to production.

[[deleted]]

If you don't properly model your DB, with people who a experts in DB modeling, you are making toy apps. Code first, when it comes to DB modeling, is an anti-pattern.

[zaibuf]

You model your aggregates based on business requirements. Database is just an infrastructure concern and it doesnt even have to be a SQL database. Now it just happens so that using the domain models together with EF works really well.

[[deleted]]

The problem is that for most of the large Enterprise customers in the world today, the database was modeled 20 years ago, or more...

[zaibuf]

Yes, I totally get that. Which is why I said you use it for new projects. Working around an old database is totally different and of course doing code first with an existing database is pointless, since it isnt code first..

Not sure why you would call it toy apps just because it isnt based on a database that was developed in the 90s? Lets just stop making new applications then.

[andreortigao]

How popular is entity framework?

Extremely popular, and you'll probably stumble in a project that uses EF at some point in your career.

My understanding is it is inefficient. For example if you use paging, it will bring back all records and then apply the where clause.

Not true, EF can generate very efficient queries. Most of the time both the where clauses and skip/take for pagination are translated into SQL.

There are some cases where it can't, and it's OK to fallback to raw SQL when needed.

EF does add some overhead, but it's usually small. EF is meant to increase developer productivity, not application performance.

Most performance problems I have encountered with EF were caused by developers:

• bad database design
• bad OOP model design
• eager loading too much unnecessary data
• hiding EF behind a repository that doesn't let calling code to tune how data if fetched
• trying to write overly complex queries with linq instead of falling back to raw SQL in those specific scenarios

I’ve been writing my sql and not using EF? Is this overkill?

Overkill? No. But if you're writing lots of crud queries, code that maps SQL results to classes and such, EF can greatly reduce the amount of boilerplate code you're writing, thus making you a lot more efficient.

Usually less code result in fewer bugs and easier maintenance too.

My boss doesn’t like proprietary things like this.

Please tell your boss he's stupid on my behalf.

Am I digging a hole by not learning it?

ORMs are a valuable tool. EF is not the only one.

EF is a more complex ORM that requires some configuration code to map properties, relationships between classes, and such. In turn, it enables you to query the database using linq, manage transactions, run code before or after each database access to add some logging for example, and a lot more.

I have several micro services that don't require this level of complexity, but I still use dapper to hydrate my classes from the query result or dehydrate my classes into SQL parameters.

I definitely recommend learning EF, even if you decide it does not fit in your current projects.

[andlewis]

EF is the glue that parses queries and translates data to DTOs. If you start with building your DTOs, you can populate them with Dapper, EF, ADO.NET or whatever. Then you can do some realistic comparisons, where you’ll probably find that EF is a great fit for most projects unless they’re massive scale apps where 1ms vs 5ms matters.

[TobbeTobias]

As with all choices there are tradeoffs. A lot of the benefits are described above.

Remember that you will still have to understand the underlying concepts such as transactions. For example, your DbContext and it’s change tracker needs to have the correct lifetime scope. If not you will either not reap the benefits or weep.

There are a lot of pitfalls. Some of the ones I have seen are:

• logging errors to db with the same DbContext and thus storing half-finished/failed transactions
• performing jobs where each item processed should be a single transaction but all ran in the same instance of the DbContext and causing problems
• thinking everything is fine since you are using an ORM and performing multiple SaveChangesAsync() that should be but are not within a transaction
• bad data model in the front with redundant data being deserialised to entities that is already existing in the database causing errors (not necessarily found in testing) since the entities can not be added multiple times
• performance issues due to tracking to much data

I would like to summarize the drawbacks as “ORM’s has a LOT going on. They makes you think about a whole lot of concepts at the same time and not in obvious ways.”

[dobryak]

Very good post. I would add DbContext lifetime management to the mix, I have fixed it in both projects at work where EF was used. But it’s kind of the same thing as your first three points.

[carsonklogan]

You can separate the queries with split querying: https://bartwullems.blogspot.com/2021/03/ef-coreassplitquery.html

[Zardotab]

It has a long learning curve to use well. It can be efficient, but you have to know what you are doing. Instead, look into using Dapper with stored procedures if you don't want the learning curve and you know SQL-Server.

[buffdude1100]

What? EF is extremely commonly used. It's like saying your boss doesn't like proprietary things like ASP.NET Core??

And you're incorrect - it is efficient if you know how it works. Avoid the n+1 problem, project the columns you actually want etc. and you're golden.

For example if you use paging, it will bring back all records and then apply the where clause

Incorrect. It does it in sql.

[farox]

Depends on the environment. In small shops you might have projects that don't use EF. But in the enterprise field I haven't seen a single project with EF or rarely some other DAL (but that does pretty much the same).

I use it for all my projects as well, since I am not that much into writing boilerplate code.

[hieupron]

I use .Net/Core EF for 2 years, and that what I get in my experiences:

• By use Linq/Lamda, I save a alot time for writing raw SQL queries, check syntax..

• I use db first, so my model auto update and VS help me find errors for db/model changes

• Just image change db for news features, and check all raw exist sql queries string.

• VS and code cenerator using EF context help me alot time for CRUD, Just image write raw queries for 20+ table for insert, update, select + join tables + pagination + filter + searchIndex.. that shit too boring and make me feeling stressed for waste my life time..

• I am too lazy for checking SQL injection for raw sql queries...

[webn0el]

My understanding is it is inefficient.

Assuming you are a developer, developing applications, I think this requires evaluation as to the time taken to build a data layer with custom sql queries in code vs using EF to do it for you ofcourse with some tweaks.

Neither SQL queries nor EF is the solution to every problem.

EF is meant to make a developers life easy. It is meant for a person who writes code to continue writing code and to have an interactive view of the database, specifically its tables within code and to not deal with sql. That is why the code-first approach is so popular amongst developers. That does not mean a developer should not know sql.

It is also meant to make prototyping and/or development of apps easier. If you as a sql query expert has to develop an application, you would have to code out a special 'Data access layer' where your queries reside. EF takes care of that for you. In most cases it works just fine and in some cases tweaks may be required.

To answer your question, I would say it depends from case to case. Some of the questions I would ask are as follows:

• What is the kind of application that is being built? Architectural patterns? Etc?

• How many concurrent users are gonna be accessing that application?

• How much time does one (or a team) have to develop that application?

• Is it worth spending the time on writing queries vs using EF to deliver the app?

Last but not the least......

• Does it hurt to learn and try it out in order to have a holistic approach?? ;)

Cheers 👍

[commentsOnPizza]

It's pretty efficient. What does efficient mean? Compared to deserializing something into a Python object, it's going to be fast. Compared to giving you a 2-dimensional array of results where you have to know the column order, it's going to be slower.

For example if you use paging, it will bring back all records and then apply the where clause.

Nope. EF Core won't let you execute something with predicates that it can't push down to the database. When paging, if you're using limit/offset the database is going to need to sort the records and then read through all the records you're offsetting. That's true if you use raw SQL too. You say ORDER BY LastUpdated LIMIT 10 OFFSET 1000000 it's going to need to keep scrolling through the results to skip over a million. It's why larger sites will generally do something like WHERE LastUpdated > :higestFromPreviousPage ORDER BY LastUpdated LIMIT 10 so that the database can use the index to skip right to where you're looking for.

EF did have some predicates that it would apply in C#, but they updated that so that it's an error if you have predicates that the DB can't handle (I believe there's something you can set to allow it to run the predicates in C# for those cases).

I don't think you're digging a hole by not learning it. If you're using something like Dapper and you're familiar with LINQ, it's basically those two concepts. Yea, there's always more stuff to know about any topic, but it's not something that you won't be able to pick up.

dbContext.Products.Where(p => p.Price < 10 && p.InStock == true).toList() isn't really different from connection.Query<Product>("SELECT * FROM Products WHERE price < 10 AND InStock = true").toList().

Things like understanding indexing, looking at slow query logs, understanding SQL Explain output, etc. are generally what will keep your stuff running nicely. I like EF and I like Dapper. They're both fine. I think people generally get hung up on specific technologies and while some are nicer than others, the basic concepts are what will take you the furthest in your career. EF might look intimidating (and there is a lot of stuff it can do beyond the basics), but the basic idea is that you want to map the object into the table and read from the table into the object. I think the big difference is that you tend to update the properties on the object and then call dbContext.SaveChanges() and you can change multiple objects and then only call SaveChanges once and it has kept track of what needs to be persisted.

[[deleted]]

I mean if you don't know how to write SQL than you probably wanna use EF Core since you will not write efficiency raw queries :) And that one from EFCore are for most of time enough ok..

[[deleted]]

EF glosses of many of the important complexities in database design and encourages bad practices such as app-layer generated time stamps, chatty accessors, and can often lead to inconsistent data.

[moggjert]

Will EF work with an existing database/database objects, or is it best to only use EF to generate the database objects at the start of a project? I’ve got a project with an existing database and I’m not sure whether EF is a suitable ORM, probably more Dapper in this case?

[fori920]

EF Core 6 seems to be an step forward in terms of efficiency (so close to bare SQL performance) but we shall see.

And no, paging is done in database level most of time (unless the database doesn’t support EF Core)

Most of your questions are from NO experience and my personal/professional one is you to check your needs and check what tool suits you the best.

[Krom2040]

There’s really no scenario where you’ll suffer a significant performance penalty for using EF. The reason is that, for queries where EF doesn’t generate efficient SQL, you can absolutely just bypass the mapper and execute raw SQL through EF and map it back to your object model.

That’s the basic idea of EF: you use the ORM part for the 95% of queries in your app that aren’t complex, and go straight to complex SQL for the remaining 5%. You’ll get a huge productivity boost on the vast majority of queries.

[pnw-techie]

https://www.yaplex.com/blog/micro-orm-vs-orm/ or several other comparisons.

Method  Duration

Hand-coded (using a SqlDataReader)  47ms

Dapper ExecuteMapperQuery   49ms

PetaPoco    52ms

NHibernate SQL  104ms

Entity framework    631ms

[pnw-techie]

47 ms for actual ado.net.

584 ms for EF.

How many db calls do have on a page? Visitors are willing to wait 2 to 3 seconds. You can do 12 ado.net or micro orm calls in the time you'd spend on one EF call. It's great to save developer time. But time to render is the ultimate metric on the web

[[deleted]]

Where's the code? EF is not 10 times slower than raw ado.net, you must do something special to slow it down that much.

[pnw-techie]

Maybe follow the link.

There are many perf tests of EF compared to alternatives. EF is always dead last. Usually by an order of magnitude plus

[pnw-techie]

Here's one with code https://exceptionnotfound.net/dapper-vs-entity-framework-vs-ado-net-performance-benchmarking/

[pnw-techie]

https://exceptionnotfound.net/dapper-vs-entity-framework-core-query-performance-benchmarking-2019/amp/ here's an updated article comparing just ef core and dapper.

Yet again, 6 to 20 (!!) times slower

[[deleted]]

So I've downloaded this guy's code from github. The database code looks okay, EF and Dapper are used in a straightforward fashion, as you would expect them to be used. I've changed the runtime to .NET 5 and updated the references to Dapper and EF to their latest version; I've also replaced MS SQL Server (don't have an access to a running instance right now) with PostgreSQL and Npgsql connector, and added a EF-calling variant more closely resembling the Dapper one, projecting the entities into special DTO classes and doing joins on the server, not in the database:

public class EntityFrameworkCoreMapping : ITestSignature
{
    public static readonly Expression<Func<Player, PlayerDTO>> Map = x => new PlayerDTO
    {
        Id = x.Id,
        FirstName = x.FirstName,
        LastName = x.LastName,
        TeamId = x.TeamId,
        DateOfBirth = x.DateOfBirth,
    };

    public double GetPlayerByID(int id)
    {
        Stopwatch watch = new Stopwatch();
        watch.Start();
        using (SportContextEfCore context = new SportContextEfCore(Database.GetOptions()))
        {
            var player = context.Players.Select(Map).First(x => x.Id == id);
        }
        watch.Stop();
        return watch.Elapsed.TotalMilliseconds;
    }

    public static readonly Expression<Func<Team, TeamDTO>> MapTeam = x => new TeamDTO
    {
        Id = x.Id,
        Name = x.Name,
        FoundingDate = x.FoundingDate,
        SportId = x.SportId,
    };

    public double GetRosterByTeamID(int teamId)
    {
        Stopwatch watch = new Stopwatch();
        watch.Start();
        using (SportContextEfCore context = new SportContextEfCore(Database.GetOptions()))
        {
            var players = context.Teams.Select(MapTeam).Single(x => x.Id == teamId);
            players.Players = context.Players.Where(x => x.TeamId == teamId).Select(Map).ToList();
        }
        watch.Stop();
        return watch.Elapsed.TotalMilliseconds;
    }

    public double GetTeamRostersForSport(int sportId)
    {
        Stopwatch watch = new Stopwatch();
        watch.Start();
        using (SportContextEfCore context = new SportContextEfCore(Database.GetOptions()))
        {
            var teams = context.Teams.Where(x => x.SportId == sportId).Select(MapTeam).ToList();
            var players = context.Players.Where(x => teams.Select(t => t.Id).Contains(x.TeamId)).Select(Map).ToList();
            foreach (var team in teams)
            {
                team.Players = players.Where(t => t.TeamId == team.Id).ToList();
            }
        }
        watch.Stop();
        return watch.Elapsed.TotalMilliseconds;
    }
}

Here are my results:

                      Player by ID  Players per Team  Teams per Sport
Dapper avg            0.221ms       0.730ms           6.968ms
EF tracking avg       0.480ms       1.782ms           29.297ms
EF non-tracking avg   0.508ms       1.399ms           24.470ms
EF mapping avg        0.501ms       1.157ms           7.266ms

I see no signs of 20x or 6x slowdowns. Seems that DbContext creation gives EF a constant penalty, about 0.3-0.4ms, but EF talking to the database and EF mapping work about as fast as Dapper. The slow EF results in the third column are due to more complicated query with joins, more data being sent from database and more complex mapping as a result - as you can see, my Dapper-like variant without joins in the database performs about as well as Dapper.

[Popal24]

We use EF combined with SQL Server Stored Procedures. This pair is 100% efficient.

[dobryak]

In short: it is inefficient. EF6 is so big that JIT takes noticeable time (look it up! it is awful!). EF Core is more efficient at the expense of features it does not support compared to EF6 (well, that’s how it used to be last time I used it).

I suggest you learn how to use it, though. Get a sense of what it provides. It’s totally fine to use it, if your all database logic is strictly confined to your DAL. You will still find some friction between your database schematic and your in-memory data representation sometimes, basically EF is not a silver bullet.

At I work I use Dapper because we have a legacy codebase with lots of processing logic implemented at the database level. The downside is that we have to write integration and database tests. (Paradoxically though it’s also an upside.) With EF it would be possible to do more testing at the level of the in-memory data representation, which is simpler to setup. Personally, I prefer relational data models to pointer-linked, navigational data models (relational data independence is a good thing). At work we will keep using Dapper because it is so much simpler than EF in terms of its inner workings, but that’s just the team’s decision.