ORM⚓︎

ORM stands for Object-relational Mapping, which is a design pattern. It is a technique for converting data between a relational database and an object-oriented programming language.

Entity Framework (EF) is a ORM framework for .NET applications that provides us a way to map the objects in the application to database tables and perform CRUD operations without writing explicit SQL queries.

With the help of EF, we can use LINQ queries to fetch data from the underlying database.

EF provides higher abstraction, improved productivity, and flexibility by allowing developers to work with entities and LINQ instead of writing SQL manually.

In contrast, ADO.NET offers more control and better performance for low-level operations but requires more manual coding. EF is ideal for larger, more complex applications, while ADO.NET is better for performance-sensitive scenarios with simple queries.

• EF introduces a performance overhead compared to using raw ADO.NET or other lightweight data access methods. The ORM layer adds an extra layer of abstraction and introduces additional processing and mapping steps, which can impact performance, especially in complex or high-load scenarios.

• EF-generated queries may not always be optimized compared with hand-written SQL queries. In some cases, EF may generate inefficient SQL or execute multiple queries instead of utilizing more advanced techniques like joins or optimizations.

To improve performances of EF:

• Query Optimization: write efficient and optimized LINQ queries, such as Where() filtering and Select() projection.
• Disable Lazy Loading: use eager loading (Include) or explicit loading for better retrieval of related data to avoid N+1 problem.
• Database Indexing: adding appropriate indexes on the database tables
• Caching: Implement in-memory caching or distributed caching to store frequently accessed data.
• Using Compiled Queries: Use compiled queries to pre-compile LINQ queries and improve query execution time.

• Lazy loading: the related data is not retrieved from the database until it is actually needed.

  C#

  //Related data is loaded on demand, only when it is accessed in the code.
  var student = context.Students.FirstOrDefault(s => s.Id == 1);
  var courses = student.Courses; // This triggers the database query for Courses
  

• Eager loading: all of the related data is retrieved from the database in a single query.

  C#

  // Related data is loaded together with the primary entity using "Include()".
  var student = context.Students
                       .Include(s => s.Courses)
                       .FirstOrDefault(s => s.Id == 1);
  

The N+1 problem is a performance issue that can occur when using lazy loading in Entity Framework. It occurs when you have a collection of objects, and each object has a related entity that needs to be loaded lazily. If you access the related entity for each object in the collection, Entity Framework will execute N+1 database queries to retrieve the related data, where N is the number of objects in the collection. This can result in a significant performance penalty, as each database query involves overhead for network communication, query parsing, and result materialization.

You can disable lazy loading in EF:

• Set up DbContextOptionsBuilder.UseLazyLoadingProxies globally by configuring your DbContext with the following code:
  C#

  protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
  {
      optionsBuilder.UseLazyLoadingProxies(false);  // Disable lazy loading
  }
  

• Alternatively, you can directly disable lazy loading at the DbContext level:
  C#

  public MyDbContext(DbContextOptions<MyDbContext> options)
      : base(options)
  {
      this.ChangeTracker.LazyLoadingEnabled = false;  // Disable lazy loading for this context
  }
  

• Use of the virtual keyword. When using virtual keyword, EF creates proxy classes at runtime, which override the virtual navigation properties. When you access a navigation property for the first time, the proxy triggers a database query to load the related entity.
  C#

  public class Student
  {
      public int Id { get; set; }
      public string Name { get; set; }
  
      // Enabling lazy loading for the Courses navigation property
      public virtual ICollection<Course> Courses { get; set; }
  }
  

By carefully choosing when to enable or disable lazy loading, you can avoid performance pitfalls while leveraging EF’s flexibility.

• Code-first approach: EF creates database for you

  • Usage: When starting a new application and wanted to focus on the domain model and business logic first. In this case, the approach gives the flexibility to design and implement the data model from scratch.

• Database-first approach: provide an existing database built outside of EF.

  • Usage: When there were existing database constraints or stored procedures that are critical to the application’s functionality. It allows to use these existing database artifacts directly.

• DbContext - represents your database.
• DbSet - represents your tables. It is properties of DbContext.

By default, EF tracks the changes of the entities.

AsNoTracking() is a method in EF that tells the context to fetch not to track the changes of the entities. Even if you change contexts' properties, the context won’t detect or persist those changes. It is useful when you are querying data that you don't intend to update, which can help to improve the performance of your application when working with large amounts of data or read-only data.

When you call SaveChanges(), EF generates an UPDATE SQL to apply the context setting-ups to the database.

using (var context = new MyDbContext())
{
    // Example 1: Query with tracking (default behavior)
    var product = context.Products.FirstOrDefault(p => p.Id == 1);
    product.Name = "Updated Product"; // Changes are tracked.
    context.SaveChanges(); // EF generates an UPDATE SQL statement.

    // Example 2: Query with AsNoTracking()
    var readOnlyProduct = context.Products
        .AsNoTracking()
        .FirstOrDefault(p => p.Id == 2);
    // No tracking, so changing properties won’t be detected.
    readOnlyProduct.Name = "New Name"; 
    context.SaveChanges(); // No SQL generated because changes aren’t tracked.
}

• The Skip() and Take() methods are used for pagination or paging functionality in queries.
  • Skip() method is used to skip a specified number of elements in the query result.
  • Take() method is used to retrieve a specified number of elements in the query result.

var products = context.Products
    .OrderBy(p => p.Name)
    .Skip(10) // Skip the first 10 records
    .Take(5) // Take the next 5 records
    .ToList();

Dapper is a micro-ORM that provides a simple and fast way to query the database. It is a lightweight library that focuses on performance and simplicity. Dapper is ideal for scenarios where you need to execute raw SQL queries and map the results to objects without the overhead of a full-fledged ORM like EF Core.

• Performance: Dapper is generally faster than EF because it generates more efficient SQL and loads data faster. EF can be faster if you use it correctly and take advantage of features like compiled queries and efficient loading patterns.
• Ease of Use: EF has a higher learning curve than Dapper, but Dapper can be more convenient to use because it provides a lot of features out of the box, such as change tracking, lazy loading, and automated database migrations.
• Flexibility: Dapper can be more flexible than EF in turns of writing raw SQL and mapping it to your own objects. (Database-First Approach)
• DB requirement: Dapper follows Database-first workflow, which requires a database while EF does not require writing SQL statements. EF can be more flexible if you use it with the Code-first workflow, which allows you to define your model classes and let EF create the database for you.