using If statement inside where clause in LINQ

Question

I have the following SQL query to be translated to LINQ. I'm using oracle DB.

SELECT TableA.Id, 
       TableA.Date,
       TableA.ItemId
       TableB.Quantity,
       TableB.Total
FROM   TableA, TableB, TableC, TableD
Where  TableA.Id = TableB.Id and
       TableA.Id = TableC.Id (+) and
       TableA.Id = TableD.Id (+) and
       TableA.ItemId = _itemId and 
       TableA.Date >= _from_date and
       TableA.Date < _to_date and
       DECODE(TableD.Id,NULL,0,1) = (some boolean variable)

TableA and TableC have one <--> (one or zero) where TableC is the optional.

The LINQ query I wrote is:

var data = from ta in context.TableAs
                join tB in context.TableBs 
                     on tA.Id equals tB.Id
                join tD in context.TableDs
                     on tA.Id equals tD.Id into A
           from itemA in A.DefaultIfEmpty()
                join tC in context.TableCs
                     on itemA.tA.Id equals tC.Id into B
           from itemB in B.DefaultIfEmpty()
           where itemA.tA.ItemId == _itemId && 
                 itemA.tA.Date >= _startDate && 
                 itemA.Ta.Date< _endDate && // this is where I got stuck...

           select new
           {                               
               itemA.tA.Id,
               itemA.tA.Date,
               itemA.tA.ItemId,
               itemA.tB.Quantity,
               itemA.tB.BalanceQuantity,                               
           };

How can I write the last line in the SQL querty (i.e.

DECODE(TableD.Id,NULL,0,1) = some boolean variable)

in the where clause of my constructed LINQ query?

Many thanks...

you can write like this : tD.Id ?? true. I assume that Id is of bit type in your database — bhavesh lad
– bhavesh lad, Commented Mar 19, 2013 at 6:31

Andomar · Accepted Answer · 2013-03-19 06:36:59Z

2

Oracle's decode is basically a switch statement:

decode(value, case1, result1, case2, result2, ..., defaultresult)

With the classic example:

select  decode(supplier_id, 10000, 'IBM',
                            10001, 'Microsoft',
                            10002, 'Hewlett Packard',
                            'Gateway') as result
from    suppliers;

So for your query:

decode(TableD.Id,null,0,1) = some boolean variable)

A LINQ equivalent could be:

(itemA.tD.Id != DBNull.Value) == some boolean variable

edited Mar 19, 2013 at 6:36

answered Mar 19, 2013 at 6:31

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

using If statement inside where clause in LINQ

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related