SubQueryAttribute Class

.NET Standard 2.x

Defines a subquery in the model class.

Namespace: SnapObjects.Data

Assembly: SnapObjects.Data.dll

Inheritance Constructor

System.Attribute

Syntax

   [AttributeUsage(AttributeTargets.Class | AttributeTargets.Struct, AllowMultiple = true)]
   public class SubQueryAttribute : Attribute, ISubQuery

Constructors

Name	Description
SubQueryAttribute(string name, string rawSql)	Initializes a new instance of the SubQueryAttribute class.
SubQueryAttribute(string name, Type modelType)	Initializes a new instance of the SubQueryAttribute class.
SubQueryAttribute(string name, Type modelType, string selectName)	Initializes a new instance of the SubQueryAttribute class.

Properties

Name	Return Type	Description
IsRawSql	bool	Gets whether a raw SQL has been specified for the subquery.
ModelType	Type	Gets the type of the model that is specified for the subquery.
Name	string	Gets the name of the subquery.
RawSql	string	Gets the raw SQL that is specified for the subquery.
SelectName	string	Gets the Select Name (SqlSelectAttribute.SelectName) that is predefined in the model type.

Remarks

The subquery can be referenced by its name in the SQL expression parameter for the other attributes in the model class. The format to make reference: $SubQuery(Name).

Examples

Example 1

The following code example demonstrates how to use constructor: SubQuery (name, rawSql) to apply the SubQuery attribute to the DepartmentSubQuery class. It defines a SubQuery named "departmentIds" by a string of raw SQL, and references it in the constructor of the SqlWhere attribute.

Model: DepartmentSubQuery

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

namespace Appeon.ApiDoc.Models.School
{
    /// <summary>
    /// This model class maps to the dbo.Department table.
    /// </summary>
    [SubQuery("departmentIds", rawSql: @"select DepartmentID from Course")]
    [FromTable("Department", Schema = "dbo")]
    [SqlWhere("DepartmentID in $SubQuery(departmentIds)")]
    public class DepartmentSubQuery
    {
        [Key]
        public int DepartmentID { get; set; }

        public string Name { get; set; }

        public decimal Budget { get; set; }

        public DateTime StartDate { get; set; }

        public int? Administrator { get; set; }

    }
}

Example Method: Example1

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

namespace Appeon.ApiDoc.SubQueryAttributeExamples
{
    public class SubQueryAttributeExample
    {
        private SchoolContext _context;

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

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

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

            // Executes the SQL statement.
            var departments = _context.SqlExecutor.Select<DepartmentSubQuery>(sql);

            /* This code example produces the following output:
            
            SQL statement:
            SELECT
             [DepartmentID],
            [Name],
            [Budget],
            [StartDate],
            [Administrator]
            FROM [dbo].[Department]
            WHERE ([DepartmentID] IN (SELECT
             [DepartmentID]
            FROM [Course]))
            */
        }
    }
}

Example 2

The following code example demonstrates how to use constructor: SubQuery (name, modelType) to apply the SubQuery attribute to the DepartmentSubQuery2 class. It defines a SubQuery named "departmentIds" by another model class, and references it in the constructor of the SqlWhere attribute.

Model: DepartmentSubQuery2

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

namespace Appeon.ApiDoc.Models.School
{
    /// <summary>
    /// This model class maps to the dbo.Department table.
    /// </summary>
    [SubQuery("departmentIds", typeof(CourseSubQuery2))]
    [FromTable("Department", Schema = "dbo")]
    [SqlWhere("DepartmentID in $SubQuery(departmentIds)")]
    public class DepartmentSubQuery2
    {
        [Key]
        public int DepartmentID { get; set; }

        public string Name { get; set; }

        public decimal Budget { get; set; }

        public DateTime StartDate { get; set; }

        public int? Administrator { get; set; }

    }
}

Model: CourseSubQuery2

using System;
using System.ComponentModel.DataAnnotations.Schema;

namespace Appeon.ApiDoc.Models.School
{
    /// <summary>
    /// This model class maps to the dbo.Course table. 
    /// </summary>
    [Table("Course", Schema = "dbo")]
    public class CourseSubQuery2
    {
        public int DepartmentID { get; set; }

    }
}

Example Method: Example2

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

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

            // Executes the SQL statement.
            var departments = _context.SqlExecutor.Select<DepartmentSubQuery2>(sql);

            /* This code example produces the following output:
            
            SQL statement:
            SELECT
             [DepartmentID],
            [Name],
            [Budget],
            [StartDate],
            [Administrator]
            FROM [dbo].[Department]
            WHERE ([DepartmentID] IN (SELECT
             [DepartmentID]
            FROM [dbo].[Course]))
            */
        }

Example 3

The following code example demonstrates how to use constructor: SubQuery (name, modelType, selectName) to apply the SubQuery attribute to the DepartmentSubQuery3 class. It defines a subquery named "departmentIds" by another model class and specifies the select list by the selectName parameter, then references the subquery in the constructor of the SqlWhere attribute.

Model: DepartmentSubQuery3

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

namespace Appeon.ApiDoc.Models.School
{
    /// <summary>
    /// This model class maps to the dbo.Department table.
    /// </summary>
    [SubQuery("departmentIds", typeof(CourseSubQuery3), "DepartmentIDs")]
    [FromTable("Department", Schema = "dbo")]
    [SqlWhere("DepartmentID in $SubQuery(departmentIds)")]
    public class DepartmentSubQuery3
    {
        [Key]
        public int DepartmentID { get; set; }

        public string Name { get; set; }

        public decimal Budget { get; set; }

        public DateTime StartDate { get; set; }

        public int? Administrator { get; set; }

    }
}

Model: CourseSubQuery3

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

namespace Appeon.ApiDoc.Models.School
{
    /// <summary>
    /// This model class maps to the dbo.Course table. 
    /// </summary>
    [SqlSelect("DepartmentIds", RawSelect = "DepartmentID")]
    [Table("Course", Schema = "dbo")]
    public class CourseSubQuery3
    {
        [Key]
        public int CourseID { get; set; }

        public string Title { get; set; }

        public int Credits { get; set; }

        public int DepartmentID { get; set; }

    }
}

Example Method: Example3

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

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

            // Executes the SQL statement.
            var departments = _context.SqlExecutor.Select<DepartmentSubQuery3>(sql);

            /* This code example produces the following output:
            
            SQL statement:
            SELECT
             [DepartmentID],
            [Name],
            [Budget],
            [StartDate],
            [Administrator]
            FROM [dbo].[Department]
            WHERE ([DepartmentID] IN (SELECT
             [DepartmentID]
            FROM [dbo].[Course]))
            */
        }

Applies to

.NET Standard

2.x