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

// league table query  
var query5 = db. The Queryable < Order > ()  
.LeftJoin < Custom > ((o, cus) = > o. chua ustomId = = cus. Id) / / multiple conditions using && 
.LeftJoin<OrderDetail> ((o,cus,oritem) => o.Id == oritem.OrderId)
.Where( o => o.Id == 1)
.Select((o,cus,o ritem) => new ViewOrder {Id=o.Id,CustomName = cus.Name })
.ToList();   //ViewOrder is a new c 
 
//The generated SQL
SELECT 
  [o].[Id] AS [Id],  [cus].[Name] AS [CustomN ame]FROM
  [Order] o  Left JOIN [Custom] cus ON ([o].[CustomId] = [cus ].[Id])  Left JOIN [OrderDetail] oritem ON ([ o].[I d] = [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 following document 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.I.D) // Just use o 
. OrderBy ((o,i) = > i.x x) / / 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

Alias is recommended to write all, after the convenient maintenance expansion

For example: (o,i,c)=> (o => or (o,i)=> is not recommended)

Manual mapping

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

// the new class 
. The Select ((o, I) = > new class Name {Id = o.I d, Name = o.N ame, SchoolName = i.N ame}). ToList ();  
// anonymous objects 
. Select ((o, I) = > new {Id = o.I d, Name = o.N ame, SchoolName = i.N ame}). ToList ();  
// See the following document 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 said the rest of the field mapping automatically, 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();