コード例 #1
0
        /// <summary>
        /// Queries a data from the database in an asynchronous way.
        /// </summary>
        /// <param name="tableName">The name of the target table.</param>
        /// <param name="where">The query expression to be used.</param>
        /// <param name="fields">The list of fields to be queried.</param>
        /// <param name="orderBy">The order definition of the fields to be used.</param>
        /// <param name="top">The top number of data to be used.</param>
        /// <param name="hints">The table hints to be used. See <see cref="SqlServerTableHints"/> class.</param>
        /// <param name="cacheKey">
        /// The key to the cache. If the cache key is present in the cache, then the item from the cache will be returned instead. Setting this
        /// to null would force the repository to query from the database.
        /// </param>
        /// <param name="transaction">The transaction to be used.</param>
        /// <returns>An enumerable list of dynamic object.</returns>
        public async Task<IEnumerable<dynamic>> QueryAsync(string tableName,
            QueryGroup where = null,
            IEnumerable<Field> fields = null,
            IEnumerable<OrderField> orderBy = null,
            int? top = 0,
            string hints = null,
            string cacheKey = null,
            IDbTransaction transaction = null)
        {
            // Create a connection
            var connection = (transaction?.Connection ?? CreateConnection());

            try
            {
                // Call the method
                return await connection.QueryAsync(tableName: tableName,
                    where: where,
                    fields: fields,
                    orderBy: orderBy,
                    top: top,
                    hints: hints,
                    cacheKey: cacheKey,
                    cacheItemExpiration: CacheItemExpiration,
                    commandTimeout: CommandTimeout,
                    transaction: transaction,
                    cache: Cache,
                    trace: Trace,
                    statementBuilder: StatementBuilder);
            }
            catch
            {
                // Throw back the error
                throw;
            }
            finally
            {
                // Dispose the connection
                DisposeConnectionForPerCall(connection, transaction);
            }
        }
コード例 #2
0
        /// <summary>
        /// Creates a SQL Statement for update operation.
        /// </summary>
        /// <param name="queryBuilder">The query builder to be used.</param>
        /// <param name="tableName">The name of the target table.</param>
        /// <param name="fields">The list of fields to be updated.</param>
        /// <param name="where">The query expression.</param>
        /// <param name="primaryField">The primary field from the database.</param>
        /// <param name="identityField">The identity field from the database.</param>
        /// <returns>A sql statement for update operation.</returns>
        public string CreateUpdate(QueryBuilder queryBuilder,
                                   string tableName,
                                   IEnumerable <Field> fields,
                                   QueryGroup where      = null,
                                   DbField primaryField  = null,
                                   DbField identityField = null)
        {
            // Ensure with guards
            GuardTableName(tableName);
            GuardPrimary(primaryField);
            GuardIdentity(identityField);

            // Append the proper prefix
            where?.PrependAnUnderscoreAtTheParameters();

            // Gets the updatable fields
            var updatableFields = fields
                                  .Where(f => f.UnquotedName.ToLower() != primaryField?.UnquotedName.ToLower() &&
                                         f.UnquotedName.ToLower() != identityField?.UnquotedName.ToLower());

            // Check if there are updatable fields
            if (updatableFields?.Any() != true)
            {
                throw new InvalidOperationException("The list of updatable fields cannot be null or empty.");
            }

            // Build the query
            (queryBuilder ?? new QueryBuilder())
            .Clear()
            .Update()
            .TableNameFrom(tableName)
            .Set()
            .FieldsAndParametersFrom(updatableFields)
            .WhereFrom(where)
            .End();

            // Return the query
            return(queryBuilder.GetString());
        }
コード例 #3
0
        /// <summary>
        /// Creates a SQL Statement for count operation.
        /// </summary>
        /// <param name="queryBuilder">The query builder to be used.</param>
        /// <param name="tableName">The name of the target table.</param>
        /// <param name="where">The query expression.</param>
        /// <param name="hints">The table hints to be used. See <see cref="SqlTableHints"/> class.</param>
        /// <returns>A sql statement for count operation.</returns>
        public string CreateCount(QueryBuilder queryBuilder,
                                  string tableName,
                                  QueryGroup where = null,
                                  string hints     = null)
        {
            // Ensure with guards
            GuardTableName(tableName);

            // Build the query
            (queryBuilder ?? new QueryBuilder())
            .Clear()
            .Select()
            .CountBig()
            .WriteText("(1) AS [Counted]")
            .From()
            .TableNameFrom(tableName)
            .HintsFrom(hints)
            .WhereFrom(where)
            .End();

            // Return the query
            return(queryBuilder.GetString());
        }
コード例 #4
0
        /// <summary>
        /// Queries a data from the database.
        /// </summary>
        /// <typeparam name="TEntity">The type of the data entity object.</typeparam>
        /// <param name="where">The query expression to be used.</param>
        /// <param name="orderBy">The order definition of the fields to be used.</param>
        /// <param name="top">The top number of data to be used.</param>
        /// <param name="hints">The table hints to be used. See <see cref="SqlServerTableHints"/> class.</param>
        /// <param name="cacheKey">
        /// The key to the cache. If the cache key is present in the cache, then the item from the cache will be returned instead. Setting this
        /// to null would force the repository to query from the database.
        /// </param>
        /// <param name="transaction">The transaction to be used.</param>
        /// <returns>An enumerable list of data entity object.</returns>
        public IEnumerable<TEntity> Query<TEntity>(QueryGroup where = null,
            IEnumerable<OrderField> orderBy = null,
            int? top = 0,
            string hints = null,
            string cacheKey = null,
            IDbTransaction transaction = null)
            where TEntity : class
        {
            // Create a connection
            var connection = (transaction?.Connection ?? CreateConnection());

            try
            {
                // Call the method
                return connection.Query<TEntity>(where: where,
                    orderBy: orderBy,
                    top: top,
                    hints: hints,
                    cacheKey: cacheKey,
                    cacheItemExpiration: CacheItemExpiration,
                    commandTimeout: CommandTimeout,
                    transaction: transaction,
                    cache: Cache,
                    trace: Trace,
                    statementBuilder: StatementBuilder);
            }
            catch
            {
                // Throw back the error
                throw;
            }
            finally
            {
                // Dispose the connection
                DisposeConnectionForPerCall(connection, transaction);
            }
        }
コード例 #5
0
ファイル: Exists.cs プロジェクト: orthoxerox/RepoDB
        /// <summary>
        /// Check whether the rows are existing in the table.
        /// </summary>
        /// <typeparam name="TEntity">The type of the data entity.</typeparam>
        /// <param name="where">The query expression to be used.</param>
        /// <param name="hints">The table hints to be used.</param>
        /// <param name="transaction">The transaction to be used.</param>
        /// <returns>A boolean value that indicates whether the rows are existing in the table.</returns>
        public bool Exists <TEntity>(QueryGroup where,
                                     string hints = null,
                                     IDbTransaction transaction = null)
            where TEntity : class
        {
            // Create a connection
            var connection = (transaction?.Connection ?? CreateConnection());

            try
            {
                // Call the method
                return(connection.Exists <TEntity>(where : where,
                                                   hints: hints,
                                                   commandTimeout: CommandTimeout,
                                                   transaction: transaction,
                                                   trace: Trace,
                                                   statementBuilder: StatementBuilder));
            }
            finally
            {
                // Dispose the connection
                DisposeConnectionForPerCall(connection, transaction);
            }
        }
コード例 #6
0
        /// <summary>
        /// Creates a SQL Statement for repository query operation that is meant for SQL Server.
        /// </summary>
        /// <typeparam name="TEntity">
        /// The data entity object bound for the SQL Statement to be created.
        /// </typeparam>
        /// <param name="queryBuilder">An instance of query builder used to build the SQL statement.</param>
        /// <param name="where">The query expression for SQL statement.</param>
        /// <param name="orderBy">The list of fields  to be used for ordering in SQL Statement composition.</param>
        /// <param name="top">The number of rows to be returned by the query operation in SQL Statement composition.</param>
        /// <returns>A string containing the composed SQL Statement for query operation.</returns>
        public string CreateQuery <TEntity>(QueryBuilder <TEntity> queryBuilder, QueryGroup where = null, IEnumerable <OrderField> orderBy = null, int?top = null)
            where TEntity : class
        {
            var properties = PropertyCache.Get <TEntity>(Command.Query);

            if (properties?.Any() == false)
            {
                throw new InvalidOperationException($"No queryable fields found from type '{typeof(TEntity).FullName}'.");
            }
            var fields = properties?.Select(property => new Field(property.GetMappedName().AsQuoted(true)));

            queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
            queryBuilder
            .Clear()
            .Select()
            .TopFrom(top)
            .FieldsFrom(fields)
            .From()
            .TableName()
            .WhereFrom(where)
            .OrderByFrom(orderBy)
            .End();
            return(queryBuilder.GetString());
        }
コード例 #7
0
 /// <summary>
 /// Appends a word WHERE and the stringified values of the <see cref="QueryGroup"/> to the SQL Query Statement.
 /// </summary>
 /// <param name="queryGroup">The query group to be stringified.</param>
 /// <param name="index">The parameter index.</param>
 /// <param name="dbSetting">The currently in used <see cref="IDbSetting"/> object.</param>
 /// <returns>The current instance.</returns>
 public QueryBuilder WhereFrom(QueryGroup queryGroup,
                               int index,
                               IDbSetting dbSetting)
 {
     return((queryGroup?.GetFields(true)?.Any() == true) ? Append(string.Concat("WHERE ", queryGroup.GetString(index, dbSetting))) : this);
 }
コード例 #8
0
 /// <summary>
 /// Appends a word WHERE and the stringified values of the <see cref="QueryGroup"/> to the SQL Query Statement.
 /// </summary>
 /// <param name="queryGroup">The query group to be stringified.</param>
 /// <param name="dbSetting">The currently in used <see cref="IDbSetting"/> object.</param>
 /// <returns>The current instance.</returns>
 public QueryBuilder WhereFrom(QueryGroup queryGroup,
                               IDbSetting dbSetting)
 {
     return(WhereFrom(queryGroup, 0, dbSetting));
 }
コード例 #9
0
ファイル: QueryBuilder.cs プロジェクト: softempire/RepoDb
 /// <summary>
 /// Appends a word WHERE and the stringified values of the <see cref="QueryGroup"/> to the SQL Query Statement.
 /// </summary>
 /// <param name="queryGroup">The query group to be stringified.</param>
 /// <param name="index">The parameter index.</param>
 /// <param name="dbSetting">The currently in used <see cref="IDbSetting"/> object.</param>
 /// <returns>The current instance.</returns>
 public QueryBuilder WhereFrom(QueryGroup queryGroup,
                               int index,
                               IDbSetting dbSetting)
 {
     return((queryGroup != null) ? Append(string.Concat("WHERE ", queryGroup.GetString(index, dbSetting))) : this);
 }
コード例 #10
0
        /// <summary>
        /// Creates a SQL Statement for repository inline update operation that is meant for SQL Server.
        /// </summary>
        /// <typeparam name="TEntity">
        /// The data entity object bound for the SQL Statement to be created.
        /// </typeparam>
        /// <param name="queryBuilder">An instance of query builder used to build the SQL statement.</param>
        /// <param name="fields">The list of the fields to be a part of inline update operation in SQL Statement composition.</param>
        /// <param name="where">The query expression for SQL statement.</param>
        /// <param name="overrideIgnore">
        /// Set to true if the defined <see cref="IgnoreAttribute"/> would likely
        /// be ignored on the inline update operation in SQL Statement composition.
        /// </param>
        /// <returns>A string containing the composed SQL Statement for inline-update operation.</returns>
        public string CreateInlineUpdate <TEntity>(QueryBuilder <TEntity> queryBuilder, IEnumerable <Field> fields = null,
                                                   QueryGroup where = null, bool?overrideIgnore = false)
            where TEntity : class
        {
            // Check for the fields presence
            if (fields == null)
            {
                throw new NullReferenceException("The target fields must be present.");
            }

            // Check for all the fields
            var properties = PropertyCache.Get <TEntity>(Command.None)?
                             .Select(property => property.GetMappedName());
            var unmatchesFields = fields?.Where(field =>
                                                properties?.FirstOrDefault(property =>
                                                                           field.Name.ToLower() == property.ToLower()) == null);

            if (unmatchesFields?.Count() > 0)
            {
                throw new InvalidOperationException($"The fields '{unmatchesFields.Select(field => field.AsField()).Join(", ")}' are not " +
                                                    $"present at type '{typeof(TEntity).FullName}'.");
            }

            // Important fields
            var primary  = PrimaryKeyCache.Get <TEntity>();
            var identity = IdentityCache.Get <TEntity>();

            if (identity != null && identity != primary)
            {
                throw new InvalidOperationException($"Identity property must be the primary property for type '{typeof(TEntity).FullName}'.");
            }

            // Variables
            var hasFields = fields?.Any(field => field.Name.ToLower() != primary?.GetMappedName().ToLower()) == true;

            // Check if there are fields
            if (hasFields == false)
            {
                throw new InvalidOperationException($"No inline updatable fields for object '{ClassMappedNameCache.Get<TEntity>()}'.");
            }

            // Append prefix to all parameters
            where?.AppendParametersPrefix();

            // Check for the unmatches
            if (overrideIgnore == false)
            {
                var updateableFields = PropertyCache.Get <TEntity>(Command.Update)
                                       .Select(property => property.GetMappedName());
                var inlineUpdateableFields = PropertyCache.Get <TEntity>(Command.InlineUpdate)
                                             .Select(property => property.GetMappedName())
                                             .Where(field => field.ToLower() != primary?.GetMappedName().ToLower() && updateableFields.Contains(field));
                var unmatchesProperties = fields?.Where(field =>
                                                        inlineUpdateableFields?.FirstOrDefault(property => field.Name.ToLower() == property.ToLower()) == null);
                if (unmatchesProperties.Count() > 0)
                {
                    throw new InvalidOperationException($"The fields '{unmatchesProperties.Select(field => field.AsField()).Join(", ")}' are not " +
                                                        $"inline updateable for object '{ClassMappedNameCache.Get<TEntity>()}'.");
                }
            }

            // Build the SQL Statement
            queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
            queryBuilder
            .Clear()
            .Update()
            .TableName()
            .Set()
            .FieldsAndParametersFrom(fields)
            .WhereFrom(where)
            .End();

            // Return the query
            return(queryBuilder.GetString());
        }
コード例 #11
0
        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="expression"></param>
        /// <param name="isNot"></param>
        /// <param name="isEqualsTo"></param>
        /// <returns></returns>
        private static QueryGroup ParseContainsForArrayOrList <TEntity>(MethodCallExpression expression,
                                                                        bool isNot,
                                                                        bool isEqualsTo)
            where TEntity : class
        {
            // TODO: Refactor this

            // Return null if there is no any arguments
            if (expression.Arguments?.Any() != true)
            {
                return(null);
            }

            // Get the last arg
            var last = expression
                       .Arguments
                       .LastOrDefault();

            // Make sure the last arg is a member
            if (last == null || last?.IsMember() == false)
            {
                throw new NotSupportedException($"Expression '{expression.ToString()}' is currently not supported.");
            }

            // Make sure it is a property info
            var member = last.ToMember().Member;

            if (member.IsPropertyInfo() == false)
            {
                throw new NotSupportedException($"Expression '{expression.ToString()}' is currently not supported.");
            }

            // Get the property
            var property = member.ToPropertyInfo();

            // Make sure the property is in the entity
            if (PropertyCache.Get <TEntity>().FirstOrDefault(p => string.Equals(p.PropertyInfo.Name, property.Name, StringComparison.OrdinalIgnoreCase)) == null)
            {
                throw new InvalidExpressionException($"Invalid expression '{expression.ToString()}'. The property {property.Name} is not defined on a target type '{typeof(TEntity).FullName}'.");
            }

            // Get the values
            var values = (object)null;

            // Array/List Separation
            if (expression.Object == null)
            {
                // Expecting an array
                values = expression.Arguments.First().GetValue();
            }
            else
            {
                // Expecting a list here
                values = expression.Object.GetValue();
            }

            // Add to query fields
            var operation  = (isNot == false && isEqualsTo == true) ? Operation.In : Operation.NotIn;
            var queryField = new QueryField(PropertyMappedNameCache.Get(property), operation, values);

            // Return the result
            var queryGroup = new QueryGroup(queryField);

            // Set the IsNot value
            queryGroup.SetIsNot(isNot == true && isEqualsTo == false);

            // Return the instance
            return(queryGroup);
        }
コード例 #12
0
        /// <summary>
        /// Creates a SQL Statement for repository batch-query operation that is meant for SQL Server.
        /// </summary>
        /// <typeparam name="TEntity">
        /// The data entity object bound for the SQL Statement to be created.
        /// </typeparam>
        /// <param name="queryBuilder">An instance of query builder used to build the SQL statement.</param>
        /// <param name="where">The query expression for SQL statement.</param>
        /// <param name="page">The page of the batch.</param>
        /// <param name="rowsPerBatch">The number of rows per batch.</param>
        /// <param name="orderBy">The list of fields used for ordering.</param>
        /// <returns>A string containing the composed SQL Statement for batch-query operation.</returns>
        public string CreateBatchQuery <TEntity>(QueryBuilder <TEntity> queryBuilder, QueryGroup where = null, int?page = null, int?rowsPerBatch = null, IEnumerable <OrderField> orderBy = null)
            where TEntity : class
        {
            var queryProperties      = PropertyCache.Get <TEntity>(Command.Query);
            var batchQueryProperties = PropertyCache.Get <TEntity>(Command.BatchQuery)
                                       .Where(property => queryProperties.Contains(property));
            var fields = batchQueryProperties.Select(property => new Field(property.GetMappedName()));

            // Validate the fields
            if (fields?.Any() == false)
            {
                throw new InvalidOperationException($"No batch queryable fields found from type '{typeof(TEntity).FullName}'.");
            }

            // Build the SQL Statement
            queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
            queryBuilder
            .Clear()
            .With()
            .WriteText("CTE")
            .As()
            .OpenParen()
            .Select()
            .RowNumber()
            .Over()
            .OpenParen()
            .OrderByFrom(orderBy)
            .CloseParen()
            .As("[RowNumber],")
            .FieldsFrom(fields)
            .From()
            .TableName()
            .WhereFrom(where)
            .CloseParen()
            .Select()
            .FieldsFrom(fields)
            .From()
            .WriteText("CTE")
            .WriteText($"WHERE ([RowNumber] BETWEEN {(page * rowsPerBatch) + 1} AND {(page + 1) * rowsPerBatch})")
            .OrderByFrom(orderBy)
            .End();

            // Return the query
            return(queryBuilder.GetString());
        }
コード例 #13
0
ファイル: QueryBuilder.cs プロジェクト: tommyannguyen/RepoDb
 /// <summary>
 /// Appends a word WHERE and the stringified values of the Query Group to the SQL Query Statement.
 /// </summary>
 /// <param name="queryGroup">The query group to be stringified.</param>
 /// <returns>The current instance.</returns>
 public QueryBuilder <TEntity> WhereFrom(QueryGroup queryGroup)
 {
     return((queryGroup != null) ? Append($"WHERE {queryGroup.FixParameters().GetString()}") : this);
 }
コード例 #14
0
ファイル: QueryBuilder.cs プロジェクト: tengtium/RepoDb
 /// <summary>
 /// Appends a word WHERE and the stringified values of the Query Group to the SQL Query Statement.
 /// </summary>
 /// <param name="queryGroup">The query group to be stringified.</param>
 /// <returns>The current instance.</returns>
 public QueryBuilder <TEntity> WhereFrom(QueryGroup queryGroup)
 {
     return((queryGroup != null) ? Append(string.Concat("WHERE ", queryGroup.Fix().GetString())) : this);
 }
コード例 #15
0
ファイル: Delete.cs プロジェクト: softempire/RepoDb
 /// <summary>
 /// Deletes an existing data from the database in an asynchronous way.
 /// </summary>
 /// <param name="where">The query expression to be used.</param>
 /// <param name="transaction">The transaction to be used.</param>
 /// <returns>The number of rows affected by the execution.</returns>
 public Task <int> DeleteAsync(QueryGroup where,
                               IDbTransaction transaction = null)
 {
     return(DbRepository.DeleteAsync <TEntity>(where : where,
                                               transaction: transaction));
 }
コード例 #16
0
        /// <summary>
        /// Creates a SQL Statement for batch query operation.
        /// </summary>
        /// <param name="queryBuilder">The query builder to be used.</param>
        /// <param name="tableName">The name of the target table.</param>
        /// <param name="fields">The list of fields to be queried.</param>
        /// <param name="page">The page of the batch.</param>
        /// <param name="rowsPerBatch">The number of rows per batch.</param>
        /// <param name="orderBy">The list of fields for ordering.</param>
        /// <param name="where">The query expression.</param>
        /// <param name="hints">The table hints to be used. See <see cref="SqlTableHints"/> class.</param>
        /// <returns>A sql statement for batch query operation.</returns>
        public string CreateBatchQuery(QueryBuilder queryBuilder,
                                       string tableName,
                                       IEnumerable <Field> fields,
                                       int page,
                                       int rowsPerBatch,
                                       IEnumerable <OrderField> orderBy = null,
                                       QueryGroup where = null,
                                       string hints     = null)
        {
            // Ensure with guards
            GuardTableName(tableName);

            // There should be fields
            if (fields?.Any() != true)
            {
                throw new NullReferenceException($"The list of queryable fields must not be null for '{tableName}'.");
            }

            // Validate order by
            if (orderBy == null || orderBy?.Any() != true)
            {
                throw new InvalidOperationException("The argument 'orderBy' is required.");
            }

            // Validate the page
            if (page < 0)
            {
                throw new InvalidOperationException("The page must be equals or greater than 0.");
            }

            // Validate the page
            if (rowsPerBatch < 1)
            {
                throw new InvalidOperationException($"The rows per batch must be equals or greater than 1.");
            }

            // Build the query
            (queryBuilder ?? new QueryBuilder())
            .Clear()
            .With()
            .WriteText("CTE")
            .As()
            .OpenParen()
            .Select()
            .RowNumber()
            .Over()
            .OpenParen()
            .OrderByFrom(orderBy)
            .CloseParen()
            .As("[RowNumber],")
            .FieldsFrom(fields)
            .From()
            .TableNameFrom(tableName)
            .HintsFrom(hints)
            .WhereFrom(where)
            .CloseParen()
            .Select()
            .FieldsFrom(fields)
            .From()
            .WriteText("CTE")
            .WriteText(string.Concat("WHERE ([RowNumber] BETWEEN ", (page * rowsPerBatch) + 1, " AND ", (page + 1) * rowsPerBatch, ")"))
            .OrderByFrom(orderBy)
            .End();

            // Return the query
            return(queryBuilder.GetString());
        }
コード例 #17
0
ファイル: Delete.cs プロジェクト: softempire/RepoDb
 /// <summary>
 /// Deletes an existing data from the database.
 /// </summary>
 /// <param name="where">The query expression to be used.</param>
 /// <param name="transaction">The transaction to be used.</param>
 /// <returns>The number of rows affected by the execution.</returns>
 public int Delete(QueryGroup where,
                   IDbTransaction transaction = null)
 {
     return(DbRepository.Delete <TEntity>(where : where,
                                          transaction: transaction));
 }
コード例 #18
0
 /// <summary>
 /// Appends a word WHERE and the stringified values of the <see cref="QueryGroup"/> to the SQL Query Statement.
 /// </summary>
 /// <param name="queryGroup">The query group to be stringified.</param>
 /// <returns>The current instance.</returns>
 public QueryBuilder WhereFrom(QueryGroup queryGroup)
 {
     return(WhereFrom(queryGroup, 0));
 }
コード例 #19
0
        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="expression"></param>
        /// <returns></returns>
        private static QueryGroup Parse <TEntity>(BinaryExpression expression)
            where TEntity : class
        {
            var leftQueryGroup  = (QueryGroup)null;
            var rightQueryGroup = (QueryGroup)null;
            var rightValue      = (object)null;
            var skipRight       = false;
            var isEqualsTo      = true;

            // TODO: Refactor this

            /*
             * LEFT
             */

            // Get the value in the right
            if (expression.IsExtractable())
            {
                rightValue = expression.Right.GetValue();
                skipRight  = true;
                if (rightValue is bool)
                {
                    isEqualsTo = Equals(rightValue, false) == false;
                }
            }

            // Binary
            if (expression.Left.IsBinary() == true)
            {
                leftQueryGroup = Parse <TEntity>(expression.Left.ToBinary());
                leftQueryGroup.SetIsNot(isEqualsTo == false);
            }
            // Unary
            else if (expression.Left.IsUnary() == true)
            {
                leftQueryGroup = Parse <TEntity>(expression.Left.ToUnary(), rightValue, expression.NodeType, isEqualsTo);
            }
            // MethodCall
            else if (expression.Left.IsMethodCall())
            {
                leftQueryGroup = Parse <TEntity>(expression.Left.ToMethodCall(), false, isEqualsTo);
            }
            else
            {
                // Extractable
                if (expression.IsExtractable())
                {
                    var queryField = QueryField.Parse <TEntity>(expression);
                    leftQueryGroup = new QueryGroup(queryField);
                    skipRight      = true;
                }
            }

            // Identify the node type
            if (expression.NodeType == ExpressionType.NotEqual)
            {
                leftQueryGroup.SetIsNot(leftQueryGroup.IsNot == isEqualsTo);
            }

            /*
             * RIGHT
             */

            if (skipRight == false)
            {
                // Binary
                if (expression.Right.IsBinary() == true)
                {
                    rightQueryGroup = Parse <TEntity>(expression.Right.ToBinary());
                }
                // Unary
                else if (expression.Right.IsUnary() == true)
                {
                    rightQueryGroup = Parse <TEntity>(expression.Right.ToUnary(), null, expression.NodeType, true);
                }
                // MethodCall
                else if (expression.Right.IsMethodCall())
                {
                    rightQueryGroup = Parse <TEntity>(expression.Right.ToMethodCall(), false, true);
                }

                // Return both of them
                if (leftQueryGroup != null && rightQueryGroup != null)
                {
                    var conjunction = (expression.NodeType == ExpressionType.OrElse) ? Conjunction.Or : Conjunction.And;
                    return(new QueryGroup(new[] { leftQueryGroup, rightQueryGroup }, conjunction));
                }
            }

            // Return either one of them
            return(leftQueryGroup ?? rightQueryGroup);
        }
コード例 #20
0
ファイル: QueryGroupTypeMap.cs プロジェクト: tengtium/RepoDb
 /// <summary>
 /// Creates an instance of <see cref="QueryGroupTypeMap"/> class.
 /// </summary>
 /// <param name="queryGroup">The <see cref="QueryGroup"/> object.</param>
 /// <param name="type">The type where the <see cref="QueryGroup"/> object is mapped.</param>
 public QueryGroupTypeMap(QueryGroup queryGroup, Type type)
 {
     QueryGroup = queryGroup;
     MappedType = type;
 }
コード例 #21
0
ファイル: SqlDbStatementBuilder.cs プロジェクト: ovidr/RepoDb
        /// <summary>
        /// Creates a SQL Statement for repository <i>BatchQuery</i> operation that is meant for SQL Server.
        /// </summary>
        /// <typeparam name="TEntity">
        /// The <i>DataEntity</i> object bound for the SQL Statement to be created.
        /// </typeparam>
        /// <param name="queryBuilder">An instance of query builder used to build the SQL statement.</param>
        /// <param name="where">The query expression for SQL statement.</param>
        /// <param name="page">The page of the batch.</param>
        /// <param name="rowsPerBatch">The number of rows per batch.</param>
        /// <param name="orderBy">The list of fields used for ordering.</param>
        /// <returns>A string containing the composed SQL Statement for <i>BatchQuery</i> operation.</returns>
        public string CreateBatchQuery <TEntity>(QueryBuilder <TEntity> queryBuilder, QueryGroup where, int page, int rowsPerBatch, IEnumerable <OrderField> orderBy)
            where TEntity : DataEntity
        {
            var queryProperties      = DataEntityExtension.GetPropertiesFor <TEntity>(Command.Query);
            var batchQueryProperties = DataEntityExtension.GetPropertiesFor <TEntity>(Command.BatchQuery)
                                       .Where(property => queryProperties.Contains(property));
            var fields = batchQueryProperties.Select(property => new Field(property.GetMappedName()));

            // Build the SQL Statement
            queryBuilder = queryBuilder ?? new QueryBuilder <TEntity>();
            queryBuilder
            .Clear()
            .With()
            .WriteText("CTE")
            .As()
            .OpenParen()
            .Select()
            .RowNumber()
            .Over()
            .OpenParen()
            .OrderByFrom(orderBy)
            .CloseParen()
            .As("[RowNumber],")
            .FieldsFrom(fields)
            .From()
            .TableFrom(Command.BatchQuery)
            .WhereFrom(where)
            .CloseParen()
            .Select()
            .FieldsFrom(fields)
            .From()
            .WriteText("CTE")
            .WriteText($"WHERE ([RowNumber] BETWEEN {(page * rowsPerBatch) + 1} AND {(page + 1) * rowsPerBatch})")
            .OrderByFrom(orderBy)
            .End();

            // Return the query
            return(queryBuilder.GetString());
        }
コード例 #22
0
        // Static Methods

        /// <summary>
        /// This method is used to parse the customized query tree expression. This method expects a dynamic object and converts it to the actual
        /// <i>RepoDb.QueryGroup</i> that defines the query tree expression.
        /// </summary>
        /// <param name="obj">
        /// A dynamic query tree expression to be parsed.
        /// Example:
        /// var expression = new { Conjunction = Conjunction.And, Company = "Microsoft",
        /// FirstName = new { Operation = Operation.Like, Value = "An%" },
        /// UpdatedDate = new { Operation = Operation.LessThan, Value = DateTime.UtcNow.Date }}
        /// </param>
        /// <returns>An instance of the <i>RepoDb.QueryGroup</i> object that contains the parsed query expression.</returns>
        public static QueryGroup Parse(object obj)
        {
            // Cannot further optimize and shortify this method, this one works like a charm for now.

            // Check for value
            if (obj == null)
            {
                throw new ArgumentNullException($"Parameter '{StringConstant.Obj.ToLower()}' cannot be null.");
            }

            // Variables
            var queryFields = new List <QueryField>();
            var queryGroups = new List <QueryGroup>();
            var conjunction = Conjunction.And;

            // Iterate every property
            var objectProperties = obj.GetType().GetTypeInfo().GetProperties();

            objectProperties.ToList().ForEach(property =>
            {
                var fieldName = property.Name;

                // Identify the fields
                if (string.Equals(fieldName, StringConstant.Conjunction, StringComparison.CurrentCultureIgnoreCase))
                {
                    // Throws an exception if conjunction is not a conjunction type
                    if (property.PropertyType != typeof(Conjunction))
                    {
                        throw new InvalidQueryExpressionException($"Conjunction field must be of type {typeof(Conjunction).FullName}.");
                    }

                    // Conjunction
                    conjunction = (Conjunction)property.GetValue(obj);
                }
                else if (string.Equals(fieldName, StringConstant.QueryGroups, StringComparison.CurrentCultureIgnoreCase))
                {
                    // Child QueryGroups
                    var value = property.GetValue(obj);
                    if (value is Array)
                    {
                        ((Array)value).AsEnumerable().ToList().ForEach(item =>
                        {
                            queryGroups.Add(Parse(item));
                        });
                    }
                    else
                    {
                        queryGroups.Add(Parse(value));
                    }
                }
                else
                {
                    // Other pre-defined fields
                    var value = property.GetValue(obj);
                    var type  = value?.GetType();

                    if (type?.GetTypeInfo().IsGenericType == false || value == null)
                    {
                        // Most likely, (Field.Name = <value|null>)
                        queryFields.Add(new QueryField(fieldName, value));
                    }
                    else
                    {
                        // Another dynamic object type, get the 'Operation' property
                        var properties        = type?.GetTypeInfo().GetProperties();
                        var operationProperty = properties?.FirstOrDefault(p => p.Name.ToLower() == StringConstant.Operation.ToLower());

                        // The property 'Operation' must always be present
                        if (operationProperty == null)
                        {
                            throw new InvalidQueryExpressionException($"The 'Operation' property must be present for field '{property.Name}'.");
                        }

                        // The property operatoin must be of type 'RepoDb.Enumerations.Operation'
                        if (operationProperty.PropertyType != typeof(Operation))
                        {
                            throw new InvalidQueryExpressionException($"The 'Operation' property for field '{property.Name}' must be of type '{typeof(Operation).FullName}'.");
                        }

                        // The 'Value' property must always be present
                        var valueProperty = properties?.FirstOrDefault(p => p.Name.ToLower() == StringConstant.Value.ToLower());

                        // Check for the 'Value' property
                        if (valueProperty == null)
                        {
                            throw new InvalidQueryExpressionException($"The 'Value' property for dynamic type query must be present at field '{property.Name}'.");
                        }

                        // Get the 'Operation' and the 'Value' value
                        var operation = (Operation)operationProperty.GetValue(value);
                        value         = valueProperty.GetValue(value);

                        // For other operation, the 'Value' property must be present
                        if (value == null && (operation != Operation.Equal && operation != Operation.NotEqual))
                        {
                            throw new InvalidQueryExpressionException($"The value property '{valueProperty.Name}' must not be null.");
                        }

                        // Identify the 'Operation' and parse the correct value
                        if ((operation == Operation.Equal || operation == Operation.NotEqual) && value == null)
                        {
                            // Most likely, new { Field.Name = { Operation = Operation.<Equal|NotEqual>, Value = (object)null } }
                            // It should be (IS NULL) or (IS NOT NULL) in SQL Statement
                            queryFields.Add(QueryField.Parse(fieldName, value));
                        }
                        else if (operation == Operation.All || operation == Operation.Any)
                        {
                            // Special case: All (AND), Any (OR)
                            if (value.GetType().IsArray)
                            {
                                var childQueryGroupFields = new List <QueryField>();
                                ((Array)value).AsEnumerable().ToList().ForEach(underlyingValue =>
                                {
                                    childQueryGroupFields.Add(QueryField.Parse(fieldName, underlyingValue));
                                });
                                var queryGroup = new QueryGroup(childQueryGroupFields, null, operation == Operation.All ? Conjunction.And : Conjunction.Or);
                                queryGroups.Add(queryGroup);
                            }
                            else
                            {
                                queryFields.Add(QueryField.Parse(fieldName, value));
                            }
                        }
                        else
                        {
                            if (operation == Operation.Between || operation == Operation.NotBetween)
                            {
                                // Special case: (Field.Name = new { Operation = Operation.<Between|NotBetween>, Value = new [] { value1, value2 })
                                ValidateBetweenOperations(fieldName, operation, value);
                            }
                            else if (operation == Operation.In || operation == Operation.NotIn)
                            {
                                // Special case: (Field.Name = new { Operation = Operation.<In|NotIn>, Value = new [] { value1, value2 })
                                ValidateInOperations(fieldName, operation, value);
                            }
                            else
                            {
                                // Other Operations
                                ValidateOtherOperations(fieldName, operation, value);
                            }

                            // Add the field values
                            queryFields.Add(new QueryField(fieldName, operation, value));
                        }
                    }
                }
            });

            // Return
            return(new QueryGroup(queryFields, queryGroups, conjunction).FixParameters());
        }
コード例 #23
0
        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="TEntity"></typeparam>
        /// <param name="expression"></param>
        /// <param name="rightValue"></param>
        /// <param name="expressionType"></param>
        /// <param name="isNot"></param>
        /// <param name="isEqualsTo"></param>
        /// <returns></returns>
        private static QueryGroup Parse <TEntity>(MemberExpression expression,
                                                  object rightValue,
                                                  ExpressionType expressionType,
                                                  bool isNot,
                                                  bool isEqualsTo)
            where TEntity : class
        {
            var queryGroup   = (QueryGroup)null;
            var value        = rightValue;
            var isForBoolean = expression.Type == typeof(bool) &&
                               (expressionType == ExpressionType.Not || expressionType == ExpressionType.AndAlso || expressionType == ExpressionType.OrElse);
            var ignoreIsNot = false;

            // Handle for boolean
            if (value == null)
            {
                if (isForBoolean)
                {
                    value       = false;
                    ignoreIsNot = true;
                }
                else
                {
                    value = expression.GetValue();
                }
            }

            // Check if there are values
            if (value != null)
            {
                // Specialized for enum
                if (expression.Type.IsEnum)
                {
                    value = Enum.ToObject(expression.Type, value);
                }

                // Create a new field
                var field = (QueryField)null;

                if (isForBoolean)
                {
                    field = new QueryField(expression.Member.GetMappedName(),
                                           value);
                    ignoreIsNot = true;
                }
                else
                {
                    field = new QueryField(expression.Member.GetMappedName(),
                                           QueryField.GetOperation(expressionType),
                                           value);
                }

                // Set the query group
                queryGroup = new QueryGroup(field);

                // Set the query group IsNot property
                if (ignoreIsNot == false)
                {
                    queryGroup.SetIsNot(isEqualsTo == false);
                }
            }

            // Return the result
            return(queryGroup);
        }