CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-entities
Shpongle
Shpongle

Reputation: 217

using If statement inside where clause in LINQ

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...

Upvotes: 1

Views: 3699

Answers (1)

Andomar
Andomar

Reputation: 238296

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

Upvotes: 2

Related Questions