Skip to content
FreeSql DocumentsFreeSql Documents
Guide
NuGetopen in new window
Apiopen in new window
github icon
  • Guide

      • Query Data
        • Pagination
          • Query from Single Table
            • Query from Multi Tables
              • Group Aggregation Query
                • Return Data
                  • Lazy Loading
                    • Greed-Loading
                      • Navigation Properties - ManyToOne
                        • Navigation Properties - OneToMany/ManyToMany
                          • Mutations
                            • IncludeMany Extensions
                              • Comparison of the Two Ways of IncludeMany
                                • Reference
                                • Linq to Sql
                                  • withsql
                                    • Parent Child Relationship Query

                                  Greed-Loading

                                  author iconnicyecalendar iconNovember 21, 2021timer iconAbout 2 minword iconAbout 663 words

                                  On This Page
                                  • Navigation Properties - ManyToOne
                                  • Navigation Properties - OneToMany/ManyToMany
                                  • Mutations
                                  • IncludeMany Extensions
                                  • Comparison of the Two Ways of IncludeMany
                                  • Reference

                                  # Greed-Loading

                                  # Navigation Properties - ManyToOne

                                  ManyToOne navigation properties are loaded by ToList(includeNestedMembers: false), parameter description:

                                  false: Return the navigation data of Level 2 Join (default);

                                  true: Return the navigation data of all levels of depth Join (unused navigation data will not be returned).

                                  Select<Tag>().Include(a => a.Parent.Parent).ToList(true);
                                  
                                  Select<Tag>().Where(a => a.Parent.Parent.Name == "1").ToList(true);
                                  //Write in this way, no need to mark Join, 
                                  //it will be automatically processed into LeftJoin when parsing the expression
                                  
                                  1
                                  2
                                  3
                                  4
                                  5

                                  # Navigation Properties - OneToMany/ManyToMany

                                  IncludeMany greedily loads the navigation properties of the collection. In fact, it is queried twice, and data is assembled after ToList.

                                  Select<Tag>().IncludeMany(a => a.Songs).ToList();
                                  
                                  1

                                  IncludeMany has a second parameter, which can be modified before the second query.

                                  Select<Tag>().IncludeMany(a => a.Songs, 
                                      then => then.Where(song => song.User == "admin")).ToList();
                                  
                                  1
                                  2

                                  In fact, in Then, you can continue to use Include/IncludeMany. As long as you like it, it’s okay to go down 100 levels.

                                  # Mutations

                                  It can also be greedily loaded without configuring the navigation relationship.

                                  Select<Tag>().IncludeMany(a => a.TestManys.Where(b => b.TagId == a.Id));
                                  
                                  1

                                  Only query the first few pieces of data in each sub-collection to avoid poor IO performance caused by loading all data like EfCore (for example, there are 2000 comments under a product).

                                  Select<Tag>().IncludeMany(a => a.TestManys.Take(10));
                                  
                                  1

                                  The sub-collection returns a part of the fields to avoid the problem of too many fields.

                                  Select<Tag>().IncludeMany(a => a.TestManys.Select(b => new TestMany { Title = b.Title ... }));
                                  
                                  1

                                  # IncludeMany Extensions

                                  When the main data already exists in the memory, how to load the sub-data? So we added the List<T> extension method, the example is as follows:

                                  new List<Song>(new[] { song1, song2, song3 })
                                      .IncludeMany(fsql, a => a.Tags);
                                  
                                  1
                                  2
                                  new List<Song>(new[] { song1, song2, song3 })
                                      .IncludeByPropertyName(
                                          orm: fsql, 
                                          property: "Tags", 
                                          where: "ParentId=Code", 
                                          take: 5, 
                                          select: "id,name"
                                      );
                                  //v3.2.605+
                                  
                                  1
                                  2
                                  3
                                  4
                                  5
                                  6
                                  7
                                  8
                                  9

                                  # Comparison of the Two Ways of IncludeMany

                                  Way 1: IncludeMany extensions

                                  var list111 = fsql.Select<SysModule>()
                                      .Page(1, 10)
                                      .ToList(a => new { Id = a.Id }) //Query data id
                                      .Select(a => new SysModule { Id = a.Id }).ToList() //Memory operation
                                      .IncludeMany(fsql, a => a.Permissions, then => then.Include(a => a.Button));
                                  
                                  1
                                  2
                                  3
                                  4
                                  5
                                  SELECT a."Id" as1 
                                  FROM "SysModule" a 
                                  limit 0,10
                                  
                                  SELECT a."Id", a."SysModuleId", a."SysModuleButtonId", a."Status", 
                                  a__Button."Id" as5, a__Button."Name", a__Button."EventName", a__Button."EnCode", a__Button."Icon", a__Button."Sort", a__Button."CreateTime" 
                                  FROM "SysModulePermission" a 
                                  LEFT JOIN "SysModuleButton" a__Button ON a__Button."Id" = a."SysModuleButtonId" 
                                  WHERE ((a."SysModuleId") in ('menu1','menu2'))
                                  
                                  1
                                  2
                                  3
                                  4
                                  5
                                  6
                                  7
                                  8
                                  9

                                  Way 2: Directly IncludeMany + ToList

                                  var list222 = fsql.Select<SysModule>()
                                      .IncludeMany(m => m.Permissions, then => then.Include(a => a.Button))
                                      .Page(1, 10)
                                      .ToList();
                                  
                                  1
                                  2
                                  3
                                  4
                                  SELECT a."Id", a."ParentId", a."Name", a."Icon", a."UrlAddress", a."IsShow", a."Sort", a."Description", a."CreateTime" 
                                  FROM "SysModule" a 
                                  limit 0,10
                                  
                                  SELECT a."Id", a."SysModuleId", a."SysModuleButtonId", a."Status", 
                                  a__Button."Id" as5, a__Button."Name", a__Button."EventName", a__Button."EnCode", a__Button."Icon", a__Button."Sort", a__Button."CreateTime" 
                                  FROM "SysModulePermission" a 
                                  LEFT JOIN "SysModuleButton" a__Button ON a__Button."Id" = a."SysModuleButtonId" 
                                  WHERE ((a."SysModuleId") in ('menu1','menu2'))
                                  
                                  1
                                  2
                                  3
                                  4
                                  5
                                  6
                                  7
                                  8
                                  9

                                  Case: Query Vod table, 10 data for each of category 1, category 2, and category 3

                                  class Vod {
                                      public Guid Id { get; set; }
                                      public int TypeId { get; set; }
                                  }
                                  
                                  //Define a temporary class, it can also be a DTO
                                  class Dto {
                                      public int TypeId { get; set; }
                                      public List<Vod> Vods { get; set; }
                                  }
                                  
                                  var dto = new [] { 1,2,3 }.Select(a => new Dto { TypeId = a }).ToList();
                                  dto.IncludeMany(fsql, d => d.Vods.Take(10).Where(vod => vod.TypeId == d.TypeId));
                                  
                                  //After execution, each element.Vods of DTO will only have 10 records
                                  
                                  1
                                  2
                                  3
                                  4
                                  5
                                  6
                                  7
                                  8
                                  9
                                  10
                                  11
                                  12
                                  13
                                  14
                                  15

                                  # Reference

                                  • 《Query from Multi Tables》
                                  • 《Return Data》
                                  • 《LinqToSql》
                                  • 《Repository Layer》
                                  • 《Filters and Global Filters》
                                  • 《FreeSql Optimization: Lazy Loading》
                                  • 《FreeSql Optimization: Greed Loading》
                                  • 《Expression Function》
                                  edit iconEdit this pageopen in new window
                                  Last update: 5/31/2022, 10:08:53 PM
                                  Contributors: luoyunchong,igeekfan,luoyunchong
                                  Prev
                                  Lazy Loading
                                  Next
                                  Linq to Sql
                                  Copyright © 2018-present nicye
                                  Copyright © 2022 nicye

                                  This app can be installed on your PC or mobile device. This will allow this web app to look and behave like any other installed app. You will find it in your app lists and be able to pin it to your home screen, start menus or task bars. This installed web app will also be able to safely interact with other apps and your operating system.

                                  Description