NorthwindSqlQuerySqlServerTest.cs

// Licensed to the .NET Foundation under one or more agreements.
// The .NET Foundation licenses this file to you under the MIT license.

using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore.TestModels.Northwind;
namespace Microsoft.EntityFrameworkCore.Query;

#nullable disable

public class NorthwindSqlQuerySqlServerTest : NorthwindSqlQueryTestBase<NorthwindQuerySqlServerFixture<NoopModelCustomizer>>
{
    public NorthwindSqlQuerySqlServerTest(NorthwindQuerySqlServerFixture<NoopModelCustomizer> fixture, ITestOutputHelper testOutputHelper)
        : base(fixture)
        => Fixture.TestSqlLoggerFactory.SetTestOutputHelper(testOutputHelper);

    [ConditionalFact]
    public virtual void Check_all_tests_overridden()
        => TestHelpers.AssertAllMethodsOverridden(GetType());

    public override async Task SqlQueryRaw_over_int(bool async)
    {
        await base.SqlQueryRaw_over_int(async);

        AssertSql(
            """
SELECT "ProductID" FROM "Products"
""");
    }

    public override async Task SqlQuery_composed_Contains(bool async)
    {
        await base.SqlQuery_composed_Contains(async);

        AssertSql(
            """
SELECT [o].[OrderID], [o].[CustomerID], [o].[EmployeeID], [o].[OrderDate]
FROM [Orders] AS [o]
WHERE [o].[OrderID] IN (
    SELECT [s].[Value]
    FROM (
        SELECT "ProductID" AS "Value" FROM "Products"
    ) AS [s]
)
""");
    }

    public override async Task SqlQuery_composed_Join(bool async)
    {
        await base.SqlQuery_composed_Join(async);

        AssertSql(
            """
SELECT [o].[OrderID], [o].[CustomerID], [o].[EmployeeID], [o].[OrderDate], CAST([s].[Value] AS int) AS [p]
FROM [Orders] AS [o]
INNER JOIN (
    SELECT "ProductID" AS "Value" FROM "Products"
) AS [s] ON [o].[OrderID] = CAST([s].[Value] AS int)
""");
    }

    public override async Task SqlQuery_over_int_with_parameter(bool async)
    {
        await base.SqlQuery_over_int_with_parameter(async);

        AssertSql(
            """
p0='10'

SELECT "ProductID" FROM "Products" WHERE "ProductID" = @p0
""");
    }

    [ConditionalFact]
    public virtual void Projection_binding_clean_up_non_nullable_value_type()
    {
        using var context = Fixture.CreateContext();
        
        var query = context.Set<Customer>()
            .Select(c => c.CustomerID) 
            .ToQueryString();
        var result = context.Set<Customer>().Select(c => c.CustomerID).ToList();
        Assert.NotEmpty(result);
    }

    [ConditionalFact]
    public virtual void Projection_binding_stays_nullable_for_nullable_types()
    {
        using var context = Fixture.CreateContext();
        var result = context.Set<Employee>().Select(e => e.ReportsTo).ToList();
        
        Assert.Contains(null, result); 
    }
    protected override DbParameter CreateDbParameter(string name, object value)
        => new SqlParameter { ParameterName = name, Value = value };

    private void AssertSql(params string[] expected)
        => Fixture.TestSqlLoggerFactory.AssertBaseline(expected);
}