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
                      • Linq to Sql
                        • Special Note
                          • IQueryable
                            • Where
                              • Specify Fields: Select
                                • CaseWhen
                                  • Join
                                    • LeftJoin
                                      • Multi-table Query: From
                                        • Grouping: GroupBy
                                          • Reference
                                          • withsql
                                            • Parent Child Relationship Query

                                          Linq to Sql

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

                                          On This Page
                                          • Special Note
                                          • IQueryable
                                          • Where
                                          • Specify Fields: Select
                                          • CaseWhen
                                          • Join
                                          • LeftJoin
                                          • Multi-table Query: From
                                          • Grouping: GroupBy
                                          • Reference

                                          # Linq to Sql

                                          Originally not supporting IQueryable is mainly due to the consideration of usage habits. The intelligence of writing code will always prompt a bunch of methods you don't want to use. IQueryable itself provides a bunch of methods that cannot be implemented, as well as external intrusion extension methods, which seriously affect Coding experience. As shown below:

                                          image

                                          For FreeSql v1.4.0+ version, please use the following items to install with commands (old version does not need to be installed):

                                          dotnet add package FreeSql.Extensions.Linq

                                          # Special Note

                                          • Please try not to use the following Linq methods in ISelect mode: GroupJoin, Select, SelectMany, Join and DefaultIfEmpty.

                                          • If you must use the .Select() method in ISelect, be sure to call it before .ToList().

                                          # IQueryable

                                          FreeSql provides a powerful data query object: ISelect.

                                          FreeSql.Extensions.Linq v1.4.0+ implements the common functions of IQueryable query objects for interactive use in various frameworks.

                                          //Convert ISelect to IQueryable
                                          IQueryable<Student> queryable = fsql.Select<Student>().AsQueryable();
                                          
                                          //Linq Query
                                          var t1 = queryable.Where(a => a.id == 1).FirstOrDefault();
                                          
                                          //Restore IQueryable to ISelect
                                          ISelect<Studeng> select = queryable.RestoreToSelect();
                                          
                                          1
                                          2
                                          3
                                          4
                                          5
                                          6
                                          7
                                          8

                                          Note: The implementation of IQueryable does not currently support GroupBy. You can consider using the RestoreSelect method to switch back to ISelect for query.

                                          # Where

                                          var t1 = (
                                              from a in fsql.Select<Student>()
                                              where a.id == item.id
                                              select a
                                          ).ToList();
                                          
                                          1
                                          2
                                          3
                                          4
                                          5

                                          # Specify Fields: Select

                                          var t1 = (
                                              from a in fsql.Select<Student>()
                                              where a.id == item.id
                                              select new { a.id }
                                          ).ToList();
                                          
                                          1
                                          2
                                          3
                                          4
                                          5

                                          # CaseWhen

                                          var t1 = (
                                              from a in fsql.Select<Student>()
                                              where a.id == item.id
                                              select new {
                                                  a.id,
                                                  a.name,
                                                  testsub = new {
                                                      time = a.age > 10 ? "大于" : "小于或等于"
                                                  }
                                              }
                                          ).ToList();
                                          
                                          1
                                          2
                                          3
                                          4
                                          5
                                          6
                                          7
                                          8
                                          9
                                          10
                                          11

                                          # Join

                                          var t1 = (
                                              from a in fsql.Select<Student>()
                                              join b in fsql.Select<School>() on a.id equals b.StudentId
                                              select a
                                          ).ToList();
                                          
                                          var t2 = (
                                              from a in fsql.Select<Student>()
                                              join b in fsql.Select<School>() on a.id equals b.StudentId
                                              select new { a.id, bid = b.id }
                                          ).ToList();
                                          
                                          var t3 = (
                                              from a in fsql.Select<Student>()
                                              join b in fsql.Select<School>() on a.id equals b.StudentId
                                              where a.id == item.id
                                              select new { a.id, bid = b.id }
                                          ).ToList();
                                          
                                          1
                                          2
                                          3
                                          4
                                          5
                                          6
                                          7
                                          8
                                          9
                                          10
                                          11
                                          12
                                          13
                                          14
                                          15
                                          16
                                          17
                                          18

                                          # LeftJoin

                                          var t1 = (
                                              from a in fsql.Select<Student>()
                                              join b in fsql.Select<School>() on a.id equals b.StudentId into temp
                                              from tc in temp.DefaultIfEmpty()
                                              select a
                                          ).ToList();
                                          
                                          var t2 = (
                                              from a in fsql.Select<Student>()
                                              join b in fsql.Select<School>() on a.id equals b.StudentId into temp
                                              from tc in temp.DefaultIfEmpty()
                                              select new { a.id, bid = tc.id }
                                          ).ToList();
                                          
                                          var t3 = (
                                              from a in fsql.Select<Student>()
                                              join b in fsql.Select<School>() on a.id equals b.StudentId into temp
                                              from tc in temp.DefaultIfEmpty()
                                              where a.id == item.id
                                              select new { a.id, bid = tc.id }
                                          ).ToList();
                                          
                                          1
                                          2
                                          3
                                          4
                                          5
                                          6
                                          7
                                          8
                                          9
                                          10
                                          11
                                          12
                                          13
                                          14
                                          15
                                          16
                                          17
                                          18
                                          19
                                          20
                                          21

                                          # Multi-table Query: From

                                          var t1 = (
                                              from a in fsql.Select<Student>()
                                              from b in fsql.Select<School>()
                                              where a.id == b.StudentId
                                              select a
                                          ).ToList();
                                          
                                          var t2 = (
                                              from a in fsql.Select<Student>()
                                              from b in fsql.Select<School>()
                                              where a.id == b.StudentId
                                              select new { a.id, bid = b.id }
                                          ).ToList();
                                          
                                          var t3 = (
                                              from a in fsql.Select<Student>()
                                              from b in fsql.Select<School>()
                                              where a.id == b.StudentId
                                              where a.id == item.id
                                              select new { a.id, bid = b.id }
                                          ).ToList();
                                          
                                          1
                                          2
                                          3
                                          4
                                          5
                                          6
                                          7
                                          8
                                          9
                                          10
                                          11
                                          12
                                          13
                                          14
                                          15
                                          16
                                          17
                                          18
                                          19
                                          20
                                          21

                                          # Grouping: GroupBy

                                          var t1 = (
                                              from a in fsql.Select<Student>()
                                              where a.id == item.id
                                              group a by new {a.id, a.name } into g
                                              select new {
                                                  g.Key.id, g.Key.name,
                                                  cou = g.Count(),
                                                  avg = g.Avg(g.Value.age),
                                                  sum = g.Sum(g.Value.age),
                                                  max = g.Max(g.Value.age),
                                                  min = g.Min(g.Value.age)
                                              }
                                          ).ToList();
                                          
                                          1
                                          2
                                          3
                                          4
                                          5
                                          6
                                          7
                                          8
                                          9
                                          10
                                          11
                                          12
                                          13

                                          # Reference

                                          • 《Query from Multi Tables》
                                          • 《Return Data》
                                          • 《Repository Layer》
                                          • 《FreeSql Optimization: Lazy Loading》
                                          • 《FreeSql Optimization: Greed Loading》
                                          • 《Expression Function》
                                          edit iconEdit this pageopen in new window
                                          Last update: 11/21/2021, 4:13:16 PM
                                          Contributors: luoyunchong
                                          Prev
                                          Greed-Loading
                                          Next
                                          withsql
                                          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