asked on

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

kaufmed

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

Sheritlw

ASKER

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

ASKER CERTIFIED SOLUTION

kaufmed

membership

This solution is only available to members.

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

Start Free Trial

Sheritlw

ASKER

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

kaufmed

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?

Sheritlw

ASKER

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

Fernando Soto

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

Fernando Soto

So it excepted an object in place of a Decimal?