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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
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!