Entity Framework

Entity Framework is an object-relational mapper (O/RM) that enables .NET developers to work with a database using .NET objects. It eliminates the need for most of the data-access code that developers usually need to write.
It automate all these database related activities for your application.

Entity Framework is an open-source ORM framework for .NET applications supported by Microsoft. It enables developers to work with data using objects of domain specific classes without focusing on the underlying database tables and columns where this data is stored.

Entity Framework Features

• Cross-platform: EF Core is a cross-platform framework which can run on Windows, Linux and Mac.
• Modelling: EF (Entity Framework) creates an EDM (Entity Data Model) based on POCO (Plain Old CLR Object) entities with get/set properties of different data types. It uses this model when querying or saving entity data to the underlying database.
• Querying: EF allows us to use LINQ queries (C#/VB.NET) to retrieve data from the underlying database. The database provider will translate this LINQ queries to the database-specific query language (e.g. SQL for a relational database). EF also allows us to execute raw SQL queries directly to the database.
• Change Tracking: EF keeps track of changes occurred to instances of your entities (Property values) which need to be submitted to the database.
• Saving: EF executes INSERT, UPDATE, and DELETE commands to the database based on the changes occurred to your entities when you call the SaveChanges()method. EF also provides the asynchronous SaveChangesAsync() method.
• Concurrency: EF uses Optimistic Concurrency by default to protect overwriting changes made by another user since data was fetched from the database.
• Transactions: EF performs automatic transaction management while querying or saving data. It also provides options to customize transaction management.
• Caching: EF includes first level of caching out of the box. So, repeated querying will return data from the cache instead of hitting the database.
• Built-in Conventions: EF follows conventions over the configuration programming pattern, and includes a set of default rules which automatically configure the EF model.
• Configurations: EF allows us to configure the EF model by using data annotation attributes or Fluent API to override default conventions.
• Migrations: EF provides a set of migration commands that can be executed on the NuGet Package Manager Console or the Command Line Interface to create or manage underlying database Schema.

Entity Framework Latest Versions:EF 6 and EF Core.

https://www.entityframeworktutorial.net/basics/how-entity-framework-works.aspx
Fluent API:

• Fluent API is another way to configure your domain classes.
• The Code First Fluent API is most commonly accessed by overriding the OnModelCreating method on your derived DbContext.

•  //Configure default schema
     modelBuilder.HasDefaultSchema("Admin");
  
     //Map entity to table
     modelBuilder.Entity<Student>().ToTable("StudentData");

also configure the primary key(haskey),foreign key()

 [Key, ForeignKey("Student")]

//Configure default schema
   modelBuilder.HasDefaultSchema("Admin");

   // Configure ID as PK for StudentLogIn
   modelBuilder.Entity<StudentLogIn>()
   .HasKey(s ⇒ s.ID);

   // Configure ID as FK for StudentLogIn
   modelBuilder.Entity<Student>()
   
   .HasOptional(s ⇒ s.StudentLogIn) //StudentLogIn is optional
   .WithRequired(t ⇒ t.Student); // Create inverse relationship

Entity Framework coding steps:

1. create model

2. create dbcontext and inherit DbContext

3. create configuration for connection string

4. SaveChanges() method for insert/update

How Entity Framework Works?

Entity Framework Architecture :

Entity Data Model

The very first task of EF API is to build an Entity Data Model (EDM). EDM is an in-memory representation of the entire metadata: conceptual model, storage model, and mapping between them.

Conceptual Model: EF builds the conceptual model from your domain classes, context class, default conventions followed in your domain classes, and configurations.

Storage Model: EF builds the storage model for the underlying database schema. In the code-first approach, this will be inferred from the conceptual model. In the database-first approach, this will be inferred from the targeted database.

Mappings: EF includes mapping information on how the conceptual model maps to the database schema (storage model).

EF performs CRUD operations using this EDM. It uses EDM in building SQL queries from LINQ queries, building INSERT, UPDATE, and DELETE commands, and transform database result into entity objects.

LINQ to Entities: LINQ-to-Entities (L2E) is a query language used to write queries against the object model. It returns entities, which are defined in the conceptual model. You can use your LINQ skills here.

Entity SQL: Entity SQL is another query language (For EF 6 only) just like LINQ to Entities. However, it is a little more difficult than L2E and the developer will have to learn it separately.

Object Service: Object service is a main entry point for accessing data from the database and returning it back. Object service is responsible for materialization, which is the process of converting data returned from an entity client data provider (next layer) to an entity object structure.

Entity Client Data Provider: The main responsibility of this layer is to convert LINQ-to-Entities or Entity SQL queries into a SQL query which is understood by the underlying database. It communicates with the ADO.Net data provider which in turn sends or retrieves data from the database.

ADO.Net Data Provider: This layer communicates with the database using standard ADO.Net.

DbContext Class:  system.data.entity.dbcontext

The context class is used to query or save data to the database. It is also used to configure domain classes, database related mappings, change tracking settings, caching, transaction etc.

Database Initialization WorkFlow in Entity Framework 6

As per the above figure, the base constructor of the context class can have the following parameter.

1. No Parameter
2. Database Name
3. Connection String Name

No Parameter

If you do not specify the parameter in the base constructor of the context class then it creates a database in your local SQLEXPRESS server with a name that matches your {Namespace}.{Context class name}.

public class Context: DbContext     {

        public Context(): base()        {            

        }    }

Database Name

You can also specify the database name as a parameter in a base constructor of the context class. If you specify a database name parameter, then Code First creates a database with the name you specified in the base constructor in the local SQLEXPRESS database server.

public class Context: DbContext     {

        public Context(): base("MySchoolDB")    

     {                           }    }

ConnectionString Name

You can also define the connection string in app.config or web.config and specify the connection string name starting with "name=" in the base constructor of the context class.

public class Context: DbContext     {

        public SchoolDBContext() : base("name=SchoolDBConnectionString")               {        }    }

App.config
<configuration>
    <connectionStrings>
    <add name="SchoolDBConnectionString" 
    connectionString="Data Source=.;Initial Catalog=SchoolDB-ByConnectionString;
     Integrated Security=true"  providerName="System.Data.SqlClient"/>   
    </connectionStrings>

</configuration>

Types of Entities:

There are two types of Entities in Entity Framework: POCO Entities and Dynamic Proxy Entities.

A POCO entity is a class that doesn't depend on any framework-specific base class. It is like any other normal .NET CLR class, which is why it is called "Plain Old CLR Objects". POCO entities are supported in both EF 6 and EF Core.

Dynamic Proxy Entities (POCO Proxy)

Dynamic Proxy is a runtime proxy class which wraps POCO entity. Dynamic proxy entities allow lazy loading.

public virtual StudentAddress StudentAddress { get; set; }

Entity States:

Refrences:https://www.entityframeworktutorial.net/basics/entity-states.aspx

Development Approaches with Entity Framework:

There are three different approaches you can use while developing your application using Entity Framework:

1. Database-First
2. Code-First
3. Model-First

Database-First Approach

In the database-first development approach, you generate the context and entities for the existing database using EDM wizard integrated in Visual Studio or executing EF commands.

Code-First Approach

Use this approach when you do not have an existing database for your application. In the code-first approach, you start writing your entities (domain classes) and context class first and then create the database from these classes using migration commands.

Model-First Approach

In the model-first approach, you create entities, relationships, and inheritance hierarchies directly on the visual designer integrated in Visual Studio and then generate entities, the context class, and the database script from your visual model.

DbSet in Entity Framework 6

The DbSet class represents an entity set that can be used for create, read, update, and delete operations.

The following table lists important methods of the DbSet class:

Method Name	Return Type	Description
Add	Added entity type	Adds the given entity to the context with the Added state. When the changes are saved, the entities in the Added states are inserted into the database. After the changes are saved, the object state changes to Unchanged. Example: dbcontext.Students.Add(studentEntity)
AsNoTracking<Entity>	DBQuery<Entity>	Returns a new query where the entities returned will not be cached in the DbContext. (Inherited from DbQuery.) Entities returned as AsNoTracking will not be tracked by DBContext. This will be a significant performance boost for read-only entities.  Example: var studentList = dbcontext.Students.AsNoTracking<Student>().ToList<Student>();
Attach(Entity)	Entity which was passed as parameter	Attaches the given entity to the context in the Unchanged state Example: dbcontext.Students.Attach(studentEntity);
Create	Entity	Creates a new instance of an entity for the type of this set. This instance is not added or attached to the set. The instance returned will be a proxy if the underlying context is configured to create proxies and the entity type meets the requirements for creating a proxy. Example: var newStudentEntity = dbcontext.Students.Create();
Find(int)	Entity type	Uses the primary key value to find an entity tracked by the context. If the entity is not in the context, then a query will be executed and evaluated against the data in the data source, and null is returned if the entity is not found in the context or in the data source. Note that the Find also returns entities that have been added to the context but have not yet been saved to the database. Example: Student studEntity = dbcontext.Students.Find(1);
Include	DBQuery	Returns the included non-generic LINQ to Entities query against a DbContext. (Inherited from DbQuery) Example: var studentList = dbcontext.Students.Include("StudentAddress").ToList<Student>(); var studentList = dbcontext.Students.Include(s => s.StudentAddress).ToList<Student>();
Remove	Removed entity	Marks the given entity as Deleted. When the changes are saved, the entity is deleted from the database. The entity must exist in the context in some other state before this method is called. Example: dbcontext.Students.Remove(studentEntity);
SqlQuery	DBSqlQuery	Creates a raw SQL query that will return entities in this set. By default, the entities returned are tracked by the context; this can be changed by calling AsNoTracking on theDbSqlQuery<TEntity> returned from this method. Example: var studentEntity = dbcontext.Students.SqlQuery("select * from student where studentid = 1").FirstOrDefault<Student>()

Refrences:https://www.entityframeworktutorial.net/crud-operation-in-connected-scenario-entity-framework.aspx

Saving Data:

Insert:

using (var context = new SchoolDBEntities())
{
    var std = new Student()
    {
        FirstName = "Bill",
        LastName = "Gates"
    };
    context.Students.Add(std);

    context.SaveChanges();
}

Update:

using (var context = new SchoolDBEntities())
{
    var std = context.Students.First<Student>(); 
    std.FirstName = "Steve";
    context.SaveChanges();
}

Delete:

using (var context = new SchoolDBEntities())
{
    var std = context.Students.First<Student>();
    context.Students.Remove(std);

    context.SaveChanges();
}

Querying in Entity Framework:

1.LINQ-to-Entities :

Language-Integrated Query (LINQ) is a powerful query language introduced in Visual Studio 2008. As the name suggests, LINQ-to-Entities queries operate on the entity set (DbSet type properties) to access the data from the underlying database. You can use the LINQ method syntax or query syntax when querying with EDM.

var query = context.Students

                       .where(s => s.StudentName == "Bill")

                       .FirstOrDefault<Student>();

2. Entity SQL

Entity SQL is another way to create a query. It is processed by the Entity Framework's Object Services directly. It returns ObjectQuery instead of IQueryable.

//Querying with Object Services and Entity SQL

string sqlString = "SELECT VALUE st FROM SchoolDBEntities.Students " +

                    "AS st WHERE st.StudentName == 'Bill'";    

var objctx = (ctx as IObjectContextAdapter).ObjectContext;                

ObjectQuery<Student> student = objctx.CreateQuery<Student>(sqlString);

Student newStudent = student.First<Student>();

3 .Native SQL

You can execute native SQL queries for a relational database, as shown below:

 var studentName = ctx.Students.SqlQuery("Select studentid, studentname, standardId from Student where studentname='Bill'").FirstOrDefault<Student>();

Disable Lazy loading :

this.Configuration.LazyLoadingEnabled = false;

Eager Loading in Entity Framework

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. Eager loading is achieved using the Include() method.

EF Core supports additional method IncludeThen for eager loading. 

using (var context = new SchoolDBEntities())
{
    var stud1 = (from s in context.Students.Include("Standard")
                where s.StudentName == "Bill"
                select s).FirstOrDefault<Student>();
}

Lazy Loading in Entity Framework:

Even with lazy loading disabled (in EF 6), it is still possible to lazily load related entities, but it must be done with an explicit call. Use the Load() method to load related entities explicitly. Consider the following example.

using (var context = new SchoolContext())
{
    var student = context.Students
                        .Where(s => s.FirstName == "Bill")
                        .FirstOrDefault<Student>();

    context.Entry(student).Reference(s => s.StudentAddress).Load(); // loads StudentAddress
    context.Entry(student).Collection(s => s.StudentCourses).Load(); // loads Courses collection 
}     

Execute Raw SQL Queries in Entity Framework 6

Entity Framework allows you to execute raw SQL queries for the underlying relational database.

The following methods can be used to execute raw SQL queries to the database using Entity Framework 6.x:

• DbSet.SqlQuery()
• DbContext.Database.SqlQuery()
• DbContext.Database.ExecuteSqlCommand()

DbSet.SqlQuery(): required entity

using (var ctx = new SchoolDBEntities())
{
    var studentList = ctx.Students
                        .SqlQuery("Select * from Students")
                        .ToList<Student>();
}

Database.SqlQuery(): return value any type

using (var ctx = new SchoolDBEntities())
{
    //Get student name of string type
    string studentName = ctx.Database.SqlQuery<string>("Select studentname from Student where studentid=1")
                            .FirstOrDefault();

    //or
    string studentName = ctx.Database.SqlQuery<string>("Select studentname from Student where studentid=@id", new SqlParameter("@id", 1))
                            .FirstOrDefault();
}

Database.ExecuteSqlCommand()

The Database.ExecuteSqlCommnad() method is useful in executing database commands, such as the Insert, Update and Delete command.

using (var ctx = new SchoolDBEntities())
{
    int noOfRowUpdated = ctx.Database.ExecuteSqlCommand("Update student 
            set studentname ='changed student by command' where studentid=1");

    int noOfRowInserted = ctx.Database.ExecuteSqlCommand("insert into student(studentname) 
            values('New Student')");

    int noOfRowDeleted = ctx.Database.ExecuteSqlCommand("delete from student 
            where studentid=1");
}

Eager loading vs lazy loading,
With eager loading, all the data is retrieved in a single query, which can then be cached to improve the application performance. With eager loading we are trading memory consumption for database round trips. With lazy loading, we only retrieve just the amount of data that we need in a single query.

Eager Loading: Eager Loading helps you to load all your needed entities at once. i.e. related objects (child objects) are loaded automatically with its parent object.

When to use:

1. Use Eager Loading when the relations are not too much. Thus, Eager Loading is a good practice to reduce further queries on the Server.
2. Use Eager Loading when you are sure that you will be using related entities with the main entity everywhere.

Lazy Loading: In case of lazy loading, related objects (child objects) are not loaded automatically with its parent object until they are requested. By default LINQ supports lazy loading.

When to use:

1. Use Lazy Loading when you are using one-to-many collections.
2. Use Lazy Loading when you are sure that you are not using related entities instantly.

Stored Procedure in Entity Framework:

using (var context = new SchoolDBEntities())
{
    var courses = context.GetCoursesByStudentId(1); //after include sp in edm

    foreach (Course cs in courses)
        Console.WriteLine(cs.CourseName);
}