Outer joining with parameter values

[Robert Lautenbach]

I'm trying to figure out how to write a LINQ statement where in SQL I would use LEFT JOINs with multiple join columns. My persistent objects are reverse engineered from an existing database. The tables look like this:

Package detail table: 

package_detail_id (pk)

group_id (fk)

item_id (fk)

lookup_item_type_id (fk)

....

Lookup item type table: 

lookup_item_type_id (pk)

description

...

I need to get all lookup item types that are *not* already in the package detail table for a given group id and item id. This is how I would write the SQL query:

SELECT IT.*

FROM lookup_item_type IT

LEFT JOIN package P

ON IT.lookup_item_type_id = P.lookup_item_type_id

AND P.group_id = ??? (given group id value)

AND P.item_id = ??? (given item id value)

WHERE P.package_id IS NULL

Can anyone suggest how to write a LINQ statement that covers this scenario?

[Serge]

Hello Robert Lautenbach,

In this case you do not necessarily need to make a triple join. I assume that item.packages is a collection of referenced details. So your query can be translated into this:

var items = from item in ctx.lookup_item_type
        where item.packages.Count == 0 ||
          item.packages.Any(x=>x.group_id == value && item_id == another_value)
        select item

Given the fact that you do not project columns from the package details table you do not need to use the join construct. All you need to do is filter the data appropriately.

Kind regards,
Serge
the Telerik team

Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Public Issue Tracking system and vote to affect the priority of the items.