public virtual ISqlParam ToList(int pageSize, int pageIndex, bool isDistinct = false) { // 不分页 if (pageIndex == 1) { ToList(pageSize, isDistinct); return(this); } var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect); var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere); var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy); var strDistinctSql = isDistinct ? "Distinct " : string.Empty; Check.IsTure(string.IsNullOrWhiteSpace(strOrderBySql) && ExpBuilder.Map.FieldMap.PrimaryState.Key == null, "不指定排序字段时,需要设置主键ID"); strOrderBySql = "ORDER BY " + (string.IsNullOrWhiteSpace(strOrderBySql) ? string.Format("{0} ASC", ExpBuilder.Map.FieldMap.PrimaryState.Value.FieldAtt.Name) : strOrderBySql); var strOrderBySqlReverse = strOrderBySql.Replace(" DESC", " [倒序]").Replace("ASC", "DESC").Replace("[倒序]", "ASC"); if (!string.IsNullOrWhiteSpace(strWhereSql)) { strWhereSql = "WHERE " + strWhereSql; } Sql.AppendFormat("SELECT {0}TOP {2} {1} FROM (SELECT TOP {3} * FROM {4} {5} {6}) a {7};", strDistinctSql, strSelectSql, pageSize, pageSize * pageIndex, Name, strWhereSql, strOrderBySql, strOrderBySqlReverse); return(this); }
public override ISqlParam ToList(int top = 0, bool isDistinct = false, bool isRand = false) { var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect); var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere); var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy); var strTopSql = top > 0 ? $"TOP {top}" : string.Empty; var strDistinctSql = isDistinct ? "Distinct " : string.Empty; if (!string.IsNullOrWhiteSpace(strWhereSql)) { strWhereSql = "WHERE " + strWhereSql; } if (!string.IsNullOrWhiteSpace(strOrderBySql)) { strOrderBySql = "ORDER BY " + strOrderBySql; } if (!isRand) { Sql.Append($"SELECT {strDistinctSql}{strTopSql} {strSelectSql} FROM {DbProvider.KeywordAegis(Name)} {strWhereSql} {strOrderBySql}"); } else if (string.IsNullOrWhiteSpace(strOrderBySql)) { Sql.Append(string.Format("SELECT {0}{1} {2}{5} FROM {3} {4} BY Rnd(-(TestID+\" & Rnd() & \"))", strDistinctSql, strTopSql, strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, isDistinct ? ",Rnd(-(TestID+\" & Rnd() & \")) as newid" : "")); } else { Sql.Append(string.Format("SELECT {2} FROM (SELECT {0}{1} *{6} FROM {3} {4} BY Rnd(-(TestID+\" & Rnd() & \"))) a {5}", strDistinctSql, strTopSql, strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, strOrderBySql, isDistinct ? ",Rnd(-(TestID+\" & Rnd() & \")) as newid" : "")); } return(this); }
public override ISqlParam ToList(int pageSize, int pageIndex, bool isDistinct = false) { // 不分页 if (pageIndex == 1) { ToList(pageSize, isDistinct); return(this); } var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect); var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere); var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy); var strDistinctSql = isDistinct ? "Distinct " : string.Empty; if (string.IsNullOrWhiteSpace(strSelectSql)) { strSelectSql = "*"; } if (!string.IsNullOrWhiteSpace(strWhereSql)) { strWhereSql = "WHERE " + strWhereSql; } if (!string.IsNullOrWhiteSpace(strOrderBySql)) { strOrderBySql = "ORDER BY " + strOrderBySql; } Sql.AppendFormat("SELECT {0}{1} FROM {2} {3} {4} LIMIT {5},{6}", strDistinctSql, strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, strOrderBySql, pageSize * (pageIndex - 1), pageSize); return(this); }
public override ISqlParam ToList(int pageSize, int pageIndex, bool isDistinct = false) { // 不分页 if (pageIndex == 1) { ToList(pageSize, isDistinct); return(this); } var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect); var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere); var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy); var strDistinctSql = isDistinct ? "Distinct" : string.Empty; if (!string.IsNullOrWhiteSpace(strWhereSql)) { strWhereSql = "WHERE " + strWhereSql; } Check.IsTure(string.IsNullOrWhiteSpace(strOrderBySql) && (ExpBuilder.SetMap.PhysicsMap.PrimaryFields.Count == 0), "不指定排序字段时,需要设置主键ID"); strOrderBySql = "ORDER BY " + (string.IsNullOrWhiteSpace(strOrderBySql) ? $"{ConvertHelper.ToString(ExpBuilder.SetMap.PhysicsMap.PrimaryFields.Select(o => o.Value.Name), ",")} ASC" : strOrderBySql); Sql.Append(string.Format("SELECT {1} FROM (SELECT {0} {1},ROW_NUMBER() OVER({2}) as Row FROM {3} {4}) a WHERE Row BETWEEN {5} AND {6};", strDistinctSql, strSelectSql, strOrderBySql, Name, strWhereSql, (pageIndex - 1) * pageSize + 1, pageIndex * pageSize)); return(this); }
public virtual ISqlParam ToList(int top = 0, bool isDistinct = false, bool isRand = false) { var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect); var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere); var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy); var strTopSql = top > 0 ? string.Format("TOP {0} ", top) : string.Empty; var strDistinctSql = isDistinct ? "Distinct " : string.Empty; if (!string.IsNullOrWhiteSpace(strWhereSql)) { strWhereSql = "WHERE " + strWhereSql; } if (!string.IsNullOrWhiteSpace(strOrderBySql)) { strOrderBySql = "ORDER BY " + strOrderBySql; } if (!isRand) { Sql.AppendFormat("SELECT {0}{1}{2} FROM {3} {4} {5}", strDistinctSql, strTopSql, strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, strOrderBySql); } else if (string.IsNullOrWhiteSpace(strOrderBySql)) { Sql.AppendFormat("SELECT {0}{1}{2}{5} FROM {3} {4} ORDER BY NEWID()", strDistinctSql, strTopSql, strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, isDistinct ? ",NEWID() as newid" : ""); } else { Sql.AppendFormat("SELECT {2} FROM (SELECT {0} {1} *{6} FROM {3} {4} ORDER BY NEWID()) a {5}", strDistinctSql, strTopSql, strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, strOrderBySql, isDistinct ? ",NEWID() as newid" : ""); } return(this); }
public override ISqlParam ToList(int top = 0, bool isDistinct = false, bool isRand = false) { var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect); var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere); var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy); var strDistinctSql = isDistinct ? "Distinct " : string.Empty; var randField = ",dbms_random.value as newid"; if (!string.IsNullOrWhiteSpace(strWhereSql)) { strWhereSql = "WHERE " + strWhereSql; } if (!string.IsNullOrWhiteSpace(strOrderBySql)) { strOrderBySql = "ORDER BY " + strOrderBySql; } if (!isRand) { Sql.Append(BuilderTop(top, $"SELECT {strDistinctSql}{strSelectSql} FROM {DbProvider.KeywordAegis(Name)} {strWhereSql} {strOrderBySql}")); } else if (!isDistinct && string.IsNullOrWhiteSpace(strOrderBySql)) { Sql.Append(BuilderTop(top, $"SELECT {strSelectSql}{randField} FROM {DbProvider.KeywordAegis(Name)} {strWhereSql} ORDER BY dbms_random.value")); } else { Sql.Append(BuilderTop(top, $"SELECT * {randField} FROM (SELECT {strDistinctSql} {strSelectSql} FROM {DbProvider.KeywordAegis(Name)} {strWhereSql} {strOrderBySql}) s ORDER BY dbms_random.value")); } return(this); }
public override ISqlParam ToList(int pageSize, int pageIndex, bool isDistinct = false) { // 不分页 if (pageIndex == 1) { ToList(pageSize, isDistinct); return(this); } var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect); var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere); var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy); var strDistinctSql = isDistinct ? "Distinct " : string.Empty; if (!string.IsNullOrWhiteSpace(strWhereSql)) { strWhereSql = "WHERE " + strWhereSql; } if (!string.IsNullOrWhiteSpace(strOrderBySql)) { strOrderBySql = "ORDER BY " + strOrderBySql; } Sql.Append($"SELECT {strDistinctSql}{strSelectSql} FROM {DbProvider.KeywordAegis(Name)} {strWhereSql} {strOrderBySql} LIMIT {pageSize} OFFSET {pageSize * (pageIndex - 1)}"); return(this); }
public override ISqlParam ToList(int pageSize, int pageIndex, bool isDistinct = false) { // 不分页 if (pageIndex == 1) { ToList(pageSize, isDistinct); return(this); } var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect); var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere); var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy); var strDistinctSql = isDistinct ? "Distinct" : string.Empty; Check.IsTure(string.IsNullOrWhiteSpace(strOrderBySql) && ExpBuilder.SetMap.PhysicsMap.PrimaryFields.Count == 0, "不指定排序字段时,需要设置主键ID"); strOrderBySql = "ORDER BY " + (string.IsNullOrWhiteSpace(strOrderBySql) ? $"{ConvertHelper.ToString(ExpBuilder.SetMap.PhysicsMap.PrimaryFields.Select(o => o.Value.Name), ",")} ASC" : strOrderBySql); var strOrderBySqlReverse = strOrderBySql.Replace(" DESC", " [倒序]").Replace("ASC", "DESC").Replace("[倒序]", "ASC"); if (!string.IsNullOrWhiteSpace(strWhereSql)) { strWhereSql = "WHERE " + strWhereSql; } Sql.Append(string.Format("SELECT {0} TOP {2} {1} FROM (SELECT TOP {3} {1} FROM {4} {5} {6}) a {7};", strDistinctSql, strSelectSql, pageSize, pageSize * pageIndex, Name, strWhereSql, strOrderBySql, strOrderBySqlReverse)); return(this); }
public void Exploration() { Expression <Func <DummyA, object> > expression = (a) => new { Truls = a.B1, Morten = a.B1.Tittel }; var visitor = new SelectVisitor(); visitor.Visit(expression.Body); Assert.AreEqual(1, visitor.RelatedObjects.Count()); Assert.AreEqual("B1", visitor.RelatedObjects.ElementAt(0)); }
public virtual ISqlParam Min() { var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect); var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere); if (string.IsNullOrWhiteSpace(strSelectSql)) { strSelectSql = "0"; } if (!string.IsNullOrWhiteSpace(strWhereSql)) { strWhereSql = "WHERE " + strWhereSql; } Sql.AppendFormat("SELECT MIN({0}) FROM {1} {2}", strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql); return(this); }
public override ISqlParam GetValue() { var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect); var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere); var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy); if (!string.IsNullOrWhiteSpace(strWhereSql)) { strWhereSql = "WHERE " + strWhereSql; } if (!string.IsNullOrWhiteSpace(strOrderBySql)) { strOrderBySql = "ORDER BY " + strOrderBySql; } Sql.Append($"SELECT TOP 1 {strSelectSql} FROM {DbProvider.KeywordAegis(Name)} {strWhereSql} {strOrderBySql}"); return(this); }
public virtual ISqlParam ToEntity() { var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect); var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere); var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy); if (!string.IsNullOrWhiteSpace(strWhereSql)) { strWhereSql = "WHERE " + strWhereSql; } if (!string.IsNullOrWhiteSpace(strOrderBySql)) { strOrderBySql = "ORDER BY " + strOrderBySql; } Sql.AppendFormat("SELECT TOP 1 {0} FROM {1} {2} {3}", strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, strOrderBySql); return(this); }
public override ISqlParam GetValue() { var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect); var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere); var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy); if (!string.IsNullOrWhiteSpace(strWhereSql)) { strWhereSql = "WHERE " + strWhereSql; } if (!string.IsNullOrWhiteSpace(strOrderBySql)) { strOrderBySql = "ORDER BY " + strOrderBySql; } Sql.AppendFormat("SELECT {0} FROM {1} {2} {3} rownum <=1", strSelectSql, DbProvider.KeywordAegis(Name), strWhereSql, strOrderBySql); return(this); }
public override ISqlParam ToEntity() { var strSelectSql = SelectVisitor.Visit(ExpBuilder.ExpSelect); var strWhereSql = WhereVisitor.Visit(ExpBuilder.ExpWhere); var strOrderBySql = OrderByVisitor.Visit(ExpBuilder.ExpOrderBy); if (!string.IsNullOrWhiteSpace(strWhereSql)) { strWhereSql = "WHERE " + strWhereSql; } if (!string.IsNullOrWhiteSpace(strOrderBySql)) { strOrderBySql = "ORDER BY " + strOrderBySql; } var strTopSql = (string.IsNullOrWhiteSpace(strWhereSql) ? "WHERE" : "AND") + " rownum <=1"; Sql.Append($"SELECT {strSelectSql} FROM {DbProvider.KeywordAegis(Name)} {strWhereSql} {strTopSql} {strOrderBySql}"); return(this); }