这个问题是在处理层级查询上出现的,按照以前的经验当查询A以及A的子集合B[]的时候join一下比分两次查询快,但是当子集合比较多等原因的时候时间不是线性增长而是指数,原因应该是重复数据带来的效率负载增加,比如第一种我的单元测试时间Duration在4-6左右,当增加到3个子集合的时候就在14-22之间了,而Ef中的做法是把每个join结果unicon 起来,看着那么大串的数据不慢才怪,还是乖乖分开了。
时间如图:
测试代码:
public class ModelTesting
{
private EFContext _dbContext;
public ModelTesting()
{
string cnn = System.Configuration.ConfigurationManager.ConnectionStrings[0].ConnectionString;
_dbContext = new EFContext(cnn);
}
[Fact]
public void Test()
{
var key = 1000;
var uid = new Guid("3905858E-A32E-DF11-BA8F-001CF0CD104B");
var myResume = _dbContext.Set<MyUser>()
.Single(u => u.UserId == uid);
myResume.Works.ToList();
myResume.Resumes.ToList();
myResume.Projects.ToList();
myResume.Jobs.ToList();
myResume=_dbContext.Set<MyUser>()
.Include("Resumes")
.Include("Jobs")
.Include("Projects")
.Include("Works")
.Single(u => u.UserId == uid);
}
}
public class EFContext:DbContext
{
public EFContext(string cnn):base(cnn)
{
this.Configuration.LazyLoadingEnabled = true;
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<MyResume>();
modelBuilder.Entity<MyWorkExper>();
modelBuilder.Entity<MyProject>();
modelBuilder.Entity<MyUser>();
}
}
[Table("Resume", Schema = "dbo")]
public class MyResume
{
[Key]
public int Key { get; set; }
[ForeignKey("Owner")]
public Guid UserId { get; set; }
public MyUser Owner { get; set; }
}
[Table("WorkExperience", Schema = "Resume")]
public class MyWorkExper
{
[Key]
public int WorkId { get; set; }
[ForeignKey("Owner")]
public Guid UserId { get; set; }
public MyUser Owner { get; set; }
}
[Table("project",Schema = "Resume")]
public class MyProject
{
[Key]
public int ProjectId { get; set; }
[ForeignKey("Owner")]
public Guid UserId { get; set; }
public MyUser Owner { get; set; }
}
[Table("EnterpriseUser", Schema = "Offer")]
public class MyUser
{
[Key]
[Column("UserGuid")]
public Guid UserId { get; set; }
public virtual ICollection<MyResume> Resumes { get; set; }
public virtual ICollection<MyProject> Projects { get; set; }
public virtual ICollection<MyWorkExper> Works { get; set; }
public virtual ICollection<MyJob> Jobs { get; set; }
}
[Table("Offer", Schema = "Offer")]
public class MyJob
{
[Key]
public long Key
{
get;
set;
}
[ForeignKey("Owner")]
[Required]
public Guid UserID { get; set; }
public virtual MyUser Owner { get; set; }
}
{
private EFContext _dbContext;
public ModelTesting()
{
string cnn = System.Configuration.ConfigurationManager.ConnectionStrings[0].ConnectionString;
_dbContext = new EFContext(cnn);
}
[Fact]
public void Test()
{
var key = 1000;
var uid = new Guid("3905858E-A32E-DF11-BA8F-001CF0CD104B");
var myResume = _dbContext.Set<MyUser>()
.Single(u => u.UserId == uid);
myResume.Works.ToList();
myResume.Resumes.ToList();
myResume.Projects.ToList();
myResume.Jobs.ToList();
myResume=_dbContext.Set<MyUser>()
.Include("Resumes")
.Include("Jobs")
.Include("Projects")
.Include("Works")
.Single(u => u.UserId == uid);
}
}
public class EFContext:DbContext
{
public EFContext(string cnn):base(cnn)
{
this.Configuration.LazyLoadingEnabled = true;
}
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<MyResume>();
modelBuilder.Entity<MyWorkExper>();
modelBuilder.Entity<MyProject>();
modelBuilder.Entity<MyUser>();
}
}
[Table("Resume", Schema = "dbo")]
public class MyResume
{
[Key]
public int Key { get; set; }
[ForeignKey("Owner")]
public Guid UserId { get; set; }
public MyUser Owner { get; set; }
}
[Table("WorkExperience", Schema = "Resume")]
public class MyWorkExper
{
[Key]
public int WorkId { get; set; }
[ForeignKey("Owner")]
public Guid UserId { get; set; }
public MyUser Owner { get; set; }
}
[Table("project",Schema = "Resume")]
public class MyProject
{
[Key]
public int ProjectId { get; set; }
[ForeignKey("Owner")]
public Guid UserId { get; set; }
public MyUser Owner { get; set; }
}
[Table("EnterpriseUser", Schema = "Offer")]
public class MyUser
{
[Key]
[Column("UserGuid")]
public Guid UserId { get; set; }
public virtual ICollection<MyResume> Resumes { get; set; }
public virtual ICollection<MyProject> Projects { get; set; }
public virtual ICollection<MyWorkExper> Works { get; set; }
public virtual ICollection<MyJob> Jobs { get; set; }
}
[Table("Offer", Schema = "Offer")]
public class MyJob
{
[Key]
public long Key
{
get;
set;
}
[ForeignKey("Owner")]
[Required]
public Guid UserID { get; set; }
public virtual MyUser Owner { get; set; }
}