Entity Framework Best Practices - Should EFCore Be Your Data Access of Choice?

Entity Framework is an amazing set of tooling around data access. With EFCore, that tooling becomes even more powerful. So why is it that I still don't recommend that people use EFCore? In this video, I will walk you through the best practices of Entity Framework and EFCore and point out the pitfalls to avoid. We will discuss where there are problems and what to do to resolve those problems.

Thanks to Ralfs HBK for the chapters breakdown:
0:00 - Intro
3:11 - Project setup: ASP. NET Core project
4:39 - Project setup: .NET standard library
6:04 - Project setup: Adding data models
11:02 - Project setup: EF Core SQL server NuGet
13:01 - Project setup: Data Access class
17:43 - EF Configuration: Connection string
22:46 - Migration scripts and concerns
30:05 - Migration script: Down command
31:50 - Migration script: Up command
37:19 - Multiple migrations
38:46 - Model snapshot...
40:10 - Model snapshot: Nvarchar explained
45:14 - Problem: Nvarchar(max): Indexing
46:49 - Problem: Nvarchar(max): performance
50:52 - ... Model snapshot: why nvarchar(max) ...
52:13 - Problem: EF and data access
53:39 - ... Model snapshot
54:58 - Created Data Base overview
57:50 - Data model optimization...
1:00:48 - Problem: Development vs production
1:03:45 - ... Data model optimization
1:05:18 - Possible data loss with new migration for in production app
1:07:40 - The updated migration script overview
1:09:15 - No data rollback
1:10:39 - Data Base update
1:11:45 - Using the EF: data import from JSON file
1:18:34 - Using the XEventProfiler
1:23:57 - Monitoring and filtering SQL server commands from the EF app
1:27:42 - How the EF inserts data: sp_executesql
1:39:11 - EF query
1:46:39 - EF query: efficiency and possible consequences
1:58:34 - EF query: where clause: SQL vs C#
2:10:40 - Recap: best practices covered in this video
2:12:08 - Why use Entity Framework? Supposed benefits
2:20:12 - Benefits of using Dapper
2:25:46 - Summary and concluding remarks

[GreggBursey]

Man, there are hundreds of videos on “how to EF”, and that’s fine. But your walkthrough here that included not only that, but also the pitfalls, what could cause problems, and WHY those can be problems was extremely helpful.

The section explaining the string fields like NVARCHAR(MAX) was very informative and helpful.

[jamesbest]

"If the way someone develops a C# application makes you angry, I suggest you reevaluate your priorities in life." LOL legend

[tchpowdog]

I agree with everything your saying here in regards to having to be experienced in order to use EF efficiently. However, everything you laid out is from a "code-first" perspective. I use EF in all my projects, but I don't use their migration and schema tooling. I use good-ole fashioned DB-first approach. I use SSMS as much as I use Visual Studio. Also, I only use EF for entity level CRUD operations and very basic queries. If I have ANY more-than-basic queries or data logic, I use SPs, Views, Functions, etc. - then I call those functions through EF. I wouldn't recommend using EF for a code-first approach, either. However, I wouldn't recommend code-first AT ALL. Personally, I despise the code-first approach - but I also appreciate the fact that it works for other people.

[MrCalletennis]

If you're to lazy to use your mouse :)

00:02 Consider EF Core for improved performance
02:23 Setting up the environment in Visual Studio
08:01 Utilize string for zip codes for flexibility in address modeling.
10:59 Setting up Entity Framework with SQL Server
16:51 Configuring Entity Framework Core for SQL Server
19:09 Setting up a local DB for Entity Framework usage
23:32 Setting up Entity Framework Core Tools for Package Manager Console
26:15 Separation of concerns can prevent major overhauls
31:09 Rollbacks in Entity Framework come with potential data loss risks
33:37 Database design considerations for EFCore
38:53 Understanding model snapshot in EFCore
41:28 Unicode takes up twice as much storage as ASCII
46:35 Storage space and memory allocation implications for nvarchar(max) columns
49:09 Column type impacts memory usage and performance in Entity Framework.
54:22 Understanding one-to-many relationship in Entity Framework
56:43 Modifying tables for better design
1:01:49 Memory usage becomes a limiting factor in production environment.
1:04:10 Modifications made to models for validation.
1:08:58 Roll back in Entity Framework affects schema, not data.
1:11:15 Best practices for using Entity Framework
1:16:25 Database data loading and insertion process explained
1:19:06 Utilizing X event profiler for diagnosing EF queries
1:24:27 Entity Framework efficiently uses 'select count star' to find number of rows in table.
1:27:04 Entity Framework batch inserts without creating a stored procedure
1:32:01 Entity Framework lacks database security
1:34:23 Use of SP_ExecuteSQL for efficiency improvement
1:39:21 Using Include to fetch related data efficiently
1:41:46 Understanding batch completion and memory information retrieval
1:46:45 Entity Framework handles one-to-many relationships by duplicating the primary entity
1:49:21 Entity Framework compresses data efficiently
1:54:20 Understanding performance implications of using Entity Framework vs Dapper
1:56:31 Understanding Entity Framework migrations is crucial for application performance.
2:01:40 Entity Framework 6 doesn't alert about problematic queries
2:04:00 Utilizing toList method in Entity Framework query
2:09:21 Avoid calling C# methods in WHERE clause for better performance
2:11:51 Entity Framework provides faster development speed but may compromise on performance.
2:16:55 Entity Framework is faster to develop but can result in database performance issues
2:19:20 Optimize resource usage for cost efficiency
2:24:28 Entity Framework Core designed for loose coupling
2:26:58 Entity Framework Core is a powerful tool but requires training and experience to avoid pitfalls.
2:31:30 Consider nuanced factors when choosing Entity Framework

[vivekkaushik]

The fact that is 2.5 hrs tutorial and for FREE on youtube is ming boggling. Most people share knowledge in general. Thankyou so much Tim.

[metax]

If all of your other videos are this detailed, I'm going to watch your entire library. I was expecting to to see a couple instances of how to do something in Entity, but this video was so much more valuable then that.

[vkumbaji]

Mr.Tim Corey, your courses are superb educational and great resources for learners of all expertise levels.

[RyomaGG]

16:58 You can put in an override for OnConfiguring in the PeopleContext class

protected override void optionsBuilder)
{



}

Therefore your front end no longer knows about entity in this circumstance.

[InsolentDrummer]

The reason I find Tim's channel so useful is because he always explains why we do certain things one way and not the other and not just because he said so!

[EricHarmon]

I watched this video with great interest, and I have some comments to make. I have used Entity Framework fairly extensively since EF 4, up until EF Core 2.x. I didn't get a chance to use EF Core 3.x in production, as I retired three months ago. That should give you an indication of my frame of reference as it relates to EF. Anything I see here is, of course, the way *I* worked, and I'm not claiming my way was the best - only that it well worked for me, and in some cases reduces or eliminates some of the areas that you pointed to as causing you some concern.


First, I would never allow any data access tool, EF or otherwise, to create the database for me. Though I wasn't a DBA per se, I created multiple databases for use with my applications, and I always created those databases manually in SSMS. Not only did that allow me to define the tables and columns in exactly the way I wanted (for example, I didn't need to know or care whether EF (or nHibernate, etc.) pluralized table names), but I could also create secondary indexes in the "normal" way in SSMS, without needing to learn anything about how EF data annotations (or fluent API) worked for defining indexes and so on. I just think it's better to have complete control over the database structure. The only thing I ever used data annotations for was for required or max length, which allowed the UI to perform validation of data via Validator.ValidateObject(), et al.



You showed the long sp_executesql query generated by EF. I actually never encountered that, as that's something new in EF Core 3.x. In EF "Classic" and EF Core 2.x, one insert statement was sent to the database per row insert (and all the inserts were performed in a transaction). It's interesting to note that the EF team got crucified for that approach, as it caused numerous INSERT statements to be sent to SQL Server when bulk inserting. I myself encountered more than one occasion where I saw an order of magnitude or better performance increase when I used EF addons such as EFCore.BulkExtensions and the like, in the few cases where I needed to insert many rows into the database at once. Now that the EF team wrapped the multiple inserts into a single statement, they're again facing criticism. I'm not any kind of security expert, so perhaps there's a better way they could have reached the same end goal, but definitely sending a single SQL statement per inserted row wasn't being happily accepted.



I personally never used migrations, as I found them to be too limiting (at least, when I looked at them; it's possible they gained functionality later on). I found that in many cases when I made a database schema change, I needed to do some serious work on the data. For example, I might factor out three columns from one table and two columns from another table and put them into a new table, referenced by both of the original tables. That kind of work didn't lend itself to migrations, so I wrote a little "framework" for database updates, and then whenever I had occasion to update the database, I filled in the guts of the framework with the handcrafted SQL statements that I needed. So I didn't use EF at all for database migrations.



I wrote desktop applications, and I was able to completely encapsulate EF into a .NET Standard 2.0 class library, with no reference whatsoever from the UI project(s). This might be different for me because I didn't write web apps (though I'm assuming this would work equally well for a web app), but I got the (encrypted) server name, database name, user id, and password from a local config file, and then passed those to a static method in the data access library, which cached the information. I wrote a UnitOfWork class that encapsulated the DBContext, and the application made calls to UnitOfWork. The UnitOfWork class instantiated the DBContext, using the cached connection information. The main application never knew it was using Entity Framework at all.



Finally, I don't think the query where you loaded all people, addresses, and email addresses was a fair one. You wouldn't load them like in reality that unless you planned to do a bunch of updates to all the data and then resave it. Instead, you'd use DTOs and you'd have a projection query, the same as I imagine you'd do when using a different ORM, or when issuing direct SQL statements. I have, though, on occasion done the same thing in EF Core (2.x) that you said you do in Dapper, which is to issue two or more SQL statements independently, and then merge the resulting data using C#.



I understand what you're saying about needing to learn EF before using it, but I would argue that that can be said about literally any technology. I wouldn't want to sit down and try to write an application using Dapper (I've never used Dapper before) without studying it fairly extensively. I wouldn't allow Dapper to automatically create the database, just as I wouldn't allow EF, nHibernate, or anything else to create it. So I think that you're being a little bit hard on EF, as it's an incredible data access technology. In my opinion, the types of "gotchas" that you point out relate to any technology, including direct SQL / ADO.NET. I can easily write a horribly performing, though straightforward, query, in direct SQL. For example, I've seen tons of examples that show "SELECT * FROM Customers", which you wouldn't (hopefully) ever do in real life.

[viajeromero]

You are the best programming teacher I've ever met. Thank you so much for this content.

[gasparottogui]

Hey Tim, thanks for another great video! I've been learning a lot in your tutorials and it's impressive to see how deeply you understand the language and how clearly you explain it. Thanks for sharing!

[TehDarkWolf]

I feel like it's still important to learn it for people who are planning to get into professional industry. EF is widely used in many companies ranging from small to large, I honestly I have heard of dapper until I came across your channel. The best way to learn is to use the frameworks, if you consistently avoid well-known frameworks you won't really grow as a developer and limit yourself in the professional world.

[justtohide]

While I didn't know about this channel, when I was trying to write migrations from other channels, no one went that deep, they didn't show the errors, and so I had a problem every time I found a problem myself. in the end, I lost my enthusiasm and instead of writing with migration, I switched to writing db first. when I discovered this channel, everything was great. Thanks

[mehedihasanopu]

Thank you Tim Corey. It's a great chance for us to learn from your courses.

For checking if there is any record in table, Any() functions should be used for better performance. Count() has to read the whole collection of the table but Any() immediately returns after reading a single record.

[greenwalmart]

Tim, you did an great job covering this topic. I try to tell people that programming is the last thing that you do. And your videos demonstrate this. You have to know exactly what you're doing or pay the price on the backend. Awesome video!

[lolop]

I consider myself a pretty experienced EF developer, but had only just learnt about the drawback of using Includes from this video. I had assumed it did something along the lines of QueryMultiple in Dapper. Just shows it's always worth looking at videos on topics you consider yourself well versed in.

One thing I was waiting for you to bring up was projections. Using Select statements is an extremely important part of making sure that EF runs smoothly for me. It solves the issue of Include, and removes the change tracking which is another big cause of slowdowns. Using projections you can get near identical performance to Dapper, while also keeping all the benefits of EF core. One of the most important things for me is the compile time safety of my queries. Admittedly I haven't watched your videos on how you use Dapper yet so I'm not sure if you've addressed this.


I also think using the right tool for the job is an important aspect. If a query is complex and needs to be hand crafted for performance, writing the SQL manually and using dapper is the better option. If you just want to pull a few fields from a table or two and put it into a VM/DTO, I think EF with projections is usually better. I'm not against using both in the same project and using EF for 90% of my needs, and Dapper for the last 10% of more tricky/important aspects of the application.


Lastly, in case you didn't know, you should be able to change the 'Logging.LogLevel.Microsoft' in the appsetting.json to be 'Information' and all the queries run by EF core will be logged to the ASP.net console. I find it much easier to look at the queries as they go through there than trying to filter through the junk in SQL Management Studio.


Just my 2 cents, great informational video overall, especially for newer developers!

[E-Dh]

i am currently using this video as a go to guide whenever a friend of mine mentions using EFCore.
Thank you Tim, you really are awesome !

[torrvic]

Your lessons are pure gold Mr. Corey! Some things you said I learned the hard way via trial and error myself but other things you advised can’t be underestimated. Such a good advices you are giving to us! You even know about War and Peace book and it tells us as to how knowledgeable and intelligent you are. I am amazed by your charming personality Tim!
Thank you so much!

[soucianceeqdamrashti]

What I love about this video is that you explain each topic with the right level of detail and in a succint and informative manner. Even someone who has primarly used Dapper loved the content and learned a lot.