Step 1. Define Model
Create your entity models as usual to begin. These models should represent your entities and contain all of the necessary properties. Suppose, for instance, we have a "Product" entity:
public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public bool IsDeleted {get; set; }
}

Configure the Global Query Filter
Implement an override for the OnModelCreating method in your DbContext class. Using the Entity and HasQueryFilter methods, you can configure global query filters within this method. Here's an example of excluding soft-deleted products using a global query filter:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Product>().HasQueryFilter(p => !p.IsDeleted);
}

3. Execute Filtered Queries
Once the global query filter is configured, it will be automatically applied to any query involving the Product entity. For instance, soft-deleted products will be excluded by default when retrieving products.
activeProducts is equal to dbContext.Products.ToList();

Advantages of Global Query Filters in C#
    Data Integrity: Ensure that all queries involving the entity adhere to the desired filtering conditions, thereby fostering the consistency and integrity of the data.
    By using global query filters, you can centralize the filtering logic, making your code simpler and easier to maintain.
    Global query filters enable the exclusion of superfluous data at the database level, resulting in improved query performance and resource utilization.

Implementing global query filters in Entity Framework provides a potent tool for consistently applying filtering conditions to your entities. By configuring global query filters during the OnModelCreating method, you can ensure that the filters are applied automatically to all queries involving the specified entity, resulting in clearer code and enhanced data integrity. Test it out!

In my previous article Simple Way To Create Sqlite Database, I talked about the choice of the database and I focused on the simplest one which is Sqlite. But even if we choose the right one, we still need to work on the design and on its integration in the application side.

The old way of doing things was to use a database driver in your application, define the connexion and then execute the SQL command and queries from your code. When you get the data from the query execution, you need to parse them and then insert them to your objects. And this was a heavy task.

Later in time appeared Object-Relational Mapping. The ORM.

The ORM is the object-relational Mapping Tool. To make it simple, it's a tool that helps you to translate your database tables to a class Model in your application and vice versa. It also provides you with the methods to perform all the CRUD operation

The approach of creating the database then generating the class model is called Database First. The approach of creating the model in your application and then generating the database is called Code First.

In this article, we will focus on the database first part.

Entity Framework
Entity framework is the most commonly used ORM in .NET projects. It doesn't only allow you to access the database from your application. But also it also simplifies your life by giving you advanced possibilities like managing the concurrency and configuring your model. It has also the advantage of the use of the Language Integrated Query LINQ which is a powerful tool to perform your queries and filters.

Step 1: Nuget packages
    Create a new console project in visual studio.
    Open the commad line: Go to Tools => NuGet Package Manager => Package Manager Console and then you will get the command line displayed in your editor.

Install the required libraries by executing the following commands :

    PM>  Install-Package Microsoft.EntityFrameworkCore.Tools
    PM> install-Package Microsoft.EntityFrameworkCore.Sqlite
    Once you installed all packages you should have the following references in your solution

Step 2: Model generation

    To generate the model, you need to use also the command line
    You need to copy the needed database to your project folder and choose the right mode of copy to output directory:

Now you can generate your classes using the following command. You need to specify the output path, in our case it will be the folder Models
Scaffold-DbContext "Data Source=.\Database\products.db" Microsoft.EntityFrameworkCore.Sqlite -OutputDir Models
if everything is ok, you should get the classes generated under the folder Models and the following message in the package manager console should appear

The warning is there because the generator hardcoded the database's connection string in the code. You need to consider moving it in a protected way to a configuration file. But let's ignore it because it's not the main topic of this article.

Step 3: Check your generated classes
Now you should have the Context that contains the model creation and for each table you will get a class. In our case we have only one entity.

The Context class,
using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;
namespace EntityFrameworkDbFirst.Models;
public partial class ProductsContext: DbContext {
    public ProductsContext() {}
    public ProductsContext(DbContextOptions < ProductsContext > options): base(options) {}
    public virtual DbSet < Item > Items {
        get;
        set;
    }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    #warning To protect potentially sensitive information in your connection string, you should move it out of source code.You can avoid scaffolding the connection string by using the Name = syntax to read it from configuration - see https: //go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
        => optionsBuilder.UseSqlite("Data Source=.\\Database\\products.db");
    protected override void OnModelCreating(ModelBuilder modelBuilder) {
        modelBuilder.Entity < Item > (entity => {
            entity.HasKey(e => e.Barcode);
            entity.Property(e => e.Barcode).HasColumnType("VARCHAR");
            entity.Property(e => e.Brand).HasColumnType("VARCHAR");
            entity.Property(e => e.Name).HasColumnType("VARCHAR");
            entity.Property(e => e.PruchasePrice).HasColumnType("DOUBLE");
            entity.Property(e => e.SellingPrice).HasColumnType("DOUBLE");
        });
        OnModelCreatingPartial(modelBuilder);
    }
    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

The class Item,
using System;
using System.Collections.Generic;
namespace EntityFrameworkDbFirst.Models;
public partial class Item {
    public string Barcode {
        get;
        set;
    } = null!;
    public string ? Name {
        get;
        set;
    }
    public string ? Brand {
        get;
        set;
    }
    public double ? PruchasePrice {
        get;
        set;
    }
    public double ? SellingPrice {
        get;
        set;
    }
}

Step 4: Write your cruds
Once you have created your model, you can now start performing operations on it. The following code in program.cs allows to add / update or delete an item.
You can test it and then see the results in the database directly.

It's a simple call to every method of the crud. You can play with it by changing the data and adding more controls.
For example if you call the AddItem twice without modifying it you will get an exception related to the constraint of the primary key
// See https://aka.ms/new-console-template for more information
using EntityFrameworkDbFirst.Models;
using System.Runtime.CompilerServices;
using static System.Net.Mime.MediaTypeNames;
using System.Xml.Linq;
try {
    Item item = new Item() {
        Barcode = "B12345678",
            Brand = "Milky Way",
            Name = "Milk",
            PruchasePrice = 20.5,
            SellingPrice = 25.5
    };
    // Uncomment this line if you want to test the delete method
    //DeleteItem("B12345678");
    AddItem(item);
    ////Uncomment this line if you want to test the update method
    //item.SellingPrice = 30;
    //UpdateItem(item);
    Console.WriteLine("Everything is Ok !");
} catch (Exception e) {
    Console.WriteLine(e.Message);
    if (e.InnerException != null) Console.WriteLine(e.InnerException.Message);
}
void AddItem(Item item) {
    using(var db = new ProductsContext()) {
        db.Items.Add(item);
        db.SaveChanges();
    }
}
//Delete the item
void DeleteItem(string Barcode) {
    using(var db = new ProductsContext()) {
        var item = db.Items.Find(Barcode);
        if (item == null) return;
        db.Items.Remove(item);
        db.SaveChanges();
    }
}
//Update the price of the item
void UpdateItem(Item item) {
    using(var db = new ProductsContext()) {
        db.Items.Update(item);
        db.SaveChanges();
    }
}

Step 4: And finally check your database
If you remember, we have set the property of the database in the solution explorer to "Copy if newer" so the file products.db is copied to the binary folder.
Open the database and browse your file then you will see the inserted item. Ensure that you left at least one product after testing the add and the delete

We were developing a database project in .NET 6 using Entity Framework Core. Our back-end database engine was MySql rather than SQL server. It was a remote database. Short operations like single read and write worked fine but suddenly I stuck somewhere and got this horrible error.

The error was
“A second operation was started on this context instance before a previous operation was completed. This is usually caused by different threads concurrently using the same instance of DbContext.”

I started the operation again and surprisingly, this time the error was different regardless of the fact that all the inputs I provided were the same.

The error I got this time is:
The configured execution strategy 'MySqlRetryingExecutionStrategy' does not support user-initiated transactions. Use the execution strategy returned by 'DbContext.Database.CreateExecutionStrategy()' to execute all the operations in the transaction as a retriable unit.' ”

Please note that if you are using SQL Server as a database engine, You’ll receive SqlRetryingExecutionStrategy rather than MySqlRetryingExecutionStrategy.

I tested the application many times and explored the stack trace in detail and also do a lot of search online but failed to figure out the issue as my app followed all the suggested solution standards.

I started following a thread on github where one important thing was mentioned.

One has to be careful while working with sync and async method. If you are following sync method patterns in your code, better to follow it completely, mean use sync method in all of your codebase. Don’t mix sync calls with async calls in your code.

If you working with async methods in your code it is highly recommended to use the async method with await in all the places in your codebase.

This last line give me some hint to find out the issue so started exploring the code backward. From the position where error or exception was coming while going backward from line to line, there was an async function which has no await before its invocation.

As soon as I applied this await prior to this call, both errors are gone.

A simple rule says that for optimal performance, we need to make as few database requests as possible. This is especially relevant for insert and update scenarios, where we sometimes need to work with thousands of objects. Sending those objects to the database one by one is usually significantly slower than in a batch. With tools like Entity LINQ we can easily write efficient data modification queries.

Efficient Data Modification With Entity Framework Core 

Let's analyze the possible cases:
    INSERT
    UPDATE (DELETE)
    Upsert (UPDATE or INSERT)

INSERT case optimizations
Insert multiple rows in a single statement. Assuming we have a collection of promotions, the following snippet inserts them in a single query:
    public void InsertBatch(IEnumerable<Promotions> promos) 
    { 
        var query = DbContext.Promotions.Query((Promotions promo) => 
        { 
            var set = promo.@using((promo.PromotionName, promo.Discount, promo.StartDate, promo.ExpiredDate)); 
     
            INSERT().INTO(set); 
            var r = OUTPUT(INSERTED<Promotions>()); 
            VALUES(set.RowsFrom(promos)); 
     
            return r; 
        }); 
     
        foreach (var promo in query) 
            Console.WriteLine((promo.PromotionId, promo.PromotionName, promo.Discount, promo.StartDate, promo.ExpiredDate)); 
    } 

INSERT INTO ... SELECT ...
 
This is a so-called bulk insert. In the case that the data is already in the database, it is much cheaper to avoid data pulling altogether. The operation can potentially be performed inside the database. The following snippet copies addresses from one table to another according to some criteria without a single byte to leave the database.
    var cities = new[] { "Santa Cruz", "Baldwin" }; 
     
    DbContext.Database.Query((Stores stores, Addresses address) => 
    { 
        var set = address.@using((address.Street, address.City, address.State, address.ZipCode)); 
     
        INSERT().INTO(set); 
        SELECT((stores.Street, stores.City, stores.State, stores.ZipCode)); 
        FROM(stores); 
        WHERE(cities.Contains(stores.City)); 
    }); 

UPDATE case optimizations
A bulk update works when there is a need to update multiple rows in the same table. There is a special SQL construct for this case - UPDATE ... WHERE, which performs the update in a single query. Some databases, like SQL server, also support a more powerful UPDATE ... JOIN construct. Below we update all the tax configurations without pulling them to the application server:
    var one = 0.01M; 
    var two = 0.02M; 
    DbContext.Database.Query((Taxes taxes) => 
    { 
        UPDATE(taxes).SET(() => { 
                taxes.MaxLocalTaxRate += two; 
                taxes.AvgLocalTaxRate += one; 
            }); 
        WHERE(taxes.MaxLocalTaxRate == one); 
    }); 

Bulk delete, same idea for the delete case.

    private static void PrepareProductHistory(Products products) 
    { 
        var productHistory = ToTable<Products>(PRODUCT_HISTORY); 
     
        SELECT(products).INTO(productHistory); 
        FROM(products); 
     
        Semicolon(); 
    } 
     
    ... 
     
    var year = 2017; 
    DbContext.Database.Query((Products products) => 
    { 
        PrepareProductHistory(products); 
        var productHistory = ToTable<Products>(PRODUCT_HISTORY); 
     
        DELETE().FROM(productHistory); 
        WHERE(productHistory.ModelYear == year); 
    }); 

UPSERT optimization
Efficient Data Modification With Entity Framework Core
 
Upsert means UPDATE or INSERT in a single statement. In cases when a table has more than 1 unique constraint (in addition to PK), plain INSERT can fail on duplicate key. In those cases, we usually want to ignore, replace or combine some existing fields with new values. Most vendors support this capability, but using different syntax and providing different features.
 
MERGE
SQL Server and Oracle. In fact, this is the official standard. In its simplest form, it allows for the specification of what to do WHEN MATCHED, i.e. when there is a unique key collision, and what to do WHEN NOT MATCHED, i.e. we can INSERT safely.

    DbContext.Category.Query((Category category) =>   
    {   
        var staging = ToTable<Category>(CATEGORY_STAGING);   
       
        MERGE().INTO(category).USING(staging).ON(category == staging);   
       
        WHEN_MATCHED().THEN(MERGE_UPDATE().SET(() =>   
                {   
                    category.CategoryName = staging.CategoryName;   
                    category.Amount = staging.Amount;   
                }));   
       
        var set = category.@using((category.CategoryId, category.CategoryName, category.Amount));   
        WHEN_NOT_MATCHED().THEN(MERGE_INSERT(set.ColumnNames(), VALUES(set.RowFrom(staging))));   
       
        Semicolon();   
       
        return SelectAll(category);   
    });    

As the picture says, it's trickier than it should be. There are many tutorials and official documentation.
 
INSERT ... ON DUPLICATE ... - MySQL and Postgres. The syntax is much simpler and allows us to handle the most common case only (compared to feature-packed MERGE):

    // There is a store which might already exist in the database. 
    // Should we add it or update? (PK is not always the only UNIQUE KEY) 
    newOrExisting.LastUpdate = DateTime.Now; 
     
    DbContext.Database.Query((Store store) => 
    { 
        var view = store.@using((store.StoreId, store.AddressId, store.ManagerStaffId, store.LastUpdate)); 
        INSERT().INTO(view); 
        VALUES(view.RowFrom(newOrExisting)); 
        ON_DUPLICATE_KEY_UPDATE(() => store.LastUpdate = INSERTED_VALUES(store.LastUpdate)); 
    }); 

Batch/bulk updates are usually in the order of a magnitude faster than working with entities one by one. Optimizing those scenarios is usually an easy improvement.

Eager loading is the process whereby a query for one type of entity also loads related entities as part of the query, so that we don't need to execute a separate query for related entities.

In simple language, Eager loading joins the entities which have foreign relation and returns the data in a single query.

Now, the question is how to properly handle the eager-loading problem for complex object graphs within the Repository pattern.

Let's get started.

Step 1:  Add a method into your Interface which eager loads the entities which we specify:
  public interface IProjectInterface<T> where T : class 
  { 
  Task<IEnumerable<T>> EntityWithEagerLoad(Expression<Func<T, bool>> filter,                string[] children); 
  Task<List<T>> GetModel(); 
  T GetModelById(int modelId); 
  Task<bool> InsertModel(T model); 
  Task<bool> UpdateModel(T model); 
  Task<bool> DeleteModel(int modelId); 
  void Save(); 
  }

The method EntityWithEagerLoad() takes 2 arguments, one is filter criteria and another is an array of entities which we want to eager load.

Step 2: Define the method EntityWithEagerLoad in your base repository:
  public async Task<IEnumerable<T>> EntityWithEagerLoad(Expression<Func<T, bool>> filter, string[] children) 
  { 
              try 
              { 
                  IQueryable<T> query = dbEntity; 
                  foreach (string entity in children) 
                  { 
                      query = query.Include(entity); 
   
                  } 
                  return await query.Where(filter).ToListAsync(); 
              } 
              catch(Exception e) 
              { 
                  throw e; 
              }

Step 3 : For using this method , call this function from your controller as shown  below:
  [HttpGet] 
         [Route("api/Employee/getDetails/{id}")] 
         public async Task<IEnumerable<string>> GetDetails([FromRoute]int id) 
         { 
             try 
             { 
        var children = new string[] { "Address", “Education” }; 
   
        var employeeDetails=await _repository.EntityWithEagerLoad (d => d.employeeId==id, 
        children); 
   
             } 
             catch(Exception e) 
             { 
                 Throw e;             
             }

The variable employeeDetails contains employee details with their Address and education details.

Entity Framework (EF) is Object Relational Mapper(ORM) for .NET. In simple words, it is a collection of libraries that connect the objects in code with the schema. EF is a bit different than other ORMs. It has a mapping layer between domain classes and schema. It is Microsoft’s recommended data access technology. Of course, data access can be achieved by writing your own DAL (Data Access Layer) using ADO.NET or by using third-party mappers like Dapper but EF is a persistent and open source framework supported by Microsoft. It is possible to perform full CRUD (Create, Read, Update, Delete) operations. EF can help in the consistency of task, developer's productivity, and LINQ syntax helps to use any RDBMS (regardless of SQL writing style ie: Oracle or SQL Server). Actually, the purpose of EF is to let developers focus on the domain, not on the database.

There are mainly three workflows for Entity Framework.

Database-first approach
In this workflow, a database is created first with all tables and related objects (i.e, stored procedures). Entity Framework creates domain classes using the Entity Data Model Wizard. In this approach, most of the efforts are focused on designing database structures. It is a traditional approach which many developers are doing for years.
 
This approach suites when different teams like DBA design the database and programmers are supposed to integrate the database with an application or when requirements/goal are not clear and changes in the database are incremental. Or, if you have already one long-lasting stable database and you have a scenario to use an existing database – well, it is still possible to convert an existing database to code-first approach (reverse engineering is challenging in some scenarios but it is doable).
 
Advantages

• You can start initial development soon.
• Existing databases can be utilized easily
• If there are requirements like to have stored procedures, triggers, and table columns in a certain order (this feature is added in EF 2.1 now) then this workflow has an advantage over code first
• If you need GUI for designing. For example: In SQL Server you can generate database diagrams and create or update objects from there.

Disadvantages

• The sync of changes is not easy. For example, you make changes in schema or table which you need to sync on your different working environments. You would need an external tool or do it manually.
• If you are interested in version control like Git or SVN, then you cannot do it with database first. EDMX has no history of changes.
• Sometimes, changes in structures of the database are complicated to update EDMX
• Classes generated in this approach are auto-generated

Code-first approach
In this approach, we first create domain classes and then EF generates database tables. It is possible to create POCO classes (business objects) and you have complete control of it. You can write classes with properties which generate tables with columns using Migration process. Along with migration, the History table is also generated which can give you a version control feature.
 
This is a good approach if you are the programmer and you are the one who designs the database as well. This approach is useful if changes in the database are more, the application is scalable and needs tracking as well.
 
Advantages
The database sync is easy for environments using migration. It is a really needed feature which makes it possible to upgrade or downgrade any change/commit.
You get control on the code so you can validate fields from classes as well

Disadvantages
No GUI so you need programming experience
We do not have real full control on the database, of course, EF is releasing a new version with many new features but let's say: It is not possible to create or remove trigger or stored procedures from EF until we use SQL in Context class.

Model-first approach
In simple words, this approach is based on the GUI. All you need is to create entities and relationships on EDMX design surface so it is like a UML diagram. From the Entity Data Model wizard, you can choose the Designer model and create your entities all in GUI. When you are done with your design, you can choose “Generate Database from model” to auto-generate domain classes and database. This approach can be useful if a data structure is big and you need very little control on the database. For example, if you need triggers or stored procedures or custom business logic on fields of entities, then you should consider either database first or code first.
 
Advantages
The visual design interface can help to create entities easily in a short time
Programming experience not needed

Disadvantages
Auto-generated code is a limitation here
Little control on database
Sometimes, EMDX auto-generated scripts still need modification which needs good SQL level of expertise to get a workaround.

How Entity Framework Core is different than Entity Framework?
Entity Framework Core(EF Core) is lightweight (collection of composable API), cross-platform (Linux, Windows, UWP) and extensible (with modern software practices). EF Core works with .NET Core but with .NET Core, it is recommended to use EF Core. Of course, all the features are not released yet and there are many features which are missing. The EDMX/Designer is missing in EF Core so model first is not possible with EF Core without using third party tools. EF Core supports both, database-first and code-first, approaches. Database first with EF Core is like; it reverse engineers the existing database which later can be used as code first. So, if you want to say strictly then database first is not fully supported. EF Core supports not only RDBMS(SQL Server, Oracle, etc.) but also non-relational stores and in-memory databases which can be used for unit testing.
 
Conclusion
In my opinion about workflows, there is no good or bad approach but our specific requirement helps to decide which one to choose. But, whatever flow you choose - never mix different workflows in one project.