示例使用的是最新 SqlRepoEx 2.0.2
可在:https://github.com/AzThinker/SqlRepoEx2.0Demo
或:https://gitee.com/azthinker/SqlRepoEx2.0Demo
演示模块:GettingStartedNorthwind
此演示以 InnerJoin 为案例,LeftJoin,RightJoin和InnerJoin 使用上没区别。
1、新建一个控制台程序;
2、通过Nuget包管理,下载SqlRepoEx.MsSql.Static
3、根据Northwind数据库的 Orders、Customers、Employees三张表结构生成三个简单的同名类;
4、 创建初始方法,初始一个工厂类。
/// <summary>
/// init
/// 创建初始方法,初始一个工厂类。
/// </summary>
static void Init()
{
// Set Connection String
string ConnectionString = "Data Source=(Local);Initial Catalog=Northwind;User ID=test;Password=test";
var connectionProvider = new ConnectionStringConnectionProvider(ConnectionString);
MsSqlRepoFactory.UseConnectionProvider(connectionProvider);
// this Demo is POJO ,So Using SimpleWritablePropertyMatcher()。
// 本例中,使用的是简单类,所以用SimpleWritablePropertyMatcher()来操作属性。
MsSqlRepoFactory.UseWritablePropertyMatcher(new SimpleWritablePropertyMatcher());
}
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 建立一个联接查询
var cust = repoCustomers.Query().Select(c => c.OrderID, c => c.CompanyName, c => c.FirstName, c => c.LastName, c => c.OrderDate)
.InnerJoin<Customers>()
.On<Customers>((r, l) => r.CustomerID == l.CustomerID, l => l.CompanyName)
.InnerJoin<Employees>()
.On<Employees>((k, q) => k.EmployeeID == q.EmployeeID, q => q.FirstName, q => q.LastName)
.Top(10);
8、此查询的实际 SQL 语句
SELECT TOP (10) [dbo].[Orders].[OrderID]
, [dbo].[Customers].[CompanyName]
, [dbo].[Employees].[FirstName]
, [dbo].[Employees].[LastName]
, [dbo].[Orders].[OrderDate]
FROM [dbo].[Orders]
INNER JOIN [dbo].[Customers]
ON [dbo].[Orders].[CustomerID] = [dbo].[Customers].[CustomerID]
INNER JOIN [dbo].[Employees]
ON [dbo].[Orders].[EmployeeID] = [dbo].[Employees].[EmployeeID];
9、查询结果
10258 Ernst Handel Nancy Davolio 1996-07-17 0:00:00;
10270 Wartian Herkku Nancy Davolio 1996-08-01 0:00:00;
10275 Magazzini Alimentari Riuniti Nancy Davolio 1996-08-07 0:00:00;
10285 QUICK-Stop Nancy Davolio 1996-08-20 0:00:00;
10292 Tradi??o Hipermercados Nancy Davolio 1996-08-28 0:00:00;
10293 Tortuga Restaurante Nancy Davolio 1996-08-29 0:00:00;
10304 Tortuga Restaurante Nancy Davolio 1996-09-12 0:00:00;
10306 Romero y tomillo Nancy Davolio 1996-09-16 0:00:00;
10311 Du monde entier Nancy Davolio 1996-09-20 0:00:00;
10314 Rattlesnake Canyon Grocery Nancy Davolio 1996-09-25 0:00:00;