public void Test()
{
using (var conn = new OracleConnection(oracleConnection))
{
conn.Open();
EntityCache.Register(typeof(ResourceMapping));
CodeFirst codeFirst = new CodeFirst(conn);
codeFirst.SyncStructure();
DateTime dateTime = DateTime.Now.AddDays(-10);
var test = conn.QuerySet <Comment>()
.ToList();
var comm = conn.QuerySet <Comment>().PageList(1, 10);
var getIfTest = conn.QuerySet <Comment>()
.Where(x => x.Content.In(new string[] { "1", "2", "3" }))
.Get(false, x => new CommentDto()
{
Id = x.Id,
ArticleIds = x.ArticleId
}, x => new CommentDto()
{
Id = x.Id,
Content = x.Content
});
//单个属性返回
var ContentList = conn.QuerySet <Comment>()
.Where(x => x.Content.IsNotNull() && x.Content != "")
.WhereIf(!string.IsNullOrEmpty("aaa"), x => x.ArticleId == 1, x => x.ArticleId == 2)
.ToList(x => new CommentDto()
{
Id = x.Id,
ArticleIds = x.ArticleId,
count = conn.QuerySet <News>().Where(y => y.Id == x.ArticleId).Count(),
NewsList = conn.QuerySet <News>().Where(y => y.Id == x.ArticleId).ToList(y => new NewsDto()
{
Id = y.Id,
Contents = y.Content
}).ToList(),
NewsDto = conn.QuerySet <News>().Where(y => y.Id == x.ArticleId).Get(y => new NewsDto()
{
Id = y.Id,
Contents = y.Content
})
});
var commne = conn.QuerySet <Comment>()
.Where(x => x.Id > 0)
.Get(x => new
{
x.Id,
x.Content
});
//翻页
var comment1 = conn.QuerySet <Comment>()
.Join <Comment, News>((a, b) => a.ArticleId == b.Id)
.Where(x => x.Id.Between(80, 100) &&
x.SubTime.AddDays(-10) < DateTime.Now && x.Id > 10 &&
x.Id > conn.QuerySet <News>().Where(y => y.Id < 3 && x.Id < y.Id).Sum(y => y.Id))
.From <Comment, News>()
.OrderBy <News>(x => x.Id)
.PageList(1, 1, (a, b) => new
{
test = new List <int>()
{
3, 3, 1
}.FirstOrDefault(y => y == 1),
aaa = "6666" + "777",
Content = a.Content + "'test'" + b.Headlines + a.IdentityId,
bbb = conn.QuerySet <Comment1>()
.Where(y => y.ArticleId == b.Id && y.Content.Contains("test"))
.Sum(x => x.Id),
ccc = a.IdentityId,
a.Id,
times = DateTime.Now
});
//多视图查询
var comment5 = conn.QuerySet <Comment>()
.Join <Comment, News>((a, b) => a.ArticleId == b.Id)
.Join <Comment, ResourceMapping>((a, b) => a.Id == b.FKId)
.Where(x => x.Content == "test")
.From <Comment, News, ResourceMapping>()
.OrderBy <News>(x => x.Id)
.Where((a, b, c) => a.ArticleId == b.Id)
.PageList(1, 10, (a, b, c) => new
{
id = a.Id,
name = b.NewsLabel,
resources = c.RPath,
});
//计总
var sum = conn.QuerySet <Comment>()
.Join <Comment, News>((a, b) => a.ArticleId == b.Id)
.Where(x => x.Content == "test")
.WithNoLock()
.Sum(x => x.Id);
//计数
var count = conn.QuerySet <Comment>()
.Join <Comment, News>((a, b) => a.ArticleId == b.Id)
.Where(x => x.Content == "test1")
.Where <News>(x => x.NewsLabel.Contains("足球"))
.WithNoLock()
.Count();
}
}
public void Test()
{
// using (var conn = new SqlConnection(mssqlConnection))
// {
//var aaa = conn.QuerySet<Comment>().Where(x => 1 == 1).ToList();
////单个属性返回
//var ContentList = conn.QuerySet<Comment>()
// .AsTableName(typeof(Comment), "Comment_4")
// .Where(x => x.Id > 0)
// .ToList(x => x.Content);
// //单条记录
// var commne = conn.QuerySet<Comment>()
// .AsTableName(typeof(Comment), "Comment_4")
// .Where(x => x.Id > 0)
// .Get();
// //翻页
// var comment1 = conn.QuerySet<Comment>()
// .Join<Comment, News>((a, b) => a.ArticleId == b.Id)
// .Where(x => x.Id.Between(80, 100)
// && x.SubTime.AddDays(-10) < DateTime.Now && x.Id > 10
// && x.Id > new QuerySet<News>(conn, new MsSqlProvider()).Where(y => y.Id < 3 && x.Id < y.Id).Sum<News>(y => y.Id))
// .From<Comment, News>()
// .OrderBy<News>(x => x.Id)
// .PageList(1, 1, (a, b) => new
// {
// test = new List<int>() { 3, 3, 1 }.FirstOrDefault(y => y == 1),
// aaa = "6666" + "777",
// Content = a.Content + "'test'" + b.Headlines + a.IdentityId,
// bbb = new QuerySet<Comment>(conn, new MsSqlProvider())
// .Where(y => y.ArticleId == b.Id && y.Content.Contains("test"))
// .Sum<Comment>(x => x.Id),
// ccc = a.IdentityId,
// ddd = Convert.ToInt32("(select count(1) from Comment)"),
// a.Id,
// cccTime = DateTime.Now
// });
// //计总
// var sum = conn.QuerySet<Comment>()
// .Join<Comment, News>((a, b) => a.ArticleId == b.Id)
// .Where(x => x.Content == "test")
// .WithNoLock()
// .Sum<News>(x => x.Id);
// //计数
// var count = conn.QuerySet<Comment>()
// .Join<Comment, News>((a, b) => a.ArticleId == b.Id)
// .Where(x => x.Content == "test1")
// .Where<News>(x => x.NewsLabel.Contains("足球"))
// .WithNoLock()
// .Count();
// }
SqlMapper.Aop.OnExecuting += Aop_OnExecuting;
using (var connection = new SqlConnection("server=localhost;database=Lige;user=sa;password=!RisingupTech/././.;max pool size=300"))
{
EntityCache.Register(typeof(Comment));
//测试codefirst
CodeFirst codeFirst = new CodeFirst(connection);
codeFirst.SyncStructure();
string account = "admin";
string password = "******";
var adminUser = connection.QuerySet <AdminUser>()
.Where(x => x.Account == account && x.Password == password)
.Where(x => x.IsDelete == false && x.IsDelete != x.IsDelete)
.Get();
var outStockList = new List <string>()
{
"6FACCBB4-C378-4CE0-8BAB-37D16B612426",
"6FACCBB4-C378-4CE0-8BAB-37D16B612426",
"C7F4300A-5166-4177-A45A-8E77027D6668",
"97206B94-EEC1-443A-8B37-D359D5206986",
"19D1D6F9-E311-490B-A692-1B2858C0D9B0",
"1DBA06C6-DFCA-484C-ABF4-293D7A43ED11",
"ED761DEA-C57D-4B9C-8000-E434FE280337",
}.ToArray();
//把有库存的恢复
connection.CommandSet <Product>()
.Where(x => outStockList.Contains(x.ProductCode))
.Update(x => new Product()
{
IsDelete = false,
UpdateUser = "******",
UpdateDate = DateTime.Now
});
var pageList = connection.QuerySet <Lige.Model.Order>()
.WhereIf(0 != 0, x => x.IsDelete == false && x.Status == 0, x => x.IsDelete == false)
.OrderByDescing(x => x.CreateDate)
.PageList(1, 10, x => new OrderResDto()
{
Id = x.Id,
OrderNo = x.OrderNo,
OrderTime = x.CreateDate,
Status = x.Status,
Amount = x.Amount,
Point = x.Point,
IsAnyOrderDetail = Convert.ToBoolean(connection.QuerySet <OrderDetail>().Where(y => y.OrderNo == x.OrderNo).Count()),
OrderDetailList = connection.QuerySet <OrderDetail>()
.Where(y => y.IsDelete == false && y.OrderNo == x.OrderNo)
.WhereIf(1 == 1, y => y.IsDelete == false && y.OrderNo == x.OrderNo, y => y.IsDelete == false)
.Join <OrderDetail, Product>((a, b) => a.ProductCode == b.ProductCode, JoinMode.LEFT, true)
.From <OrderDetail, Product>()
.OrderBy <Product>(y => y.Id)
.ToList((a, b) => new OrderDetailResDto()
{
Id = a.Id,
Name = a.ProductName,
Point = a.Point,
Price = a.Price,
Qty = a.Qty,
OriginalPrice = b.Price,
OriginalPoint = b.Point,
}),
DetailList = connection.QuerySet <OrderDetail>().Where(y => y.OrderNo == x.OrderNo).Get(),
});
//List<string> N5_ProfileIdList = new List<string>() { "510002443", "510002444", "510002445", "510002446", "510002447", "510002449", "510002458" };
//var list = connection.QuerySet<PurchaseTransaction>()
// .Where(x => x.N5_ProfileId.In(N5_ProfileIdList.ToArray()) && x.TransactionAmount >= 30 && x.SqlId > 0
// && x.CreateDateTime >= Convert.ToDateTime("2019-11-28 00:33:33.627") && !(x.InvoiceNumber.Contains("LK")))
// .OrderBy(x => x.SqlId)
// .ToList(x => new PurchaseTransaction()
// {
// SqlId = x.SqlId,
// PhysicalCardId = x.PhysicalCardId,
// TransactionAmount = x.TransactionAmount,
// MachineId = x.MachineId,
// N5_ProfileId = x.N5_ProfileId,
// Point = Convert.ToInt32(x.TransactionAmount / 30),
// InvoiceNumber = x.InvoiceNumber
// });
var statusArr = new int[] { 0, 2 };
var orderList = connection.QuerySet <Order>()
.Where(x => statusArr.Contains(x.Status) && x.CreateDate.AddMinutes(15) < DateTime.Now && !string.IsNullOrEmpty(x.OrderNo))
.ToDataSet(x => new
{
x.Id,
x.OrderNo
});
var pageLists = connection.QuerySet <Order>()
.OrderByDescing(x => x.CreateDate)
.PageList(1, 10, x => new OrderResDto()
{
Id = x.Id,
OrderNo = x.OrderNo,
OrderTime = x.CreateDate,
Status = x.Status,
Amount = x.Amount,
Point = x.Point,
DetailList = connection.QuerySet <OrderDetail>().Where(y => y.OrderNo == x.OrderNo).Get(),
//IsAnyOrderDetail = Convert.ToBoolean(connection.QuerySet<OrderDetail>().Where(y => y.OrderNo == x.OrderNo).Count()),
//OrderDetailList = connection.QuerySet<OrderDetail>()
//.Where(y => y.IsDelete == false && y.OrderNo == x.OrderNo)
//.Join<OrderDetail, Product>((a, b) => a.ProductCode == b.ProductCode, JoinMode.LEFT, true)
//.Join<Product, GiftDetail>((a, b) => a.ProductCode == b.ProductCode, JoinMode.LEFT, true)
//.From<OrderDetail, Product, GiftDetail>()
//.ToList(1 == 1
//, (a, b, c) => new OrderDetailResDto()
//{
// Id = a.Id,
// Name = a.ProductName,
// Point = a.Point,
// Price = a.Price,
// Qty = a.Qty,
// OriginalPrice = b.Price,
// OriginalPoint = b.Point,
// ImgUrl = c.ImgUrl_CN
//}
//, (a, b, c) => new OrderDetailResDto()
//{
// Id = a.Id,
// Name = a.ProductName,
// Point = a.Point,
// Price = a.Price,
// Qty = a.Qty,
// OriginalPrice = b.Price,
// OriginalPoint = b.Point,
// ImgUrl = c.ImgUrl_EN
//})
});
//// var test = connection.QuerySet<Order>()
//// .OrderBy(x => x.Id)
//// .PageList(1, 10);
//////获取上次同步成功的最大id
//var maxId = connection.QuerySet<Lige.Model.ActivitySendPoints>()
// .OrderByDescing(x => x.SqlId)
// .Get(x => x.SqlId);
connection.CommandSet <Product>()
.Where(x => x.Id == 44)
.Update(x => new Product()
{
Ext_F1 = (Convert.ToInt32(x.Ext_F1) + 1).ToString()
});
var product = connection.QuerySet <Product>()
.Where(x => x.ProductCode == "ed761dea-c57d-4b9c-8000-e434fe280337" && x.IsDelete == false)
.Get(x => new GiftDto()
{
ProductCode = x.ProductCode,
Name = x.Name_CN,
Stock = Convert.ToInt32(x.Ext_F1)
});
}
}