自引用
public class PictureCategory
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public virtual int CategoryId { get; set; }
public virtual string Name { get; set; }
public virtual int? ParentCategoryId { get; set; }
[ForeignKey("ParentCategoryId")]
public virtual PictureCategory ParentCategory { get; set; }
public virtual List<PictureCategory> SubCategories { get; set; }
public PictureCategory()
{
SubCategories = new List<PictureCategory>();
}
}
在dbcontext类下添加：
public DbSet<PictureCategory> PictureCategories { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<PictureCategory>()
.HasMany(p => p.SubCategories)
.WithOptional(p => p.ParentCategory);
}
拆分实体到多张表
public class Product
{
public int ProductId { get; set; }
public string Description { get; set; }
public decimal Price { get; set; }
public string ImgUrl { get; set; }
}
在dbcontext类下添加：
public DbSet<Product> Products { get; set; }
public override void OnModelCreating(DbModelBuiler modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Product>()
.Map(p=>{
p.Properties(t=>new { t.ProductId, t.Description, t.Price });
p.ToTable("Product","Chapter2");
})
.Map(p=>{
p.Properties(t=>new { t.ProductId, t.ImgUrl });
p.ToTable("ProductWebInfo","Chapter2");
});
}
分拆一张表到多个实体
public class Photograph
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int PhotoId { get; set; }
public string Title{ get; set; }
public byte[] ThumbnailBits { get; set; }
[ForeignKey("PhotoId")]
public virtual PhotographFullImage PhotographFullImage { get; set; }
}
public class PhotographFullImage
{
[Key]
public int PhotoId { get; set; }
public byte[] HighResolutionBits { get; set; }
[ForeignKey("PhotoId")]
public virtual Photograph Photograph{ get; set; }
}
在dbcontext类下添加：
public DbSet<Photograph> Photographs { get; set; }
public DbSet<PhotographFullImage> PhotographFullImages { get; set; }
modelBuilder.Entity<Photograph>()
.HasRequired(p => p.PhotographFullImage)
.WithRequiredPrincipal(p => p.Photograph);
modelBuilder.Entity<Photograph>().ToTable("Photograph", "Chapter2");
modelBuilder.Entity<PhotographFullImage>().ToTable("Photograph", "Chapter2");
TPT映射
[Table("Bussiness", Schema = "Chapter2")]
public class Bussiness
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int BussinessId { get; set; }
public string Name { get; set; }
public string LicenseNumber { get; set; }
}
[Table("ECommerce", Schema = "Chapter2")]
public class ECommerce : Bussiness
{
public string Url { get; set; }
}
[Table("Retail", Schema = "Chapter2")]
public class Retail : Bussiness
{
public string Address { get; set; }
public string City { get; set; }
public string State { get; set; }
public string ZipCode { get; set; }
}
在dbcontext类下添加：
public DbSet<Bussiness> Bussinesses { get; set; }
获得子类类型时：
dbContext.Bussinesses.OfType<Retail>();
TPH映射
[Table("Employee",Schema = "Chapter2")]
public abstract class Employee
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int EmployeeId { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
public class FullTimeEmployee : Employee
{
public decimal? Salary { get; set; }
}
public class HourlyEmployee : Employee
{
public decimal? Wage { get; set; }
}
在dbcontext类下添加：
public DbSet<Employee> Employees { get; set; }
modelBuilder.Entity<Employee>()
.Map<FullTimeEmployee>(m => m.Requires("EmployeeType").HasValue(1)
.Map<HourlyEmployee>(m => m.Requires("EmployeeType").HasValue(2));
常用查询方法
1.执行非查询sql语句
db.DataBase.ExecuteSqlCommand("delete a where id=xx");
2.执行sql查询语句
db.DataBase.SqlQuery<ADto>("select * from a"); 其重载方法可以传入参数，可以执行存储过程
3.构建ado.net对象
using (var conn = new EntityConnection("name=EFRecipesEntities"))
{
Console.WriteLine("Customers...");
var cmd = conn.CreateCommand();
conn.Open();
cmd.CommandText = @"select c.Name, C.Email from EFRecipesEntities.Customers as c";
using (var reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess))
{
while (reader.Read())
{
Console.WriteLine("{0}'s email is: {1}",
reader.GetString(0), reader.GetString(1));
}
}
}
4.查找主从复合结构关系中的拥有从表记录的主表记录
from blog in db.BlogPost
where blog.Comments.Any()
select blog
5.在查询中设置默认值
from e in context.Employees
select new { Name = e.Name, YearsWorked = e.YearsWorked ?? 0 };
6.从存储过程中返回多结果集
using (var context = new EFRecipesEntities())
{
var cs = @"Data Source=.;Initial Catalog=EFRecipes;Integrated Security=True";
var conn = new SqlConnection(cs);
var cmd = conn.CreateCommand();
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "Chapter3.GetBidDetails";
conn.Open();
var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
var jobs = ((IObjectContextAdapter)context).ObjectContext.Translate<Job>(reader, "Jobs",
MergeOption.AppendOnly).ToList();
reader.NextResult();
((IObjectContextAdapter)context).ObjectContext.Translate<Bid>(reader, "Bids", MergeOption.AppendOnly)
.ToList();
foreach (var job in jobs)
{
Console.WriteLine("
Job: {0}", job.JobDetails);
foreach (var bid in job.Bids)
{
Console.WriteLine(" Bid: {0} from {1}",
bid.Amount.ToString(), bid.Bidder);
}
}
Console.WriteLine("
Press <enter> to continue...");
Console.ReadLine();
}
7.与列表值比较
var cats = new List<string> { "Programming", "Databases" };
var books = from b in context.Books
where cats.Contains(b.Category.Name)
select b;
8.过滤关联实体
using (var context = new EFRecipesEntities())
{
// 显式禁用延迟加载
//如果打开延迟加载，所有的accidents将在我们引用worker的accidents时才加载。这将导致过虑失败
context.Configuration.LazyLoadingEnabled = false;
var query = from w in context.Workers
select new
{
Worker = w,
Accidents = w.Accidents.Where(a => a.Severity > 2)
};
// 匿名类型不会把accidents附加到workers上，
//但是通过把它们带到上下文中，实体框架会填充导航属性，
//将每一个严重事故集合accidents附加到合适的worker上。这个过程一般叫做：Entity Span。
//这是一个强大而微妙的，发生在实体框架实例化实体类型及它们之间关系的幕后的副作用
query.ToList();
var workers = query.Select(r => r.Worker);
Console.WriteLine("Workers with serious accidents...");
foreach (var worker in workers)
{
Console.WriteLine("{0} had the following accidents", worker.Name);
if (worker.Accidents.Count == 0)
Console.WriteLine(" --None--");
foreach (var accident in worker.Accidents)
{
Console.WriteLine(" {0}, severity: {1}",
accident.Description, accident.Severity.ToString());
}
}
}
9.应用左连接
Product与 TopSellings(热销产品)的关系为 1-0...1
from p in context.Products
join t in context.TopSellings on
//注意，我们如何将结果集投影到另一个名为'g'的序列中，
//以及应用DefaultIfEmpty方法,当g为空时用null填充
p.ProductID equals t.ProductID into g
from tps in g.DefaultIfEmpty()
orderby tps.Rating descending
select new
{
Name = p.Name,
Rating = tps.Rating == null ? 0 : tps.Rating
};
10.通过TPH派生类排序
from m in context.Media
let mediumtype = m is Article
? 1
: m is Video ? 2 : 3
orderby mediumtype
select m;
11.按日期分组
var groups = from r in context.Registrations
// 凭借内置的TruncateTime函数提取Date部分
group r by DbFunctions.TruncateTime(r.RegistrationDate)
into g
select g;
12.结果集扁平化
Associates与AssociateSalariess是 1-*的关系
var allHistory = from a in context.Associates
from ah in a.AssociateSalaries.DefaultIfEmpty()
orderby a.Name
select new
{
Name = a.Name,
Salary = (decimal?)ah.Salary,
Date = (DateTime?)ah.SalaryDate
};
13.使用多属性分组
var results = from e in context.Events
// 使用匿名类型封闭复合key State 和City
group e by new { e.State, e.City } into g
select new
{
State = g.Key.State,
City = g.Key.City,
Events = g
};
14.过滤中使用位操作
用整型来做标识位，这个整型的值分别是2的整数次幂，存入数据时用或（|）操作，查询时用与（&）操作
public enum SponsorTypes
{
None = 0,
ContributesMoney = 1,
Volunteers = 2,
IsABoardMember = 4
};
context.Patrons.Add(new Patron
{
Name = "Ryan Keyes",
//注意位操作符中的OR操作符'|'的用法
SponsorType = (int)(SponsorTypes.ContributesMoney |
SponsorTypes.IsABoardMember)
});
var sponsors = from p in context.Patrons
//注意位操作符中的AND操作符'&'的用法
where (p.SponsorType &
(int)SponsorTypes.ContributesMoney) != 0
select p;
15.多列连接（Join)
var orders = from o in context.Orders
join a in context.Accounts on
// 使用匿名类型来构造一个复合的查询表达式
new { Id = o.AccountId, City = o.ShipCity, State = o.ShipState }
equals
new { Id = a.AccountId, City = a.City, State = a.State }
select o;