Linq2Sql How to write outer join query?

Question

Hi,

I have following SQL tables.

ImportantMessages

impID

Message

ImportantMessageUsers

imuID

imuUserID

imuImpID

I want to write a Linq2Sql query so that it returns any rows from ImportantMessages that does not have a record in ImportantMessagesUsers.

Matchiing fields are impID ----- imuImpID

Assume imuUserID of 6

Answer 1

It would be just as simple as

var messages = context.ImportantMessages.Where(x => x.ImportantMessageUsers.Count() == 0);

EDIT

I think I have understood the problem well, you have to get all messages from ImportantMessages table without any row in ImportantMessageUsers table, which is connected with a foreign key ImportantMessagesUsers.imuImpID = ImportantMessages.impID.

Please check if you have a foreign key between these tables and then delete these tables from Linq context designer and add them again - the foreign key should be visible between them thus creating such properties as ImportantMessages.ImportantMessageUsers which is as IEnumerable of type ImportantMessageUser allowing to use expressions like x.ImportantMessageUsers.Count() == 0.

EDIT2

When user Id must be filtered, this lambda expression should do the trick:

var messages = context.ImportantMessages.Where(x => x.ImportantMessageUsers.Where(y => y.imuUserID == 6).Count() == 0);

Using lambda over LINQ notation is only a matter of preference, yet when multiple joins aren't required, lambda is generally more intuitive to use.

Answer 2

Check out DefaultIfEmpty().

Here's an example from one of my old SO questions:

http://stackoverflow.com/questions/1395069/linq-to-sql-how-to-add-a-where-clause-to-a-left-join

var z = 
    from im in importantMessages
    join imu in importantMessageUsers
        on new { im.impID,  imuUserID = 7 } equals 
        new { imu.imuImpID, imu.imuUserID  }
    into imJoin
    from ij in imJoin.DefaultIfEmpty()
    where ij.imuImpID == null
    select new
    {
        im.Message
        ...

Answer 3

This is what worked for me.

var qry = from imp in ImportantMessages
where !(from imu in ImportantMessagesUsers where imu.ImuUsrID == 6 select imu.ImuImpID).Contains(imp.ImpID)
select imp;