.NetCore中EFCore的使用整理(二)-关联表查询
EF常用处理关联加载的方式有3中:延迟加载(Lazy Loading)、贪婪加载 (Eager Loading)以及显示加载。
一、EF Core 1.1
1.当前的版本,还不支持延迟加载(Lazy Loading),不将来是否支持
2.目前支持贪婪加载:使用Include加载 关联表 的数据,这种方式 使用一条Join的 Sql语句进行查询。
3. 贪婪加载的优势在于仅执行1次SQL查询即返回所需要的结果。但使用JOIN查询在数据库记录条数较多时,多条简单的SQL查询往往比一条复杂的JOIN查询效率要好。
Include语句可以在 一次查询中使用多次 :
ctx.Categories .Include(c => c.Products) .Include(c => c.News);
4.限制加载的方式暂时忽略使用。
二、EF Core 中主外键设置
1.使用数据注释,DataAnnotations模式,这种方式适合Code First或者说手写实体类和自定义主外键 关联。
Menu表
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[Table("Menu")] public partial class Menu { [Key] public int MenuID { get; set; } public string MenuName { get; set; } public string LinkUrl { get; set; } public DateTime AddTime { get; set; } public int SortNumber { get; set; } public int ModelID { get; set; } [ForeignKey("ModelID")] public virtual Model Model { get; set; } }
Model表
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
[Table("Model")] public partial class Model { public Model() { this.Menus = new HashSet<Menu>(); } [Key] public int ModelID { get; set; } public string ModelName { get; set; } public int SortNumber { get; set; } public DateTime AddTime { get; set; } public virtual ICollection<Menu> Menus { get; set; } }
上下文类
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public class MenuModelContext : DbContext { public virtual DbSet<Menu> Menus { get; set; } public virtual DbSet<Model> Models { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;database=MenuModel;Trusted_Connection=True;"); } }
2.使用Fluent API模式,这种方式,是自动DBFirst自动生成实体层的默认方式
Menu表
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public partial class Menu { public int MenuId { get; set; } public string MenuName { get; set; } public string LinkUrl { get; set; } public DateTime AddTime { get; set; } public int SortNumber { get; set; } public int ModelId { get; set; } public virtual Model Model { get; set; } }
Model表
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public partial class Model { public Model() { Menu = new HashSet<Menu>(); } public int ModelId { get; set; } public string ModelName { get; set; } public int SortNumber { get; set; } public DateTime AddTime { get; set; } public virtual ICollection<Menu> Menu { get; set; } }
上下文类:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
public partial class MenuModelContext : DbContext { public virtual DbSet<Menu> Menu { get; set; } public virtual DbSet<Model> Model { get; set; } protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;database=MenuModel;Trusted_Connection=True;"); } protected override void OnModelCreating(ModelBuilder modelBuilder) { modelBuilder.Entity<Menu>(entity => { entity.Property(e => e.MenuId).HasColumnName("MenuID"); entity.Property(e => e.AddTime).HasColumnType("datetime"); entity.Property(e => e.LinkUrl).HasMaxLength(200); entity.Property(e => e.MenuName) .IsRequired() .HasMaxLength(10); entity.Property(e => e.ModelId).HasColumnName("ModelID"); entity.HasOne(d => d.Model) .WithMany(p => p.Menu) .HasForeignKey(d => d.ModelId) .OnDelete(DeleteBehavior.Restrict) .HasConstraintName("FK__Menu__ModelID__25869641"); }); modelBuilder.Entity<Model>(entity => { entity.Property(e => e.ModelId).HasColumnName("ModelID"); entity.Property(e => e.AddTime).HasColumnType("datetime"); entity.Property(e => e.ModelName) .IsRequired() .HasMaxLength(10); }); } }
三、使用Include 获取关联表数据实例
注 :使用Include 方式获取的数据为对应类型的对象,而不是动态类型Dynamic_xxxx
MenuModelContext _Context = new MenuModelContext(); List<Menu> list = _Context.Menus .Include(q => q.Model) //手动指定关联表查询,一对一 .ToList(); foreach (var item in list) { Console.WriteLine(item.MenuName); Console.WriteLine(item.Model); }
MenuModelContext _Context = new MenuModelContext(); List<Model> list = _Context.Models .Include(q => q.Menus) //手动指定关联表查询,一对多 .ToList(); foreach (var item in list) { Console.WriteLine(item.ModelName); Console.WriteLine(item.Menus.Count); }
更多 :
Ef core的其他参考:
http://www.cnblogs.com/libingql/p/3381571.html