SqlAndHavingAttribute.SqlAndHavingAttribute Class

.NET Standard 2.x | Current Version (1.0.1)

0.5.0-alpha

1.0.1 (current)

Sets an additional search condition on top of the existing search condition for a group or an aggregate in the model class. Both conditions must be met. Multiple SqlAndHaving attributes can be applied to one model class.

Namespace: SnapObjects.Data

Assembly: SnapObjects.Data.dll

Inherited Constructor

SnapObjects.Data.SqlHavingAttribute

Syntax

   [AttributeUsage(AttributeTargets.Class | AttributeTargets.Struct, AllowMultiple = true)]
   public class SqlAndHavingAttribute : SqlHavingAttribute

Constructors

Name	Description
SqlAndHavingAttribute(string rawCondition)	Initializes a new instance of the SqlAndHavingAttribute class.
SqlAndHavingAttribute(string left, string right)	Initializes a new instance of the SqlAndHavingAttribute class.

Properties

Name	Return Type	Description
Left	string	Gets the raw SQL expression on the left of the operator.
Operator	SqlBinaryOperator	Gets or sets the operator to test the two expression on the left and right.
RawCondition	string	Gets the string of the raw SQL search condition if it has been specified in the constructor.
RawConditionDefined	bool	Gets whether the raw SQL search condition has been specified in the constructor.
Right	string	Gets the raw SQL expression on the right of the operator.

Remarks

The SqlAndHaving attribute must be used together with the SqlHaving attribute.

The SqlAndHaving attribute denotes that a model class should add an AND logical operator and another search condition in the existing HAVING clause of the SQL SELECT statement.

Examples

The following code example demonstrates how to use the AndHaving attribute. It applies two AndHaving attributes to the StudentGradeSqlHaving class, and each AndHavingAttribute instance adds the AND logical operator and a search condition to the HAVING clause of the SQL SELECT statement.

Model: StudentGradeSqlHaving

using SnapObjects.Data;
using System;
using System.ComponentModel.DataAnnotations.Schema;

namespace Appeon.ApiDoc.Models.School
{
    /// <summary>
    /// This model class maps to the dbo.StudentGrade table.
    /// </summary>
    [Table("StudentGrade", Schema = "dbo")]
    [SqlGroupBy("StudentID")]
    [SqlHaving("Sum(Grade) is not null")]
    [SqlAndHaving("Sum(Grade) < 5")]
    [SqlAndHaving("Sum(Grade)", "2", Operator = SqlBinaryOperator.GreaterThan)]

    public class StudentGradeSqlHaving
    {
        public Int32 StudentID { get; set; }

        [SqlCompute(alias: "SumGrade", expression: "Sum(Grade)")]
        public decimal? SumGrade { get; set; }

    }
}

Example Method:

using SnapObjects.Data;
using Appeon.ApiDoc.Models.School;
using System;

namespace Appeon.ApiDoc.SqlAndHavingAttributeExamples
{
    public class SqlAndHavingAttributeExample
    {
        private SchoolContext _context;

        public SqlAndHavingAttributeExample(SchoolContext dataContext)
        {
            // Sets the data context.
            _context = dataContext;
        }

        public void Example()
        {
            // Gets the SQL statement.
            string sql = ModelSqlBuilder.GetBuilder<StudentGradeSqlHaving>(_context).QuerySql;

            Console.WriteLine("SQL statement:");
            Console.WriteLine(sql);

            // Executes the SQL by a SqlExecutor object. 
            var resultSet = _context.SqlExecutor.Select<StudentGradeSqlHaving>(sql);

            /* This code example produces the following output:
            
            SQL statement:
            SELECT
             [StudentID],
            SUM([Grade]) AS [SumGrade]
            FROM [dbo].[StudentGrade]
            GROUP BY
             [StudentID]
            HAVING (SUM([Grade]) IS NOT NULL
            AND SUM([Grade]) < 5
            AND SUM([Grade]) > = 2)
            */
        }
    }
}

Applies to

.NET Standard

2.x