user1490835 user1490835 - 6 months ago 11
Vb.net Question

Creating an object in C# behaves differently on different SQL servers

I have following code which is behaving differently on different servers. In the below method if i write this line of code:

Dim customerPositionsFromPaid = vwCustomerPositionInPaid.SelectAll().Where(conditions).Select(Function(o) New CustomerPositionFromPaidDto(o.FundingYearId.Value, o.DsoId.Value, o.CustomerId.Value, o.CustomerPosition.Value)).ToList()


it returns result on one sql instance but does not return result on other sql instance.

However if i replace the above line with the following, it returns result on both sql instances.

Dim customerPositionsFromPaid = vwCustomerPositionInPaid.
SelectAll().
Where(conditions).
Select(Function(o) New CustomerPositionFromPaidDto() With {.FundingYearId = o.FundingYearId.Value, .DsoId = o.DsoId.Value, .CustomerId = o.CustomerId.Value, .CustomerPosition = o.CustomerPosition.Value}).
ToList()


Could it be because sql server instances have different settings or it's something to do with the code itself?

--Function

Private Shared Function GetCustomerPositionsFromPaid(ByVal customerID As Integer, ByVal fundingYearID As Integer) As IEnumerable(Of CustomerPositionFromPaidDto)
Dim conditions = PredicateBuilder.True(Of vwCustomerPositionInPaid)()
conditions = conditions.And(Function(o) o.CustomerId.Equals(customerID))
conditions = conditions.And(Function(o) o.FundingYearId.Equals(fundingYearID))
conditions = conditions.And(Function(o) o.DsoId.HasValue)

'Dim customerPositionsFromPaid = vwCustomerPositionInPaid.SelectAll().Where(conditions).Select(Function(o) New CustomerPositionFromPaidDto(o.FundingYearId.Value, o.DsoId.Value, o.CustomerId.Value, o.CustomerPosition.Value)).ToList()
'Dim customerPositionsFromPaid = vwCustomerPositionInPaid.SelectAll().Where(conditions).Select(Function(o) New With {.FundingYearId = o.FundingYearId.Value, .DsoId = o.DsoId.Value, .CustomerId = o.CustomerId.Value, .CustomerPosition = o.CustomerPosition.Value}).ToList().Select(Function(o) New CustomerPositionFromPaidDto(o.FundingYearId, o.DsoId, o.CustomerId, o.CustomerPosition)).ToList()
Dim customerPositionsFromPaid = vwCustomerPositionInPaid.
SelectAll().
Where(conditions).
Select(Function(o) New CustomerPositionFromPaidDto() With {.FundingYearId = o.FundingYearId.Value, .DsoId = o.DsoId.Value, .CustomerId = o.CustomerId.Value, .CustomerPosition = o.CustomerPosition.Value}).
ToList()
Return customerPositionsFromPaid
End Function


--Select All

Public Shared Function [SelectAll](ByVal conditions As Expression(Of Func(Of T, Boolean))) As IEnumerable(Of T)
Return [SelectAll]().Where(conditions)
End Function

Public Shared Function [SelectAll]() As IQueryable(Of T)
Return Table
End Function

Private Shared ReadOnly Property Table() As Table(Of T)
Get
Return Context.GetTable(Of T)()
End Get
End Property

Answer

I manage to solve the above by writing the following code. Looks like because customerid and fundingyearid are nullable objects, i had to use .Value attribute but still not sure why the previous code will work on one server and not on the other one.

Private Shared Function GetCustomerPositionsFromPaid(ByVal customerID As Integer, ByVal fundingYearID As Integer) As IEnumerable(Of CustomerPositionFromPaidDto)
Dim conditions = PredicateBuilder.True(Of vwCustomerPositionInPaid)()
conditions = conditions.And(Function(o) o.CustomerId.Equals(customerID.Value))
conditions = conditions.And(Function(o) o.FundingYearId.Equals(fundingYearID.Value))
conditions = conditions.And(Function(o) o.DsoId.HasValue)


Dim customerPositionsFromPaid = vwCustomerPositionInPaid.
    SelectAll().
    Where(conditions).
    Select(Function(o) New CustomerPositionFromPaidDto() With {.FundingYearId = o.FundingYearId.Value, .DsoId = o.DsoId.Value, .CustomerId = o.CustomerId.Value, .CustomerPosition = o.CustomerPosition.Value}).
    ToList()
Return customerPositionsFromPaid

End Function