CODEWITHSUNDEEP
linq-to-sql
Malcolm
Malcolm

Reputation: 12874

Linq2Sql How to write outer join query?

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

Upvotes: 0

Views: 215

Answers (3)

Michał Turecki
Michał Turecki

Reputation: 3197

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.

Upvotes: 2

Malcolm
Malcolm

Reputation: 12874

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;

Upvotes: 1

Steve
Steve

Reputation: 5952

Check out DefaultIfEmpty().

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

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
        ...

Upvotes: 1

Related Questions