SqlServer: Unable to compare text with null

[smitpatel]

Using typeMapping of text from column to add parameter of null value throws with following error
Test: Can_query_using_any_mapped_data_types_with_nulls

string param30 = null;
                Assert.Same(entity, context.Set<MappedNullableDataTypes>().Single(e => e.Int == 911 && e.StringAsText == param30));

Generated SQL

exec sp_executesql N'SELECT TOP(2) [m].[Int], [m].[BoolAsBit], [m].[ByteAsTinyint], [m].[BytesAsBinaryVaryingMax], [m].[BytesAsImage], [m].[BytesAsVarbinaryMax], [m].[CharAsAsCharVaryingMax], [m].[CharAsCharacterVaryingMax], [m].[CharAsInt], [m].[CharAsNationalCharVaryingMax], [m].[CharAsNationalCharacterVaryingMax], [m].[CharAsNtext], [m].[CharAsNvarcharMax], [m].[CharAsText], [m].[CharAsVarcharMax], [m].[DateTimeAsDate], [m].[DateTimeAsDatetime], [m].[DateTimeAsDatetime2], [m].[DateTimeAsSmalldatetime], [m].[DateTimeOffsetAsDatetimeoffset], [m].[Decimal], [m].[DecimalAsDec], [m].[DecimalAsMoney], [m].[DecimalAsNumeric], [m].[DecimalAsSmallmoney], [m].[DoubleAsDoublePrecision], [m].[DoubleAsFloat], [m].[EnumAsNvarchar20], [m].[EnumAsVarcharMax], [m].[FloatAsReal], [m].[GuidAsUniqueidentifier], [m].[LongAsBigint], [m].[SByteAsSmallint], [m].[SbyteAsTinyint], [m].[ShortAsSmallint], [m].[SqlVariantInt], [m].[SqlVariantString], [m].[StringAsCharVaryingMax], [m].[StringAsCharacterVaryingMax], [m].[StringAsNationalCharVaryingMax], [m].[StringAsNationalCharacterVaryingMax], [m].[StringAsNtext], [m].[StringAsNvarcharMax], [m].[StringAsText], [m].[StringAsVarcharMax], [m].[TimeSpanAsTime], [m].[UShortAsInt], [m].[UShortAsSmallint], [m].[UintAsBigint], [m].[UintAsInt], [m].[UlongAsBigint], [m].[UlongAsDecimal200]
FROM [MappedNullableDataTypes] AS [m]
WHERE ([m].[Int] = 911) AND ((([m].[StringAsText] = @__param30_0) AND ([m].[StringAsText] IS NOT NULL AND @__param30_0 IS NOT NULL)) OR ([m].[StringAsText] IS NULL AND @__param30_0 IS NULL))',N'@__param30_0 varchar(8000)',@__param30_0=NULL

Message: 
    Microsoft.Data.SqlClient.SqlException : The data types text and varchar are incompatible in the equal to operator.
  Stack Trace: 
    at SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    at TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    at SqlDataReader.TryConsumeMetaData()
    at SqlDataReader.get_MetaData()
    at SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
    at SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
    at SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
    at SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    at SqlCommand.ExecuteReader(CommandBehavior behavior)
    at SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
    at DbCommand.ExecuteReader()
    at RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues, IDiagnosticsLogger`1 logger) in RelationalCommand.cs line: 239
    at RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues, IDiagnosticsLogger`1 logger) in RelationalCommand.cs line: 158
    at Enumerator.MoveNext() in QueryingEnumerable.cs line: 103
    at Enumerable.Single[TSource](IEnumerable`1 source)
    at QueryCompiler.Execute[TResult](Expression query) in QueryCompiler.cs line: 99
    at EntityQueryProvider.Execute[TResult](Expression expression) in EntityQueryProvider.cs line: 79
    at Queryable.Single[TSource](IQueryable`1 source, Expression`1 predicate)
    at BuiltInDataTypesSqlServerTest.Can_query_using_any_mapped_data_types_with_nulls() in BuiltInDataTypesSqlServerTest.cs line: 574

[smitpatel]

There are multiple others like that image to varbinary, ntext to varchar.
If the type mapping is right then we should block server translation.

[smitpatel]

Related #15586