"Failed to convert parameter value from a String to a Int32." when using subquery and grouping.

[ttv86]

When trying to create a query that contains a sub query that contains a string parameter and then grouping the result, Entity Framework tries to convert given string parameter to Int32.

Similar to #10871, but happens in latest version (2.2.6) of EF core.

Exception message:
System.FormatException: 'Failed to convert parameter value from a String to a Int32.'
Inner Exception
FormatException: Input string was not in a correct format.
Stack trace:
   at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
   at System.Data.SqlClient.SqlParameter.GetCoercedValue()
   at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)
   at System.Data.SqlClient.SqlCommand.BuildParamList(TdsParser parser, SqlParameterCollection parameters)
   at System.Data.SqlClient.SqlCommand.BuildExecuteSql(CommandBehavior behavior, String commandText, SqlParameterCollection parameters, _SqlRPC& rpc)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, DbCommandMethod executeMethod, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_GroupBy>d__23`3.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackGroupedEntities>d__21`2.MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Linq.Enumerable.ToDictionary[TSource,TKey,TElement](IEnumerable`1 source, Func`2 keySelector, Func`2 elementSelector, IEqualityComparer`1 comparer)
   at System.Linq.Enumerable.ToDictionary[TSource,TKey,TElement](IEnumerable`1 source, Func`2 keySelector, Func`2 elementSelector)
   at Program.Main() in Program.cs:line 17

Steps to reproduce

Console application (.NET Core)
Target framework: netcoreapp2.1

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;

public static class Program
{
    public static void Main()
    {
        var context = new Context(new DbContextOptionsBuilder<Context>().UseSqlServer("Server=.\\SQLEXPRESS;Initial Catalog=Test;Trusted_Connection=True").Options);

        string searchValue = "TestValue";
        var query = context.ProductChangeLog
            .Where(x => context.Products.Where(y => y.ProductParameters.FirstOrDefault(p => p.ParameterId == 1).ParameterValue == searchValue).Select(y => y.Id).Contains(x.ProductId))
            .GroupBy(x => x.ProductId);

        var list = query.ToDictionary(x => x.Key, x => x.ToList()); // <-- this line throws an exception "Failed to convert parameter value from a String to a Int32."
    }
}

public class Context : DbContext
{
    public Context(DbContextOptions<Context> options) : base(options) { }

    public DbSet<Product> Products { get; set; }
    public DbSet<ProductChangeLog> ProductChangeLog { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<ProductParameter>().HasKey(x => new { x.ProductId, x.ParameterId });
    }
}

public class Product
{
    public int Id { get; set; }
    public virtual ICollection<ProductParameter> ProductParameters { get; set; }
}

public class ProductParameter
{
    public int ProductId { get; set; }
    public int ParameterId { get; set; }
    public string ParameterValue { get; set; }
}

public class ProductChangeLog
{
    public int Id { get; set; }
    public int ProductId { get; set; }
}

Further technical details

EF Core version: 2.2.6
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10 1903
IDE: Visual Studio 2019 (version 16.2)

[ajcvickers]

Putting this on the backlog for translation since I can't find a specific duplicate.

[PawelGerr]

I'm getting the same error when using subqueries along with global filters.

// inside DbContext
private string _localeFilter; 
private bool IsFilterDisabled => _localeFilter == null;

----------------------

// in OnModelCreating
var translationBuilder = modelBuilder.Entity<ProductTranslation>();
translationBuilder.HasQueryFilter(t => IsFilterDisabled || t.Locale == _localeFilter);

----------------------

IQueryable<ProductTranslation> translations = Context.ProductTranslations;
IQueryable<Guid> ids = translations.Select(i => i.ProductId);

var list = translations.Where(t => ids.Contains(t.ProductId)).ToList(); // throws FormatException

The error is raised in EF Core 2.2.6 only. No exception is thrown when updating to 3.0.

[smitpatel]

Query uses client side group by is not supported 3.x onwards. See #19929