public static void AddWhereCondition(this SQlQuery query, string whereCondition)
{
if (!string.IsNullOrEmpty(whereCondition))
{
query.WhereCondition.Add(whereCondition);
}
}
/// <summary>
/// "Any" keyword is used without expression
/// Way to use:
/// Condition[Table].Any()
/// </summary>
public static bool Any <T>(IDbConnection db)
{
SQlQuery sql = new SQlQuery(TableHelper.GetTableName <T>());
string newSql = @"SELECT CASE WHEN ( EXISTS ( " + sql.Query + " )) THEN cast(1 as bit) ELSE cast(0 as bit) END";
var result = db.Query <bool>(newSql).FirstOrDefault();
return(result);
}
//public static int Count<TSource>(this IEnumerable<TSource> source, Func<TSource, bool> predicate);
//public static int Count<TSource>(this IEnumerable<TSource> source);
//public static decimal? Min(this IEnumerable<decimal?> source);
//public static decimal? Sum<TSource>(this IEnumerable<TSource> source, Func<TSource, int> selector);
//public static decimal? Max<TSource>(this IEnumerable<TSource> source, Func<TSource, decimal?> selector);
#endregion
#region Count
/// <summary>
/// result[0] = result[1]
/// </summary>
//private static string[] GetForeignKeyColumn(Type table1, Type table2)
//{
// string[] result = new string[2];
// PropertyInfo[] table1PInfos = table1.GetProperties();
// string table1NameInDb = TableHelper.GetTableName(table1);
// string table2NameInDb = TableHelper.GetTableName(table2);
// PropertyInfo pForeignKeyInfosTable1 = table1PInfos.FirstOrDefault(p => p.GetCustomAttributes(true)
// .Any(a => (a as ForeignKeyAttribute) != null && (a as ForeignKeyAttribute).Name == table2NameInDb));
// if (pForeignKeyInfosTable1 != null)
// {
// result[0] = table1NameInDb + '.' + pForeignKeyInfosTable1.Name;
// result[1] = table2NameInDb + '.' + pForeignKeyInfosTable1.Name;
// return result;
// }
// else
// {
// PropertyInfo[] table2PInfos = table2.GetProperties();
// PropertyInfo pForeignKeyInfosTable2 = table2PInfos.FirstOrDefault(p => p.GetCustomAttributes(true)
// .Any(a => (a as ForeignKeyAttribute) != null && (a as ForeignKeyAttribute).Name == table1NameInDb));
// if (pForeignKeyInfosTable2 != null)
// {
// result[0] = table1NameInDb + '.' + pForeignKeyInfosTable1.Name;
// result[1] = table2NameInDb + '.' + pForeignKeyInfosTable1.Name;
// return result;
// }
// //External fields of table 1
// //search id of table 2 in table 1
// PropertyInfo primaryKeyTable2 = table2PInfos.FirstOrDefault(p => string.Compare(p.Name, "id", true) == 0);
// if (primaryKeyTable2 == null)
// primaryKeyTable2 = table2PInfos.FirstOrDefault(p => p.GetCustomAttributes(true).Any(a => (a as PrimaryKey) != null));
// if (primaryKeyTable2 == null)
// throw new Exception("Could not found the primary key in entity: " + table1.FullName);
// PropertyInfo allPExternalTable2InTable1 = table1PInfos.FirstOrDefault(p => string.Compare(p.Name, primaryKeyTable2.Name, true) == 0);
// if (allPExternalTable2InTable1 != null) // primary key of table 2 is existed in table 1
// {
// result[0] = table1NameInDb + '.' + primaryKeyTable2.Name;
// result[1] = table2NameInDb + '.' + primaryKeyTable2.Name;
// return result;
// }
// else
// {
// //search id of table 1 in table 2
// PropertyInfo primaryKeyTable1 = table1PInfos.FirstOrDefault(p => string.Compare(p.Name, "id", true) == 0);
// if (primaryKeyTable1 == null)
// primaryKeyTable1 = table1PInfos.FirstOrDefault(p => p.GetCustomAttributes(true).Any(a => (a as PrimaryKey) != null));
// if (primaryKeyTable1 == null)
// throw new Exception("Could not found the primary key in entity: " + table1.FullName);
// PropertyInfo pExternalTable1InTable2 = table2PInfos.FirstOrDefault(p => string.Compare(p.Name, primaryKeyTable1.Name, true) == 0);
// if (pExternalTable1InTable2 != null)
// {
// result[0] = table1NameInDb + '.' + primaryKeyTable1.Name;
// result[1] = table2NameInDb + '.' + primaryKeyTable1.Name;
// return result;
// }
// else
// throw new Exception("Could not found the foreign key between two entities: " + table1.FullName + " and " + table2.FullName);
// }
// }
//}
//private static string GetPrimaryIdColumn(Type table)
//{
// PropertyInfo[] table1PInfos = table.GetProperties();
// PropertyInfo primaryKeyTable1 =
// table1PInfos.FirstOrDefault(p => string.Compare(p.Name, "id", true) == 0);
// if (primaryKeyTable1 == null)
// primaryKeyTable1 = table1PInfos.FirstOrDefault(p => p.GetCustomAttributes(true).Any(a => (a as PrimaryKey) != null));
// if (primaryKeyTable1 == null)
// throw new Exception("Count not find the primaryKey of table: " + table.FullName);
// return primaryKeyTable1.Name;
//}
/// <summary>
/// "Count" keyword is used without expression --
/// Way to use: [Table].Count()
/// </summary>
public static int Count <T>(IDbConnection db)
{
SQlQuery query = new SQlQuery(TableHelper.GetTableName <T>());
string newSql = @"SELECT COUNT(1) FROM ( " + query.Query + " ) as Extend";
var result = db.Query <int>(newSql).FirstOrDefault();
return(result);
}
public static QueryResult <T> Get <T>(Expression <Func <T, bool> > expression)
{
var body = expression.Body as BinaryExpression;
SQlQuery query = new SQlQuery(TableHelper.GetTableName <T>());
WalkTree <T>(expression, ref query);
return(new QueryResult <T>(query));
}
public static void AddJoinOperator(this SQlQuery query, List <string> joinOperator)
{
if (joinOperator != null && joinOperator.Any())
{
query.JoinContidion.AddRange(joinOperator);
}
query.JoinContidion = query.JoinContidion.Distinct().ToList();
}
/// <summary>
/// "Any" keyword is used with expression
/// Way to use:
/// Any[Table](x=> x.id = 10);
/// Find[Table](x=> x.id = 10).Any(x=>x.id = 10);
/// </summary>
public static bool Any <T>(IDbConnection db, Expression <Func <T, bool> > expression)
{
var body = expression.Body as BinaryExpression;
SQlQuery query = new SQlQuery(TableHelper.GetTableName <T>());
WalkTree <T>(expression, ref query);
string newSql = @"SELECT CASE WHEN ( EXISTS ( " + query.Query + " )) THEN cast(1 as bit) ELSE cast(0 as bit) END";
var result = db.Query <bool>(newSql, (object)query.Param).FirstOrDefault();
return(result);
}
/// <summary>
/// "Count" keyword is used without expression --
/// Way to use: [Table].Count(expression)
/// </summary>
public static int Count <T>(Expression <Func <T, bool> > expression, IDbConnection db)
{
var body = expression.Body as BinaryExpression;
SQlQuery query = new SQlQuery(TableHelper.GetTableName <T>());
WalkTree <T>(expression, ref query);
string newSql = @"SELECT COUNT(1) FROM ( " + query.Query + " ) as Extend";
var result = db.Query <int>(newSql, (object)query.Param).FirstOrDefault();
return(result);
}
public static QueryResult <T> Get <T>()
{
SQlQuery query = new SQlQuery(TableHelper.GetTableName <T>());
return(new QueryResult <T>(query));
}
public QuerySource(SQlQuery sql)
{
Sql = sql;
}
///// <summary>
///// Gets the param.
///// </summary>
///// <value>
///// The param.
///// </value>
//public dynamic Param
//{
// get
// {
// return _result.Param;
// }
//}
/// <summary>
/// Initializes a new instance of the <see cref="QueryResult" /> class.
/// </summary>
/// <param name="sql">The SQL.</param>
/// <param name="param">The param.</param>
public QueryResult(SQlQuery sql)
{
_result = new QuerySource <SQlQuery>(sql);
}
public static void AddQueryParameter(this SQlQuery query, QueryParameter parameters)
{
query.QueryParameters.Add(parameters);
}
