IQueryWhereBuilder.WhereNotExists(ISqlBuilder sqlBuilder) Method

.NET Standard 2.x

Creates a WHERE clause, and adds a search condition to the WHERE clause. Uses the NOT EXISTS operator to test if the record returned from a subquery does not exist.

Namespace: SnapObjects.Data

Assembly: SnapObjects.Data.dll

Syntax

IQueryAndOrBuilder<TModel> WhereNotExists(ISqlBuilder sqlBuilder);

Parameters

sqlBuilder SnapObjects.Data.ISqlBuilder

An ISqlBuilder object which represents a SQL subquery on the right of the NOT EXISTS operator.

Returns

IQueryAndOrBuilder<TModel>

Returns an IQueryAndOrBuilder<TModel> object which can be used to add more search conditions to the current WHERE clause.

Examples

The following code example uses the WhereNotExists method to specify that the "departmentid=3" value of the "Department" table must not exist in the result set of the subquery.

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

namespace Appeon.ApiDoc.IQueryWhereBuilderExamples
{
    public class WhereNotExistsExample
    {
        private SchoolContext _context;
        
        public WhereNotExistsExample(SchoolContext dataContext)
        {
            // Sets Data Context
            _context = dataContext;
        }

        public async Task<int> Example()
        {
            // Get a QueryBuilder.
            var Builder = _context.SqlModelMapper.GetQueryBuilder<Department>();
            
            // Defines a subquery.
            var subsqlbuilder = new SqlQueryBuilder();
            subsqlbuilder.Select("1")
                .From("Department")
                .Where("departmentid", SqlBuilder.Parameter<int>("id"));
                
            // Specifies the WHERE EXIST condition:
            // the data exists in the result set of the subquery.
            Builder.WhereNotExists(subsqlbuilder);
            
            // Data with departmentID equal to 3 does not exist.
            var result = (await Builder.LoadAsync(3)).ToList();
            
            if (result.Count != 0)
            {
                // If the result of subquery does not exist, the data can be loaded.
                Console.WriteLine("The result of the subquery does not exist.");
            }
            else
            {
                // If the result of subquery exists, the data cannot be loaded.
                Console.WriteLine("The result of the subquery exists.");
            }
            
            return result.Count;
            
            /*This code example produces the following output:   
             
                The result of the subquery does not exist.
            */
        }
    }
}

Example Refer To

Model Class: Department

Applies to

.NET Standard

2.x