My legacy SQL Server database stores bool values as integers.
I know how to setup this conversion. And it works in simple queries like:
var query = db.TableChild.Select(a => new { a.BoolField }).ToList();
But when doing query with one-to-one relation the exception has occurred:
System.InvalidOperationException: An exception occurred while reading a database value. The expected type was 'System.Boolean' but the actual value was of type 'System.Int32'. ---> System.InvalidCastException: Unable to cast object of type 'System.Int32' to type 'System.Boolean'.
If I change the relation to table join or use an Include, everything works fine, types are converted.
Bad query with exception:
var query = db.TableParent
.Where(a => a.Child != null)
.Select(a => new { a.Child.BoolField })
.ToList();
Relation is created as:
entityTypeChild
.HasOne(b => b.Parent)
.WithOne(a => a.Child)
.HasForeignKey<TableChild>(b => b.ParentRefId)
.HasPrincipalKey<TableParent>(a => a.RefId);
Tables are mapped as:
class TableParent
{
[Key]
public long Id { get; set; }
[Required]
public string RefId { get; set; }
public TableChild Child { get; set; }
}
class TableChild
{
[Key]
public long Id { get; set; }
[Required]
public string ParentRefId { get; set; }
[Column(TypeName = "int")]
public bool BoolField { get; set; }
public TableParent Parent { get; set; }
}
Produced SQL is normal:
SELECT [a.Child].[BoolField]
FROM [TableParent] AS [a]
LEFT JOIN [TableChild] AS [a.Child] ON [a].[RefId] = [a.Child].[ParentRefId]
WHERE [a.Child].[Id] IS NOT NULL
I expect this linq query to work without exceptions and not ignore Column(TypeName = "int")
attribute