Linq query join is not working

Question

Hi i am trying to join two tables in c#. the join code is given below. The problem is that when there is null value for tourid in tb_abc then in will not include that row from tb_abc in the list.

return (from p in context.tb_abc
                    from o in context.tb_Second
                    where o.id==p.tourId
                    where p.driverId == driverId
                    select new abcBean
                    {
                        id=p.id,
                        name=o.name
                    }).ToList<abcBean>();

Can anyone tell me what i am doing wrong

Answer 1

You are not doing an inner join in that query. You are doing a cross join, its where you have two tables and join each record to every other record.

If you want to include rows that return null on one of the constraints you need a left outer join.

return (from p in tb_abc
        join o in tb_Second on p.tourId equals o.id into po
        where p.driverId == driverId 
        from subpo in po.DefaultIfEmpty()
        select new abcBean
        {
            id=p.id,
            name=(subpo == null ? String.Empty : subpo.Name)
        }).ToList();

Consider these two sql statements:

The first a cross join:

select id, name
from tb_abc o, 
     tb_Second p
where
     o.id = p.tourID
     and p.driverID = @driverID

The second a left outer join:

select id, name
from tb_abc o
LEFT OUTER JOIN tb_Second p on o.id = p.tourID
where 
    p.driverId = @driverID

The second will give you one set of the records, that include the null value of o.id.

The first will give you something of a Cartesian product which you rarely want.

Linq's DefaultIfEmpty() puts the default value (null) into the record if it doesnt find a match for the one side, so it behaves like the left outer join.