1.2 Query‐Join (original) (raw)

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() .LeftJoin < Custom > ((o, cus) = > o. chua ustomId = = cus. Id) / / use && multiple conditions .LeftJoin ((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() .LeftJoin((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() .LeftJoin((o,i)=>o.Id == i.OrderId) .LeftJoin((o,i,c)=>o.CustomId == c.Id) .Select().ToList();

Nested query

var q11 = db.Queryable().Where(it => it.Id>1); var q22 = db.Queryable().Where(it => it.Id > 2); var q33 = db.Queryable().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();