1.2 Query‐Join

Join usage

Syntactic sugars 1 and 2 are the same in Where OrderBy GroupBy Select. The only difference is that they are joined in a different way. Everything else is the same

Syntactic sugar 1

Pros: Easy to understand, 5 tables less than the joint table is very cool, support full function Disadvantages: After more than 5 tables (x,b,c...) Can be ugly, syntactic sugar 2 can compensate Table and table left join new Grammar

//Multi-table query
var query5 = db.Queryable<Order>()
.LeftJoin < Custom > ((o, cus) = > o. chua ustomId = = cus. Id) / / use && multiple conditions
.LeftJoin<OrderDetail> ((o,cus,oritem) => o.Id == oritem.OrderId)
.Where(o => o.Id == 1)
.Select((o,cus,oritem) => new ViewOrder {Id=o.Id,CustomName = cus.Name })
.ToList();   //ViewOrder is a newly created class. See the following documentation for more Select usage
 
//The generated SQL
SELECT
  [o].[Id] AS [Id],
  [cus].[Name] AS [CustomName]
FROM
  [Order] o
  Left JOIN [Custom] cus ON ([o].[CustomId] = [cus].[Id])
  Left JOIN [OrderDetail] oritem ON ([o].[Id] = [oritem].[OrderId])
WHERE
  ([o].[Id] = @Id0)

Syntactic sugar 2

You can Join the table directly in this way if all are Inner joins

var list = db.Queryable<Order, OrderItem, Custom>((o, i, c) => o.Id == i.OrderId&&c.Id == o.CustomId)
.Select((o,i,c)=>new Class1{ Id=o.Id,Name=o.Name,CustomName=c.Name})
.ToList();  //Class1 is a newly created class. See the documentation below for more Select usage
//sql:
SELECT  c.[Name] AS [CustomName],
      o.[Id] AS [Id],
      o.[Name] AS [Name]       
      FROM [Order] o  ,[OrderDetail]  i ,[Custom]  c  
     WHERE (( [o].[Id] = [i].[OrderId] ) AND ( [c].[Id] = [o].[CustomId] ))

Where usage

Note: Write before.select ()

.Where (o=>o.id==1) // Just use o 
.Where ((o,i) = > i.xx = = 1) / / if I need so write 

Usage of OrderBy

Note: Write before.select ()

.OrderBy (o=> o.Id) // Just use o 
.OrderBy ((o,i) = > i.xx) / / if "i" need so write 

GroupBy Usage

Note: Write before.select ()

.GroupBy (o=> o.Id) // Just use o 
.GroupBy ((o,i) = > i.xx) / / if  "i" need so write 

Usage of Select

Select location:

The normal situation is generally followed by.Where(..) .OrderBy(..) .Select(..) .ToList()

If Where etc is to be written after Select it should use Select(...) .MergeTable().Where

Manual mapping

Select write a few columns check a few columns, not much

// New class
Select ((o, I) = > new class Name {Id = o.I d, Name = o.N ame, SchoolName = i.N ame}). ToList ();
// Anonymous object
.Select((o,i)=>new {Id=o.Id,Name=o.Name,SchoolName=i.Name}).ToList();
// See the documentation below for more usage

Entity Automatic Mapping1

var list4=db.Queryable<SchoolA>()
.LeftJoin<StudentA>((x, y) => (x.SchoolId == y.SchoolId))
.Select((x,y) => new UnitView01()
{
   Name=x.SchoolName,
   Count=100
},true)//true indicates that the remaining fields are automatically mapped, according to the field name
.ToList();
//SQL:
SELECT    [x].[ID] AS [id] , --automatic 
          [x].[Time] AS [Time] , --automatic
          [x].[SchoolName] AS [Name] --manual
          100 as [Count]  --manual
          FROM [SchoolA] x
          Left JOIN StudentA  y  ON ( [x].[SchoolId] =[y].[SchoolId])

Entity Automatic Mapping2

public class ViewOrder
{
 public string Name { get; set; } // name in ORDER table Main table rule [Field name]
 public string CustomName { get; set; }//Query is the name in Custom from the table rule [class+ field name]
 public string OrderItemPrice { get; set; }// Queries the name in OrderItem from the table rule [class+ field name]
}
var viewModel= db.Queryable<Order>()
             .LeftJoin<OrderItem>((o,i)=>o.Id == i.OrderId)
             .LeftJoin<Custom>((o,i,c)=>o.CustomId == c.Id)
              .Select<ViewOrder>().ToList();

Nested query

 var q11 = db.Queryable<Order>().Where(it => it.Id>1);
 var q22 = db.Queryable<Order>().Where(it => it.Id > 2);
 var q33 = db.Queryable<Order>().Where(it => it.Id > 3);
  
  var list= q11.LeftJoin(q22, (x, y) => x.Id == y.Id)
         .LeftJoin(q33, (x, y, z) => x.Id == z.Id)
         .ToList();