Help with LINQ relational query

[Thomas W. Brown]

I'm having trouble formulating a LINQ query that, in SQL
would be handled via a sub-select. Let me give you a
vanilla view of the tables:

Items: The "main" table of items I'm trying to query.
Id (int)
...

Resources: A table of people available in the system.
Id (int)
FirstName (varchar)
MiddleName (varchar)
LastName (varchar)
...

ItemResources: a mapping of people to items
ItemId (int)
ResourceId (int)
ResourceType (int)
...

For example, we may have resource types like: 1 = Creator,
2 = Reviewer, 3 = Approver, etc. I want to query for
certain items in the database and pull the name of the
Creator. One wrinkle is that there may be no Creator, i.e.
no entry in ItemResources for ResourceType=1 for my item.

Here is what I would do in SQL:

SELECT Items.Id
, ( SELECT CASE ISNULL(ItemResources.ResourceId, -1)
WHEN -1
THEN 'System'
ELSE Resources.FirstName + ' ' +
Resources.MiddleName + ' ' +
Resources.LastName
END
FROM ItemResources
LEFT OUTER JOIN Resources ON Resources.Id =
ItemResources.ResourceId
WHERE ItemResources.ItemId = Items.Id and
ItemResources.ResourceType=1
)
FROM Items

Any help translating this to LINQ would be much appreciated!

Regards,
-- TB

 

[Pavel Minaev]

I'm having trouble formulating a LINQ query that, in SQL
would be handled via a sub-select.  Let me give you a
vanilla view of the tables:

Items: The "main" table of items I'm trying to query.
  Id (int)
  ...

Resources: A table of people available in the system.
  Id (int)
  FirstName (varchar)
  MiddleName (varchar)
  LastName (varchar)
  ...

ItemResources: a mapping of people to items
  ItemId (int)
  ResourceId (int)
  ResourceType (int)
  ...

For example, we may have resource types like: 1 = Creator,
2 = Reviewer, 3 = Approver, etc.  I want to query for
certain items in the database and pull the name of the
Creator.  One wrinkle is that there may be no Creator, i.e.
no entry in ItemResources for ResourceType=1 for my item.

Here is what I would do in SQL:

   SELECT Items.Id
      , (   SELECT CASE ISNULL(ItemResources.ResourceId, -1)
            WHEN -1
               THEN 'System'
               ELSE Resources.FirstName + ' ' +
                    Resources.MiddleName + ' ' +
                    Resources.LastName
            END
            FROM ItemResources
            LEFT OUTER JOIN Resources ON Resources.Id =
ItemResources.ResourceId
            WHERE ItemResources.ItemId = Items.Id and
ItemResources.ResourceType=1
        )
   FROM Items

Any help translating this to LINQ would be much appreciated!

You can use nested queries in LINQ as well:

from i in items
join ir in itemResources on i.Id equals ir.ItemId into irs
let ir = irs.SingleOrDefault(ir => ir.ResourceType == 1)
let r = (ir != null) ? resources.Single(r => r.Id ==
ir.ResourceId) : null
let name = (r != null) ? (r.FirstName + " " + r.MiddleName + " " +
r.LastName) : "System"
select new { i.Id, name }