Microsoft.EntityFrameworkCore 2.0

Entity Framework Core is a lightweight and extensible version of the popular Entity Framework data access technology.
 EF Core API creates a database schema based on domain and context classes, without any additional configurations 
Commonly Used Types:
Microsoft.EntityFrameworkCore.DbContext
Microsoft.EntityFrameworkCore.DbSet

Ref: https://docs.microsoft.com/en-us/ef/core/get-started/aspnetcore/existing-db
Ref: https://www.learnentityframeworkcore.com/walkthroughs/existing-database

Install-Package Microsoft.EntityFrameworkCore -Version 2.1.2
Install-Package Microsoft.EntityFrameworkCore.SqlServer -Version 2.1.2
Install-Package Microsoft.EntityFrameworkCore.Tools

Auto Model generation: npm command-

Scaffold-DbContext "Server=xxxx;Database=xxx;User id=sa; Password=xx;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

https://stackoverflow.com/questions/50838156/unable-to-resolve-service-for-type-microsoft-entityframeworkcore-storage-irelat

Configuration

public class SchoolContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    { 
        optionsBuilder.UseSqlServer(@"Server=.\SQLEXPRESS;Database=SchoolDB;Trusted_Connection=True;");
    }

    public DbSet<Student> Students { get; set; }
}

There are two ways to configure domain classes in EF Core (same as in EF 6).

1. By using Data Annotation Attributes
2. By using Fluent API

Data Annotation Attributes

Data Annotations is a simple attribute based configuration method where different .NET attributes can be applied to domain classes and properties to configure the model.

[Table("StudentInfo")]
public class Student
{
    public Student() { }
        
    [Key]
    public int SID { get; set; }

    [Column("Name", TypeName="ntext")]
    [MaxLength(20)]
    public string StudentName { get; set; }

    [NotMapped]
    public int? Age { get; set; }
        
        
    public int StdId { get; set; }

    [ForeignKey("StdId")]
    public virtual Standard Standard { get; set; }
}
  

Fluent API:
Another way to configure domain classes is by using Entity Framework Fluent API. EF Fluent API is based on a Fluent API design pattern (a.k.a Fluent Interface) where the result is formulated by method chaining.

Override the OnModelCreating method and use a parameter modelBuilder of type ModelBuilder to configure domain classes, as shown below.

public class SchoolDBContext: DbContext 
{
    public DbSet<Student> Students { get; set; }
        
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        //Write Fluent API configurations here

        //Property Configurations
        modelBuilder.Entity<Student>()
                .Property(s => s.StudentId)
                .HasColumnName("Id")
                .HasDefaultValue(0)
                .IsRequired();
    }
}

Configurations	Fluent API Methods	Usage
Model Configurations	HasDbFunction()	Configures a database function when targeting a relational database.
	HasDefaultSchema()	Specifies the database schema.
	HasAnnotation()	Adds or updates data annotation attributes on the entity.
	HasSequence()	Configures a database sequence when targeting a relational database.
Entity Configuration	HasAlternateKey()	Configures an alternate key in the EF model for the entity.
	HasIndex()	Configures an index of the specified properties.
	HasKey()	Configures the property or list of properties as Primary Key.
	HasMany()	Configures the Many part of the relationship, where an entity contains the reference collection property of other type for one-to-Many or many-to-many relationships.
	HasOne()	Configures the One part of the relationship, where an entity contains the reference property of other type for one-to-one or one-to-many relationships.
	Ignore()	Configures that the class or property should not be mapped to a table or column.
	OwnsOne()	Configures a relationship where the target entity is owned by this entity. The target entity key value is propagated from the entity it belongs to.
	ToTable()	Configures the database table that the entity maps to.
Property Configuration	HasColumnName()	Configures the corresponding column name in the database for the property.
	HasColumnType()	Configures the data type of the corresponding column in the database for the property.
	HasComputedColumnSql()	Configures the property to map to computed column in the database when targeting a relational database.
	HasDefaultValue()	Configures the default value for the column that the property maps to when targeting a relational database.
	HasDefaultValueSql()	Configures the default value expression for the column that the property maps to when targeting relational database.
	HasField()	Specifies the backing field to be used with a property.
	HasMaxLength()	Configures the maximum length of data that can be stored in a property.
	IsConcurrencyToken()	Configures the property to be used as an optimistic concurrency token.
	IsRequired()	Configures whether the valid value of the property is required or whether null is a valid value.
	IsRowVersion()	Configures the property to be used in optimistic concurrency detection.
	IsUnicode()	Configures the string property which can contain unicode characters or not.
	ValueGeneratedNever()	Configures a property which cannot have a generated value when an entity is saved.
	ValueGeneratedOnAdd()	Configures that the property has a generated value when saving a new entity.
	ValueGeneratedOnAddOrUpdate()	Configures that the property has a generated value when saving new or existing entity.
	ValueGeneratedOnUpdate()	Configures that a property has a generated value when saving an existing entity.

one-to-many relationship:

modelBuilder.Entity<Student>()
    .HasOne<Grade>(s => s.Grade)
    .WithMany(g => g.Students)
    .HasForeignKey(s => s.CurrentGradeId);

Ref: https://www.entityframeworktutorial.net/efcore/configure-one-to-many-relationship-using-fluent-api-in-ef-core.aspx

One to one relationship:

 modelBuilder.Entity<Student>()
            .HasOne<StudentAddress>(s => s.Address)
            .WithOne(ad => ad.Student)
            .HasForeignKey<StudentAddress>(ad => ad.AddressOfStudentId);

Insert /Update 

using (var context = new SchoolContext())
{
    // Attach an entity to DbContext with Added state
    context.Add<Student>(std);

    // Calling SaveChanges to insert a new record into Students table
    context.SaveChanges();
}

Using DbSet:

using (var context = new SchoolContext())
{
    context.Students.Add(std);

    // or
    // context.Students.Attach(std);

    context.SaveChanges();
}

Execute Raw SQL Queries in Entity Framework Core:

Entity Framework Core provides the DbSet.FromSql() method to execute raw SQL queries for the underlying database and get the results as entity objects.

var context = new SchoolContext();

var students = context.Students
                  .FromSql("Select * from Students where Name = 'Bill'")
                  .ToList();

FromSql Limitations

1. SQL queries must return entities of the same type as DbSet<T> type. e.g. the specified query cannot return the Course entities if FromSql is used after Students. Returning ad-hoc types from FromSql() method is in the backlog.
2. The SQL query must return all the columns of the table. e.g. context.Students.FromSql("Select StudentId, LastName from Students).ToList()will throw an exception.
3. The SQL query cannot include JOIN queries to get related data. Use Include method to load related entities after FromSql() method.

Entity Framework Core: DbContext:

Ref:https://www.entityframeworktutorial.net/efcore/entity-framework-core-dbcontext.aspx

An instance of DbContextrepresents a session with the database which can be used to query and save instances of your entities to a database. DbContext is a combination of the Unit Of Work and Repository patterns.

DbContext in EF Core allows us to perform following tasks:

1. Manage database connection
2. Configure model & relationship
3. Querying database
4. Saving data to the database
5. Configure change tracking
6. Caching
7. Transaction management

DbContext Methods

Method	Usage
Add	Adds a new entity to DbContext with Added state and starts tracking it. This new entity data will be inserted into the database when SaveChanges() is called.
AddAsync	Asynchronous method for adding a new entity to DbContext with Added state and starts tracking it. This new entity data will be inserted into the database when SaveChangesAsync() is called.
AddRange	Adds a collection of new entities to DbContext with Added state and starts tracking it. This new entity data will be inserted into the database when SaveChanges() is called.
AddRangeAsync	Asynchronous method for adding a collection of new entities which will be saved on SaveChangesAsync().
Attach	Attaches a new or existing entity to DbContext with Unchanged state and starts tracking it.
AttachRange	Attaches a collection of new or existing entities to DbContext with Unchanged state and starts tracking it.
Entry	Gets an EntityEntry for the given entity. The entry provides access to change tracking information and operations for the entity.
Find	Finds an entity with the given primary key values.
FindAsync	Asynchronous method for finding an entity with the given primary key values.
Remove	Sets Deleted state to the specified entity which will delete the data when SaveChanges() is called.
RemoveRange	Sets Deleted state to a collection of entities which will delete the data in a single DB round trip when SaveChanges() is called.
SaveChanges	Execute INSERT, UPDATE or DELETE command to the database for the entities with Added, Modified or Deleted state.
SaveChangesAsync	Asynchronous method of SaveChanges()
Set	Creates a DbSet<TEntity> that can be used to query and save instances of TEntity.
Update	Attaches disconnected entity with Modified state and start tracking it. The data will be saved when SaveChagnes() is called.
UpdateRange	Attaches a collection of disconnected entities with Modified state and start tracking it. The data will be saved when SaveChagnes() is called.
OnConfiguring	Override this method to configure the database (and other options) to be used for this context. This method is called for each instance of the context that is created.
OnModelCreating	Override this method to further configure the model that was discovered by convention from the entity types exposed in DbSet<TEntity> properties on your derived context.

 public class SchoolContext : DbContext
{

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {// optionsBuilder.UseSqlServer("connection string ")}

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
    }
}

Querying in Entity Framework Core :

Notes:EF Core has a new feature in LINQ-to-Entities where we can include C# or VB.NET functions in the query. This was not possible in EF 6.

private static void Main(string[] args)
{
    var context = new SchoolContext();
    var studentsWithSameName = context.Students
                                      .Where(s => s.FirstName == GetName())
                                      .ToList();
}

public static string GetName() { 
    return "Bill";
}

Disable the  lazy loading:
context.ChangeTracker.LazyLoadingEnabled = false;

Eager Loading--Left join using Include()

Entity Framework Core supports eager loading of related entities, same as EF 6, using the Include() extension method and projection query. In addition to this, it also provides the ThenInclude() extension method to load multiple levels of related entities. (EF 6 does not support the ThenInclude() method.)

var context = new SchoolContext();

var studentWithGrade = context.Students
                           .Where(s => s.FirstName == "Bill")
                           .Include(s => s.Grade)
                           .FirstOrDefault();