public IQueryable <T> LoadEntities <T, OrderType>(EFPager <T, OrderType> pager) where T : class
{
if (pager.PageSize < 1)
{
pager.PageSize = 1;
}
if (pager.PageIndex < 0)
{
pager.PageIndex = 0;
}
IQueryable <T> query = db.Set <T>().Where <T>(pager.WhereLambds);
int rowsCount = query.Count();
pager.SetTotalCount(rowsCount);
int pagesCount = rowsCount % pager.PageSize == 0 ? (rowsCount / pager.PageSize) : (rowsCount / pager.PageSize + 1);
pager.SetTotalPages(pagesCount);
if (pager.PageIndex >= pagesCount)
{
pager.PageIndex = (pagesCount <= 0?0: pagesCount - 1);
}
if (pager.IsAsc)
{
query = query.OrderBy <T, OrderType>(pager.OrderByLambds);
}
else
{
query = query.OrderByDescending <T, OrderType>(pager.OrderByLambds);
}
query = query.Skip <T>(pager.PageSize * pager.PageIndex).Take <T>(pager.PageSize);
return(query);
}
/// <summary>
/// 获取列表数据
/// </summary>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <returns></returns>
public UIPager GetDataList(int pageIndex, int pageSize)
{
UIPager uip = new UIPager();
EFPager <ChatHistories, DateTime> pager = new EFPager <ChatHistories, DateTime>();
pager.PageIndex = pageIndex;
pager.PageSize = pageSize;
//pager.WhereLambds = x => DbFunctions.DiffDays(x.CreateTime, DateTime.Now) == 0;
pager.WhereLambds = x => 1 == 1;
pager.IsAsc = false;
pager.OrderByLambds = o => o.CreateTime;
IQueryable <ChatHistories> items = LoadEntities <ChatHistories, DateTime>(pager);
uip.PageSize = pager.PageSize;
uip.PageIndex = pager.PageIndex;
uip.TotalCount = pager.TotalCount;
uip.TotalPages = pager.TotalPages;
List <ChatHistoryView> list = new List <ChatHistoryView>();
foreach (var it in items)
{
ChatHistoryView view = new ChatHistoryView();
view.ChatContent = Utils.ReplaceIllegalWord(it.ChatContent);
view.CreateTime = it.CreateTime.ToString("yyyy-MM-dd HH:mm:ss");
view.ClientName = it.ClientName;
view.IdentityMd5 = it.IdentityMd5;
view.ImgIndex = it.ImgIndex;
list.Add(view);
}
uip.ReponseObj = list.OrderBy(x => x.CreateTime).ToList();
return(uip);
}
/// <summary>
/// All tests/work are performed here
/// </summary>
/// <returns></returns>
internal async Task DoWork()
{
// Create DSuite dummy data to play with
await PopulateDummyCars(500);
// Demonstrate the deferred execution POWER of IQueryable!
// Find a small list of cars that we don't need to page through.
FindMultipleUsingHelperClass(_dSuite);
//------- Same query using pure LINQ -----------------
FindMultipleUsingLINQ();
// Ask the DBContextFactory to create the desired DbContext object for use for database access
await CRUDUsingHelperClass();
// Equivalent CRUD using just linq
await CRUDUsingLINQ();
// Demonstrate data PAGING! Construct a pager that specifies the where clause, page size and column order criteria
EFPager <Car> pager = new EFPager <Car>(x => x.Mileage > 50000, 50, x => x.Make, x => x.Model, x => x.Mileage);
do
{
// Note: The database will NOT be accessed during this call.
IQueryable <Car> resultQuery = await EFHelper.QueryEntities <Car, DSuiteContext>(pager);
// Physically realize the data from the database. The act of enumeration causes the current thread
// to block while data is retrieved from the database. To perform this asynchronously, use the RealizeData extension.
resultQuery.DumpData($"\r\n\r\n==============Page {pager.PageIndex} of Car data==============================");
} while (pager.HasNextPage);
Console.WriteLine($"Query {pager}:: {pager.TotalRowCount} total rows in {pager.TotalPages} pages were displayed\r\n\r\n");
// Delete all play data
EFCrud.DeleteAll <Car, DSuiteContext>();
//-------------------- Use Code First ----------------------
// Ask the DBContextFactory to create the desired DbContext object for use for database access.
// NOTE: This is for demo purposes only! The DbContexts for this application were injected
// in the constructor, and this is how you would "USUALLY" access the DbContext to work with.
SchoolContext schoolContext = DBContextFactory.GetDbContext <SchoolContext>();
DbInitializer.Initialize(schoolContext);
IQueryable <Student> studentList = schoolContext.FindMultiple <Student, SchoolContext>((x) => x.LastName.StartsWith("A"), (x) => x.LastName, false);
studentList.DumpData($"\r\n\r\n==============EFCrud.FindMultiple==============================");
// Demonstrate basic CRUD with related tables
Student newStudent = new Student {
FirstMidName = "Tony", LastName = "Franklin", EnrollmentDate = DateTime.Parse("2009-09-09")
};
await schoolContext.Create(newStudent);
Course newCourse = new Course {
CourseID = 5022, Title = "Advanced C#", Credits = 4
};
await schoolContext.Create(newCourse);
Enrollment newEnrollment = new Enrollment {
StudentID = newStudent.Id, CourseID = newCourse.CourseID, Grade = Grade.A
};
await schoolContext.Create(newEnrollment);
// Now find a specific enrollment.
Enrollment enrollmentFound = schoolContext.FindSingle <Enrollment, SchoolContext>(x => x.EnrollmentID == newEnrollment.EnrollmentID);
string grade = enrollmentFound?.Grade.ToString() ?? "??";
Console.WriteLine($"Student({enrollmentFound.Student.FirstMidName} {enrollmentFound.Student.LastName}) Course({enrollmentFound.Course.Title}) Grade({grade})");
// Delete the student that was just added
schoolContext.Delete(newEnrollment);
schoolContext.Delete(newCourse);
schoolContext.Delete(newStudent);
// Find all classes a student is enrolled into. NOTE:!!!! DbContextFactory uses "UseLazyLoadingProxies"
// to ensure that properties "Student" and "Course" are lazy loaded when their properties are accessed.
// If that were not the case, then you've have to manually load the data based on ID values.
newStudent = schoolContext.FindSingle <Student, SchoolContext>(x => x.LastName == "Alonso" && x.FirstMidName == "Meredith");
IQueryable <Enrollment> enrolledList = schoolContext.FindMultiple <Enrollment, SchoolContext>(x => x.StudentID == newStudent.Id);
Console.WriteLine("\r\nMeredith Alonso was inrolled in the following courses:");
foreach (Enrollment enrolled in enrolledList.ToList())
{
Console.WriteLine($"{enrolled.Course.Title} enrolled on {enrolled.Student.EnrollmentDate}");
}
// Use the DbContext classes that were passed in using Dependency Injection
Car myCar = GenerateCar();
_dSuite.Cars.Add(myCar);
_dSuite.SaveChanges();
myCar.TraceInformation("Using DI created context");
Car foundMyCar = (from c in _dSuite.Cars where c.CarId == myCar.CarId select c).FirstOrDefault();
foundMyCar.TraceInformation("Example of how to use LINQ to find an entity");
// Use generic repository pattern (using dependency injection)
foundMyCar = _cars.GetById(myCar.CarId);
foundMyCar.TraceInformation("Found using generic repository");
// Remove the car to clean up the DB
_dSuite.Cars.Remove(myCar);
_dSuite.SaveChanges();
// Use the open generic type provided by dependency injection for the "school" repo
Student Bobby = new Student {
FirstMidName = "Bobby", LastName = "Simpson", EnrollmentDate = DateTime.Parse("2010-08-01")
};
_genericStudentDataRepository.Insert(Bobby);
Bobby.TraceInformation("New student inserted using dependency injection open generic");
Student foundStudent = _genericStudentDataRepository.GetById(Bobby.Id);
foundStudent.TraceInformation("Found student using dependency injection open generic");
_genericStudentDataRepository.Delete(Bobby.Id);
foundStudent = _genericStudentDataRepository.GetById(Bobby.Id);
if (foundStudent == null)
{
Bobby.TraceInformation("Successfully deleted student");
}
else
{
Bobby.TraceInformation("Failed to deleted student");
}
// Perform the same action with the IStudentRepository
Bobby = new Student {
FirstMidName = "Bobby", LastName = "Simpson", EnrollmentDate = DateTime.Parse("2010-08-01")
};
_students.Insert(Bobby);
Bobby.TraceInformation("New student inserted using dependency injection open generic");
foundStudent = _students.GetById(Bobby.Id);
foundStudent.TraceInformation("Found student using dependency injection open generic");
_students.Delete(Bobby.Id);
foundStudent = _students.GetById(Bobby.Id);
if (foundStudent == null)
{
Bobby.TraceInformation("Successfully deleted student");
}
else
{
Bobby.TraceInformation("Failed to deleted student");
}
}
public IQueryable <T> LoadEntities <T, OrderType>(EFPager <T, OrderType> pager) where T : class
{
return(dao.LoadEntities(pager));
}