Petapoco issue with nullable SQl Server smallint and int16 in vb.net
I have an SQL Server table with a view and a smallint field. All data in the view are automatically marked as nullable from Petapoco T4 generator since you can't specifiy it in a view. I'm fine with all the other datatypes so far (guid, int, tinyint, string, etc.) but it seems that the nullable smallint cause some issues.
The T4 generator in VB.NET creates this for the SMALLINT field:
Private mPasswordResetDays As Integer?
<Column> _
Public Property PasswordResetDays() As Integer?
Get
Return mPasswordResetDays
End Get
Set
mPasswordResetDays = Value
End Set
End Property
BUt I receive an exception:
Cast from 'System.Int16' to 'System.Nullable`1[[System.Int32, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]'
At Petapoco line 2677:
Line 2675: else
Line 2676: {
Line 2677: converter = src => Convert.ChangeType(src, dstType, null);
Line 2678: }
Line 2679: }
The SQL Command executed from PetaPoco is quite simple:
SQL Command: SELECT UserId, AllowPersistentCookie, MinPasswordLenght,
NonAlphanumericPasswordChars, AlphanumericPassword, PasswordResetDays FROM
EVA_vw_UserLoginStatusFromRoles
WHERE UserId = @0 -> @0 [String] = "d85674ab-d389-4548-ad89-5322892ca0e2"
I think a similar issue has been noted on ExecuteScalar command returning null but the line is different and the code too.
If I change the type from Integer? to Integer, making them not nullable, everything works fine.
Any hint on this issue with Petapoco and SQL Server Smallints?
ADDENDUM: the view code is:
SELECT a.UserId, CAST(MIN(CAST(b.AllowPersistentCookie AS int)) AS bit) AS AllowPersistentCookie, MAX(b.MinPasswordLenght) AS MinPasswordLenght,
CAST(MAX(CAST(b.NonAlphanumericPasswordChars AS int)) AS bit) AS NonAlphanumericPasswordChars, CAST(MAX(CAST(b.AlphanumericPassword AS int)) AS bit)
AS AlphanumericPassword, MIN(b.PasswordResetDays) AS PasswordResetDays
FROM dbo.EVA_UsersInRoles AS a INNER JOIN
dbo.EVA_Roles AS b ON a.RoleId = b.RoleId
WHERE (DATEDIFF(day, CONVERT(date, GETUTCDATE()), a.StartDate) <= 0) AND (DATEDIFF(day, CONVERT(date, GETUTCDATE()), a.EndDate) >= 0) OR
(DATEDIFF(day, CONVERT(date, GETUTCDATE()), a.StartDate) <= 0) AND (a.EndDate IS NULL) OR
(DATEDIFF(day, CONVERT(date, GETUTCDATE()), a.EndDate) >= 0) AND (a.StartDate IS NULL) OR
(a.EndDate IS NULL) AND (a.StartDate IS NULL)
GROUP BY a.UserId
Issue for this plus a solution is at https://github.com/toptensoftware/PetaPoco/issues/153
PetaPoco needs patching with:
- converter = delegate(object src) { return Convert.ChangeType(src, dstType, null); };
+ var underlyingType = Nullable.GetUnderlyingType(dstType) ?? dstType;
+ converter = src => Convert.ChangeType(src, underlyingType, null);
Views are a red herring; you can reproduce this on a concrete table by returning an int NULL column as a uint? or similar.
It seams that you generated the code using the table but now you are fetching the values from a view. SQL must be hinting the column as non nullable from the view.
链接地址: http://www.djcxy.com/p/42826.html