Entity Framework Core Querying

Author : MD TAREQ HASSAN

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

Get all data

var samurais = context.Samurais.ToList();

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 );

Eager loading with Find or FindAsync

Find(..) / FindAsync(...) is DbSet method that first tries to find the requested entity in the context’s cache. Only when it’s not found there, the entity is fetched from the database.
Because of this special behavior (of Find), Include and Find can’t be mixed
StackOverflow:
- Question: https://stackoverflow.com/questions/39088847/how-to-specify-eager-loading-with-dbcontext-find-method
- Working answer: https://stackoverflow.com/a/39095357/4802664

Eager loading is done by the .Include(...) method (do not use Find/FindAsync) :

_dbContext.Employees.Include(e => e.Id);

Update

In service: EditModel & Entity -> perform update operation

public async Task<bool> SaveEditedDataAsync(EditModel editModel)
{
	int affectedRows = -1;

	try
	{
		var trackedEntity = await _dbContext.FooList.FindAsync(editModel.Id);

		//
		// Detaching entity is dangerous, so do manual copying
		//
		if (editModel.Bar.HasValue)
		{
			trackedEntity.Bar = editModel.Bar.Value;
		}

		trackedEntity.Bazz = editModel.Bazz;
		
		// ... ... ...

		affectedRows = await _dbContext.SaveChangesAsync();
	}
	catch (Exception ex)
	{
		Log.Error(ex.Message, ex);
	}

	return affectedRows >= 1;
}

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

DbSet.Update(entity)
DbSet.UpdateRange(entityA, entityB)
DbContext.Update(entity)
DbContext.UpdateRange(entityA, entityB)
DbContext.Entry(entity).State = EntityState.Modified

Remove

use whole entity
delete by id i.e. context.Remove(id) is not supported yet

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:

must return full entity
query must be flat no related data
must be: result set column names = mapped column names

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