ISqlJoinBuilder.FullJoinRaw(string joinClause, string builderAlias) Method

.NET Standard 2.x

Uses the FULL OUTER JOIN operator to join the original table source directly with a raw SQL to create a joined table. You need to specify an alias for the raw SQL.

Namespace: SnapObjects.Data

Assembly: SnapObjects.Data.dll

Syntax

  ISqlJoinOnBuilder FullJoinRaw(string joinClause, string builderAlias);

Parameters

joinClause System.String

A string of raw SQL after the FULL OUTER JOIN keyword.

builderAlias System.String

The alias for the raw SQL.

Returns

SnapObjects.Data.ISqlJoinOnBuilder

Returns an ISqlJoinOnBuilder object which can be used to create the condition on which the join is based.

Examples

The following code example demonstrates how to join a table with another using the FullJoinRaw method.

using SnapObjects.Data;
using System;

namespace Appeon.ApiDoc.ISqlJoinBuilderExamples
{
    public class FullJoinRawExample
    {
        private readonly SchoolContext _context;

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

        public void Example2()
        {
            var sqlQueryBuilder = new SqlQueryBuilder();

            // Defines a query "C" to get the number for different DepartmentID in the Course table.
            // Gets DepartmentID and Name from the "Department" table, and the number from query C.
            // Sets to join "Department" table and query C by: Department.DepartmentID = C.DepartmentID.   
            // Defines a query (named as "C"), and gets the count of courses in each department from the "Course" table.
            // Gets DepartmentID values and Name values from the "Department" table, and the count of courses returned by the above query.
            // Sets the condition to join the "Department" table and the derived table is: Department.DepartmentID = C.DepartmentID.              
            sqlQueryBuilder
                .Select("Department.DepartmentID")
                .Select("Department.Name")
                .Select("C.CourseCount")
                .From("Department")
                .FullJoinRaw("(Select DepartmentID, count(1) as CourseCount From " +
                    "Course  group by DepartmentID ) C " +
                    "On Department.DepartmentID = C.DepartmentID"
                    , "Course");

            // Converts to raw SQL for the database corresponding to the data context.
            string sql = sqlQueryBuilder.ToSqlString(_context);

            Console.WriteLine(sql);
            
            /*This code example produces the following output:           
              SELECT
                [Department].[DepartmentID],
                [Department].[Name],
                [C].[CourseCount]
              FROM [Department]
              FULL JOIN (SELECT
                            [DepartmentID],
                            COUNT(1) AS [CourseCount]
                         FROM [Course]
                         GROUP BY
                               [DepartmentID] ) [C]
              ON [Department].[DepartmentID] = [C].[DepartmentID]
            */
        }
    }
}

Applies to

.NET Standard

2.x