SQL Query to LINQ C# [Joining multiple table]

Question

I am working on this query in my sql server

select a.care_type_id, a.description,
isChecked = case when b.care_type_id is null then 'false' else 'true' end
from caretype a
left join patientinsurancetacitem b on a.care_type_id = b.care_type_id and
b.tac_id = 1

I want to translate the query into LINQ. However, I am having trouble with the and operator. I have this code so far;

from a in context.CareTypes
join b in context.PatientInsuranceTACItems on a.care_type_id equals
b.care_type_id into x
from xx in x.Where(w => w.tac_id == 1).DefaultIfEmpty()
                   select new { 
                   isChecked = (b.care_type_id == null ? false : true),
                   care_type_id = a.care_type_id,
                   description = a.description}

And, also, I cannot get the b that I equated in isChecked variable. From where will I start modifying in order to get the same result as my sql query? In where I got it wrong?

score 5 · Accepted Answer · edited May 23 '17 at 10:28

5

Try this

from a in context.caretype
join b on context.patientinsurancetacitem
      on new { CA = a.care_type_id, CB =  1}  equals
         new { CA = b.care_type_id, CB =  b.tac_id}
      into tmp from b in tmp.DefaultIfEmpty()
select new
{
    care_type_id = a.care_type_id, 
    description = a.description,
    checked = (b != null) // Or ((b == null) ? false : true)
}

Also check this StackOverflow answer.

edited May 23 '17 at 10:28

Community

1
1

answered Jul 12 '16 at 08:25

neer

4,031
6
20
34

I tried, and it worked. Thank you so much, sir! Cheers! – Mr.Right Jul 12 '16 at 08:31

score 2 · Answer 2 · answered Jul 12 '16 at 08:28

2

The very simple example about joining on multiple columns is ;

from x in entity1
             join y in entity2
             on new { X1= x.field1, X2= x.field2 } equals new { X1=y.field1, X2= y.field2 }

answered Jul 12 '16 at 08:28

ilkerkaran

4,214
3
27
42

@Hebele's answer http://stackoverflow.com/a/38323556/2630427 is the right one tho – ilkerkaran Jul 12 '16 at 08:30
Yes! Thank you, sir! This was indeed an example. However, I cannot mark two correct answers. Anyways, thank you again sir, cheers! – Mr.Right Jul 12 '16 at 08:32

SQL Query to LINQ C# [Joining multiple table]

2 Answers2