CODEWITHSUNDEEP
linqjoinc#-4.0
Sachu
Sachu

Reputation: 7766

convert sql query to linq with complex left join

I am new to Linq joins.

i have a sql query as below. Please help me convert the same to linq.

select c.mem_id,
c.po_start,
c.po_end,
isnull(pp.new_policy_code,c.policy_no)
from claims_data c 
left join tob_policy tp on (c.subgroup_id =tp.subgroup_id  and c.category_id = tp.category_id
    and c.service_from_date Between tp.Start_Date And isnull (tp.End_Date,  
cast(GETDATE() as date)))
    Left Join Tob_Policy_Period Pp On (Pp.Policy_Id = tp.Policy_Id And 
 c.service_from_date Between Pp.Start_Date And Pp.End_Date )
    where c.cid = 13
    and c.g_id = 19013
    and c.mem_id = '123'
    and c.code ='555'

Upvotes: 0

Views: 55

Answers (2)

code passionate
code passionate

Reputation: 108

Try with below code:

var results = (from c in modelentity.claims_data
                           join tp in modelentity.tob_policy on new { first = c.subgroup_id, second= c.category_id } equals new { first = tp.subgroup_id, second = tp.category_id } into tpjoin
                           from tplj in tpjoin.DefaultIfEmpty()
                           join pp in modelentity.Tob_Policy_Period on new { first = tplj.Policy_Id ?? 0 } equals new { first = Pp.Policy_Id } into ppjoin
                           from pplj in tpjoin.DefaultIfEmpty()
                         where ((c.service_from_date) >= tplj.Start_Date && (c.service_from_date) <= (tplj.End_Date ?? DateTime.Now)) && 
                         ((c.service_from_date) >= pplj.Start_Date && (c.service_from_date) <= pplj.End_Date) &&
                         (c.cid = 13 && c.g_id = 19013 && c.mem_id = "123" && c.code = "555")
                         select new
                         {
                             c.mem_id,
                             c.po_start,
                             c.po_end,
                             policy_no = (pplj != null ? (pplj.new_policy_code ?? c.policy_no) : c.policy_no)
                         }).ToList();

Upvotes: 0

Dhananjay Singh
Dhananjay Singh

Reputation: 161

Try with this code: 

var results = (from c in yourmodelentity.claims_data
                         join tp in yourmodelentity.tob_policy on c.subgroup_id equals tp.subgroup_id  && c.category_id equals tp.category_id  && c.service_from_date Between tp.Start_Date && isnull (tp.End_Date, datetime.now)))
                        join Pp  in yourmodelentity.Tob_Policy_Period on Pp.Policy_Id equals tp.Policy_Id &&  c.service_from_date Between Pp.Start_Date && Pp.End_Date 
                         where c.cid = 13 && c.g_id = 19013 && c.mem_id = '123' && c.code ='555'
                         select new
                         {
                             c.mem_id, 
                             c.po_start,
                             c.po_end
                         }).ToList();

Upvotes: 1

Related Questions