I would need a little help to write this linq query

Question

I have this SQL query

select case when AllowanceId is null then 2  else AllowanceId end as AllowanceId
  , d.descen as domdescen
  , t.descen as typdescen
  , case when qty is null then 0 else qty end as qty
  , u.descen as unidescen
from (select t.allowancetypeid, d.allowancedomainid 
   from allowancedomain as d, allowancetype as t
   where t.allowancetypeid in (1,2) and d.active = 1 and t.active = 1) as a left join 
 allowanceqty as q on a.allowancetypeid = q.allowancetypeid and
       a.allowancedomainid = q.allowancedomainid and 
       q.allowanceid = 2 inner  join 
    allowancedomain as d on a.allowancedomainid = d.allowancedomainid
 inner  join 
    allowancetype as t on a.allowancetypeid = t.allowancetypeid
 inner join
    unit as u on case when q.unitid is null then 1 else q.unitid end = u.unitid

there is one variable in that query and it's number 2 in the select clause(first line) and the "= 2" in the from clause(in the middle)

I wrote what I wanted, look at my answer below

Answer 1

While this is not the solution, may i ask why you need to transform a whole SQL statement to Linq?

Answer 2

after a long try/retry/try/retry here is what I wanted....

from a in (from d in AllowanceDomains _
  from t in AllowanceTypes _
  where (new integer(){1,2}).contains(t.AllowanceTypeID) and t.active = true and d.active=true _
  select  t.allowancetypeid,tdescen =t.descen, d.allowancedomainid,ddescen=d.descen)  _
group join qqq in AllowanceQties on new with {.k1 = a.allowancetypeid, .k2 = a.allowancedomainid, .k3 = 2} equals _
       new with {.k1 = qqq.allowancetypeid, .k2 = qqq.allowancedomainid, .k3 = qqq.allowanceid} into qq = group _
from q in qq.DefaultIfEmpty _
join u in units on if(object.equals(q.unitid,nothing),1,q.unitid) equals u.unitid _
select  AllowanceID =if(object.equals(q.AllowanceID,nothing),2,q.AllowanceID) ,a.tdescen,a.ddescen,qty = if(object.equals(q.qty,nothing),0,q.qty),u.descen

Answer 3

Linqer is your friend. http://www.sqltolinq.com. It allows you to convert almost all sql to linq and works with your database and dbml. It has never failed me so far. It's not a free product but well worth the money. [I have no associations whatsoever with Linqer]