The expected type was 'System.Nullable`1[System.Boolean]' but the actual value was of type 'System.Int32

[sepehr1014]

It seems there's a serious issue in the translation of this query:

myOrderedQueryable.Select(u => new MyModel
                {
                    CanSeeOnlineStatus = (
                        currentUser.OnlineShownToEveryone ||
                        (currentUser.OnlineShownToContacts && db.UserContactNumbers.Any(n => n.User_Id == currentUser.Id && n.Number == u.PhoneNumber)) ||
                        (currentUser.OnlineShownToFollowings && db.UserFollowers.Any(f => f.Follower_Id == currentUser.Id && f.User_Id == u.Id)) ||
                        (currentUser.OnlineShownToFollowers && db.UserFollowers.Any(f => f.Follower_Id == u.Id && f.User_Id == currentUser.Id))
                    ) && (
                        u.OnlineShownToEveryone ||
                        (u.OnlineShownToContacts && db.UserContactNumbers.Any(n => n.User_Id == u.Id && n.Number == currentUser.PhoneNumber)) ||
                        (u.OnlineShownToFollowings && db.UserFollowers.Any(f => f.Follower_Id == u.Id && f.User_Id == currentUser.Id)) ||
                        (u.OnlineShownToFollowers && db.UserFollowers.Any(f => f.Follower_Id == currentUser.Id && f.User_Id == u.Id))
                    )
                }).Take(myCount).ToArray();

System.InvalidOperationException: An exception occurred while reading a database value. The expected type was 'System.Nullable`1[System.Boolean]' but the actual value was of type 'System.Int32'. ---> System.InvalidCastException: Unable to cast object of type 'System.Int32' to type 'System.Nullable`1[System.Boolean]'.
   at Microsoft.EntityFrameworkCore.Metadata.Internal.EntityMaterializerSource.TryReadValue[TValue](ValueBuffer valueBuffer, Int32 index, IPropertyBase property)
   --- End of inner exception stack trace ---
   at Microsoft.EntityFrameworkCore.Metadata.Internal.EntityMaterializerSource.ThrowReadValueException[TValue](Exception exception, Object value, IPropertyBase property)
   at Microsoft.EntityFrameworkCore.Metadata.Internal.EntityMaterializerSource.TryReadValue[TValue](ValueBuffer valueBuffer, Int32 index, IPropertyBase property)
   at lambda_method(Closure , QueryContext , ValueBuffer )
   at Microsoft.EntityFrameworkCore.Query.ExpressionVisitors.Internal.ProjectionShaper.TypedProjectionShaper`3.Shape(QueryContext queryContext, ValueBuffer valueBuffer)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.BufferlessMoveNext(DbContext _, Boolean buffer)
   at Microsoft.EntityFrameworkCore.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.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
...

Steps to reproduce

Use the above query. It should return bool but returns int instead.

Further technical details

EF Core version: 2.1.0-preview1-final
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows Server 2016
Using in-memory tables in SQL Server 2017

[ilmax]

@sepehr1014 Can you share also your model?

[divega]

@sepehr1014 yes, it would really help if you can provide a repro project.

[sepehr1014]

@ilmax @divega Sorry for the delay. The model is pretty normal & it doesn't even map to a table. It's just a query model (not specified as such in the context's OnModelCreating though)

private class FriendModel
        {
            public string Id { get; set; }
            public string FullName { get; set; }
            public string Picture { get; set; }
            public DateTime? LastOnlineDate { get; set; }
            public string UserName { get; set; }
            public bool CanSeeOnlineStatus { get; set; }
        }

[maumar]

I could not reproduce the issue. I used the following repro:

            using (var ctx = new MyContext())
            {
                ctx.Database.EnsureDeleted();
                ctx.Database.EnsureCreated();

                var f1 = new UserEntity
                {
                    OnlineShownToContacts = true,
                    OnlineShownToEveryone = true,
                    OnlineShownToFollowers = true,
                    OnlineShownToFollowings = true,
                    PhoneNumber = "12345"
                };

                var f2 = new UserEntity
                {
                    OnlineShownToContacts = false,
                    OnlineShownToEveryone = false,
                    OnlineShownToFollowers = false,
                    OnlineShownToFollowings = false,
                    PhoneNumber = "54321"
                };

                ctx.Friends.AddRange(f1, f2);
                ctx.SaveChanges();

                ctx.Database.EnsureCreated();
            }

            using (var db = new MyContext())
            {
                var myCount = 2;

                var currentUser = new UserEntity
                {
                    Id = 1,
                    OnlineShownToContacts = true,
                    OnlineShownToEveryone = true,
                    OnlineShownToFollowers = true,
                    OnlineShownToFollowings = true,
                    PhoneNumber = "12345"
                };

                var query = db.Friends.Select(u => new MyModel
                {
                    CanSeeOnlineStatus = (
                                        currentUser.OnlineShownToEveryone ||
                                        (currentUser.OnlineShownToContacts && db.UserContactNumbers.Any(n => n.User_Id == currentUser.Id && n.Number == u.PhoneNumber)) ||
                                        (currentUser.OnlineShownToFollowings && db.UserFollowers.Any(f => f.Follower_Id == currentUser.Id && f.User_Id == u.Id)) ||
                                        (currentUser.OnlineShownToFollowers && db.UserFollowers.Any(f => f.Follower_Id == u.Id && f.User_Id == currentUser.Id))
                                    ) && (
                                        u.OnlineShownToEveryone ||
                                        (u.OnlineShownToContacts && db.UserContactNumbers.Any(n => n.User_Id == u.Id && n.Number == currentUser.PhoneNumber)) ||
                                        (u.OnlineShownToFollowings && db.UserFollowers.Any(f => f.Follower_Id == u.Id && f.User_Id == currentUser.Id)) ||
                                        (u.OnlineShownToFollowers && db.UserFollowers.Any(f => f.Follower_Id == currentUser.Id && f.User_Id == u.Id))
                                    )
                }).Take(myCount).ToArray();
            }

        public class MyModel
        {
            public bool? CanSeeOnlineStatus { get; set; }
        }

        public class UserEntity
        {
            public int Id { get; set; }

            public bool OnlineShownToEveryone { get; set; }
            public bool OnlineShownToContacts { get; set; }
            public bool OnlineShownToFollowings { get; set; }
            public string PhoneNumber { get; set; }

            public bool OnlineShownToFollowers { get; set; }
        }

        public class MyContext : DbContext
        {
            public DbSet<UserEntity> Friends { get; set; }
            public DbSet<UserFollower> UserFollowers { get; set; }
            public DbSet<UserContactNumber> UserContactNumbers { get; set; }

            protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
            {
               optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Repro11516;Trusted_Connection=True;MultipleActiveResultSets=True");
            }
        }

        public class UserFollower
        {
            public int Id { get; set; }
            public int Follower_Id { get; set; }
            public int User_Id { get; set; }
        }

        public class UserContactNumber
        {
            public int Id { get; set; }
            public int User_Id { get; set; }
            public string Number { get; set; }
        }

        public class FriendModel
        {
            public string Id { get; set; }
            public string FullName { get; set; }
            public string Picture { get; set; }
            public DateTime? LastOnlineDate { get; set; }
            public string UserName { get; set; }
            public bool CanSeeOnlineStatus { get; set; }
        }

@sepehr1014 please modify the code above so that it reproduces the problem you are seeing and re-open the issue.

This issue could already be fixed by a recent commit: 3902d94, although I tested the repro above on a code that didn't have the fix yet.

[sepehr1014]

@maumar Thank you for following up on this matter. I will test and try to reproduce this error. BTW we're using memory optimized tables which are not created by EF. Does that make any difference regarding this issue?

[smitpatel]

@sepehr1014 - Memory optimized table should not make any difference when it comes to query. It only affects DDL & update SQLs.

[olofd]

I'm seeing a similar error on 2.1.0-preview2-final. Did not see this on preview1.

System.InvalidOperationException: An exception occurred while reading a database value. The expected type was 'HansaEvent.Db.Entities.tblTeam' but the actual value was of type 'System.Int32'. ---> System.InvalidCastException: Unable to cast object of type 'System.Int32' to type 'HansaEvent.Db.Entities.tblTeam'.

From a simple query looking like this:

            var teamAndCompetition = await (from competition in Db.tblCompetitions
                                            let team = competition.Teams.FirstOrDefault(b => b.TeamName == teamName)
                                            where competition.CompetitionCode == competitionCode && team != null && !competition.Deleted
                                            select new
                                            {
                                                competition.CompetitionId,
                                                team.TeamId
                                            }).FirstOrDefaultAsync();

[olofd]

Rewriting to this fixed the issue for me:

            var teamAndCompetition = await (from competition in Db.tblCompetitions
                                            join team in Db.tblTeams on competition.CompetitionId equals team.CompetitionId
                                            where team.TeamName == teamName && competition.CompetitionCode == competitionCode && !competition.Deleted
                                            select new
                                            {
                                                competition.CompetitionId,
                                                team.TeamId
                                            }).FirstOrDefaultAsync();

But that should not be necessary.

[maumar]

@olofd this is a different issue caused by bug around 'let'. Filed a new issue to track this: #11728

[sepehr1014]

Should be fixed by #12280