Introduction to Entity Framework Core – Cooperation with the Database in C#

If we want to work with a database in C#, we have several options. We can use the once more popular ADO.NET or one of several ORM frameworks. In ADO.NET you have to write pure SQL, and in ORM frameworks you can rely on C# classes that generate SQL themselves underneath. In my experience, .NET developers most often use Entity Framework, Entity Framework Core and Dapper. In today's article, I wanted to introduce you to Entity Framework Core, which in my opinion is currently the best.

ORM

INSERT INTO Books (column1, column2) 
VALUES (value1, value2);

Just call the method:

Books.Add(book);

And pass the object we want to add.

From a programmer's point of view, this is an ideal solution, thanks to which a programmer, even without typical database knowledge, can work with the database without any problems. Using an object-oriented language is more natural for us programmers. Of course, it is worth knowing at least the basic SQL syntax, thanks to which you will be able to optimize these queries, but this is not mandatory knowledge at the beginning.

In addition, of course, writing queries using an object-oriented language is much faster, sometimes it is enough to call one method or property instead of writing many lines in SQL. What's more, you definitely won't make any typos, which sometimes happens in SQL. In addition, ORMs have many advanced functions such as transaction support, migrations, and connection pool support.

When it comes to the disadvantages of ORM, the first thing that comes to mind is the fact that the created queries may not be fast and optimal, but I guarantee you that with the right knowledge, your queries will be just that. You just need to remember a few basic rules.

Entity Framework

Entity Framework Core

Practice – discussion of the project

namespace Bookstore
{
    class ApplicationDbContext : DbContext
    {
        public DbSet<Book> Books { get; set; }
        public DbSet<Category> Categories { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var builder = new ConfigurationBuilder()
                .AddJsonFile($"appsettings.json", true, true);

            var config = builder.Build();

            optionsBuilder
                .UseSqlServer(config["ConnectionString"]);
        }
    }
}

It contains 2 DbSets Books and Categories. These are mappings of 2 tables in the database with the same names. This is what our entities look like:

namespace Bookstore
{
    public class Book
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public decimal Price { get; set; }

        public int? CategoryId { get; set; }
        public Category Category { get; set; }
    }
}
  

namespace Bookstore
{
    public class Category
    {
        public int Id { get; set; }
        public string Name { get; set; }

        public ICollection<Book> Books { get; set; } = new HashSet<Book>();
    }
}

The Books entity has properties such as Id, Name, Price and a reference to Category. In turn, the Category entity has Id, Name properties and a Books collection navigation property. Also, a book can have 1 category, but 1 category can be assigned to multiple books.

Finally, the connection string in the appsettings.json file:

{
  "ConnectionString": "Server=.\\SQLEXPRESS;Database=Bookstore;User Id=x;Password=y;"
}

We can switch to the Program class and the main Main method, where we will write some basic queries and commands using Entity Framework Core.

namespace Bookstore
{
    class Program
    {
        static async Task Main(string[] args)
        {
        }
    }
}

Adding a new record to the database

To add a new record to the database using Entity Framework Core, you first need to create a new object, in our example an instance of the Book class.

var book = new Book { Name = "Książka 1", Price = 10 };

We do not have to fill in the Category property, it is not required, so for the simplicity of the example, we will skip setting the category itself for now (it will be useful for subsequent, more advanced examples). The ID will be generated automatically by the database. To add such a book, we need a new instance of a class implementing the DbContext class, in our case ApplicationDbContext.

using (var context = new ApplicationDbContext())
{

}

Then, inside this using, we call the Add method on the context and DbSet Books and pass our previously generated object.

context.Books.Add(book);

We save changes to the database.

await context.SaveChangesAsync();

At this point, the record will actually be added to the database.

This is what the whole code will look like:

namespace Bookstore
{
    class Program
    {
        static async Task Main(string[] args)
        {
            var book = new Book { Name = "Książka 1", Price = 10 };

            using (var context = new ApplicationDbContext())
            {
                context.Books.Add(book);
                await context.SaveChangesAsync();

            }
        }
    }
})

Retrieving data from the database

var books = new List<Book>();

And then, right after saving the first book, we will download information about all books from the database. To do this, we will refer to the context again, then the Books DbSet and call the ToListAsync() method.

books = await context.Books.ToListAsync();

Here, all records from the Books table will first be retrieved and then assigned to the books variable. Below, we can best display these records on the console using a foreach loop.

foreach (var item in books)
{
    Console.WriteLine($"Id: {item.Id}. Książka: '{item.Name}' - {item.Price:0.00} PLN.");
}

Using string interpolation, we display first the id of a given book, then its name and price.

Now we will run the application and verify whether the data has actually been correctly added to the database.

As you can see, we have 1 record in the database. Everything seems to be working properly. All properties have been completed. If we run the applications again now:

This will also add another record. A unique ID was generated for each of them. Also, this is how adding and displaying records works. Whole code:

namespace Bookstore
{
    class Program
    {
        static async Task Main(string[] args)
        {
            var book = new Book { Name = "Książka 1", Price = 10 };
            var books = new List<Book>();

            using (var context = new ApplicationDbContext())
            {
                context.Books.Add(book);
                await context.SaveChangesAsync();

                books = await context.Books.ToListAsync();
            }

            foreach (var item in books)
            {
                Console.WriteLine($"Id: {item.Id}. Książka: '{item.Name}' - {item.Price:0.00} PLN.");
            }
        }
    }
}

Updating data in the database

var bookToUpdate = await context.Books.FindAsync(1);

Using the FindAsync method, we will retrieve the record with Id 1 from the database. Then we will change the value of this book to 99.

bookToUpdate.Price = 99;

And we will save the changes made to the database.

await context.SaveChangesAsync();

We can run the application and verify the changes.

As you can see, now the book with Id 1 has a value of PLN 99, so this record has been updated correctly. Similarly, we can download a book about Id 2 and update its name.

var bookToUpdate = await context.Books.FindAsync(2);
bookToUpdate.Name = "Ksiażka 2";

As you can see, the name has also been updated. This is what the whole code looks like:

namespace Bookstore
{
    class Program
    {
        static async Task Main(string[] args)
        {
            var books = new List<Book>();

            using (var context = new ApplicationDbContext())
            {
                var bookToUpdate = await context.Books.FindAsync(2);
                bookToUpdate.Name = "Ksiażka 2";

                await context.SaveChangesAsync();

                books = await context.Books.ToListAsync();
            }

            foreach (var item in books)
            {
                Console.WriteLine($"Id: {item.Id}. Książka: '{item.Name}' - {item.Price:0.00} PLN.");
            }
        }
    }
}

Deleting data from the database

context.Books.Remove(new Book { Id = 1 });

We then save these changes to the database.

await context.SaveChangesAsync();

I'm launching the application now.

Notice that there is no longer a book for Id 1 in the database. There is only a book for Id 2. Full code:

namespace Bookstore
{
    class Program
    {
        static async Task Main(string[] args)
        {
            var books = new List<Book>();

            using (var context = new ApplicationDbContext())
            {
                context.Books.Remove(new Book { Id = 1 });
                await context.SaveChangesAsync();

                books = await context.Books.ToListAsync();
            }

            foreach (var item in books)
            {
                Console.WriteLine($"Id: {item.Id}. Książka: '{item.Name}' - {item.Price:0.00} PLN.");
            }
        }
    }
}

Entity Framework Core School

SUMMARY