《Entity Framework 6 Recipes》中文翻译系列 (17) -----第三章 查询之分页、过滤和使用DateTime中的日期部分分组

using (var context = new EFRecipesEntities())
{
// 删除之前的数据
context.Database.ExecuteSqlCommand("delete from chapter3.customer");
// 添加新的测试数据
context.Customers.Add(new Customer
{
Name = "Roberts, Jill",
Email = "jroberts@abc.com"
});
context.Customers.Add(new Customer
{
Name = "Robertson, Alice",
Email = "arob@gmail.com"
});
context.Customers.Add(new Customer
{
Name = "Rogers, Steven",
Email = "srogers@termite.com"
});
context.Customers.Add(new Customer
{
Name = "Roe, Allen",
Email = "allenr@umc.com"
});
context.Customers.Add(new Customer
{
Name = "Jones, Chris",
Email = "cjones@ibp.com"
});
context.SaveChanges();
} using (var context = new EFRecipesEntities())
{
string match = "Ro";
int pageIndex = ;
int pageSize = ; var customers = context.Customers.Where(c => c.Name.StartsWith(match))
//var customers = context.Customers.Where(c => c.Name.Contains(match))
.OrderBy(c => c.Name)
.Skip(pageIndex * pageSize)
.Take(pageSize);
Console.WriteLine("Customers Ro*");
foreach (var customer in customers)
{
Console.WriteLine("{0} [email: {1}]", customer.Name, customer.Email);
}
} using (var context = new EFRecipesEntities())
{
string match = "Ro%";
int pageIndex = ;
int pageSize = ; var esql = @"select value c from Customers as c
where c.Name like @Name
order by c.Name
skip @Skip limit @Limit";
Console.WriteLine("\nCustomers Ro*");
var customers = ((IObjectContextAdapter)context).ObjectContext.CreateQuery<Customer>(esql, new[]
{
new ObjectParameter("Name",match),
new ObjectParameter("Skip",pageIndex * pageSize),
new ObjectParameter("Limit",pageSize)
});
foreach (var customer in customers)
{
Console.WriteLine("{0} [email: {1}]", customer.Name, customer.Email);
}
} Console.WriteLine("\nPress <enter> to continue...");
Console.ReadLine();

Customers Ro*
Roberts, Jill [email: jroberts@abc.com]
Robertson, Alice [email: arob@gmail.com]
Roe, Allen [email: allenr@umc.com]
Customers Ro*
Roberts, Jill [email: jroberts@abc.com]
Robertson, Alice [email: arob@gmail.com]
Roe, Allen [email: allenr@umc.com]

public class Registration
{
public int RegistrationId { get; set; }
public string StudentName { get; set; }
public DateTime? RegistrationDate { get; set; }
}

public class EFRecipesEntities : DbContext
{
public EFRecipesEntities()
: base("ConnectionString") {}
public DbSet<Registration> Registrations { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Entity<Registration>().ToTable("Chapter3.Registration");
base.OnModelCreating(modelBuilder);
}
}

using (var context = new EFRecipesEntities())
{
// 删除之前的测试数据
context.Database.ExecuteSqlCommand("delete from chapter3.registration");
// 添加新的测试数据
context.Registrations.Add(new Registration
{
StudentName = "Jill Rogers",
RegistrationDate = DateTime.Parse("12/03/2009 9:30 pm")
});
context.Registrations.Add(new Registration
{
StudentName = "Steven Combs",
RegistrationDate = DateTime.Parse("12/03/2009 10:45 am")
});
context.Registrations.Add(new Registration
{
StudentName = "Robin Rosen",
RegistrationDate = DateTime.Parse("12/04/2009 11:18 am")
});
context.Registrations.Add(new Registration
{
StudentName = "Allen Smith",
RegistrationDate = DateTime.Parse("12/04/2009 3:31 pm")
});
context.SaveChanges();
} using (var context = new EFRecipesEntities())
{
var groups = from r in context.Registrations
// 凭借内置的TruncateTime函数提取Date部分
group r by DbFunctions.TruncateTime(r.RegistrationDate)
into g
select g;
foreach (var element in groups)
{
Console.WriteLine("\nRegistrations for {0}",
((DateTime)element.Key).ToShortDateString());
foreach (var registration in element)
{
Console.WriteLine("\t{0}", registration.StudentName);
}
}
} Console.WriteLine("\nPress <enter> to continue...");
Console.ReadLine();

Registrations for //
Jill Rogers
Steven Combs
Registrations for //
Robin Rosen
Allen Smit