CODEWITHSUNDEEP
c#linq
Niranjan Godbole
Niranjan Godbole

Reputation: 2175

Linq left join with multiple tables

Hi I am developing web application in .net. I have come across with below scenario.

Below is my first table.

perm_levelid  scrn_id  perm_id  perm_read  perm_write
40               1        2         1         1
41               2        2         1         1
42               3        2         1         1

Below is my second table.

scrn_id   scrn_name   
1         UserProfile
2         Change Password
3         Dashboard
4         Lease request

The relation between above two table is scrn_id

The output i am expecting is 

scrn_id  perm_id  perm_read  perm_write
1           2           1      1
2           2           1      1
3           2           1      1
4           0           0      0

I want total 4 rows in the result. scrn_id 1,2,3 matching in both tables so i should retrieve first table perm_read and perm_write values. Remaining value from 2 nd table also i want to get.

I tried as below.

List<screendetails> obj = new List<screendetails>();
obj = (from c in db.rolsp_perm_levelmapping
       join mapdetails in db.rolsp_scrn_screen on c.scrn_id equals mapdetails.scrn_id
       into mapObj
       from wt in mapObj.DefaultIfEmpty()
       where c.perm_id== permisssionID
       select new screendetails {
            scrn_id=c.scrn_id,
            scrn_name= wt.scrn_name,
            Read=c.perm_read,
            Write=c.perm_write
}).ToList();
return obj;

Above query does not yields correct result. May i get some help here to fix this? Thank you

Upvotes: 2

Views: 551

Answers (1)

Manasa Chakka
Manasa Chakka

Reputation: 121

obj= (from scrn in db.rolsp_scrn_screen
  from rmap in db.rolsp_perm_levelmapping
  .Where(rl=>rl.scrn_id==scrn_id).DefaultIfEmpty() select new screendetails {
                        scrn_id=scrn.scrn_id,
                        scrn_name= scrn.scrn_name,
                        Read=rmap.perm_read,
                        Write=rmap.perm_write
            }).ToList();

Try using this... Hope this helps...

Upvotes: 3

Related Questions