When it comes to business software, almost all of it needs to store data that pertains to its functions. For many decades, Relational Database Management System (RDBMS) has been a go-to data storage for developers. ORM is a set of technologies that allows developers to access RDBMS data from an object-oriented programming language. There are other RDBMSes available, such as SQL Server, Oracle, DB2, MySQL, and many more. These database systems share some common characteristics. Each system supports one or more databases. Databases consist of many tables. Each table stores data in a tabular format, divided into columns and rows. Data rows in multiple tables may relate to each other. For example, a person's details stored in the Person table can have phone numbers stored in a separate Phones table.

In the following screenshot, you can see a table that allows you to store a person's information, specifically their first and last names, along with a unique identifier for each person. This type of storage, where similar data items are grouped together into tabular structures is typical:

Each column can also be constrained in some ways. For example, PersonId is an integer column. LastName is nvarchar(50) column, which means you can store Unicode data of variable size in it, up to 50 characters. You will see in subsequent chapters how we describe this information using Entity Framework.

The data stored in each column and row combination is scalar data, such as number or string. When software needs to persist or retrieve data, it must describe its intent, such as insert or select query, using the database-specific language called Structured Query Language (SQL). SQL is a common standard for all relational database systems, as issued by the American National Standards Institute (ANSI). However, some database systems have their own dialect on top of the common standard. In this book, we are not going to dive into the depths of SQL, but some concepts are important to understand. There are some basic commands that we need to look at. These are typically described as CRUD. CRUD stands for Create, Retrieve, Update, and Delete. For example, if you want to retrieve or query the data from the preceding example, you would type the following:

SELECT PersonId, FirstName, LastName
FROM Person

Historically, before tools such as Entity Framework, developers embedded SQL language statements inside the software code using .NET languages, such as C# or VB.NET or other programming languages, such as C++ or Java. The reason for this is that these languages do not natively speak or understand SQL. For example, to retrieve the data from the database and manipulate it as objects, you would write a fair amount of code using ADO.NET, .NET Framework's data access built-in framework. You would need to define a class to hold a person's data. Then, you would need to open a connection to the database, create a command that uses the preceding query as its text, execute the command's reader, and iterate through the reader results, populating an instance of our Person class with the data from the reader. As you can see, there would be a lot of steps involved. More importantly, the code we'd write would be quite fragile.

For example, if we change the column name in our database from FirstName to First_Name, our code would still compile just fine, but would throw an exception when we try to run it. Moreover, the data in the database was stored as scalar values organized in columns and rows in a table, but our destination was an object or object graph. As you can see, this way of accessing the data has a number of issues.

First of all, there is a type mismatch between RDBMS column types and .NET types. Second, there is a mismatch between storage, which is a collection of scalar values, and destination, which is an object with properties. To further complicate the situation, our person object could also have a complex property that contains a list of phone numbers, which would be represented by a completely different table. These problems are collectively referred to as impedance mismatch between object-oriented programming and relational databases.

The set of tools called ORM came about to solve this mismatch problem. An ORM tool represents data stored in database tables as objects, native to a programming language, such as .NET languages, C#, and VB.NET. ORM tools have many advantages over the traditional code, such as ADO.NET code that we mentioned. They expose the data using native .NET types. They expose related data using simple .NET properties. They provide compile time checking. They solve the problem with typos. Developers do not have to use SQL, a different language. Instead in the .NET world, developers use Language INtegrated Query (LINQ) to query the data. LINQ is simply part of C# and VB.NET languages. We will cover the basics of LINQ in subsequent chapters. By the same token, programmers use an ORM tool's API to persist data to the database. Finally, as we will see later, you will write less code. Less code means fewer bugs, right?