-
Notifications
You must be signed in to change notification settings - Fork 881
Lazy Loading
中文 | English
FreeSql 支持导航属性延时加载，即当我们需要用到的时候才进行加载（读取），支持1对1、多对1、1对多、多对多关系的导航属性。
当我们希望浏览某条订单信息的时候，才显示其对应的订单详细记录时，我们希望使用延迟加载来实现，这样不仅加快的了 读取的效率，同时也避免加载不需要的数据。延迟加载通常用于foreach循环读取数据时。
那么我们在定义Model的时候，需要在属性前面添加virtual关键字。如下
public class Order {
[Column(IsPrimary = true)]
public int OrderID { get; set; }
public string OrderTitle { get; set; }
public string CustomerName { get; set; }
public DateTime TransactionDate { get; set; }
public virtual List<OrderDetail> OrderDetails { get; set; }
}
public class OrderDetail {
[Column(IsPrimary = true)]
public int DetailId { get; set; }
public int OrderId { get; set; }
public virtual Order Order { get; set; }
}The lazy loading function is turned off by default. When using this function, please turn it on at the place of declaration.
Lazy loading function depends on FreeSql.Extensions.LazyLoading package, please go to NuGet to download.
static IFreeSql fsql = new FreeSql.FreeSqlBuilder()
.UseConnectionString(FreeSql.DataType.MySql, connectionString)
.UseLazyLoading(true) //Enable Lazy Loading
.UseMonitorCommand(
cmd => Console.WriteLine(cmd.CommandText)) //Monitor SQL commands before execution
//Be sure to define as singleton mode
.Build();
var order = fsql.Select<Order>().Where(a => a.OrderID == 1).ToOne(); //Query from Order table
var orderDetail1 = order.OrderDetails; //First visit, query the database
var orderDetail2 = order.OrderDetails; //Second visit, do not query from the database
var order1 = orderDetail1.FirstOrDefault(); //Access the navigation properties without querying the database at this time. Because this property is already populated when OrderDetails is queriedConsole output:
SELECT a.`OrderID`, a.`OrderTitle`, a.`CustomerName`, a.`TransactionDate`
FROM `Order` a
WHERE (a.`OrderID` = 1)
limit 0,1
SELECT a.`DetailId`, a.`OrderId`
FROM `OrderDetail` a
WHERE (a.`OrderId` = 1)FreeSql lazy loading supports navigation properties of One-to-One, Many-to-One, One-to-Many, and Many-to-Many relationships. The first three are similar. We separately introduce the Many-to-Many relationship.
public partial class Song {
[Column(IsIdentity = true)]
public int Id { get; set; }
public DateTime? Create_time { get; set; }
public bool? Is_deleted { get; set; }
public string Title { get; set; }
public string Url { get; set; }
public virtual ICollection<Tag> Tags { get; set; }
}
public partial class Song_tag {
public int Song_id { get; set; }
public virtual Song Song { get; set; }
public int Tag_id { get; set; }
public virtual Tag Tag { get; set; }
}
public partial class Tag {
[Column(IsIdentity = true)]
public int Id { get; set; }
public int? Parent_id { get; set; }
public virtual Tag Parent { get; set; }
public decimal? Ddd { get; set; }
public string Name { get; set; }
public virtual ICollection<Song> Songs { get; set; }
}As above, there are three tables: Song, Tag, and their relationship table.
var songs = fsql.Select<Song>().Limit(10).ToList(); //Take 10 pieces of song
var songs1 = songs.First().Tags; //First visit, query the database
var songs2 = Songs.First().Tags; //Second visit, do not query from the databaseConsole output:
SELECT a.`Id`, a.`Create_time`, a.`Is_deleted`, a.`Title`, a.`Url`
FROM `Song` a
limit 0,10
SELECT a.`Id`, a.`Parent_id`, a.`Ddd`, a.`Name`
FROM `Tag` a
WHERE (exists(SELECT 1
FROM `Song_tag` b
WHERE (b.`Song_id` = 2 AND b.`Tag_id` = a.`Id`)
limit 0,1))Advantages: Load data only when needed, without pre-planning, avoiding the inefficiency problems caused by various complicated outer joins, indexes, and view operations.
Defect: Interacting with DB many times, performance degradation.
If you want to use data in a loop, use Greed Loading, otherwise use Lazy Loading.