static void QueryingManyToMany()
{
using (var context = new EnrollmentContext())
{
//var instructor =
// context.Instructors
// .Include(x => x.Courses)
// .ThenInclude(e => e.Enrollments)
// .ThenInclude(s => s.Student)
// .FirstOrDefault(x => x.Name.Contains("Bob"));
var instructor = context.Instructors.SingleOrDefault(x => x.Name.Contains("Bob"));
var bobsCourseInfo =
context.Courses
.Where(x => x.InstructorId == instructor.InstructorId)
.Select(x => new
{
Course = new
{
Title = x.Title,
Students = x.Enrollments.Select(e => e.Student)
}
})
.ToList();
}
}
private void Initialize()
{
serviceProvider = new ServiceCollection()
.AddDbContext <EnrollmentContext>
(
options =>
{
options.UseInMemoryDatabase("Enrollment");
options.UseInternalServiceProvider(new ServiceCollection().AddEntityFrameworkInMemoryDatabase().BuildServiceProvider());
}
)
.AddTransient <IEnrollmentStore, EnrollmentStore>()
.AddTransient <IEnrollmentRepository, EnrollmentRepository>()
.AddSingleton <AutoMapper.IConfigurationProvider>
(
new MapperConfiguration(cfg =>
{
cfg.AddMaps(typeof(EnrollmentProfile).GetTypeInfo().Assembly);
cfg.AddMaps(typeof(GroupingProfile).GetTypeInfo().Assembly);
cfg.AddProfile <ExpansionParameterToViewMappingProfile>();
cfg.AddProfile <ExpansionViewToOperatorMappingProfile>();
})
)
.AddTransient <IMapper>(sp => new Mapper(sp.GetRequiredService <AutoMapper.IConfigurationProvider>(), sp.GetService))
.BuildServiceProvider();
EnrollmentContext context = serviceProvider.GetRequiredService <EnrollmentContext>();
context.Database.EnsureCreated();
Task.Run(async() => await Seed_Database(serviceProvider.GetRequiredService <IEnrollmentRepository>())).Wait();
}
static void Main(string[] args)
{
var context = new EnrollmentContext();
if (!context.Schedules.Any())
{
var schedule = new Schedule()
{
Title = "Schedule 1"
};
schedule.Instances.Add(new Instance {
Date = DateTime.Now
});
schedule.Instances.Add(new Instance {
Date = DateTime.Now.AddDays(1)
});
context.Schedules.Add(schedule);
context.SaveChanges();
}
var sc = context.Schedules
.Include(x => x.Instances)
.SingleOrDefault(x => x.Id == 1);
sc.Instances.Remove(sc.Instances.First());
context.SaveChanges();
Console.WriteLine("asd");
}
static void Like_Search()
{
var context = new EnrollmentContext();
var name = "%Taz%";
context.Students.SingleOrDefault(s => EF.Functions.Like(s.Name, name));
}
public static void RunRawSqlInterpolated()
{
using (var context = new EnrollmentContext())
{
var stats = context.CourseStats.FromSqlInterpolated($"Select * from CourseStat WHERE CourseId={2}").ToList();
stats.ForEach(st => Console.WriteLine(st.Title));
}
}
public static void DisplayFromCourseView()
{
using (var context = new EnrollmentContext())
{
var stats = context.CourseStats.ToList();
stats.ForEach(st => Console.WriteLine(st.Title));
}
}
public Enrollment(string subject, DateTime validUntil, Algorithms algorithm, int keyLength, bool machineContext)
{
_extsToAdd = new List <CX509Extension>();
Subject = subject;
Expiration = validUntil;
HashAlgorithm = algorithm;
KeyLength = keyLength;
Context = new EnrollmentContext(machineContext);
}
static void GetAllCourses()
{
using (var context = new EnrollmentContext())
{
var courses = context.Courses
.Include(c => c.Instructor)
.ToList();
courses.ForEach(c => Console.WriteLine($"{c.Title} \n\tby {c.Instructor.Name}"));
}
}
static void Paginate()
{
using (var context = new EnrollmentContext())
{
context
.Courses
.Skip(1)
.Take(2)
.ToList();
}
}
public void CanInsertInstructorIntoDatabse()
{
var builder = new DbContextOptionsBuilder();
builder.UseInMemoryDatabase("InstructorDb");
using (var context = new EnrollmentContext(builder.Options))
{
var instructor = new Instructor();
context.Instructors.Add(instructor);
Assert.AreEqual(EntityState.Added, context.Entry(instructor).State);
}
}
static void QueryAsNoTracking()
{
using (var context = new EnrollmentContext())
{
context
.Courses
.AsNoTracking()
.Skip(1)
.Take(2)
.ToList();
}
}
static void InsertCourseWithFK(int id)
{
var newCourse = new Course()
{
InstructorId = id, Title = "New Chopstick mastery course", StartDate = DateTime.Now, EndDate = DateTime.Now
};
using (var newContext = new EnrollmentContext())
{
newContext.Courses.Add(newCourse);
newContext.SaveChanges();
}
}
public static void RunRawSql()
{
using (var context = new EnrollmentContext())
{
var stats = context.CourseStats.FromSqlRaw("Select * from CourseStat").ToList();
stats.ForEach(st => Console.WriteLine(st.Title));
var id = 2;
stats = context.CourseStats.FromSqlRaw("Select * from CourseStat WHERE CourseId = {0}", id).ToList();
stats.ForEach(st => Console.WriteLine(st.Title));
}
}
static void Display_Students_Of_Course_CleanCode()
{
var context = new EnrollmentContext();
var course = context.Courses
.Include("Enrollments")
.FirstOrDefault(x => x.CourseId == 4);
foreach (var item in course.Enrollments)
{
context.Entry(item).Reference(x => x.Student).Load();
Console.WriteLine(item.Student.Name);
}
}
static void ProjectAndJoin()
{
using (var context = new EnrollmentContext())
{
var courses = context.Courses
.Where(x => x.Enrollments.Count > 2)
.Select(x => new
{
Title = x.Title,
Instructor = x.Instructor.Name
})
.ToList();
}
}
static void EagerLoading()
{
using (var context = new EnrollmentContext())
{
context.Students.
Include(x => x.Enrollments).
ToList();
context.Courses
.Include(x => x.Instructor)
.ThenInclude(x => x.Courses)
.ToList();
}
}
static void UpdateInstructor(int id)
{
using (var context = new EnrollmentContext())
{
var instructor = context.Instructors
.Include("Courses")
.FirstOrDefault(i => i.InstructorId == id);
instructor.Courses.Add(new Course()
{
Title = "Mastering chop sticks 2", StartDate = DateTime.Now, EndDate = DateTime.Now
});
context.Instructors.Update(instructor);
context.SaveChanges();
}
}
public static void GetCourseDetailByInstructor(int id)
{
using (var context = new EnrollmentContext())
{
//var stats = context.CourseDetails
// .FromSqlRaw("EXEC GetCourseByInstructor {0}", id)
// .ToList();
//stats.ForEach(st => Console.WriteLine($"{st.Title} - by {st.Name}, Number of students: {st.NumOfEnrolledStudents}"));
var stats = context.Set <CourseDetail>()
.FromSqlRaw("EXEC GetCourseByInstructor {0}", id)
.ToList();
stats.ForEach(st => Console.WriteLine($"{st.Title} - by {st.Name}, Number of students: {st.NumOfEnrolledStudents}"));
}
}
static void UpdateUsingEntry()
{
var context = new EnrollmentContext();
var instructor = context.Instructors
.Include(i => i.Courses)
.SingleOrDefault(x => x.Name.Contains("Shifu"));
var course = instructor.Courses[0];
course.Title = "The first course of master shifu";
using (var newContext = new EnrollmentContext())
{
newContext.Entry(course).State = EntityState.Modified;
newContext.SaveChanges();
}
}
static void UpdateInstructorDisconnected(int id)
{
var context = new EnrollmentContext();
var instructor = context.Instructors
.Include(i => i.Courses)
.FirstOrDefault(i => i.InstructorId == id);
instructor.Name = "Master Shifu - The true master";
using (var newContext = new EnrollmentContext())
{
newContext.Instructors.Update(instructor);
newContext.Courses.RemoveRange(instructor.Courses.OrderBy(x => x.CourseId).Last());
newContext.SaveChanges();
}
}
static void Enroll_Every_Student_In_Clean_Code_Course()
{
using (var context = new EnrollmentContext())
{
var students = context.Students.ToList();
var cleanCode = context.Courses.Find(4);
students.ForEach(s =>
{
cleanCode.Enrollments.Add(new Enrollment {
StudentId = s.StudentId, CourseId = cleanCode.CourseId
});
});
context.SaveChanges();
}
}
private void Initialize()
{
serviceProvider = new ServiceCollection()
.AddDbContext <EnrollmentContext>
(
options =>
{
options.UseInMemoryDatabase("Enrollment");
options.UseInternalServiceProvider(new ServiceCollection().AddEntityFrameworkInMemoryDatabase().BuildServiceProvider());
}
).AddTransient <IEnrollmentStore, EnrollmentStore>().BuildServiceProvider();
EnrollmentContext context = serviceProvider.GetRequiredService <EnrollmentContext>();
context.Database.EnsureCreated();
Task.Run(async() => await Seed_Database(serviceProvider.GetRequiredService <IEnrollmentStore>())).Wait();
}
static void InsertRelatedData()
{
using (var context = new EnrollmentContext())
{
context.Instructors.Add(new Instructor()
{
Name = "Master Shifu",
Courses = new List <Course>
{
new Course()
{
Title = "Mastering the troll fu", StartDate = DateTime.Now, EndDate = DateTime.Now
}
}
});
context.SaveChanges();
}
}
static void UpdateInstructorUsingAttach(int id)
{
var context = new EnrollmentContext();
var instructor = context.Instructors
.Include(i => i.Courses)
.FirstOrDefault(i => i.InstructorId == id);
instructor.Name = "Master Shifu - The true master";
instructor.Courses.Add(new Course()
{
Title = "Mastering chop sticks 3", StartDate = DateTime.Now, EndDate = DateTime.Now
});
using (var newContext = new EnrollmentContext())
{
newContext.Instructors.Attach(instructor);
newContext.SaveChanges();
}
}
static void Execution_Methods()
{
var context = new EnrollmentContext();
context.Students.ToList();
context.Students.First();
context.Students.FirstOrDefault();
context.Students.Single(s => s.StudentId == 1);
context.Students.SingleOrDefault(s => s.StudentId == 1);
// Last() and LastOrDefault() works with OrderBy()
context.Students.OrderBy(s => s.StudentId).Last();
context.Students.OrderBy(s => s.StudentId).LastOrDefault();
context.Students.Find(2);
/*
* Aggregate Functions
* Average(), Sum(), Min(), Max()
*/
}
private void StartUp()
{
var settings = new EnrollmentAPI.AppSettings()
{
Secret = "THIS IS USED TO SIGN AND VERIFY JWT TOKENS, REPLACE IT WITH YOUR OWN SECRET, IT CAN BE ANY STRING"
};
IOptions <EnrollmentAPI.AppSettings> appSettingsOptions = Options.Create(settings);
var optionsBuilder = new DbContextOptionsBuilder <EnrollmentContext>().UseInMemoryDatabase("client");
EnrollmentContext context = new EnrollmentContext(optionsBuilder.Options);
userService = new UserService(appSettingsOptions, context);
if (userService.GetById(1) == null)
{
userService.RegisterUser(new User {
Id = 1,
FirstName = "admin",
LastName = "admin",
Username = "******",
Password = "******",
Role = Role.Admin,
Token = null
});
}
if (userService.GetById(2) == null)
{
userService.RegisterUser(
new User {
Id = 2,
FirstName = "test",
LastName = "test",
Username = "******",
Password = "******",
Role = Role.User,
Token = null
});
}
}
static void Remove_Jack_Sparrow_From_CleanCode()
{
using (var context = new EnrollmentContext())
{
var student = context.Students
.Include("Enrollments")
.FirstOrDefault(s => s.StudentId == 4);
var course = context.Courses
.Include("Enrollments")
.FirstOrDefault(c => c.CourseId == 4);
var enrollment = student.Enrollments.FirstOrDefault(x => x.CourseId == course.CourseId);
if (enrollment != null)
{
course.Enrollments.Remove(enrollment);
}
context.SaveChanges();
}
}
private void Initialize()
{
if (MapperConfiguration == null)
{
MapperConfiguration = new MapperConfiguration(cfg =>
{
cfg.AddExpressionMapping();
cfg.AddProfile <DescriptorToOperatorMappingProfile>();
cfg.AddProfile <EnrollmentProfile>();
cfg.AddProfile <ExpansionDescriptorToOperatorMappingProfile>();
});
}
MapperConfiguration.AssertConfigurationIsValid();
serviceProvider = new ServiceCollection()
.AddDbContext <EnrollmentContext>
(
options => options.UseSqlServer
(
@"Server=(localdb)\mssqllocaldb;Database=RequestHelpersTest;ConnectRetryCount=0"
),
ServiceLifetime.Transient
)
.AddTransient <IEnrollmentStore, EnrollmentStore>()
.AddTransient <IEnrollmentRepository, EnrollmentRepository>()
.AddSingleton <AutoMapper.IConfigurationProvider>
(
MapperConfiguration
)
.AddTransient <IMapper>(sp => new Mapper(sp.GetRequiredService <AutoMapper.IConfigurationProvider>(), sp.GetService))
.BuildServiceProvider();
EnrollmentContext context = serviceProvider.GetRequiredService <EnrollmentContext>();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
DatabaseSeeder.Seed_Database(serviceProvider.GetRequiredService <IEnrollmentRepository>()).Wait();
}
static void Main(string[] args)
{
IConfigurationRoot config = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
.Build();
IServiceProvider serviceProvider = new ServiceCollection().AddDbContext <EnrollmentContext>(options =>
options.UseSqlServer(config.GetConnectionString("DefaultConnection")), ServiceLifetime.Transient)
.AddTransient <IEnrollmentStore, EnrollmentStore>()
.AddTransient <IEnrollmentRepository, EnrollmentRepository>()
.AddSingleton <AutoMapper.IConfigurationProvider>(new MapperConfiguration(cfg => cfg.AddMaps(typeof(EnrollmentProfile).GetTypeInfo().Assembly)))
.AddTransient <IMapper>(sp => new Mapper(sp.GetRequiredService <AutoMapper.IConfigurationProvider>(), sp.GetService))
.BuildServiceProvider();
EnrollmentContext context = serviceProvider.GetRequiredService <EnrollmentContext>();
context.Database.EnsureCreated();
Task.Run(async() => await Seed_Database(serviceProvider.GetRequiredService <IEnrollmentRepository>())).Wait();
}
public static void Initialize(EnrollmentContext context)
{
context.Database.EnsureCreated();
// Look for enrollment.
if (context.Enrollments.Any())
{
return; // DB has been seeded
}
var students = new Student[]
{
new Student {
StudentID = 1, Age = 23, FirstName = "Lebron", LastName = "James", Mail = "*****@*****.**"
},
new Student {
StudentID = 2, Age = 25, FirstName = "Pablo", LastName = "Aimar", Mail = "*****@*****.**"
},
new Student {
StudentID = 3, Age = 27, FirstName = "Pablo", LastName = "Escobar", Mail = "*****@*****.**"
},
new Student {
StudentID = 4, Age = 29, FirstName = "Estrella", LastName = "Torres", Mail = "*****@*****.**"
},
new Student {
StudentID = 5, Age = 21, FirstName = "Karen", LastName = "Alga", Mail = "*****@*****.**"
}
};
foreach (Student s in students)
{
context.Students.Add(s);
}
context.SaveChanges();
var teachers = new Teacher[]
{
new Teacher {
TeacherID = 1, Name = "Marcelo", LastName = "Bielsa"
},
new Teacher {
TeacherID = 2, Name = "Erwe", LastName = "Von Esse"
},
new Teacher {
TeacherID = 3, Name = "Ruben", LastName = "Aguirre"
}
};
foreach (Teacher t in teachers)
{
context.Teachers.Add(t);
}
context.SaveChanges();
var courses = new Course[]
{
new Course {
CourseID = 1050, Title = "Chemistry", IsOpen = true, MaxPaticipants = 10, TeacherID = 1
},
new Course {
CourseID = 4022, Title = "Microeconomics", IsOpen = false, MaxPaticipants = 10, TeacherID = 2
},
new Course {
CourseID = 1045, Title = "Calculus", IsOpen = true, MaxPaticipants = 5, TeacherID = 3
},
new Course {
CourseID = 3141, Title = "Trigonometry", IsOpen = true, MaxPaticipants = 10, TeacherID = 1
},
new Course {
CourseID = 2021, Title = "Composition", IsOpen = true, MaxPaticipants = 10, TeacherID = 2
},
new Course {
CourseID = 2042, Title = "Literature", IsOpen = true, MaxPaticipants = 10, TeacherID = 3
}
};
foreach (Course c in courses)
{
context.Courses.Add(c);
}
context.SaveChanges();
var enrollments = new Enrollment[]
{
new Enrollment {
StudentID = 1, CourseID = 1050
},
new Enrollment {
StudentID = 1, CourseID = 4022
},
new Enrollment {
StudentID = 1, CourseID = 1045
},
new Enrollment {
StudentID = 2, CourseID = 1045
},
new Enrollment {
StudentID = 2, CourseID = 3141
},
new Enrollment {
StudentID = 2, CourseID = 2021
},
new Enrollment {
StudentID = 3, CourseID = 1050
},
new Enrollment {
StudentID = 3, CourseID = 2042
},
new Enrollment {
StudentID = 4, CourseID = 4022
},
new Enrollment {
StudentID = 4, CourseID = 1045
},
new Enrollment {
StudentID = 5, CourseID = 2021
},
new Enrollment {
StudentID = 5, CourseID = 3141
},
};
foreach (Enrollment e in enrollments)
{
context.Enrollments.Add(e);
}
context.SaveChanges();
}
