SqlUnionAttribute Class

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

0.5.0-alpha

1.0.1 (current)

Denotes that a model class should combine the results of two or more queries into one single result. One or more SqlUnion attributes can be applied to one model class.

Namespace: SnapObjects.Data

Assembly: SnapObjects.Data.dll

Inheritance Constructor

System.Attribute

Syntax

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

Constructors

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

Properties

Name	Return Type	Description
IsRawSql	bool	Gets whether a raw SQL has been specified for the union operation.
ModelType	Type	Gets the type of the model that is specified for the union operator.
RawSql	string	Gets the raw SQL specified for the union operator.
SelectName	string	Gets the name of the select list (SqlSelectAttribute.SelectName) predefined in the model type specified by the union operator.
UnionAll	bool	Gets or sets whether to use UNION ALL.

Remarks

The SqlUnion attribute denotes that a model class should use an UNION operator in the SQL SELECT statement to combine the results of two or more queries into one single result.

Examples

Example 1

The following code example demonstrates how to use the SqlUnion attribute to specify a raw SQL SELECT whose result set will be combined with the current SQL query.

Model: DepartmentSqlUnion

using SnapObjects.Data;
using System;

namespace Appeon.ApiDoc.Models.School
{
    /// <summary>
    /// This model class maps to the dbo.Department and dbo.Person tables.
    /// </summary>
    [FromTable("Department", Schema = "dbo")]
    [SqlUnion("select LastName as Name from person")]
    public class DepartmentSqlUnion
    {
        public String Name { get; set; }

    }
}

Example Method: Example1

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

namespace Appeon.ApiDoc.SqlUnionAttributeExamples
{
    public class SqlUnionAttributeExample
    {
        private SchoolContext _context;

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

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

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

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

            /* This code example produces the following output:
            
            SQL statement:
            SELECT
                [Name]
            FROM [dbo].[Department]
            UNION SELECT
                [LastName] AS [Name]
            FROM [person]
            */
        }
    }
}

Example 2

The following code example demonstrates how to use the SqlUnion attribute tospecify a model class whose SQL SELECT statement will be combined with the current SQL SELECT statement.

Model: DepartmentSqlUnion2

using SnapObjects.Data;
using System;

namespace Appeon.ApiDoc.Models.School
{
    /// <summary>
    /// This model class maps to the dbo.Department and dbo.Person tables.
    /// </summary>
    [FromTable("Department", Schema = "dbo")]
    [SqlUnion(typeof(PersonSqlUnion4), UnionAll = true)]
    public class DepartmentSqlUnion2
    {
        public String Name { get; set; }

    }
}

Model: PersonSqlUnion4

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

namespace Appeon.ApiDoc.Models.School
{
    /// <summary>
    /// This model class maps to the dbo.Person table.
    /// </summary>
    [Table("Person", Schema = "dbo")]
    [UpdateWhereStrategy(UpdateWhereStrategy.KeyColumns)]
    public class PersonSqlUnion4
    {        
        [SqlColumn("Person", "LastName")]
        public String Name { get; set; }

    }
}

Example Method: Example2

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

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

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

            /* This code example produces the following output:
            
            SQL statement:
            SELECT
             [Name]
            FROM [dbo].[Department]
            UNION ALL
             SELECT
             [Person].[LastName]
            FROM [dbo].[Person]
            */
        }

Example 3

The following code example demonstrates how to use the SqlUnion attribute to specify a model class whose SQL SELECT statement will be combined with the current SQL statement, and specify the predefined Select Name to change the Select list.

Model: DepartmentSqlUnion3

using SnapObjects.Data;
using System;

namespace Appeon.ApiDoc.Models.School
{
    /// <summary>
    /// This model class maps to the dbo.Department table.
    /// </summary>
    [FromTable("Department", Schema = "dbo")]
    [SqlUnion(typeof(DepartmentSqlSelect), "sqlselectname", UnionAll = true)]
    public class DepartmentSqlUnion3
    {
        public String Name { get; set; }

    }
}

Model: DepartmentSqlSelect

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>
    [FromTable("Department", Schema = "dbo")]
    [SqlSelect("sqlselect", RawSelect = "DepartmentID, Name")]
    [SqlSelect("sqlselect2", RawSelect = "DepartmentID, Name, Budget, StartDate")]
    [SqlSelect("sqlselectname", RawSelect = "Name")]

    public class DepartmentSqlSelect
    {
        [Key]
        public Int32 DepartmentID { get; set; }

        public String Name { get; set; }

        public Decimal Budget { get; set; }

        public DateTime StartDate { get; set; }

        public Int32? Administrator { get; set; }

    }
}

Example Method: Example3

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

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

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

            /* This code example produces the following output:
            
            SQL statement:
            SELECT
             [Name]
            FROM [dbo].[Department]
            UNION ALL
             SELECT
             [Name]
            FROM [dbo].[Department]
            */
        }

Applies to

.NET Standard

2.x