System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Boolean'.

[zeje]

Steps to reproduce

    [Table("c_user_house")]
    public partial class UserHouseDO : BaseIdDO
    {
        //***other properties

        /// <summary>
        ///房屋编码
        /// </summary> 
        [Column("house_code")]
        public string HouseCode { get; set; }
        /// <summary>
        ///用户id
        /// </summary> 
        [Column("user_uid")]
        public string UserUid { get; set; }
        /// <summary>
        ///是否删除，0：未删除，1：已删除
        /// </summary> 
        [Column("is_del")]
        public bool? IsDel { get; set; }
    }

/// <summary>
    /// 客房关系
    /// </summary>
    public class HouseCustomerRelationData
    {
        //***other properties

        /// <summary>
        /// 是否删除
        /// </summary>
        public bool isDelete { get; set; }
    }

var query = from uh in dbContext.Set<UserHouseDO>()
                        join h in dbContext.Set<HouseDO>()
                        on uh.HouseCode equals h.HouseCode
                        join u in dbContext.Set<UserDO>()
                        on uh.UserUid equals u.UserUid
                        join b in dbContext.Set<BuildingDO>()
                        on h.BuildingCode equals b.BuildingCode
                        where h.ProjectCode == projectCode
                        orderby uh.LastupdateTime
                        select new HouseCustomerRelationData()
                        {
                           //***
                            isDelete = uh.IsDel ?? false
                        };

System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Boolean'.

I found that:

EF Core Query to Sql

uh.IsDel ?? false

=>

    CAST(
        COALESCE (`uh`.`is_del`, FALSE) AS CHAR
    )

And that, I got Exception Message: System.InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Boolean'.

uh.IsDel.HasValue ? uh.IsDel.Value :  false

=>

    (
        CASE
        WHEN `uh`.`is_del` IS NOT NULL THEN
            `uh`.`is_del`
        ELSE
            FALSE
        END
    ) AS `isDelete`

And that, I got Exception Message: System.InvalidCastException: Unable to cast object of type 'System.Decimal' to type 'System.Boolean'.

Further technical details

EF Core version: 2.2 Database provider: (e.g. Pomelo.EntityFrameworkCore.MySql) Target framework: (e.g. .NET Core 2.2) Operating system: Windows 10、Windows Server 2016 IDE: (e.g. Visual Studio Community 2017 15.9.18)

[ajcvickers]

@zeje This is likely to be an issue with the MySQL provider in 2.2. There have been extensive updates to both EF Core and the Pomelo MySQL provider in 3.1, so please try updating to 3.1.

[ajcvickers]

EF Team Triage: Closing this issue as the requested additional details have not been provided and we have been unable to reproduce it.

BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.

[imkaty]

I am getting a similar issue using EntityFrameworkCore version 3.1.5 for a .NET Core 3.1.4 app. I generated database models using the scaffolding technique in Visual Studio's (v 16.5.5) Package Manager Console ("Scaffold-DbContext … OutputDir Models") which resulted in a model class like this:

    public int IndexItemId { get; set; }
    public int IndexId { get; set; }
    public int? ParentId { get; set; }
    public bool? IsSubheading { get; set; }

I then scaffolded Razor Pages using Entity Framework (CRUD) using the 'Add New Scaffolded Item' dialog box. All of the resulting pages cannot be viewed and result in an error screen that has at the top:

           InvalidCastException: Unable to cast object of type 'System.String' to type 'System.Boolean'.Microsoft.Data.SqlClient.SqlBuffer.get_Boolean()

[ajcvickers]

@imkaty Please attach a small, runnable project or post a small, runnable code listing that reproduces what you are seeing so that we can investigate.

[Ben-krebs]

My business is experiencing a similar problem, and have been for several months.

The system is an ASP.Net core project (.Net 5, going to 6 soon) hosted on Azure and using EF core. and will run on several web app instances throughout the day.

Over the last several months, without warning, one of the instances will begin throwing a large number of errors, it appears like nearly every entity framework query will fail with an invalid cast exception while all other instances continue to work fine. The only solution we have is to restart the instance. We have not been able to identify the cause, and this issue has been occurring intermittently, sometimes once per month, other times it occurs multiple times per day.

The errors all occur on one instance

The errors are spread across very many different operations

System.InvalidCastException: Unable to cast object of type 'System.Int32' to type 'System.Boolean'. 
   at Microsoft.Data.SqlClient.SqlBuffer.get_Boolean (Microsoft.Data.SqlClient, Version=3.0.0.0, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5)
   at lambda_method5665 (Anonymously Hosted DynamicMethods Assembly, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1+Enumerator.MoveNext (Microsoft.EntityFrameworkCore.Relational, Version=5.0.7.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)
   at System.Linq.Enumerable.SingleOrDefault (System.Linq, Version=5.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a)
   at Microsoft.EntityFrameworkCore.Internal.EntityFinder`1.Find (Microsoft.EntityFrameworkCore, Version=5.0.7.0, Culture=neutral, PublicKeyToken=adb9793829ddae60)

Any assistance would be greatly appreciated

Update: it seems to be related to: https://stackoverflow.com/questions/43001329/new-request-is-not-allowed-to-start-because-it-should-come-with-valid-transacti

[Baranitharan84]

Any update on this issue? I'm facing the exact same issue at our end.

[Ben-krebs]

Yes, we were eventually able to solve this issue. After months of struggling and many calls with Microsoft technicians we were eventually able to pinpoint it.

On our end there was an issue where the database connection would get into a bad state and would try to parse the result from one query to another. This was because of some incorrect async code: We were calling UpdateFromQueryAsync, but not awaiting it. [image: image.png] Around the same time we removed the ARRAfinity cookie and InProc Session which which meant that if one instance went down that users could be directed onto the others

If this is not the problem for you, or if you cannot find any similar code, then a temporary solution is to call this method when you detect the problem.

SqlConnection.ClearAllPools();

I hope that helps, it was a horrible bug for us to go through, and a simple fix in the end.

Cheers, Ben

On Fri, Oct 6, 2023 at 10:41 AM Baranitharan Natarajan < @.***> wrote:

Any update on this issue? I'm facing the exact same issue at our end.

— Reply to this email directly, view it on GitHub https://github.com/dotnet/efcore/issues/19370#issuecomment-1749690171, or unsubscribe https://github.com/notifications/unsubscribe-auth/AJSSPQTAQWZOAYIDCU54HYLX54SQLAVCNFSM4J5X7UZ2U5DIOJSWCZC7NNSXTN2JONZXKZKDN5WW2ZLOOQ5TCNZUHE3DSMBRG4YQ . You are receiving this because you commented.Message ID: @.***>