CODEWITHSUNDEEP
c#entity-frameworklinq-to-entities
Badhon Jain
Badhon Jain

Reputation: 1007

Multiple table Join using LINQ returning no values

I have three tables as bellow:

Table A
Id
Divi_code
Unit_code 
Other column


Table B
Divi_code
Divi_name

Table c
Unit_code
Unit_name

I have a list generated from table A using some where condition, the list item may or may not have divi_code/unit_code values. I now need to find the divi_name/unit_name if the list contains any value for divi_code/unit_code. I'm doing this coz I don't want to send divi/unit code to the UI, I want send the names.

I tried to join these table like bellow:

var list = (from s in DataContext.HRM_HLDY_SPCL_FOR.AsEnumerable()
                    where s.HLDY_DATE == Convert.ToDateTime(Date)

                    join dv in DataContext.HRM_DIVISION on s.DIVI_CODE equals dv.DIVI_CODE

                    join un in DataContext.HRM_UNIT on s.UNIT_CODE equals un.UNIT_CODE

                    select new HRM_HLDY_SPCL_FORModel { HLDY_SPCL_SLNO = (s.HLDY_SPCL_SLNO).ToString(), DIVI_NAME= dv.DIVI_NAME, UNIT_NAME= un.UNIT_NAME, ActiveStatus= s.ACTIVE_STATUS, HLDY_SPCL_REM= s.HLDY_SPCL_REM}).ToList();

My query only facing those data which have both divi_code & unit_code. I would like to have all the data in my list even if it don't have divi_code/unit_code. In that case null for divi_name/unit_name would be good enough.

Upvotes: 1

Views: 580

Answers (1)

Arion
Arion

Reputation: 31249

I think you are describing a left join in linq. There are diffrent ways to accomplish that. I like this way the most. It is clear that it is a left join:

var list = (from s in DataContext.HRM_HLDY_SPCL_FOR.AsEnumerable()

                from dv in DataContext.HRM_DIVISION
                    .Where(d=>d.DIVI_CODE==s.DIVI_CODE).DefaultIfEmpty()

                from un in DataContext.HRM_UNIT 
                    .Where(u=>u.UNIT_CODE== s.UNIT_CODE).DefaultIfEmpty()
                where s.HLDY_DATE == Convert.ToDateTime(Date)
                select new HRM_HLDY_SPCL_FORModel 
                { 
                    HLDY_SPCL_SLNO = (s.HLDY_SPCL_SLNO).ToString(), 
                    DIVI_NAME= (dv==null?null:dv.DIVI_NAME), 
                    UNIT_NAME= (un==null?null:un.UNIT_NAME), 
                    ActiveStatus= s.ACTIVE_STATUS, 
                    HLDY_SPCL_REM= s.HLDY_SPCL_REM
                }
                ).ToList();

Upvotes: 1

Related Questions