public async Task NotLessThanOrEqual()
{
xx = string.Empty;
// !(<=) --> >
var res1 = await MyDAL_TestDB.SelectListAsync <Agent>(it => !(it.CreatedOn <= DateTime.Parse("2018-08-16 19:20:35.867228")));
Assert.True(res1.Count == 21778);
/***********************************************************************************************************************************************/
xx = string.Empty;
// > --> >
var res2 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.CreatedOn > DateTime.Parse("2018-08-16 19:20:35.867228"));
Assert.True(res2.Count == 21778);
/***********************************************************************************************************************************************/
xx = string.Empty;
}
public async Task SelectVM_SQL()
{
xx = string.Empty;
var sql = @"
select agent12.`PathId` as nn,
record12.`Id` as yy,
agent12.`Id` as xx,
agent12.`Name` as zz,
record12.`LockedCount` as mm
from `agent` as agent12
inner join `agentinventoryrecord` as record12
on agent12.`Id`=record12.`AgentId`
where record12.`CreatedOn`>=@CreatedOn;
";
var param = new List <XParam>
{
new XParam {
ParamName = "@CreatedOn", ParamValue = Convert.ToDateTime("2018-08-23 13:36:58").AddDays(-30), ParamType = ParamTypeEnum.DateTime_MySQL_SqlServer
}
};
var res1 = await MyDAL_TestDB.SelectListAsync <AgentVM>(sql, param);
Assert.True(res1.Count == 574);
Assert.True("~00-d-3-2-1-c-2-1a-1" == res1.First().nn);
xx = string.Empty;
}
public async Task NotNotEqual()
{
xx = string.Empty;
// !(!=) --> =
var res1 = await MyDAL_TestDB.SelectListAsync <Agent>(it => !(it.AgentLevel != AgentLevel.Customer));
Assert.True(res1.Count == 28063 || res1.Count == 28064); // 此处为test
/***********************************************************************************************************************/
xx = string.Empty;
// == --> =
var res2 = await MyDAL_TestDB
.Selecter <Agent>()
.Where(it => it.AgentLevel == AgentLevel.DistiAgent)
.OrderBy(it => it.CreatedOn)
.SelectListAsync <AgentVM>();
Assert.True(res2.Count == 555);
/***********************************************************************************************************************/
xx = string.Empty;
}
public async Task NotGreaterThan()
{
xx = string.Empty;
// !(>) --> <=
var res1 = await MyDAL_TestDB.SelectListAsync <Agent>(it => !(it.CreatedOn > Convert.ToDateTime("2018-08-23 13:36:58").AddDays(-30)));
Assert.True(res1.Count == 1);
/******************************************************************************************************************************************************/
xx = string.Empty;
// <= --> <=
var res2 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.CreatedOn <= Convert.ToDateTime("2018-08-23 13:36:58").AddDays(-30));
Assert.True(res2.Count == 1);
/******************************************************************************************************************************************************/
xx = string.Empty;
}
public async Task MySQL_通配符()
{
xx = string.Empty;
// %(百分号): "陈%" --> "陈%"
var res5 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.Name.Contains("陈%"));
Assert.True(res5.Count == 1421);
/***************************************************************************************************************************/
xx = string.Empty;
// _(下划线): "王_" --> "王_"
var res6 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.Name.Contains("王_"));
Assert.True(res6.Count == 498);
/***************************************************************************************************************************/
xx = string.Empty;
}
public async Task SelectSingleColumn_SQL()
{
xx = string.Empty;
var sql = @"
select agent1.`CreatedOn`
from `agent` as agent1
inner join `agentinventoryrecord` as record1
on agent1.`Id`=record1.`AgentId`
where agent1.`AgentLevel`=@AgentLevel;
";
var paras = new List <XParam>
{
new XParam {
ParamName = "AgentLevel", ParamValue = AgentLevel.DistiAgent, ParamType = ParamTypeEnum.Int_MySQL_SqlServer
}
};
var res1 = await MyDAL_TestDB.SelectListAsync <DateTime>(sql, paras);
Assert.True(res1.Count == 574);
xx = string.Empty;
}
public async Task NotLessThan()
{
xx = string.Empty;
// !(<) --> >=
var res1 = await MyDAL_TestDB.SelectListAsync <Agent>(it => !(it.CreatedOn < DateTime.Parse("2019-02-10")));
Assert.True(res1.Count == 0);
/*********************************************************************************************************************************/
xx = string.Empty;
// >= --> >=
var res2 = await MyDAL_TestDB.SelectListAsync <AlipayPaymentRecord>(it => it.CreatedOn >= DateTime.Parse("2018-08-20"));
Assert.True(res2.Count == 29);
/****************************************************************************************/
xx = string.Empty;
}
public async Task Mock_SelectAllData_SelectVM_Shortcut()
{
xx = string.Empty;
var res1 = await MyDAL_TestDB.SelectListAsync <Agent, AgentVM>(it => true);
Assert.True(res1.Count == 28620);
/***************************************************************************************************************************/
}
public async Task Mock_SelectAllData_SelectSingleColumn_Shortcut()
{
xx = string.Empty;
var res1 = await MyDAL_TestDB.SelectListAsync <Agent, Guid>(it => true, it => it.Id);
Assert.True(res1.Count == 28620);
/***************************************************************************************************************************/
}
public async Task SelectVM_Shortcut()
{
xx = string.Empty;
var res2 = await MyDAL_TestDB.SelectListAsync <AlipayPaymentRecord, AlipayPaymentRecordVM>(it => it.CreatedOn >= DateTime.Parse("2018-08-20"));
Assert.True(res2.Count == 29);
/****************************************************************************************/
}
public async Task NotEqual()
{
xx = string.Empty;
// != --> <>
var res1 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.AgentLevel != AgentLevel.Customer);
Assert.True(res1.Count == 556);
xx = string.Empty;
}
public async Task GreaterThan()
{
xx = string.Empty;
// > --> >
var res1 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.CreatedOn > Convert.ToDateTime("2018-08-23 13:36:58").AddDays(-30));
Assert.True(res1.Count == 28619);
xx = string.Empty;
}
public async Task LessThan()
{
xx = string.Empty;
// < --> <
var res1 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.CreatedOn < DateTime.Parse("2019-02-10"));
Assert.True(res1.Count == 28620);
xx = string.Empty;
}
public async Task SelectVMColumn_Shortcut()
{
xx = string.Empty;
var res3 = await MyDAL_TestDB.SelectListAsync <AlipayPaymentRecord, AlipayPaymentRecordVM>(it => it.CreatedOn >= DateTime.Parse("2018-08-20"),
it => new AlipayPaymentRecordVM
{
TotalAmount = it.TotalAmount,
Description = it.Description
});
Assert.True(res3.Count == 29);
}
public async Task test()
{
/*************************************************************************************************************************/
xx = string.Empty;
var res2 = await MyDAL_TestDB
.Selecter <Agent>()
.Distinct()
.SelectListAsync(it => it.Name);
Assert.True(res2.Count == 24444);
/****************************************************************************************************************************************/
xx = string.Empty;
var res6 = await MyDAL_TestDB
.Selecter <Agent>()
.Where(it => it.Name == "刘中华")
.Distinct()
.SelectOneAsync();
Assert.NotNull(res6);
var res61 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.Name == "刘中华");
Assert.True(res61.Count == 2);
/****************************************************************************************************************************************/
xx = string.Empty;
var res7 = await MyDAL_TestDB
.Selecter(out Agent agent1, out AgentInventoryRecord record1)
.From(() => agent1)
.InnerJoin(() => record1)
.On(() => agent1.Id == record1.AgentId)
.Where(() => agent1.AgentLevel == AgentLevel.DistiAgent)
.Distinct()
.SelectListAsync(() => agent1.Name);
Assert.True(res7.Count == 543);
/****************************************************************************************************************************************/
xx = string.Empty;
}
public async Task SelectSingleColumn_Shortcut()
{
xx = string.Empty;
var res7 = await MyDAL_TestDB.SelectListAsync <Agent, string>(it => it.Name.StartsWith("张"), it => it.Name);
Assert.True(res7.Count == 1996);
/****************************************************************************************/
xx = string.Empty;
}
public async Task Like_Shortcut()
{
xx = string.Empty;
// like
var res1 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.Name.Contains("陈"));
Assert.True(res1.Count == 1431);
/***************************************************************************************************************************/
xx = string.Empty;
}
public async Task NotLike_Shortcut()
{
xx = string.Empty;
// not like
var res1 = await MyDAL_TestDB.SelectListAsync <Agent>(it => !it.Name.Contains("刘"));
Assert.True(res1.Count == 27159);
/***************************************************************************************************************************/
xx = string.Empty;
}
public async Task SelectVM_SQL()
{
xx = string.Empty;
var totalSql = @"
select count(*)
from (
select agent9.`Name` as XXXX,
agent9.`PathId` as YYYY
from `agent` as agent9
inner join `agentinventoryrecord` as record9
on agent9.`Id`=record9.`AgentId`
where agent9.`AgentLevel`=@AgentLevel
) temp;
";
var dataSql = @"
select agent9.`Name` as XXXX,
agent9.`PathId` as YYYY
from `agent` as agent9
inner join `agentinventoryrecord` as record9
on agent9.`Id`=record9.`AgentId`
where agent9.`AgentLevel`=@AgentLevel
order by agent9.`Id` desc
limit 0,10;
";
var paras = new List <XParam>
{
new XParam {
ParamName = "AgentLevel", ParamValue = AgentLevel.DistiAgent, ParamType = ParamTypeEnum.Int_MySQL_SqlServer
}
};
var paging = new PagingResult <AgentVM>();
paging.PageIndex = 1;
paging.PageSize = 10;
paging.TotalCount = await MyDAL_TestDB.SelectOneAsync <int>(totalSql, paras);
paging.Data = await MyDAL_TestDB.SelectListAsync <AgentVM>(dataSql, paras);
Assert.True(paging.Data.Count == 10);
xx = string.Empty;
}
public async Task NotIn_Shortcut()
{
xx = string.Empty;
// in
var res2 = await MyDAL_TestDB.SelectListAsync <Agent>(it => !new List <AgentLevel?> {
AgentLevel.CityAgent, AgentLevel.DistiAgent
}.Contains(it.AgentLevel));
Assert.True(res2.Count == 28064 || res2.Count == 28065); // 此处为test
/*******************************************************************************************************************/
xx = string.Empty;
}
public async Task Mock_SelectAllData_SelectVMColumn_Shortcut()
{
xx = string.Empty;
var res1 = await MyDAL_TestDB.SelectListAsync <Agent, AgentVM>(it => true, it => new AgentVM
{
XXXX = it.Name,
YYYY = it.PathId
});
Assert.True(res1.Count == 28620);
/***************************************************************************************************************************/
xx = string.Empty;
}
public async Task SelectSingleColumn_SQL()
{
xx = string.Empty;
var totalSql = @"
select count(agent1.`Id`)
from `agent` as agent1
inner join `agentinventoryrecord` as record1
on agent1.`Id`=record1.`AgentId`
where agent1.`AgentLevel`=@AgentLevel;
";
var dataSql = @"
select agent1.`Id`
from `agent` as agent1
inner join `agentinventoryrecord` as record1
on agent1.`Id`=record1.`AgentId`
where agent1.`AgentLevel`=@AgentLevel
order by agent1.`Id` desc
limit 0,10;
";
var paras = new List <XParam>
{
new XParam {
ParamName = "AgentLevel", ParamValue = AgentLevel.DistiAgent, ParamType = ParamTypeEnum.Int_MySQL_SqlServer
}
};
var paging = new PagingResult <Guid>();
paging.PageIndex = 1;
paging.PageSize = 10;
paging.TotalCount = await MyDAL_TestDB.SelectOneAsync <int>(totalSql, paras);
paging.Data = await MyDAL_TestDB.SelectListAsync <Guid>(dataSql, paras);
Assert.True(paging.TotalPage == 58);
xx = string.Empty;
}
public async Task Mock_UpdateAll_SetField_ST()
{
xx = string.Empty;
var res1 = await MyDAL_TestDB
.Updater <AgentInventoryRecord>()
.Set(it => it.LockedCount, 0)
.UpdateAsync();
Assert.True(res1 == 574);
var res11 = await MyDAL_TestDB.SelectListAsync <AgentInventoryRecord>(it => it.LockedCount != 0);
Assert.True(res11.Count == 0);
xx = string.Empty;
}
public async Task Mock_UpdateAll_Shortcut()
{
xx = string.Empty;
var model = new AgentInventoryRecord();
model.LockedCount = 0;
var res1 = await MyDAL_TestDB.UpdateAsync <AgentInventoryRecord>(it => true, new
{
model.LockedCount
});
Assert.True(res1 == 574);
var res11 = await MyDAL_TestDB.SelectListAsync <AgentInventoryRecord>(it => it.LockedCount != 0);
Assert.True(res11.Count == 0);
xx = string.Empty;
}
public async Task MySQL_通配符_转义()
{
var resx4 = await Pre02();
/************************************************************************************************************/
xx = string.Empty;
// /%(百分号转义): "刘/%_" --> "刘/%_"
var res7 = await MyDAL_TestDB
.Selecter <Agent>()
.Where(it => it.Name.Contains("刘/%_"))
.And(it => it.Id == resx4.Id)
.And(it => it.Name.Contains("%华"))
.And(it => it.Name.Contains("%/%%"))
.SelectListAsync();
Assert.True(res7.Count == 1);
/************************************************************************************************************/
xx = string.Empty;
// /_(下划线转义): "何/__" --> "何/__"
var res8 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.Name.Contains("何/__"));
Assert.True(res8.Count == 1);
/************************************************************************************************************/
xx = string.Empty;
}
public async Task String_EndsWith()
{
/***************************************************************************************************************************/
xx = string.Empty;
// like EndsWith
var res1 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.Name.EndsWith("陈"));
Assert.True(res1.Count == 2);
/************************************************************************************************************/
xx = string.Empty;
// like EndsWith
var res12 = await MyDAL_TestDB
.Selecter <Agent>()
.Where(it => it.PathId.EndsWith("~00-d-3-1-"))
.SelectPagingAsync(1, 10);
Assert.True(res12.TotalCount == 0);
/************************************************************************************************************/
xx = string.Empty;
// like EndsWith
var res13 = await MyDAL_TestDB
.Selecter(out Agent agent13, out AgentInventoryRecord record13)
.From(() => agent13)
.InnerJoin(() => record13)
.On(() => agent13.Id == record13.AgentId)
.Where(() => agent13.Name.EndsWith("华"))
.SelectListAsync <Agent>();
Assert.True(res13.Count == 22);
/***************************************************************************************************************************/
xx = string.Empty;
// not like EndsWith
var res2 = await MyDAL_TestDB.SelectListAsync <Agent>(it => !it.Name.EndsWith("刘"));
Assert.True(res2.Count == 28620);
/************************************************************************************************************/
xx = string.Empty;
// not like EndsWith
var res21 = await MyDAL_TestDB
.Selecter <Agent>()
.Where(it => !it.PathId.EndsWith("~00-d-3-1-"))
.SelectPagingAsync(1, 10);
Assert.True(res21.TotalCount == 28620);
/************************************************************************************************************/
xx = string.Empty;
// not like EndsWith
var res22 = await MyDAL_TestDB
.Selecter(out Agent agent22, out AgentInventoryRecord record22)
.From(() => agent22)
.InnerJoin(() => record22)
.On(() => agent22.Id == record22.AgentId)
.Where(() => !agent22.Name.EndsWith("华"))
.SelectListAsync <Agent>();
Assert.True(res22.Count == 552);
/************************************************************************************************************/
xx = string.Empty;
}
public async Task WhereTestx()
{
xx = string.Empty;
/************************************************************************************************************************/
// is not null
var res2 = await MyDAL_TestDB
.Selecter <Agent>()
.Where(it => it.ActiveOrderId != null)
.SelectListAsync();
Assert.True(res2.Count == 554);
/************************************************************************************************************************/
xx = string.Empty;
var m = await PreData3();
//
try
{
var res3 = await MyDAL_TestDB
.Selecter <Agent>()
.Where(it => it.AgentLevel == WhereTest.AgentLevelNull)
.SelectListAsync();
}
catch (Exception ex)
{
var errStr = "【ERR-078】 -- [[[[Convert(value(MyDAL.Compare._09_Null).WhereTest.AgentLevelNull, Nullable`1)]] 中,传入的 SQL 筛选条件为 Null !!!]] ，请 EMail: --> [email protected] <--";
Assert.Equal(errStr, ex.Message, ignoreCase: true);
}
await ClearData3(m);
/************************************************************************************************************************/
var xx4 = string.Empty;
// is not null
var res4 = await MyDAL_TestDB
.Selecter <Agent>()
.Where(it => it.ActivedOn != null && it.ActiveOrderId != null && it.CrmUserId == null)
.SelectListAsync();
Assert.True(res4.Count == 554);
/************************************************************************************************************************/
var xx5 = string.Empty;
// is not null
var res5 = await MyDAL_TestDB
.Selecter(out Agent a5, out AgentInventoryRecord r5)
.From(() => a5)
.LeftJoin(() => r5)
.On(() => a5.Id == r5.AgentId)
.Where(() => a5.ActiveOrderId == null)
.SelectListAsync <Agent>();
Assert.True(res5.Count == 28085);
/************************************************************************************************************************/
var res7 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.ActiveOrderId == null);
Assert.True(res7.Count == 28066);
/************************************************************************************************************************/
xx = string.Empty;
// is not null
var res8 = await MyDAL_TestDB.SelectListAsync <Agent>(it => it.ActiveOrderId != null);
Assert.True(res8.Count == 554);
/************************************************************************************************************************/
xx = string.Empty;
}
