Getting Empty Row while using Left Join in EF

Question

I am getting very different type of scenario here, I am applying Left Join between 4 tables, no table returns any record but when i get result , it shows 1(one) count of row with all values set to null.

Here is my code:

var qry2 = (from p in dbModel.tbl_PROJECT

join c in dbModel.tbl_PROJECTxCOMPONENT
 on p.ProjectID equals c.ProjectID
  into list8
from l8 in list8.DefaultIfEmpty()

join a in dbModel.tbl_PROJECTCOMPONENT
on l8.ProjectComponentID equals a.ProjectComponentID
 into list9
from l9 in list9.DefaultIfEmpty()


join a in dbModel.tbl_COLOR
on l9.ColorID equals a.ColorID
 into list10
from l10 in list10.DefaultIfEmpty()

where p.ProjectID == projectId

select new ProjectDetails1
{
    Title = l9.Title,
    ColorId = l9.ColorID,
    ProjectComponentID = l9.ProjectComponentID,
    ColorDefinition = l10.ColorDefination
}).ToList();

click here

Some added context here would be helpful. For example, what does the data look like? where is the initial from clause? etc. — CodeMonkey1313
– CodeMonkey1313, Commented May 21, 2014 at 13:18
Thats not how you do multiple left joins in linq. Check this thread stackoverflow.com/questions/3859173/… — Janne Matikainen
– Janne Matikainen, Commented May 21, 2014 at 13:35
@JanneMatikainen ok i have checked the thread but what i think is that the way i am writing the query is the right method of righting...any specific reason why it shows count still all columns are null? — Vishvadeep singh
– Vishvadeep singh, Commented May 22, 2014 at 4:28

JumpingJezza · Accepted Answer · 2014-06-03 06:58:47Z

I can recreate your problem with the following SQL

SQL

create table tbl_PROJECT
(
   ProjectID int identity(1,1) primary key,
   Title varchar(255)
)

create table tbl_COLOR
(
   ColorID int identity(1,1) primary key,
   ColorDefination varchar(255)
)

create table tbl_PROJECTCOMPONENT
(
   ProjectComponentID int identity(1,1) primary key,
   ColorID int not null references tbl_COLOR(ColorID),
   Title varchar(255)
)

create table tbl_PROJECTxCOMPONENT
(
   ProjectID int not null references tbl_PROJECT(ProjectID),
   ProjectComponentID int not null references tbl_PROJECTCOMPONENT(ProjectComponentID)
)

With no rows of data inserted, then I tried running the equivalent LINQ in LINQPAD.

LINQ

from p in Tbl_PROJECTs
join c in Tbl_PROJECTxCOMPONENTs on p.ProjectID equals c.ProjectID into list8

from l8 in list8.DefaultIfEmpty()
join a in Tbl_PROJECTCOMPONENTs on l8.ProjectComponentID equals a.ProjectComponentID into list9

from l9 in list9.DefaultIfEmpty()
join a in Tbl_COLORs on l9.ColorID equals a.ColorID into list10

from l10 in list10.DefaultIfEmpty()
where p.ProjectID == 1
select new 
{
    Title = l9.Title,
    //ColorId = l9.ColorID,
    //ProjectComponentID = l9.ProjectComponentID,
    ColorDefinition = l10.ColorDefination
}

After a couple of changes to cater for having to use an anon object we get a row of nulls, as you indicated. Interestingly - the ToList() removes the null line.
The problem is that your where clause is joining on the original table "p", instead of your joined table "list8".
Swap where p.ProjectID == 1 with where list8.ProjectID == 1 and it should work fine.

Collectives™ on Stack Overflow

Getting Empty Row while using Left Join in EF

1 Answer 1

SQL

LINQ

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

SQL

LINQ

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related