Outer joining with parameter values

2 posts, 0 answers
  1. Robert Lautenbach
    Robert Lautenbach avatar
    18 posts
    Member since:
    Dec 2009

    Posted 30 Apr 2010 Link to this post

    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?
  2. Serge
    Admin
    Serge avatar
    375 posts

    Posted 05 May 2010 Link to this post

    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.
  3. DevCraft banner
Back to Top