public static void QueryCount( )
{
// 已知问题，当使用平均、合计时，short 类型,合转换成 int 所以类型需要是 int
var repository = NormalRepoFactory.Create <AzOrder_Details>();
var result = repository.Query().Select(p => p.ProductID, p => p.ProductName)
.Count(c => c.OrderID, c => c.OrderCount)
.GroupBy(c => c.ProductID)
.InnerJoin <AzProducts>()
.On <AzProducts>((r, l) => r.ProductID == l.ProductID, l => l.ProductName2)
.GroupBy <AzProducts>(p => p.ProductName2)
.Top(10);
Console.WriteLine(result.Sql());
Console.WriteLine();
// SELECT TOP (10) [dbo].[Order Details].[ProductID]
// , [dbo].[Products].[ProductName]
// , SUM([dbo].[Order Details].[Quantity]) AS [Quantity]
// FROM [dbo].[Order Details]
// INNER JOIN [dbo].[Products]
// ON [dbo].[Order Details].[ProductID] = [dbo].[Products].[ProductID]
// GROUP BY [dbo].[Order Details].[ProductID]
// , [dbo].[Products].[ProductName]
// HAVING AVG([dbo].[Order Details].[Quantity]) > 10;
}
public static void QueryAvg( )
{
var repository = NormalRepoFactory.Create <AzOrder_Details>();
var result = repository.Query().Select(p => p.ProductID, p => p.ProductName)
.Avg(c => c.Quantity, c => c.QuantityAvg)
.Avg(c => c.UnitPrice)
.GroupBy(c => c.ProductID)
.InnerJoin <AzProducts>()
.On <AzProducts>((r, l) => r.ProductID == l.ProductID, l => l.ProductName2)
.GroupBy <AzProducts>(p => p.ProductName2)
.Top(10);
Console.WriteLine(result.Sql());
Console.WriteLine();
// SELECT TOP (10) [dbo].[Order Details].[ProductID]
// , [dbo].[Products].[ProductName]
// , AVG([dbo].[Order Details].[Quantity]) AS [QuantityAvg]
// , AVG([dbo].[Order Details].[UnitPrice]) AS [UnitPrice]
// FROM [dbo].[Order Details]
// INNER JOIN [dbo].[Products]
// ON [dbo].[Order Details].[ProductID] = [dbo].[Products].[ProductID]
// GROUP BY [dbo].[Order Details].[ProductID]
// , [dbo].[Products].[ProductName];
}
public static void QueryAvg()
{
var repository = NormalRepoFactory.Create <AzOrder_Details>();
var result = repository.Query().From("a1").Select(p => p.ProductID, alias: "a1", additionalSelectors: p => p.ProductName)
.Avg(c => c.Quantity, c => c.QuantityAvg, "a1")
.Avg(c => c.UnitPrice, "a1")
.GroupBy(c => c.ProductID, "a1")
.InnerJoin <AzProducts>("a2")
.On <AzProducts>((r, l) => r.ProductID == l.ProductID, "a1", "a2", l => l.ProductName2)
.GroupBy <AzProducts>(p => p.ProductName2, "a2")
.Top(10);
Console.WriteLine(result.Sql());
Console.WriteLine();
// SELECT TOP (10) [a1].[ProductID]
// , [a1].[ProductName]
// , AVG([a1].[Quantity]) AS [QuantityAvg]
// , AVG([a1].[UnitPrice]) AS [UnitPrice]
// FROM [dbo].[Order Details] AS [a1]
// INNER JOIN [dbo].[Products] AS [a2]
// ON [a1].[ProductID] = [a2].[ProductID]
// GROUP BY [a1].[ProductID]
// , [a2].[ProductName];
}
public static void DoDelete()
{
var repository = NormalRepoFactory.Create <AzProducts>();
var resultUpdate = repository.Delete().Where(p => p.ProductID == 82);
Console.WriteLine(resultUpdate.Sql());
Console.WriteLine();
// DELETE [dbo].[Products] WHERE [ProductID] = 82;
}
public static void DoUpdateSelectorParam()
{
var repository = NormalRepoFactory.Create <AzProducts>();
var resultUpdate = repository.Update().Set(c => c.ProductName2, "testvalue").Where(p => p.ProductID == 82);
Console.WriteLine(resultUpdate.ParamSql());
Console.WriteLine();
// UPDATE [dbo].[Products]
// SET ProductName = @ProductName2
// WHERE ([dbo].[Products].[ProductID] = 82);
}
public static void DoDeleteWhere()
{
var repository = NormalRepoFactory.Create <AzProducts>();
var resultUpdate = repository.Delete().Where(p => p.ProductID == 82).And(p => p.ProductName2 == "test2");
Console.WriteLine(resultUpdate.Sql());
Console.WriteLine();
// DELETE [dbo].[Products]
// WHERE ([dbo].[Products].[ProductID] = 82 AND [dbo].[Products].[ProductName] = 'test2');
}
public static void DoDeleteEntity()
{
var repository = NormalRepoFactory.Create <AzProducts>();
AzProducts azProducts = new AzProducts {
ProductName2 = "testvalue", ProductID = 82
};
var resultUpdate = repository.Delete().For(azProducts);
Console.WriteLine(resultUpdate.Sql());
Console.WriteLine();
// DELETE [dbo].[Products] WHERE [ProductID] = 82;
}
public static void QueryUnion()
{
var repository = NormalRepoFactory.Create <AzCustomers>();
// 此语句不会参与数据查询，只是作为Union的包裹
// 如果此语句本身也是数据查询，请增加到new List<UnionSql>中
var result = repository.Query()
.Select(c => c.CustomerID, c => c.CompanyName);
var result01 = repository.Query().From("a1")
.Select(c => c.CustomerID, "a1", c => c.CompanyName)
.Where(c => c.CustomerID == "ANATR", "a1");
var result02 = repository.Query()
.Select(c => c.CustomerID, c => c.CompanyName)
.Where(c => c.CustomerID == "FRANK");
var result03 = repository.Query()
.Select(c => c.CustomerID, c => c.CompanyName)
.Where(c => c.CustomerID == "TRADH");
var resultAllSql = result.UnionSql(new List <UnionSql> {
UnionSql.New(result01, UnionType.Union),
UnionSql.New(result02, UnionType.Union),
UnionSql.New(result03, UnionType.Union),
});
Console.WriteLine(resultAllSql);
Console.WriteLine();
// SELECT [_this_is_union].[CustomerID]
// , [_this_is_union].[CompanyName]
// FROM ( SELECT [a1].[CustomerID]
// , [a1].[CompanyName]
// FROM [dbo].[Customers] AS [a1]
// WHERE (([a1].[CustomerID] = 'ANATR'))
// UNION
// SELECT [dbo].[Customers].[CustomerID]
// , [dbo].[Customers].[CompanyName]
// FROM [dbo].[Customers]
// WHERE (([dbo].[Customers].[CustomerID] = 'FRANK'))
// UNION
// SELECT [dbo].[Customers].[CustomerID]
// , [dbo].[Customers].[CompanyName]
// FROM [dbo].[Customers]
// WHERE (([dbo].[Customers].[CustomerID] = 'TRADH')) )
// AS _this_is_union
}
public static void DoUpdateEntityParam()
{
var repository = NormalRepoFactory.Create <AzProducts>();
AzProducts azProducts = new AzProducts {
ProductName2 = "testvalue", ProductID = 82
};
var resultUpdate = repository.Update().For(azProducts);
Console.WriteLine(resultUpdate.ParamSql());
Console.WriteLine();
// UPDATE [dbo].[Products]
// SET ProductName = @ProductName2, SupplierID = @SupplierID, CategoryID = @CategoryID, QuantityPerUnit = @QuantityPerUnit,
// UnitPrice = @UnitPrice, UnitsInStock = @UnitsInStock, UnitsOnOrder = @UnitsOnOrder, ReorderLevel = @ReorderLevel, Discontinued = @Discontinued
// WHERE ProductID = @ProductID;
}
public static void DoUpdateEntity()
{
var repository = NormalRepoFactory.Create <AzProducts>();
AzProducts azProducts = new AzProducts {
ProductName2 = "testvalue", ProductID = 82
};
var resultUpdate = repository.Update().For(azProducts);
Console.WriteLine(resultUpdate.Sql());
Console.WriteLine();
// UPDATE [dbo].[Products]
// SET [ProductName] = 'testvalue', [SupplierID] = NULL, [CategoryID] = NULL, [QuantityPerUnit] = NULL, [UnitPrice] = NULL,
// [UnitsInStock] = NULL, [UnitsOnOrder] = NULL, [ReorderLevel] = NULL, [Discontinued] = 0
// WHERE [ProductID] = 82;
}
public static void DoInsertSelectorParam( )
{
var repository = NormalRepoFactory.Create <AzProducts>();
var resultinsert = repository
.Insert()
.With(c => c.ProductName2, "testvalue");
Console.WriteLine(resultinsert.ParamSql());
Console.WriteLine();
// INSERT [dbo].[Products]([ProductName])
// VALUES(@ProductName2);
// SELECT [ProductName] as ProductName2,[ProductID]
// FROM [dbo].[Products]
// WHERE [ProductID] = SCOPE_IDENTITY();
}
static void Main(string[] args)
{
var repository = NormalRepoFactory.Create <Customers>();
var result = repository.Query()
.Select(e => e.CustomerID, e => e.CompanyName, e => e.Address, e => e.EmployeeID)
.Top(10)
.Where(e => e.CustomerID == "ok")
.And(e => e.Country == "china")
.Where(e => e.Fax == "xxx")
.InnerJoin <Employees>()
.On <Employees>((r, l) => r.CustomerID == l.FirstName, l => l.EmployeeID);
Console.WriteLine(result.Sql());
// var h= result.Go();
Console.ReadLine();
}
public static void DoInsertEntityParam( )
{
var repository = NormalRepoFactory.Create <AzProducts>();
AzProducts azProducts = new AzProducts {
ProductName2 = "testvalue"
};
var resultinsert = repository
.Insert()
.For(azProducts);
Console.WriteLine(resultinsert.ParamSql());
Console.WriteLine();
// INSERT [dbo].[Products]([ProductName],[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued])
// VALUES(@ProductName2,@SupplierID,@CategoryID,@QuantityPerUnit,@UnitPrice,@UnitsInStock,@UnitsOnOrder,@ReorderLevel,@Discontinued);
// SELECT [ProductID],[ProductName] as ProductName2,[SupplierID],[CategoryID],[QuantityPerUnit],[UnitPrice],[UnitsInStock],[UnitsOnOrder],[ReorderLevel],[Discontinued]
// FROM [dbo].[Products]
// WHERE [ProductID] = SCOPE_IDENTITY();
}
public static void QueryWhereIn()
{
var repository = NormalRepoFactory.Create <AzProducts>();
var result = repository.Query().From("a1")
.WhereIn(p => p.ProductName2, new string[] { "test1", "test2" }, "a1");
Console.WriteLine(result.Sql());
Console.WriteLine();
// SELECT [a1].[ProductID]
// , [a1].[ProductName] as [ProductName2]
// , [a1].[SupplierID]
// , [a1].[CategoryID]
// , [a1].[QuantityPerUnit]
// , [a1].[UnitPrice]
// , [a1].[UnitsInStock]
// , [a1].[UnitsOnOrder]
// , [a1].[ReorderLevel]
// , [a1].[Discontinued]
// FROM [dbo].[Products] AS [a1]
// WHERE ([a1].[ProductName] IN ('test1', 'test2'));
}
public static void QueryWhere()
{
var repository = NormalRepoFactory.Create <AzProducts>();
var result = repository.Query().From("a1")
.Where(p => p.ProductName2.Contains("test") && p.ProductID == 12 && p.ProductName2 == "test", alias: "a1");
Console.WriteLine(result.Sql());
Console.WriteLine();
// SELECT [a1].[ProductID]
// , [a1].[ProductName] as [ProductName2]
// , [a1].[SupplierID]
// , [a1].[CategoryID]
// , [a1].[QuantityPerUnit]
// , [a1].[UnitPrice]
// , [a1].[UnitsInStock]
// , [a1].[UnitsOnOrder]
// , [a1].[ReorderLevel]
// , [a1].[Discontinued]
// FROM [dbo].[Products] AS [a1]
// WHERE (((([a1].[ProductName] LIKE '%test%') and ([a1].[ProductID] = 12)) and ([a1].[ProductName] ='test')));
}
/// <summary>
/// 1.test query
/// 2.
/// </summary>
/// <param name="go"></param>
public static void QueryOnly()
{
// [Column("ProductName")]
// public string ProductName2 { get; set; }
// Shoud Show [dbo].[Products].[ProductName] as [ProductName2]
var repository = NormalRepoFactory.Create <AzProducts>();
var result = repository.Query().From("a1").Top(10);
Console.WriteLine(result.Sql());
Console.WriteLine();
// SELECT TOP (10) [a1].[ProductID]
// , [a1].[ProductName] as [ProductName2]
// , [a1].[SupplierID]
// , [a1].[CategoryID]
// , [a1].[QuantityPerUnit]
// , [a1].[UnitPrice]
// , [a1].[UnitsInStock]
// , [a1].[UnitsOnOrder]
// , [a1].[ReorderLevel]
// , [a1].[Discontinued]
// FROM [dbo].[Products] AS [a1];
}
public static void LeftOuterJoin()
{
var repository = NormalRepoFactory.Create <AzProducts>();
var result = repository.Query().From("a1")
.LeftOuterJoin <AzSuppliers>("a2")
.On <AzSuppliers>((l, r) => l.SupplierID == r.SupplierID, "a1", "a2", r => r.CompanyName);
Console.WriteLine(result.Sql());
Console.WriteLine();
// SELECT [a1].[ProductID]
// , [a1].[ProductName] as [ProductName2]
// , [a1].[SupplierID]
// , [a1].[CategoryID]
// , [a1].[QuantityPerUnit]
// , [a1].[UnitPrice]
// , [a1].[UnitsInStock]
// , [a1].[UnitsOnOrder]
// , [a1].[ReorderLevel]
// , [a1].[Discontinued]
// , [a1].[CompanyName] as [Supplier]
// FROM [dbo].[Products] AS [a1]
// LEFT OUTER JOIN [dbo].[Suppliers] AS [a2]
// ON [a1].[SupplierID] = [a2].[SupplierID];
}
public static void QuerySum()
{
// 已知问题，当使用平均、合计时，short 类型,合转换成 int 所以类型需要是 int
var repository = NormalRepoFactory.Create <AzOrder_Details>();
var result = repository.Query().From("a1").Select(p => p.ProductID, "a1", p => p.ProductName)
.Sum(c => c.Quantity, "a1")
.GroupBy(c => c.ProductID, "a1")
.InnerJoin <AzProducts>("a2")
.On <AzProducts>((r, l) => r.ProductID == l.ProductID, "a1", "a2", l => l.ProductName2)
.GroupBy <AzProducts>(p => p.ProductName2, "a2")
.Top(10);
Console.WriteLine(result.Sql());
Console.WriteLine();
// SELECT TOP (10) [a1].[ProductID]
// , [a1].[ProductName]
// , SUM([a1].[Quantity]) AS [Quantity]
// FROM [dbo].[Order Details] AS [a1]
// INNER JOIN [dbo].[Products] AS [a2]
// ON [a1].[ProductID] = [a2].[ProductID]
// GROUP BY [a1].[ProductID]
// , [a2].[ProductName];
}
