Entity Framework Core

bulkOperationSupport.cs

// Default size & more is set by database provider
// Additional commands will be sent in extra batches
// We can override batch size in DbContext OnConfiguring (options => options.MaxBatchSize(150))

// With SQL Server at least 4 operations are needed for it to be a bulk
// even if we add more entries separately before saving changes it will
// become a merge join, same as with adding everything at once

// we can mix up objects and add everything we want with one call! (up to 42 entries by default)

_context.AddRange(new Samurai {Name = "Shimada" },
				  new Samurai {Name = "Okamoto" },
				  new Battle {Name = "Battle of Anegawa"},
				  new Battle {Name = "Battle of Nagashino"});

codeFirst.cs

1. Define entities
2. Create DbContext
3. add-migration init
4. update-database
5. [EXTRA] Use ef-core-power-tools to display created db digram

// data annotations / mappings

    [Table("HomeAnimals")]
    public class HomeAnimal
    {
        [Key]
        public int Id { get; set; }
        
        [Column("Name")]
        [Required]
        public string Name { get; set; }
        
        [Column("Breed")]
        [Required]
        public string Breed { get; set; }
        
        [ForeignKey("PersonId")]
        [Required]
        public virtual Person Person { get; set; }
    }

	[Table("Persons")]
    public class Person
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }
        
        [Column("FirstName")]
        [Required]
        public string FirstName { get; set; }

        [Column("LastName")]
        [Required]
        public string LastName { get; set; }

        public virtual List<HomeAnimal> HomeAnimals { get; set; } = new List<HomeAnimal>();
    }

connectionStrings.cs

// simplest one
Server=(LocalDB)\\MSSQLLocalDB;Initial catalog=devserv

//Standard Security
Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;

//Trusted Connection
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;

// Connection to a SQL Server instance	
Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;Password=myPassword;

// Using a non-standard port
Server=myServerName,myPortNumber;Database=myDataBase;User Id=myUsername;Password=myPassword;

// Connect via an IP address
Data Source=190.190.200.100,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

// Enable MARS
Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;MultipleActiveResultSets=true;

// Attach a database file on connect to a local SQL Server Express instance
Server=.\SQLExpress;AttachDbFilename=C:\MyFolder\MyDataFile.mdf;Database=dbname;Trusted_Connection=Yes;

// Attach a database file, located in the data directory, on connect to a local SQL Server Express instance
Server=.\SQLExpress;AttachDbFilename=|DataDirectory|mydbfile.mdf;Database=dbname;Trusted_Connection=Yes;

// LocalDB automatic instance
Server=(localdb)\v11.0;Integrated Security=true;

// LocalDB automatic instance with specific data file
Server=(localdb)\v11.0;Integrated Security=true;AttachDbFileName=C:\MyFolder\MyData.mdf;

// LocalDB named instance
Server=(localdb)\MyInstance;Integrated Security=true;

// LocalDB named instance via the named pipes pipe name
Server=np:\\.\pipe\LOCALDB#F365A78E\tsql\query;

// LocalDB shared instance
Server=(localdb)\.\MyInstanceShare;Integrated Security=true;

// Database mirroring
Data Source=myServerAddress;Failover Partner=myMirrorServerAddress;Initial Catalog=myDataBase;Integrated Security=True;

// Asynchronous processing
Server=myServerAddress;Database=myDataBase;Integrated Security=True;Asynchronous Processing=True;

// Always Encrypted
Data Source=myServer;Initial Catalog=myDB;Integrated Security=true;Column Encryption Setting=enabled;

databaseFirst.cs

0. Prepare database schema and all entities for scaffolding
1. Install Microsoft.EntityFrameworkCore, Microsoft.EntityFrameworkCore.Tools, Microsoft.EntityFrameworkCore.Design,  
   Microsoft.EntityFrameworkCore.SqlServer (or other provider's package)
2. Scaffold-DbContext "connectionString" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Domain/Entities
			

deletingDataObjects.cs

var samurai = _context.Samurais.Find(18);
_context.Samurais.Remove(samurai);
_context.SaveChanges();

var samurais = _context.Samurais.Skip(0).Take(5);
_context.RemoveRange(samurais);
_context.SaveChanges();

eagerDataLoading.cs

// EAGER LOADING
// INCLUDE RELATED OBJECTS IN QUERY
// actually left join
var samuraiWithQuotes = _context.Samurais.Include(s => s.Quotes).ToList();

// with added filtering
var samuraiWithQuotes = _context.Samurais.Include(s => s.Quotes.Where(q => q.Text.Contains("Thanks"))).ToList();

// Include 		- include child objects
// Include.ThenInclude  - include children and grandchildren
// Include(s => s.X.Y)  - include just grandchildren
// Include.Include 	- include different children

Fundamentals---ORM.cs

/*
    ORM (Object-Relational Mapping) allows us to have a framework
    for storing objects within relational databases and translating between
    DATABASE-CODE communication.

    What is ORM?
    1.  Object relational mapping is a technique for storing,
        retrieving, updating and deleting (CRUD) from
        an object-oriented program in a relational database.
    2.  Utilization of "data layer" to manage translation between the OO and relational
    3.  Data layer is typically a library written in the OO language
        that is part of, or works in a conjuction within our web framework

    We might call it an interface for communicating with data layer.
    For C# it is Entity Framework / EF Core.
*/

manyToMany.cs

public class Samurai 
{
	public int Id {get; set;}
	public string Name {get; set;}

	// MANY
	public List<Battle> {get; set;} = new List<Battle>();
}

public class Battle
{
	public int BattleId {get; set;}
	public string Name {get; set;}

	// MANY
	public List<Samurai> Samurais {get; set;} = new List<Samurai>();
}

manyToManyWithExtraData.cs

public class Samurai 
{
	public int Id {get; set;}
	public string Name {get; set;}

	// MANY
	public List<Battle> {get; set;} = new List<Battle>();
}

public class Battle
{
	public int BattleId {get; set;}
	public string Name {get; set;}

	// MANY
	public List<Samurai> Samurais {get; set;} = new List<Samurai>();
}

// auto-generated with added property
public class BattleSamurai
{
	public int SamuraiId {get; set;}
	public int BattleId {get; set;}

	// added extra info
	// PAYLOAD
	public DateTime DateJoined {get; set;}
}

// extending mapping is important!!
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
	modelBuilder.Entity<Samurai>()
	.HasMany(s => s.Battles)
	.WithMany(b => b.Samurais)
	.UsingEntity<BattleSamurai>
	(bs => bs.HasOne<Battle>().WithMany(),
	 bs => bs.HasOne<Samurai>().WithMany())
	 .Property(bs => bs.DateJoined)
	 .HasDefaultValueSql("getdate()");
	 
}

migrations.cs

// To use migrations we need to install:
// Microsoft.EntityFrameworkCore.Tools
// Microsoft.EntityFrameworkCore.Design

// then we open nuget manager console
/*
    The following Entity Framework Core commands are available.

        Cmdlet                      Description
        --------------------------  ---------------------------------------------------
        Add-Migration               Adds a new migration.

        Drop-Database               Drops the database.

        Get-DbContext               Lists and gets information about available DbContext types.

        Get-Migration               Lists available migrations.

        Remove-Migration            Removes the last migration.

        Scaffold-DbContext          Scaffolds a DbContext and entity types for a database.

        Script-DbContext            Generates a SQL script from the DbContext. Bypasses any migrations.

        Script-Migration            Generates a SQL script from migrations.

        Update-Database             Updates the database to a specified migration.
*/

// Migration process
// 0. Install neede packages and generate DbContext
// 1. add-migration name
// 2. update-database

oneToMany.cs

public class Samurai 
{
	public int Id {get; set;}
	public string Name {get; set;}

	// Collection<T> and Enumerable<T> means MANY
	// they should be initialized
	// MANY
	public List<Quote> {get; set;} = new List<Quote>();
}

public class Quote
{
	public int Id {get; set;}
	public string Text {get; set;}

	// ONE

	// navigation property
	public Samurai Samurai {get; set;}
	// specifying property that interests us
	// name needs to match existing one!!
	// when there is only navigation property
	// then there can be null values
	// (quote can exist without samurai)

	// we could only specify this line
	// and it will work too!
	// is recognized as FK without naviation
	// quote can't exist without samurai)
	// required realtionship when FK

	public int SamuraiId {get; set;}
}

oneToOneRelationship.cs

public class Samurai
{
	public int Id {get; set;}
	public string Name {get; set;}
	
	// navigation it belongs to
	// only navigation - there can be samurais that have no horse  (nulls)
	public Horse Horse {get; set;}
}	

public class Horse
{
	public int Id {get; set;}
	public string Name {get; set;}

	// foreign key (no navigation)
	// required relationship because it is FK
	public int SamuraiId {get; set;}
}

// adding new entries

// scenario 1 - samurai had no horse
var horse = new Horse {Name = "Scout", SamuraiId = 2}
_context.Add(horse);
_context.SaveChanges();

// scenario 2 - samurai is already in memory
var samurai = _context.Samurai.Find(12);
samurai.Horse = new Horse {Name = "Black Beauty" };
_context.SaveChanges();

// query one-to-one relationships
var horseonly = _context.Set<Horse>().Find(3);

var horseWithSamurai = _context.Samurais.Include(s => s.Horse).FirstOrDefault(s => s.Horse.Id == 3);

queryAggregation.cs

// Last methods require query to have an OrderBy() method
// otherwise will return full set then pick last in memory

// Single methods expect only one match and WILL THROW EXCEPTION
// if there are none or more than one

// First methods return the first of any matches

// First/Single/Last WILL THROW EXCEPTION if no results are returned

// FirstOrDefault/SingleOrDefault/LastOrDefault WILL RETURN NULL
// if no results are returned

var samurai = _context.Samurais.FirstOrDefault(s => s.Name == name);

// find method - finding object by key value
// if object we are searching for is already in memory then no query will be executed!
// but it only works when using Find method.
var samurai = _context.Samurais.Find(2);

// and not this one, even though it does the same thing
var samurai = _context.Samurais.FirstOrDefault(s => s.Id == 2);

queryFiltering.cs

// Like method (as in sql)
_context.Samurais.Where(
	s => EF.Functions.Like(s.Name, "%abc%")
);

// contains method
_context.Samurais.Where(
	s => s.Name.Contains("abc")
);						//%abc% still!

queryWorkflow.cs

// There are two ways to express LINQ Queries

// LINQ METHODS
context.Samurais.ToList();

context.Samurais
.Where(s => s.Name == "Julie")
.ToList();

// LINQ QUERY SYNTAX
(from s in context.Samurais select s).ToList();

(from s in context.Samurais 
 where s.Name == "Julie" 
 select s)
 .ToList();

// Query enumeration
foreach (var s in context.Samurais)
{
	// Data connection stays open until end of loop!!
}

skipAndTake.cs

// Skip & Take for paging
var first_ten = _context.Animals.Skip(0).Take(10).ToList();

var next_ten = _context.Animals.Skip(10).Take(10).ToList();

storedProceduresAndRawQueries.cs

protected override void Up(MigrationBuilder migrationBuilder)
{
	// stored procedure defined in migration

	migrationBuilder.Sql(@"
	CREATE FUNCTION[dbo].[EarliestBattleFoughtBySamurai](@samuraiId int)
                RETURNS char(30) AS
                BEGIN
                  DECLARE @ret char(30)
                  SELECT TOP 1 @ret = Name
                  FROM Battles
                  WHERE Battles.BattleId IN(SELECT BattleId
                                     FROM BattleSamurai
                                    WHERE SamuraiId = @samuraiId)
                  ORDER BY StartDate
                  RETURN @ret
                END
	")
}


private static void QueryUsingFromSqlRawStoredProc()
{
	// raw sql results limitations
	// query needs to return data for all properties of the entity type
	// column names in results match mapped column names
	// query can't contain related data
	// it can return only query entities and keyless entities known by context
	
	var text = "Happy";

	// no interpolation
	var samurais = _context.Samurais.FromSqlRaw(
	"EXEC dbo.Samurais.WhoSaidAWord {0}", text).ToList();

	// using interpolation
	var samurais_alternative = _context.Samurais.FromSqlInterpolated(
	$"EXEC dbo.SamuraisWhoSaidAWord {text}").ToList();

	// using query!
	void fromQuery = _context.Samurais.FromSqlRaw(
	$"SELECT * FROM dbo.Samurais WHERE HorseId < 5"
	)

	// using linq
	var searchTerm = "Lorem ipsum";

	var blogs = context.Blogs
    .FromSqlInterpolated($"SELECT * FROM dbo.Samurais({searchTerm})")
    .Where(b => b.Name = "Robert")
    .OrderByDescending(b => b.Id)
    .ToList();

	// async versions!!
	var samurais_alternative = await _context.Samurais.FromSqlInterpolatedAsync(
	$"EXEC dbo.SamuraisWhoSaidAWord {text}").ToList();

	var samurais = _context.Samurais.FromSqlRawAsync(
	"EXEC dbo.Samurais.WhoSaidAWord {0}", text).ToList();

}