asked on

LINQ Query returning a null value for a System.Decimal type.

Because there is no data available to sum, this code is producing a null value and throwing an error. (The null value cannot be assigned to a member with type System.Decimal which is a non-nullable value type.)

Dim getLabor1 = Aggregate id In d.tblLaborInputs _
Where id.siteContract = CStr(Me.ComboBox1.SelectedItem) _
And id.NASAContract = ctrNo _
And id.WeekEnding >= CDate(CStr(mn & "-01-" & yr)) _
And id.WeekEnding <= CDate(CStr(mn & "-" & eom & "-" & yr)) _
Into Sum(id.WeeklyActuals)

I changed my code to this and a ton of other stuff but still, I get an error.

Dim getLabor1 = From id In d.tblLaborInputs _
Where id.siteContract = CStr(Me.ComboBox1.SelectedItem) _
And id.NASAContract = ctrNo _
And id.WeekEnding >= CDate(CStr(mn & "-01-" & yr)) _
And id.WeekEnding <= CDate(CStr(mn & "-" & eom & "-" & yr)) _
Aggregate xx In d.tblLaborInputs _
Into Sum(id.WeeklyActuals)

I get the error: System.Linq.IQueryable(Of <anonymous type>)' cannot be converted to 'Decimal'

I can't figure out what I am doing wrong. Help please!

Karen Wilson

ASKER

I ended up doing this. If there is a better way, please let me know!!

Dim getLabor1 As Decimal = 0

If (From id In d.tblLaborInputs _
Where id.siteContract = CStr(Me.ComboBox1.SelectedItem) _
And id.NASAContract = ctrNo _
And id.WeekEnding >= CDate(CStr(mn & "-01-" & yr)) _
And id.WeekEnding <= CDate(CStr(mn & "-" & eom & "-" & yr)) _
Select id).Count > 0 Then

getLabor1 = Aggregate id In d.tblLaborInputs _
Where id.siteContract = CStr(Me.ComboBox1.SelectedItem) _
And id.NASAContract = ctrNo _
And id.WeekEnding >= CDate(CStr(mn & "-01-" & yr)) _
And id.WeekEnding <= CDate(CStr(mn & "-" & eom & "-" & yr)) _
Into Sum(id.WeeklyActuals)

End If

getLabor += getLabor1

it_saige

Try this:

Dim getLabor1 = Aggregate id In d.tblLaborInputs _
                Where id.siteContract = CStr(Me.ComboBox1.SelectedItem) _
                And id.NASAContract = ctrNo _
                And id.WeekEnding >= CDate(CStr(mn & "-01-" & yr)) _
                And id.WeekEnding <= CDate(CStr(mn & "-" & eom & "-" & yr)) _
                Into Sum(If(DirectCast(id.WeeklyActuals, System.Nullable(Of Decimal)), 0D))

Open in new window

Proof of concept:

Module Module1
	Private values As New List(Of Object)() From {Nothing, 5D, 4D, 3D, Nothing, 5D, 6D, 7D}

	Sub Main()
		Dim i = values.Sum(Function(x) If(DirectCast(x, System.Nullable(Of Decimal)), 0D))
		Console.WriteLine(i)
		Console.ReadLine()
	End Sub
End Module

Open in new window

Produces the following output -

-saige-

Karen Wilson

ASKER

Value of type 'Decimal' cannot be converted to 'Decimal?'. I am getting this error on this line: Into Sum(If(DirectCast(id.WeeklyActuals, System.Nullable(Of Decimal)), 0D))

it_saige

I just double checked using an Aggregate call and still got the expected results:

Module Module1
	Private values As New List(Of Object)() From {Nothing, 5D, 4D, 3D, Nothing, 5D, 6D, 7D}

	Sub Main()
		Dim i = Aggregate v In values Into Sum(If(DirectCast(v, System.Nullable(Of Decimal)), 0D))
		Console.WriteLine(i)
		Console.ReadLine()
	End Sub
End Module

Open in new window

You could also try to use Decimal.TryParse instead of casting it as a nullable:

Module Module1
	Private values As New List(Of Object)() From {Nothing, 5D, 4D, 3D, Nothing, 5D, 6D, 7D}

	Sub Main()
		Dim temp As Decimal = 0D
		Dim i = Aggregate v In values Into Sum(If(Decimal.TryParse(If(v IsNot Nothing, v.ToString(), "0"), temp), temp, 0D))
		Console.WriteLine(i)
		Console.ReadLine()
	End Sub
End Module

Open in new window

Which produces the same output as above.

For your Aggregate call, you would write this as:

Dim temp As Decimal = 0D
Dim getLabor1 = Aggregate id In d.tblLaborInputs _
		Where id.siteContract = CStr(Me.ComboBox1.SelectedItem) _
		And id.NASAContract = ctrNo _
		And id.WeekEnding >= CDate(CStr(mn & "-01-" & yr)) _
		And id.WeekEnding <= CDate(CStr(mn & "-" & eom & "-" & yr)) _
		Into Sum(If(Decimal.TryParse(If(id.WeeklyActuals IsNot Nothing, id.WeeklyActuals.ToString(), "0"), temp), temp, 0D))

Open in new window

-saige-

Karen Wilson

ASKER

I probably need to give you this bit of information as well.

Here is my original code:

Dim getLabor1 = Aggregate id In d.tblLaborInputs _
Where id.siteContract = CStr(Me.ComboBox1.SelectedItem) _
And id.NASAContract = ctrNo _
And id.WeekEnding >= CDate(CStr(mn & "-01-" & yr)) _
And id.WeekEnding <= CDate(CStr(mn & "-" & eom & "-" & yr)) _
Into Sum(id.WeeklyActuals)

The date is 06-01-2016 and 06-30-2016. There are no rows in the table for that date range in the table. It throws this error:

An unhandled exception of type 'System.InvalidOperationException' occurred in System.Data.Linq.dll

Additional information: The null value cannot be assigned to a member with type System.Decimal which is a non-nullable value type.

I got the same error for id.WeeklyActuals with your second set of code.

Thanks for you continued help.

Fernando Soto

Hi Karen;

You may want to try and adding one more test to the Where clause, And id.WeeklyActuals <> Nothing.

Karen Wilson

ASKER

Sheesh, it's been a long day... my English is pretty bad in that! Sorry.

Karen Wilson

ASKER

Fernando, it doesn't matter because there are no rows to retrieve.

SOLUTION

it_saige

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Karen Wilson

ASKER

When I run this code, it produces this error: Results = Type expected.

Dim getLabor1 = From id In d.tblLaborInputs _
Where id.siteContract = CStr(Me.ComboBox1.SelectedItem) _
And id.NASAContract = ctrNo _
And id.WeekEnding >= CDate(CStr(mn & "-01-" & yr)) _
And id.WeekEnding <= CDate(CStr(mn & "-" & eom & "-" & yr)) _
Aggregate xx In d.tblLaborInputs _
Into Sum(id.WeeklyActuals)

I just keep thinking I shouldn't have to write a huge if statement for this!

Karen Wilson

ASKER

Any other help available out there? This section isn't pulling any rows from the server:

Dim getLabor1 = From id In d.tblLaborInputs _
Where id.siteContract = CStr(Me.ComboBox1.SelectedItem) _
And id.NASAContract = ctrNo _
And id.WeekEnding >= CDate(CStr(mn & "-01-" & yr)) _
And id.WeekEnding <= CDate(CStr(mn & "-" & eom & "-" & yr)) _

so when I try this part:

Aggregate xx In d.tblLaborInputs _
Into Sum(id.WeeklyActuals)

It throws an error: Results = Type expected.

I just want getLabor1 to equal 0 if no rows are retrieved.

Ioannis Paraskevopoulos

Hi,

Please try the following:

Dim getLabor1 = Aggregate actuals In (from id in d.tblLaborInputs _
	Where id.siteContract = CStr(Me.ComboBox1.SelectedItem) _
	And id.NASAContract = ctrNo _
	And id.WeekEnding >= CDate(CStr(mn & "-01-" & yr)) _
	And id.WeekEnding <= CDate(CStr(mn & "-" & eom & "-" & yr)) _
	Select id.WeeklyActuals).DefaultIfEmpty(0) _
	Into Sum(actuals)

Open in new window

Giannis

Fernando Soto

Hi Karen;

Try it this way it should do what you want.

Dim getLabor1 = Aggregate id In d.tblLaborInputs _
                Where id.siteContract = CStr(Me.ComboBox1.SelectedItem) _
                And id.NASAContract = ctrNo _
                And id.WeekEnding &gt;= CDate(CStr(mn & "-01-" & yr)) _
                And id.WeekEnding &lt;= CDate(CStr(mn & "-" & eom & "-" & yr)) _
                Into Sum(CType(If(id.WeeklyActuals.HasValue, id.WeeklyActuals.Value, 0), Decimal))

Open in new window

Karen Wilson

ASKER

Thank you both for the response

@loannis - Because it is not retrieving any rows, I get an unsupported overload used for query operator 'DefaultIfEmpty' Also an unhandled exception of type 'System.NotSupportedException' occurred in System.Data.Linq

@ Fernando - The ; in > and < is not a valid character.

it_saige

Karen,

I think Fernando meant:

Dim getLabor1 = Aggregate id In d.tblLaborInputs _
                Where id.siteContract = CStr(Me.ComboBox1.SelectedItem) _
                And id.NASAContract = ctrNo _
                And id.WeekEnding >= CDate(CStr(mn & "-01-" & yr)) _
                And id.WeekEnding <= CDate(CStr(mn & "-" & eom & "-" & yr)) _
                Into Sum(CType(If(id.WeeklyActuals.HasValue, id.WeeklyActuals.Value, 0), Decimal));

Open in new window

-saige-

ASKER CERTIFIED SOLUTION

Karen Wilson

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fernando Soto

Hi Karen;

I just copied your original post and change the last line. I changed the > and < to > and < characters.

Dim getLabor1 = Aggregate id In d.tblLaborInputs _
                Where id.siteContract = CStr(Me.ComboBox1.SelectedItem) _
                And id.NASAContract = ctrNo _
                And id.WeekEnding >= CDate(CStr(mn & "-01-" & yr)) _
                And id.WeekEnding <= CDate(CStr(mn & "-" & eom & "-" & yr)) _
                Into Sum(CType(If(id.WeeklyActuals.HasValue, id.WeeklyActuals.Value, 0), Decimal))

Open in new window

Fernando Soto

Can you please post the different code you used.

Karen Wilson

ASKER

I changed how I was getting the data from the SQL Server. I created a view because it doesn't throw an error when the answer is nothing. Then I made it equal 0 if it was nothing. The other ways weren't working because there were no rows to retrieve between the date range.

Dim getLabor1 = Aggregate id In d.vw_getLaborWBs _
Where id.WeekEnding >= CDate(pckrStartDate.Text) _
And id.WeekEnding <= CDate(pckrEndDate.Text) _
And id.NASAContract = wbcNContractNumber _
Into Sum(id.WeeklyActuals)

If getLabor1 Is Nothing Then
getLabor1 = 0
Else
'do nothing
End If

Fernando Soto

Have you tried my next to last post to see if it worked?

Karen Wilson

ASKER

We couldn't get anything to work so I did it different and it seems to be working.