Problem reading a boolean from SQL server using EF core

Question

1.00/5 (1 vote)

See more:

Im getting an exception reading a Sql Server bit into a C# bool: Unable to cast object of type 'System.Byte' to type 'System.Boolean'.

This is the log:

INFO  06-11-2019 18:30:27 Executed DbCommand (9ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT TOP(1) [c].[Company_No], [c].[Accept_Changeovers], [c].[ActionsJsonKey], [c].[Address1], [c].[Address2], [c].[Address3], [c].[Address4], [c].[Allow_Credit_Sales], [c].[Allow_Multiple_Vals], [c].[Allow_User_Overrides], [c].[Allow_Broken_Journeys], [c].[Allow_Credit_Journeys], [c].[Authority], [c].[CMDS_Proxy], [c].[ClaimType], [c].[ClientId], [c].[Company_Name], [c].[Console_Email], [c].[DefaultChallengeScheme], [c].[Default_Credit_Ticket_Type], [c].[DefaultScheme], [c].[Download_Location], [c].[Export], [c].[Export_Location], [c].[Fixed_Month_End], [c].[Fixed_Week_End], [c].[Fixed_Year_End], [c].[GetClaimsFromUserInfoEndpoint], [c].[Installation_ID], [c].[Max_Changeover_Interval], [c].[Max_Credit_Journeys], [c].[Max_Journey_Break_Time], [c].[Office_Level], [c].[Receipt_Print], [c].[ResponseType], [c].[SaveTokens], [c].[Ticket_eMail_Address], [c].[Ticket_eMail_From], [c].[Ticket_eMail_Password], [c].[Ticket_eMail_Port], [c].[Ticket_eMail_Smtp], [c].[Ticket_eMail_Username], [c].[Unreg_Loading], [c].[Upload_Location]
FROM [ConfigMaster] AS [c]
ERROR 06-11-2019 18:30:34 An exception occurred while iterating over the results of a query for context type 'VT_LocalDataHandler.Models.Contexts.ApplicationDbContext'.
System.InvalidCastException: Unable to cast object of type 'System.Byte' to type 'System.Boolean'.
   at System.Data.SqlClient.SqlBuffer.get_Boolean()
   at System.Data.SqlClient.SqlDataReader.GetBoolean(Int32 i)
   at lambda_method(Closure , DbDataReader )
   at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
   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 System.Linq.Enumerable.TryGetFirst[TSource](IEnumerable`1 source, Boolean& found)
   at lambda_method(Closure )
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ResultEnumerable`1.GetEnumerator()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities[TOut,TIn](IEnumerable`1 results, QueryContext queryContext, IList`1 entityTrackingInfos, IList`1 entityAccessors)+MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
ERROR 06-11-2019 18:30:40 An exception occurred while iterating over the results of a query for context type 'VT_LocalDataHandler.Models.Contexts.ApplicationDbContext'.
System.InvalidCastException: Unable to cast object of type 'System.Byte' to type 'System.Boolean'.
   at System.Data.SqlClient.SqlBuffer.get_Boolean()
   at System.Data.SqlClient.SqlDataReader.GetBoolean(Int32 i)
   at lambda_method(Closure , DbDataReader )
   at Microsoft.EntityFrameworkCore.Storage.Internal.TypedRelationalValueBufferFactory.Create(DbDataReader dataReader)
   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 System.Linq.Enumerable.TryGetFirst[TSource](IEnumerable`1 source, Boolean& found)
   at lambda_method(Closure )
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ResultEnumerable`1.GetEnumerator()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider._TrackEntities[TOut,TIn](IEnumerable`1 results, QueryContext queryContext, IList`1 entityTrackingInfos, IList`1 entityAccessors)+MoveNext()
   at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext()
   at System.Linq.Enumerable.TryGetFirst[TSource](IEnumerable`1 source, Boolean& found)
   at System.Linq.Enumerable.First[TSource](IEnumerable`1 source)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass15_1`1.<CompileQueryCore>b__0(QueryContext qc)

This is how I have created the table:

CREATE TABLE configmaster (
  Office_Level int NOT NULL DEFAULT '0',
  Fixed_Week_End smallint DEFAULT NULL,
  Fixed_Month_End smallint DEFAULT NULL,
  Export_Location nvarchar(90) NOT NULL DEFAULT '',
  Download_Location nvarchar(90) NOT NULL DEFAULT '',
  Upload_Location nvarchar(90) NOT NULL DEFAULT '',
  Receipt_Print bit NOT NULL DEFAULT '0',
  Allow_Multiple_Vals bit NOT NULL DEFAULT '0',
  Allow_Credit_Sales bit NOT NULL DEFAULT '0',
  Max_Credit_Journeys smallint DEFAULT NULL,
  Allow_Credit_Journeys bit NOT NULL DEFAULT '0',
  Address4 nvarchar(max) DEFAULT NULL,
  Address3 nvarchar(max) DEFAULT NULL,
  Address2 nvarchar(max) DEFAULT NULL,
  Address1 nvarchar(max) DEFAULT NULL,
  Company_No int NOT NULL DEFAULT '0',
  Company_Name nvarchar(90) NOT NULL DEFAULT '',
  Default_Credit_Ticket_Type nvarchar(30) DEFAULT NULL,
  console_email nvarchar(max) DEFAULT NULL,
  Accept_Changeovers bit NOT NULL,
  Max_Changeover_Interval time NOT NULL,
  Allow_Broken_Journeys bit NOT NULL,
  Max_Journey_Break_Time time DEFAULT NULL,
  Installation_ID smallint  NOT NULL DEFAULT '0',
  CMDS_Proxy nvarchar(max),
  Ticket_eMail_Address nvarchar(max) NOT NULL,
  Ticket_eMail_Username nvarchar(max) DEFAULT NULL,
  Ticket_eMail_Password nvarchar(100) DEFAULT NULL,
  Ticket_eMail_From nvarchar(max) DEFAULT NULL,
  Ticket_eMail_Smtp nvarchar(max) DEFAULT NULL,
  Unreg_Loading decimal(3,2) NOT NULL DEFAULT '0.00',
  Export bit NOT NULL DEFAULT '0',
  Allow_User_Overrides bit NOT NULL DEFAULT '0',
  Fixed_Year_End date DEFAULT NULL,
  Ticket_eMail_Port int DEFAULT NULL,
  CMDS_User nvarchar(max) DEFAULT NULL,
  CMDS_PasswordHash varchar(100) DEFAULT NULL,
  Authority NVARCHAR(100) NULL,
  RequireHttpsMetadata bit NULL,
  GetClaimsFromUserInfoEndpoint  bit NULL,
  ClientId NVARCHAR(45),
  ResponseType NVARCHAR(45),
  SaveTokens TINYINT NULL,
  DefaultScheme NVARCHAR(100),
  DefaultChallengeScheme NVARCHAR(100) DEFAULT NULL,
  ClaimType NVARCHAR(45) NULL,
  ActionsJsonKey NVARCHAR(45),
  PRIMARY KEY (Company_No),
CONSTRAINT ConfigMaster_dw_fk_1 FOREIGN KEY (Fixed_Week_End) REFERENCES [DaysOfWeek] ([Id]),
);

and these are the mapped properties:

protected int m_Office_Level;
protected short m_FixedMonthEnd;
protected short m_FixedWeekEnd;
protected string m_ExportLocation;
protected string m_DownloadLocation;
protected string m_UploadLocation;
protected bool m_ReceiptPrint;
protected bool m_AllowMultipleValidations;
protected bool  m_AllowCreditSales;
protected short m_Max_Credit_Journeys;
protected bool m_Allow_Credit_Journeys;
protected string m_Address4;
protected string m_Address3;
protected string m_Address2;
protected string m_Address1;
protected string m_CompanyName;
protected string m_DefaultCreditJourneyTicketType;
protected int m_CompanyNo;
protected string m_ConsoleEmail;
protected bool m_Accept_Changeovers;
protected TimeSpan m_Max_Changeover_Interval;
protected bool m_Allow_Broken_Journeys;
protected TimeSpan m_Max_Journey_Break_Time;
protected short m_Installation_ID;
protected string m_CMDS_Proxy;
protected string m_Ticket_eMail_Address;//actual address tickets mailed fromm
protected string m_Ticket_eMail_Username;
protected string m_Ticket_eMail_Password;
protected string m_Ticket_eMail_From;//The From address that will appear on the mail, if empty use company mail / ticket mail
protected string m_Ticket_eMail_Smtp;
protected float m_UnregLoading;
protected bool m_Export;
protected bool m_AllowUserOverrides;
protected DateTime m_FixedYearEnd;

protected Nullable<Int32> m_Ticket_eMail_Port;
protected string m_Authority;
protected bool m_RequireHttpsMetadata;
protected string m_ClientId;
protected string m_ResponseType;
protected bool m_SaveTokens;
protected bool m_GetClaimsFromUserInfoEndpoint;
protected string m_DefaultScheme;
protected string m_DefaultChallengeScheme;
protected string m_ClaimType;
protected string m_ActionsJsonKey;

What I have tried:

Looked this up online and looked for possible alternatives, I have called the definitions off against each other.

Posted 6-Nov-19 8:00am

Ger Hayden

Updated 8-Nov-19 9:11am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2019-11-06T09:19:00

Solution 1

Seems you need to use a.... value converter. Please, read this: Value Conversions - EF Core | Microsoft Docs[^]. All you need to do is to set value converter on OnModelCreating method.

Good luck!

Posted 6-Nov-19 9:19am

Maciej Los

Comments

Ger Hayden 6-Nov-19 15:26pm

Thanks, I will try that. All the research i did had me thinking it was a straight load. I have used OnModelCreating previously for composite keys. I could also do something with properties and map that way.

Ger Hayden 7-Nov-19 2:10am

I haven't tested it yet, but these look like my choices,

Using OnModelCreating

             builder.Entity<CConfigMasterBase>().Property(e => e.p_Accept_Changeovers).HasConversion<bool?>(f => f, t => t ?? false);

Or similar to something I've done before (battling the coercion bug in MySQL):

 
        [Column("Accept_Changeovers")]
        public Int16 p_Accept_Changeovers
        {
            get
            {
                return (m_Accept_Changeovers);
            }
            set
            {
                m_Accept_Changeovers = value;
            }
        }
        [NotMapped]
        public bool p_Accept_ChangeoversCoerced
        {
            get
            {
                return (p_Accept_Changeovers == 1);
            }
            set
            {
                p_Accept_Changeovers = value ? (Int16)1 : (Int16)0;
            }
        }

At face value the OnModelCreating is neater, but can I also update the column or is it a read only solution?

Richard Deeming · Answer 2 · 2019-11-08T09:12:00

Solution 2

Quote:

SQL

SaveTokens TINYINT NULL,

C#

protected bool m_SaveTokens;

tinyint in SQL maps to byte in C#, not bool. Either your database or your C# model is incorrect.

Posted 8-Nov-19 9:12am

Richard Deeming

Comments

Ger Hayden 8-Nov-19 15:46pm

I discovered this after solution one failed by commenting them all out, consulting my copy of Freeman and bringing them back in small groups.