Relational databases shape the data optimized for SQL. Data represented by applications should be shaped based on the domain. Converting the data from SQL optimized tables to domain classes—and back—takes work, and that work seldom provides additional business value to the application. Object-relational mappers bridge the gap and reduce the amount of plumbing code that must be written. In this section, Phil explains the case for using ORMs.
- [Instructor] Before we get into the details of Entity Framework Core, let's just talk about object-relational mappers, or ORMs, in general. So, what's the problem that we're trying to solve? Well, the problem is that SQL databases, and I don't me SQL Server, I mean databases that are based on a structured query language, they store data relationally and they typically only store scalar values. Now, there, of course, some exceptions to this, but this is very different than applications.
Applications model data after the domain and they create and use complex object-orient entities. Well, they're not always complex, but they're most typically object-oriented and not normalized as we would think in a database sense, so what we need to do in our code is translate back and forth between these relational structures and our object structures. Now, writing this data access code to translate relational data back into objects and back the other way doesn't add anything to our application that differentiates it from the competition, so what we want to do is we want to stop writing all that plumbing code and write the stuff that's going to make our customers more money, be more efficient, whatever the goal of the application is.
So, how do we do that? Well, we bring in an object-relational mapper, and from Wikipedia, an ORM is a programming technique of converting data between incompatible type systems using object-oriented programming languages. Well, that's exactly what we're looking for, right? We want to use C Sharp or .NET to convert from relational data into objects and back the other way. So, what are some of the benefits of using an ORM? Well, I've already stated the first one.
They convert relational data to domain models and back, and this removes the need for us to write all that database plumbing ourselves. Developers are working with classes that support the domain instead of classes that are defined by the confines of a SQL-based system. This allows them to focus on providing business entity application, again, instead of just writing the plumbing. And some additional benefits that we get from modern ORMs, and Entity Framework is one of those, is that they implement the Unit of Work pattern, a form of the Repository pattern, and support transactions, both implicitly and explicitly.
So, what is a Unit of Work design pattern? Well, this maintains a list of objects affected by a business transaction and coordinates the writing out of changes and resolution of concurrency problems. So, what we want to make sure that we're doing is, when we are saving changes from our domain model into the relational model, we want to make sure that when a developer calls SaveChanges that it happens as a Unit of Work and we want to make sure that it's done in a transaction.
Repository pattern, again using Martin Fowler's definition, mediates between a domain and data mapping layers using a collection-like interface for accessing domain objects. Well, what we want to make sure that we do is we have a collection of products, for example, and we don't want to have to worry about writing all of the different code to get them from the database to save them, to track changes and things like that. So, we really want to have something that lets us program to an interface and not worry about all that mapping back and forth.
There are some arguments that I quite often hear from people who just don't really understand ORMs. The first one is there's too much magic. Well, it's not magic, it's just code you don't have to write. So, LINQ gets translated into SQL or database-specific calls and there are some things going on behind the scenes that might not be intuitive. Creating a connection, creating a data reader, creating objects from that data reader.
A common one I hear is they are too slow and I would think that they're probably being used wrong. I haven't had performance issues. One advantage of Entity Framework Core over Entity Framework 6 is its order of magnitude faster and we'll see a demo of that here shortly. But typically if people are complaining that Entity Framework is too slow, I would posit that they are using it wrong. Here's another common one, our data is too complex.
As a consultant, I hear this all the time. Everybody thinks that their situation is unique and different than everybody else's, but at the end of the day, it's just data and it's relational data, so it can't be that terribly complex if it fit into SQL Server, and if it fits into SQL Server or other relational databases, then Entity Framework can handle it. They're hard to learn. Well, anytime you want to pick up a new tool for your toolbox, you're going to have to learn it. I almost agree that this is a decent argument against using an ORM because if you're not going to take time to learn it, you're probably going to be using it wrong and then it will be too slow and too hard to use.
The final one I hear quite often is that DBAs won't let us and this is unfortunate, and this is much more of an HR issue than a technical issue. DBAs shouldn't be worrying about writing every little query, they should be worrying about the bigger picture thing, and this is just my opinion. And DBAs provide extreme value into an organization, but I don't think that value is micromanaging what queries are written or what tables are named or fields are named. So, that's something that you're going to have to have a discussion with them.
Let's be human, let's talk about it, let's see why they don't want you to use an ORM, and see if their concerns are justified or not. That's beyond the scope of this course, but what I recommend is talk to them. Don't just send an email, walk over and talk to them, and try and figure out what's going on and what their motivation is for preventing you from using an ORM. Let's talk about some use cases. The good, why should we use an ORM? If we're doing CRUD operations, create, read, update, and delete, and when I'm saying read, I'm not saying, referring to reading thousands of records at a time, this is really forms over data.
A lot of business applications work great with Entity Framework. Some poor use cases for it, reporting solutions. I remember I taught an NEF class for a customer and they were doing very simple website forms over data type stuff, then I got a call from them a little while ago and they said we hate Entity Framework. I'm like, well, what's going on? You guys were doing great. Well, we're using it with SQL Server reporting services and it's slow.
Well, guess what? ORMs weren't designed to bring back thousands of records, that's not their strong point, and it's not even really a use case you should be using. So, if you're doing a reporting solution, don't use Entity Framework. You're not going to get the value that EF is designed for. ETL operations. So, if I am trying to move data from one place to another place and I have to do some transformation on that, changing things around, ETL is extract, transform, and load, EF is probably not the best way to do that.
I have done that in situations where SSIS or ASUS over integration services weren't allowed in with the customer and we were just doing small number of records at a time, but there are much better tools for ETL operations. Another poor use for Entity Framework is what I like to refer to as set-based operations. I wrote an application, it was very data-heavy. Well, it was a payroll application for nursing homes and had also tied in with a lot of reports.
Well, there's all these tax tables and everything else, and there's no benefit to bring all that data that's sitting in SQL Server into the client's memory space so that I can make changes and create records and then push them back into the database. So, computations that are relying on large sets of data, you store procedures and there's no reason why you can't blend the operation, right? So, when you're doing forms over data in your application, use an ORM, but if you're doing some heavy, set-based calculations, then use stored procedures.
- Entity Framework Core components and projects
- Working with scaffolded files
- Testing with xUnit
- Viewing generated SQL
- Composing queries
- Sorting and filtering results
- Working with aggregates
- Loading related data
- Logging and tracking
- Mapping functions
- Generics and delegates
- Checking concurrency
- Resiliency and transactions