轻量ORM-SqlRepoEx （六） JOIN

示例使用的是最新 sqlrepoex 2.0.2

可在：https://github.com/azthinker/sqlrepoex2.0demo

或：https://gitee.com/azthinker/sqlrepoex2.0demo

演示模块：gettingstartednorthwind

 

1、新建一个控制台程序；

2、通过nuget包管理，下载sqlrepoex.mssql.static

3、根据northwind数据库的 orders、customers、employees三张表结构生成三个简单的同名类；

4、 创建初始方法，初始一个工厂类。

 1 /// <summary>
 2         /// init
 3         /// 创建初始方法，初始一个工厂类。
 4         /// </summary>
 5         static void init()
 6         {
 7             // set connection string
 8             string connectionstring = "data source=(local);initial catalog=northwind;user id=test;password=test";
 9             var connectionprovider = new connectionstringconnectionprovider(connectionstring);
10             mssqlrepofactory.useconnectionprovider(connectionprovider);
11 
12             // this demo is pojo ,so using simplewritablepropertymatcher()。
13             // 本例中，使用的是简单类，所以用simplewritablepropertymatcher()来操作属性。
14             mssqlrepofactory.usewritablepropertymatcher(new simplewritablepropertymatcher());
15         }

5、为了演示join 在 orders类增加几个相关字段

（1）、关联customers

　　public string companyname { get; set; }

 （2）、  关联employees

　　public string lastname { get; set; }

　　public string firstname { get; set; }

 6、实例一个orders仓储

 　　 var repocustomers = mssqlrepofactory.create<orders>();

 7、使用 sqlrepoex 建立一个联接查询

1 var cust = repocustomers.query().select(c => c.orderid, c => c.companyname, c => c.firstname, c => c.lastname, c => c.orderdate)
2                                   .innerjoin<customers>()
3                                   .on<customers>((r, l) => r.customerid == l.customerid, l => l.companyname)
4                                   .innerjoin<employees>()
5                                   .on<employees>((k, q) => k.employeeid == q.employeeid, q => q.firstname, q => q.lastname)
6                                   .top(10);

8、此查询的实际 sql 语句

 

console.writeline(cust.sql());

...

 1 select top (10) [dbo].[orders].[orderid]
 2     , [dbo].[customers].[companyname]
 3     , [dbo].[employees].[firstname]
 4     , [dbo].[employees].[lastname]
 5     , [dbo].[orders].[orderdate]
 6 from [dbo].[orders]
 7     inner join [dbo].[customers]
 8     on [dbo].[orders].[customerid] = [dbo].[customers].[customerid]
 9     inner join [dbo].[employees]
10     on [dbo].[orders].[employeeid] = [dbo].[employees].[employeeid];

 

 

 

9、查询结果

 

 1 /// <summary>
 2         /// join 演示
 3         /// </summary>
 4         public static void dojoin()
 5         {
 6             var repocustomers = mssqlrepofactory.create<orders>();
 7 
 8 
 9             var cust = repocustomers.query().select(c => c.orderid, c => c.companyname, c => c.firstname, c => c.lastname, c => c.orderdate)
10                                   .innerjoin<customers>()
11                                   .on<customers>((r, l) => r.customerid == l.customerid, l => l.companyname)
12                                   .innerjoin<employees>()
13                                   .on<employees>((k, q) => k.employeeid == q.employeeid, q => q.firstname, q => q.lastname)
14                                   .top(10);
15 
16             console.writeline(cust.sql());
17 
18             foreach(var item in cust.go())
19             {
20                 console.writeline($"{item.orderid}\t{item.companyname}\t{item.firstname}\t{item.lastname}\t{item.orderdate};");
21             }
22 
23         }

 

 1 10258   ernst handel    nancy   davolio 1996-07-17 0:00:00;
 2 10270   wartian herkku  nancy   davolio 1996-08-01 0:00:00;
 3 10275   magazzini alimentari riuniti    nancy   davolio 1996-08-07 0:00:00;
 4 10285   quick-stop      nancy   davolio 1996-08-20 0:00:00;
 5 10292   tradi??o hipermercados  nancy   davolio 1996-08-28 0:00:00;
 6 10293   tortuga restaurante     nancy   davolio 1996-08-29 0:00:00;
 7 10304   tortuga restaurante     nancy   davolio 1996-09-12 0:00:00;
 8 10306   romero y tomillo        nancy   davolio 1996-09-16 0:00:00;
 9 10311   du monde entier nancy   davolio 1996-09-20 0:00:00;
10 10314   rattlesnake canyon grocery      nancy   davolio 1996-09-25 0:00:00;