Linq - Inner Join not retrieving all records from left table

Question

I have done an inner join, it shows only the matching record with following query :-

var data = from t1 in ctx.tblEmp
           join t2 in ctx.tblHelp 
               on t1.Field equals t2.Fld
           where t1.Id == Id && 
                 t2.Id == Id
           select new Settings { Master = t1.Labelname, OrderNo = t2.OrderNo};

I want all the records from tblEmp & only matching records from tblHelp.

How to do this?

That is a left join and not an inner join then. You can search for that here on SO. — T_D, Oct 07 '14 at 11:05
http://stackoverflow.com/questions/5537995/entity-framework-left-join — Alex, Oct 07 '14 at 11:32

Amit · Accepted Answer · 2014-10-07T12:01:15.907

2

Use as

 var data = from t1 in ctx.tblEmp
       join t2 in ctx.tblHelp 
           on t1.Field equals t2.Fld into u
           from t2 in u.DefaultIfEmpty()
       where t1.Id == Id && 
             t2.Id == Id

       select new Settings { Master = t1.Labelname, OrderNo = t2.OrderNo};

Try this

 var data = from t1 in ctx.tblEmp
       join t2 in ctx.tblHelp 
           on t1.Field equals t2.Fld into u
           from t2 in u.DefaultIfEmpty()
       where t1.Id == Id 
       orderby columnname // Added Order By
       select new Settings { Master = t1.Labelname, OrderNo = t2.OrderNo==null ?"":t2.OrderNo};

edited Oct 07 '14 at 12:01

answered Oct 07 '14 at 11:12

Amit

15,217
8
46
68

Still gives me only matching records. – Anup Oct 07 '14 at 11:25
I get error `The cast to value type 'System.Int32' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type.` – Anup Oct 07 '14 at 11:43
@Anup Try now check null condition – Amit Oct 07 '14 at 11:45
Finally Worked...Thanks a lot..@ – Anup Oct 07 '14 at 11:50
Just one last thing...how to `OrderBy` 'OrderNo' in above query – Anup Oct 07 '14 at 11:51

score 1 · Answer 2 · answered Oct 07 '14 at 11:16

    var query = from t1 in ctx.tblEmp
                    join t2 in ctx.tblHelp on t1 equals t2.Fld into tempGroup
                    from subpet in tempGroup.DefaultIfEmpty()
                    select new { t1.Labelname, OrderNo = (subpet == null ? String.Empty : subpet.OrderNo) };

Linq - Inner Join not retrieving all records from left table

2 Answers2