private static void ElementOperators(PlutoContext context)
{
//OrDefault evita la excepcion si no hay registros
var firstCourse = context.Courses.OrderBy(c => c.Level).FirstOrDefault(c => c.FullPrice > 100);
//No disponible en SQL SERVER
var lastCourse = context.Courses.OrderBy(c => c.Level).Last();
//OrDefault evita la excepcion si no hay registros
//Multiples resultados: excepción. En ese caso usar FirstOrDefault
var course = context.Courses.OrderBy(c => c.Level).SingleOrDefault(c => c.Id == 1);
//cuantificar
bool allInLevel1 = context.Courses.All(c => c.Level == 1);
bool anyInLevel1 = context.Courses.Any(c => c.Level == 1);
//Aggregating
int count = context.Courses.Count();
int countInLeve1 = context.Courses.Count(c => c.Level == 1);
int countInLeve1_ = context.Courses.Where(c => c.Level == 1).Count();
var max = context.Courses.Max(c => c.FullPrice);
var min = context.Courses.Min(c => c.FullPrice);
var avg = context.Courses.Average(c => c.FullPrice);
var sum = context.Courses.Sum(c => c.FullPrice);
}
public Timesheet SaveTimesheet(Timesheet model)
{
var timesheet = PlutoContext.Timesheets.Add(model);
PlutoContext.SaveChanges();
return(timesheet);
}
private static void Filtering(PlutoContext context)
{
var query =
from c in context.Courses
where c.Level == 1 && c.Author.Id == 1
select c;
}
public List <RegisteredSubjectEntity> GetRegisteredSubjectEntities()
{
using (var db = new PlutoContext())
{
return(db.RegisteredSubjects.Include("AttendanceEntities").ToList());
}
}
public List <TermEntity> GetTermEntities()
{
using (var db = new PlutoContext())
{
return(db.Terms.ToList());
}
}
public List <SubjectEntity> GetSubjectEntities()
{
using (var db = new PlutoContext())
{
return(db.Subjects.ToList());
}
}
private static void Ordering(PlutoContext context)
{
var query = context.Courses
.Where(c => c.Level == 1)
.OrderBy(c => c.Name)
.ThenBy(c => c.Level);
}
public void DeleteTermEntity(TermEntity termEntityToDelete)
{
using (var db = new PlutoContext())
{
db.Entry(termEntityToDelete).State = EntityState.Deleted;
db.SaveChanges();
}
}
public void UpdateTermEntity(TermEntity termEntityToUpdate)
{
using (var db = new PlutoContext())
{
db.Entry(termEntityToUpdate).State = EntityState.Modified;
db.SaveChanges();
}
}
public void DeleteAttendanceEntity(AttendanceEntity attendanceEntityToDelete)
{
using (var db = new PlutoContext())
{
db.Entry(attendanceEntityToDelete).State = EntityState.Deleted;
db.SaveChanges();
}
}
public void DeleteRegisteredSubjectEntity(RegisteredSubjectEntity registeredSubjectEntityToDelete)
{
using (var db = new PlutoContext())
{
db.Entry(registeredSubjectEntityToDelete).State = EntityState.Deleted;
db.SaveChanges();
}
}
private static void Ordering(PlutoContext context)
{
var query =
from c in context.Courses
where c.Author.Id == 1
orderby c.Level descending, c.Name
select c;
}
public void DeleteSubjectEntity(SubjectEntity subjectEntityToDelete)
{
using (var db = new PlutoContext())
{
db.Entry(subjectEntityToDelete).State = EntityState.Deleted;
db.SaveChanges();
}
}
public void UpdateSubjectEntity(SubjectEntity subjectEntityToUpdate)
{
using (var db = new PlutoContext())
{
db.Entry(subjectEntityToUpdate).State = EntityState.Modified;
db.SaveChanges();
}
}
private static void CrossJoin(PlutoContext context)
{
//En extension method se implementa con SelectMany
context.Authors.SelectMany(a => context.Courses, (author, course) => new
{
AuthorName = author.Name,
CourseName = course.Name
});
}
private static void GroupJoin(PlutoContext context)
{
//Equivalente a cuando en SQL hacemos LEFT JOIN con COUNT
context.Authors.GroupJoin(context.Courses, a => a.Id, c => c.AuthorId, (author, courses) => new
{
AuthorName = author.Name,
Courses = courses.Count()
});
}
public int AddRegisteredSubjectEntity(RegisteredSubjectEntity registeredSubjectEntity)
{
using (var db = new PlutoContext())
{
db.RegisteredSubjects.Add(registeredSubjectEntity);
db.SaveChanges();
}
return(registeredSubjectEntity.Id);
}
public int AddTermEntity(TermEntity termEntity)
{
using (var db = new PlutoContext())
{
db.Terms.Add(termEntity);
db.SaveChanges();
}
return(termEntity.Id);
}
private static void Projection(PlutoContext context)
{
var query =
from c in context.Courses
where c.Level == 1
select new {
Name = c.Name,
Author = c.Author.Name
};
}
public int AddSubjectEntity(SubjectEntity subjectEntity)
{
using (var db = new PlutoContext())
{
db.Subjects.Add(subjectEntity);
db.SaveChanges();
}
return(subjectEntity.Id);
}
public int AddAttendanceEntity(AttendanceEntity attendanceEntity)
{
using (var db = new PlutoContext())
{
db.Attendances.Add(attendanceEntity);
db.SaveChanges();
}
return(attendanceEntity.Id);
}
private static void Joining(PlutoContext context)
{
context.Courses.Join(context.Authors,
c => c.AuthorId,
a => a.Id
, (course, author) => new
{
CourseName = course.Name,
AuthorName = author.Name
});
}
public static void RemoveCourse()
{
using (var context = new PlutoContext())
{
var course = context.Courses.Find(6);
context.Courses.Remove(course);
context.SaveChanges();
}
}
private static void WithCascadeDelete()
{
//courses and tags
var context = new PlutoContext();
var course = context.Courses.Find(6); //Single(c => c.Id == 6)
context.Courses.Remove(course);
context.SaveChanges();
}
public static void RemoveAuthor()
{
using (var context = new PlutoContext())
{
var author = context.Authors.Include(a => a.Courses).Single(a => a.Id == 2);
context.Courses.RemoveRange(author.Courses);
context.Authors.Remove(author);
context.SaveChanges();
}
}
static void Main(string[] args)
{
var context = new PlutoContext();
var courses = context.Courses.ToList();
foreach (var course in courses)
{
Console.WriteLine("{0} by {1}", course.Name, course.Author.Name);
}
Console.ReadKey();
}
protected override void Up(MigrationBuilder migrationBuilder)
{
using (PlutoContext context = new PlutoContext())
{
var authorId = new SqlParameter("@AuthorId", 1);
context.Database.ExecuteSqlCommand(@"
UPDATE Courses
SET AuthorId = @AuthorId
WHERE AuthorId IS NULL;
", authorId);
}
}
private static void Projection(PlutoContext context)
{
var query = context.Courses
.Where(c => c.Level == 1)
.OrderBy(c => c.Name)
.ThenBy(c => c.Level)
.Select(c => new
{
CourseName = c.Name,
AuthorName = c.Author.Name
});
}
public static void UpdateCourse()
{
using (var context = new PlutoContext())
{
var course = context.Courses.Find(4);
course.Name = "New Name";
course.AuthorId = 2;
context.SaveChanges();
}
}
private static void WithoutCascadeDelete()
{
//authors and courses
var context = new PlutoContext();
//Eager load para sus cursos
var author = context.Authors.Include(a => a.Courses).Single(a => a.Id == 2);
context.Courses.RemoveRange(author.Courses);
context.Authors.Remove(author);
context.SaveChanges();
}
