Linq to sql with select case

Hi EE,

I am trying to evaluate numbers for 0 before I divide the numbers in the linq query.
I know how to do a select case in a stored procedure but can't figure out how to do it in code behind VB.Net.
How do I check numbers for values of 0?

Thanks

Dim q = (From a In dc.ProductDetails Where a.ProductDetailID = iItemID _
                          Select New curstock = if((a.OuncesInStock = 0,a.OuncesInStock,  0) / if(a.OuncesPerItem = 0, 1))).FirstOrDefault

Open in new window

SheritlwAsked:
Who is Participating?
 
käµfm³d 👽Commented:
Try "1.0D":
Dim q = (From a In dc.ProductDetails _
		 Where a.ProductDetailID = iItemID _
		 Select New With { .curstock = a.OuncesInStock / IIF(a.OuncesPerItem = 0, 1.0D, a.OuncesPerItem) }).FirstOrDefault()

Open in new window

0
 
käµfm³d 👽Commented:
You first "if" seems strange. No matter what, you will always be dividing into zero. Since zero can appear as a numerator, I've modded your query slightly. I'm on my Linux box right now, so I can't test, but see if this works for you:
Dim q = (From a In dc.ProductDetails _
		 Where a.ProductDetailID = iItemID _
		 Select New With { .curstock = a.OuncesInStock / IIF(a.OuncesPerItem = 0, 1, a.OuncesPerItem) }).FirstOrDefault()

Open in new window

0
 
SheritlwAuthor Commented:
Hi Kaufmed,

I received the error...
The IIF method returns two separate types: Int32, Decimal.  Translation to SQL does not support different return types.
I did change, 1 to 1.0 but I still got the error.  Both fields are of decimal type.
Any ideas?

Thanks
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
SheritlwAuthor Commented:
Hi Kaufmed,

I tried the 1.0D but it changed to 1D.
I then tried creating two decimal variables but I still get the same error.  I looked at the database and both values are 0.00.  The table allows 0 since it is an inventory table.
Dim decVal As Decimal = 1.0
 Dim decZero As Decimal = 0

 Dim q = (From a In dc.ProductDetails _
 Where a.ProductDetailID = iItemID _
 Select New With {.curstock = IIf(a.OuncesInStock = decZero, decVal, a.OuncesInStock) / IIf(a.OuncesPerItem = decZero, decVal, a.OuncesPerItem)}).FirstOrDefault()

Open in new window

0
 
käµfm³d 👽Commented:
Both fields are of decimal type.
When you say "fields" are you referring to the DB columns, the type declaration of the Linq tables' columns, or both?
0
 
SheritlwAuthor Commented:
Fields are the columns in the database.  Both OuncesInStock and OuncesPerItem are of dbtype decimal.
Normally I can divide ouncesinstock by ouncesperite.

hmmm just a thought, maybe I should do a where clause specifying that both ouncesinstock and ouncesperitem > 0,  if not wouldn't q return nothing?

Thanks
0
 
Fernando SotoRetiredCommented:
Hi Sheritlw;

The IIf method return an Object type convert it to Decimal before using it, see code snippet.

Dim q = (From a In dc.ProductDetails _
		 Where a.ProductDetailID = iItemID _
		 Select New With { .curstock = a.OuncesInStock / Convert.ToDecimal(IIF(a.OuncesPerItem = 0, 1.0D, a.OuncesPerItem)) }).FirstOrDefault()

Open in new window


Fernando
0
 
Fernando SotoRetiredCommented:
So it excepted an object in place of a Decimal?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.