Basic Queries

https://docs.microsoft.com/en-us/ef/core/querying/basic

Add

var samurai = new Samurai { Name = "Hassan" };
using (var context = new SamuraiContext())
{
	context.Samurais.Add(samurai);
	
	context.SaveChanges();
}

Inserting multiple

var hassan = new Samurai { Name = "Hassan" };
var julie = new Samurai { Name = "Julie" };
using (var context = new SamuraiContext())
{
	context.Samurais.AddRange(new List<Samurai> {hassan, julie});

	context.SaveChanges();
}

// or
_context.AddRange(
	new Samurai { Name = "Kambei Shimada" },
	new Samurai { Name = "Shichirōji " },
	new Samurai { Name = "Katsushirō Okamoto" },
	new Samurai { Name = "Heihachi Hayashida" },
	new Samurai { Name = "Kyūzō" },
	new Samurai { Name = "Gorōbei Katayama" }
);
_context.SaveChanges();

Read

using (var context = new SamuraiContext())
{
	var samurais = context.Samurais.ToList(); // var query = context.Samurais();
	
	foreach (var samurai in samurais)
	{
	  Console.Write(samurai.Name);
	}
}

// or get single samurai
var samurai = _context.Samurais.FirstOrDefault();

Find

Find by id

var samurai = _context.Samurais.Find(2);

var samuraiId = 2;
var samurai = _context.Samurais.FirstOrDefault(samurai => samurai.Id = samuraiId );

Find by name

var samurais = _context.Samurais.Where(samurai => samurai.Name = "Hassan");

var hassan = "Hassan";
var samurais = _context.Samurais.Where(samurai => samurai.Name = hassan);

var hassan = "Hassan";
var samurai = _context.Samurais.Where(samurai => samurai.Name = hassan).FirstOrDefault();

var hassan = "Hassan";
var samurai = _context.Samurais.FirstOrDefault(samurai => samurai.Name = hassan );

Update

var samurai = _context.Samurais.FirstOrDefault();
samurai.Name += "San";
_context.SaveChanges();

Updating multiple entities

var samurais = _context.Samurais.ToList();
samurais.ForEach( s => s.Name += "San");
_context.SaveChanges();

*Updating disconnected DbContext or detached entities**

var samurai = _context.Samurais.FirstOrDefault(s=>s.Name=="Kikuchiyo");
samurai.Name += "San";

using (var contextNewAppInstance =new SamuraiContext())
{
	contextNewAppInstance.Samurais.Update(samurai);
	
	contextNewAppInstance.SaveChanges();
}

Update operations

Remove

var samurai = _context.Samurais.FirstOrDefault(s  => s.Name == "Kambei Shimada" );
_context.Samurais.Remove(samurai);

//alternates:
// _context.Remove(samurai);
// _context.Entry(samurai).State=EntityState.Deleted;
// _context.Samurais.Remove(_context.Samurais.Find(1));

_context.SaveChanges();

See: https://www.learnentityframeworkcore.com/dbset/deleting-data

Remove multiple entities

var samurais = _context.Samurais.Where(s => s.Name.Contains("ō"));
_context.Samurais.RemoveRange(samurais);

//alternate: _context.RemoveRange(samurais);

_context.SaveChanges();

Removing detached entities

var samurai = _context.Samurais.FirstOrDefault(s => s.Name == "Heihachi Hayashida");
using (var contextNewAppInstance = new SamuraiContext()) {

	contextNewAppInstance.Samurais.Remove(samurai);
	
	//contextNewAppInstance.Entry(samurai).State=EntityState.Deleted;
	
	contextNewAppInstance.SaveChanges();
}

Multiple operations at once

// update
var samurai = _context.Samurais.FirstOrDefault();
samurai.Name += "San";

// add
_context.Samurais.Add(new Samurai { Name = "Kikuchiyo" });

// executing multiple operations at once
_context.SaveChanges();

Raw SQL Queries

https://docs.microsoft.com/en-us/ef/core/querying/raw-sql

var samurais = _context.Samurais.FromSql("Select * from Samurais")
			  .OrderByDescending(s => s.Name)
			  .Where(s => s.Name.Contains("San")).ToList();
			  
samurais.ForEach(s=>Console.WriteLine(s.Name));

Limitations:

Context.Database.ExecuteSqlCommand

var affected=_context.Database.ExecuteSqlCommand("update samurais set Name = REPLACE(Name,'San','Nan')");
Console.WriteLine($"Affected rows {affected}");
var samurai = new Samurai
{
	Name = "Kambei Shimada",
	Quotes = new List<Quote>{
		new Quote {Text = "I've come to save you"}
	}
};
_context.Samurais.Add(samurai);
_context.SaveChanges();

Inserting multiple

var samurai = new Samurai
{
	Name = "Kyūzō",
	Quotes = new List<Quote> {
		new Quote {Text = "Watch out for my sharp sword!"},
		new Quote {Text="I told you to watch out for the sharp sword! Oh well!"}
	}
};

_context.Samurais.Add(samurai);
_context.SaveChanges();

Inserting one-to-one

var samurai = new Samurai { Name = "Shichirōji " };
samurai.SecretIdentity = new SecretIdentity { RealName = "Julie" };
_context.Add(samurai);
_context.SaveChanges();

Inserting while tracked

var samurai = _context.Samurais.First();
samurai.Quotes.Add(new Quote
{
	Text = "I bet you're happy that I've saved you!"
});
_context.SaveChanges();

// one-to-one
var samurai = _context.Samurais.FirstOrDefault(s => s.SecretIdentity == null);
samurai.SecretIdentity = new SecretIdentity { RealName = "Sampson" };
_context.SaveChanges();

https://docs.microsoft.com/en-us/ef/core/querying/related-data

Eager Loading

var samurais = _context.Samurais.FromSql("select * from samurais")
  .Include(s => s.Quotes)
  .ToList();

Explicit loading

_context = new SamuraiContext();
var samurai = _context.Samurais.FirstOrDefault();
_context.Entry(samurai).Collection(s => s.Quotes).Load();
_context.Entry(samurai).Reference(s => s.SecretIdentity).Load();

Eager loading does not allow filtering while Explicit loading allows filtering (.Query())

// eager loading
// this won't work. No filtering, no sorting on Include
_context = new SamuraiContext();
var samurais = _context.Samurais
  .Include(s => s.Quotes.Where(q => q.Text.Contains("happy")))
  .ToList();

// explicit loading
_context.Entry(samurai)
  .Collection(s => s.Quotes)
  .Query()
  .Where(q => q.Text.Contains("happy"))
  .Load();

Lazy loading
Use virtual keyword for navigation properties