C# – EF – Beginning with Entity Framework

What is Entity Framework? If we take a look at Wikipedia, it says Entity Framework (EF) is an open source object-relational mapping (ORM) framework for ADO.NET.

What does it mean? Pretty much, magic. 🙂 You make some classes in C# and the EF maps those classes to a table. Furthermore, you may query those classes and get results. Without using SQL, the EF writes the SQL for you. That’s it!

I have decided to build up a small example of EF, showing how the “magic” works. Let’s imagine that we have to build a table with three products in it. Something like this:

The products have Id, Name, Features, Description and Price.

Thus, in C# you make a class called Products.cs and you add the following in it:

namespace Demo
{
    public class Product
    {
        public Product(string name, string features, string desciption, decimal price)
        {
            this.Name = name;
            this.Features = features;
            this.Description = desciption;
            this.Price = price;
        }

        public int Id { get; set; }
        public string Name{ get; set; }
        public string Features{ get; set; }
        public string Description{ get; set; }
        public decimal Price { get; set; }
    }
}

That’s all – one constructor and 5 fields.

Then, in the main method, using the constructor write the following:

namespace Demo
{
    class Startup
    {
        static void Main()
        {
            Product Laptop1 = new Product("Lenovo Ultra", "white laptop", "my best one", 1000);
            Product Laptop2 = new Product("Lenovo Cheap", "blach laptop", "my cheapest one", 200);
            Product Laptop3 = new Product("Dell Old", "black old laptop", "my first laptop", 700);

            ProductsContext context = new ProductsContext();
     
            context.Products.Add(Laptop1);
            context.Products.Add(Laptop2);
            context.Products.Add(Laptop3);

            context.SaveChanges();

        }
    }
}

After all is said and done, you need to make the connection to the database. Press Ctrl+Shift+A and select ADO.Net Entity Data Model. 

Then put a meaningful name and select next

You will get a new class with the name you have selected. Mine is looking like this, after a bit of customization:

namespace Demo
{
    using System.Data.Entity;

    public class ProductsContext : DbContext
    {
        public ProductsContext(): base("name=ProductsContext")
        {
        }

        public virtual DbSet Products { get; set; }
    }
}

With this one, you may run your code to get the new data in the database. This is what I got after pressing F5 a few times:

Yup. Not very meaningful, but exactly what I have expected.

How to make queries through C# in the Database?

If we want to make a simple SQL query in the database, here is a really simple and understandable solution. Make a connection, make a string for the query and execute the query with the SqlCommand class. Something like this:

using System;
using System.Data.SqlClient;

namespace Demo
{
    class Startup
    {
        static void Main()
        {         
            SqlConnection connection = new SqlConnection(@"
                    Data Source=(localdb)\MSSQLLocalDB;
                    Database=Demo.ProductsContext;
                    Integrated Security=true");
            ListAllProjects(connection);
        }

        static void ListAllProjects(SqlConnection connection)
        {
            connection.Open();

            using (connection)
            {
                string query = @"SELECT Id, Name, Price FROM Products WHERE Price > 500";
                SqlCommand cmd = new SqlCommand(query, connection);
                var reader = cmd.ExecuteReader();

                Console.WriteLine(" ID | Name        |Price    ");
                Console.WriteLine("----+-------------+---------");

                while (reader.Read())
                {
                    Console.WriteLine($"{reader[0],4}|{reader[1],-13}|{reader[2],-10}");
                }
            }
        }
    }
}

The result would be a well formatted table of Lenovo Ultra and Dell Old, as far as they are the only laptops I had with a price above 500.

Yup. It looks nice and it is nice, when it works! 🙂

Cheers!