Full Trust European Hosting

BLOG about Full Trust Hosting and Its Technology - Dedicated to European Windows Hosting Customer

European Entity Framework Hosting - HostForLIFE.eu :: Efficient Data Modification with Entity Framework Core

clock April 24, 2020 06:47 by author Peter

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 = [email protected]((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 = [email protected]((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 = [email protected]((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 = [email protected]((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.



European Entity Framework Hosting - HostForLIFE.eu :: Eager Loading In Repository Pattern Entity Framework Core

clock February 28, 2020 11:08 by author Peter

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.



European Entity Framework Hosting - HostForLIFE.eu :: What Is Entity Framework And How Entity Framework Core Is Different?

clock April 12, 2019 12:13 by author Peter

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.



European Entity Framework Hosting - HostForLIFE.eu :: Transactions In Entity Framework

clock July 11, 2018 11:11 by author Peter

In this blog, we will be talking about how transactions take place in Entity Framework. DbContext.Database.BeginTransaction() method creates a new transaction for the underlying database and allows us to commit or roll back changes made to the database using multiple SaveChanges method calls.

The following example demonstrates creating a new transaction object using BeginTransaction(), which is, then, used with multiple SaveChanges() calls.

using(var context = new SchoolContext()) { 
using(DbContextTransaction transaction = context.Database.BeginTransaction()) { 
    try { 
        var standard = context.Standards.Add(new Standard() { 
            StandardName = "1st Grade" 
        }); 
        context.Students.Add(new Student() { 
            FirstName = "Rama2", 
                StandardId = standard.StandardId 
        }); 
        context.SaveChanges(); 
        context.Courses.Add(new Course() { 
            CourseName = "Computer Science" 
        }); 
        context.SaveChanges(); 
        transaction.Commit(); //save the changes 
    } catch (Exception ex) { 
        transaction.Rollback(); //rollback the changes on exception 
        Console.WriteLine("Error occurred."); 
    } 

In the above example, we created new entities - Standard, Student, and Course and saved these to the database by calling two SaveChanges(), which execute INSERT commands within one transaction.

If an exception occurs, then the whole changes made to the database will be rolled back.

I hope it's helpful.



About HostForLIFE.eu

HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2016 Hosting, ASP.NET Core 2.2.1 Hosting, ASP.NET MVC 6 Hosting and SQL 2017 Hosting.


Tag cloud

Sign in