/// <summary>
/// 生成一个LinqDLR2SQL对象用于Linq操作
/// </summary>
/// <param name="table"></param>
/// <param name="alianname"></param>
/// <returns></returns>
public override LinqDLRTable NewLinqTable(string table, string alianname = "")
{
var tn = alianname == "" ? table : alianname;
LinqDLRTable rtn = LinqDLRTable.New <LinqDLRTable>(new SqliteLamdaSQLObject(tn, new SqliteOperatorFlags()), table, alianname, new SqliteSqlGenerator());
return(rtn);
}
/// <summary>
/// 获取一个Lamdatable对象，可以进行lamda2sql的操作
/// </summary>
/// <param name="tablename"></param>
/// <param name="alianname"></param>
/// <param name="dbtype">默认mysql</param>
/// <returns></returns>
public new LinqDLRTable LamdaTable(string tablename, string alianname = "", DBType dbtype = DBType.SqlServer)
{
if (dbtype == DBType.None && DEFAULT_DB_TYPE != DBType.None)
{
dbtype = DEFAULT_DB_TYPE;
}
return(LinqDLRTable.New <SqlServerDLRColumn>(tablename, alianname, new SqlServerOperatorFlags()));
}
public new LinqDLRTable PSQLLamdaTable(string tablename, string alianname = "", DBType dbtype = DBType.PostgreSQL)
{
if (dbtype == DBType.None && DEFAULT_DB_TYPE != DBType.None)
{
dbtype = DEFAULT_DB_TYPE;
}
return(LinqDLRTable.New <MysqlDLRColumn>(tablename, alianname, new PostgreSqlOperatorFlags()));
}
public void Run()
{
var dt = DateTime.Now;
//var s = from t in new List<object>()
// where t.ToString() == ""
// select t;
var ssa = new SQLServerAccess();
var lastweek_start = ComFunc.GetMondayDate(DateTime.Now).AddDays(-7);
var lastweek_end = ComFunc.GetSundayDate(DateTime.Now).AddDays(-7);
var dd = from t in ssa.NewLinqTable("IC_Statistic_Water", "a")
where t.SW_Estate == "1" &&
t.todatetime(t.SW_Year.tostring(4).concat("-", (t.SW_Month + 100).tostring(3).substring(2, 2), "-", (t.SW_Day + 100).tostring(3).substring(2, 2))) >= t.todatetime($"{lastweek_start.Year}-{lastweek_start.Month}-{lastweek_start.Day}") &&
t.todatetime(t.SW_Year.tostring(4).concat("-", (t.SW_Month + 100).tostring(3).substring(2, 2), "-", (t.SW_Day + 100).tostring(3).substring(2, 2))) <= t.todatetime($"{lastweek_end.Year}-{lastweek_end.Month}-{lastweek_end.Day}")
select new
{
hour = t.SW_Hour,
count = t.SW_Total
};
//var filter = "a";
//var start_time = "";
//var end_time = "";
//var s = from t in LinqDLRTable.New<LinqDLRColumn>("a", "", new SqlOperatorFlags())
// where t.notnull(filter, (t.Request_Route.contains(filter) || t.Request_IP.contains(filter) || t.Request_SubSystem_Name.contains(filter)))
// && t.notnull(start_time, t.add_time >= start_time) && t.notnull(end_time, t.add_time <= end_time) && t.start_time.isnull("").within(",1,2,3")
// orderby t.add_time descending
// select t;
Console.WriteLine(dd.ToSql());
var s = from t in LinqDLRTable.New <LinqDLRColumn>("a", "a", new SqlOperatorFlags())
join t2 in LinqDLRTable.New <LinqDLRColumn>("b", "b", new SqlOperatorFlags()) on t.id equals t2.code
join t3 in LinqDLRTable.New <LinqDLRColumn>("b", "c", new SqlOperatorFlags()) on t.id2 equals t3.code
group t by new { t.a, t.b } into g
select g;
var sql = s.ToSql();
Console.WriteLine(sql);
sql = (from t in LinqDLRTable.New <LinqDLRColumn>("a", "", new SqlOperatorFlags())
orderby t.time descending
select t).ToSql();
Console.WriteLine(sql);
sql = (from t in LinqDLRTable.New <LinqDLRColumn>("a", "", new SqlOperatorFlags())
orderby t.time descending, t.id
select new { t.id, t.name }).ToSql();
Console.WriteLine(sql);
sql = (from t in LinqDLRTable.New <LinqDLRColumn>("a", "", new SqlOperatorFlags())
group t by new { t.a, t.b } into g
select g).ToSql();
Console.WriteLine(sql);
sql = (from t in LinqDLRTable.New <LinqDLRColumn>("a", "", new SqlOperatorFlags())
from t2 in LinqDLRTable.New <LinqDLRColumn>("b", "", new SqlOperatorFlags())
where t.id == t2.id && t.time == "2018/08/17"
select new
{
t.id,
t2.name
}).ToSql();
Console.WriteLine(sql);
sql = (from t in LinqDLRTable.New <LinqDLRColumn>("a", "", new SqlServerOperatorFlags())
from t2 in LinqDLRTable.New <LinqDLRColumn>("b", "", new SqlServerOperatorFlags())
from t3 in LinqDLRTable.New <LinqDLRColumn>("c", "", new SqlServerOperatorFlags())
where t.id == t2.id
group new { t, t2, t3 } by new
{
t.id,
t2.time
} into g
select new
{
g.t.id,
g.t2.name
}).ToSql();
Console.WriteLine(sql);
Console.WriteLine($"selectmany cast:{(DateTime.Now - dt).TotalMilliseconds}"); dt = DateTime.Now;
}
public virtual LinqDLRTable LamdaTable(string tablename, string alianname = "", DBType dbtype = DBType.None)
{
return(LinqDLRTable.New <LinqDLRColumn>(tablename, alianname));
}
/// <summary>
/// 获取一个Lamdatable对象，可以进行lamda2sql的操作
/// </summary>
/// <param name="tablename"></param>
/// <param name="alianname"></param>
/// <param name="dbtype">默认mysql</param>
/// <returns></returns>
public new LinqDLRTable LamdaTable(string tablename, string alianname = "", DBType dbtype = DBType.MySql)
{
return(LinqDLRTable.New <MysqlDLRColumn>(tablename, alianname, new MySQLOperatorFlags()));
}