联表查询

1、三表查询联接查询

需要注意的是 Select用的是自动填充这样使用方便,如果需要高性能写法请写成 Select(it=>new Model(){  Id=it.id,Name=it.Name}).ToList();

 public class ViewModelStudent : Student
 {
        public string SchoolName{get;set;}// 类名 + 属性名
 }
 var list = db.Queryable<Student, School, DataTestInfo>((st, sc, di) => new JoinQueryInfos(
              JoinType.Left,st.SchoolId==sc.Id,
              JoinType.Left,st.Name==di.String
            ))
           //.Where((st,sc)=>sc.id>0) 多表条件用法
           .Select<ViewModelStudent>().ToList();
 
 //SELECT 
    //sc.[Name] AS [SchoolName],--自动生成 SchoolName
      //st.[ID] AS [Id],st.[SchoolId] AS [SchoolId],
      //st.[Name] AS [Name],st.[CreateTime] AS [CreateTime]
      //FROM [STudent] st
      //Left JOIN [School] sc ON ( [st].[SchoolId] = [sc].[Id] )  
      //Left JOIN [DataTestInfo] di ON ( [st].[Name] = [di].[String] )



2、两个queryable查询

把一个单 表查询和一个多表查询在进行了一次Join操作

var query1 = db.Queryable<Student, School>((st, sc) => new JoinQueryInfos(
    JoinType.Left,st.SchoolId==sc.Id
))
.Where(st => st.Name == "jack");
var query2 = db.Queryable<DataTestInfo>();
db.Queryable(query1, query2, (p1, p2) => p1.Id == p2.Int1).Select<ViewModelStudent>().ToList();


//SELECT * FROM 
         //    (SELECT          [st].[ID],[st].[SchoolId],[st].[Name],[st].[CreateTime] 
              //                  FROM [STudent] st 
                //                Left JOIN [School] sc ON ([st].[SchoolId]=[sc].[Id] )   
                 //               WHERE ( [st].[Name] = @Name0Join0 ))  p1 
               //
             //  Inner JOIN
               
            // (SELECT          [Int1],[Int2],[String],[Decimal1],[Decimal2],
               //                [Datetime1],[Datetime2],[Image1],[Image2], 
               //               [Guid1],[Guid2],[Money1],[Money2],[Varbinary1],
            //                  [Varbinary2],[Float1],[Float2] FROM [DataTestInfo] )p2   
              
       //     ON ( [p1].[ID] = [p2].[Int1] )


3、简单联表

代码:

var list = db.Queryable<Order, OrderItem, Custom>((o, i, c) => o.Id == i.OrderId&&c.Id == o.CustomId)
                .Select<ViewOrder>()
                .ToList();

 sql:

SELECT  c.[Name] AS [CustomName],
        o.[Id] AS [Id],o.[Name] AS [Name],
        o.[Price] AS [Price],
        o.[CreateTime] AS [CreateTime],
        o.[CustomId] AS [CustomId]         FROM [Order] o  ,[OrderDetail]  i ,[Custom]  c  
         WHERE (( [o].[Id] = [i].[OrderId] ) AND ( [c].[Id] = [o].[CustomId] ))




4、子查询

例子1:

var list = db.Queryable<Order>().Take(10).Select(it => new
{
   customName=SqlFunc.Subqueryable<Custom>().Where(s=>it.CustomId==s.Id).Select(s=>s.Name),
   customName2 = SqlFunc.Subqueryable<Custom>().Where("it.CustomId = id").Where(s => true).Select(s => s.Name)
}).ToList();
SELECT * FROM ( SELECT (SELECT TOP 1 [Name] FROM [Custom] WHERE it.CustomId=id) AS [customName] , 
              (SELECT TOP 1 [Name] FROM [Custom] WHERE ( 1 = 1 )  AND it.CustomId=id)AS[customName2] ,
                 ROW_NUMBER() OVER( ORDER BY GetDate() ) AS RowIndex  FROM [Order] it ) 
T WHERE RowIndex BETWEEN 1 AND 10


例子2:

如果NOT逻辑用 !SqlFunc.Subqueryable

var list2 = db.Queryable<Order>().Where(it => SqlFunc.Subqueryable<OrderItem>().Where(i => i.OrderId == it.Id).Any()).ToList();
SELECT [Id],[Name],[Price],[CreateTime],[CustomId] FROM [Order] it  
WHERE (EXISTS ( SELECT * FROM [OrderDetail] WHERE ( [OrderId] = [it].[Id] ) ))
--等同于 WHERE it.id in(SELECT OrderId from orderdetail )



5、将多表转成一表,实现动态查询

var pageJoin = db.Queryable<Student, School>((st, sc) => new object[]
    {
        JoinType.Left,st.SchoolId==sc.Id
    })
    .Select((st,sc) => new
    { 
        id = st.Id,
        name = sc.Name
    })
    .MergeTable()
    .Where(it=>it.id==1).OrderBy("name asc").ToList();//别名不限
SELECT * FROM 

               (SELECT  
                    [st].[Id] AS [id] , [sc].[Name] AS [name]  
                    FROM [Student] st Left 
                    JOIN [School] sc ON ( [st].[CustomId] = [sc].[Id] )  ) MergeTable--将结果变成一个表  
           
            WHERE ( [id] = @id0 )ORDER BY name asc



6、将多表转成一表后,在查询

  var pageJoin = db.Queryable<Order, Custom>((st, sc) => new object[]
            {
                JoinType.Left,st.CustomId==sc.Id
            })
      
            .Select((st, sc) => new
            {
                id = st.Id,
                name = sc.Name
            })
            .MergeTable()
            .GroupBy(it=>it.id).GroupBy(it=>it.name)
            .Select(it=>new { it.id ,name=it.name+it.id.ToString() }).ToList();
SELECT [id] AS [id] ,
        ([name] + CAST([id] AS NVARCHAR(MAX))) AS [name] 
      
       FROM 
         (SELECT  [st].[Id] AS [id] , [sc].[Name] AS [name]  FROM [Order] st 
          Left JOIN [Custom] sc ON ( [st].[CustomId] = [sc].[Id] )  
          ) MergeTable  GROUP BY [id],[name]