none
Sub query in LINQ RRS feed

  • Question

  • Hi,

    Can someone translate this query into LINQ?

    SELECT

    N.[ID],
    N.[ParentID],
    N.[Title],
    NP.[Key],
    NP.Value,
    NP.DataType,
    NP.[DataLength]
    FROM [Items] N
    JOIN Properties NP
    ON N.[ID] = NP.[ID]
    WHERE NP.[ID] IN
    (
    SELECT ID FROM Properties
    WHERE ([Key] = 'Class' AND Value = 'M')
    AND ID IN
    (
    SELECT ID FROM Properties
    WHERE ([Key] = 'App' AND Value = 'R')
    )
    )



    Thanks in advance.
    • Edited by sobo1 Sunday, June 21, 2009 11:09 AM Remove some white spaces
    Sunday, June 21, 2009 11:07 AM

Answers

  • The ideal solution depends on what kind of relationship Items and Properties are in. If they're in a one:many relationship, then do this:

      from i in db.Items
      where i.Properties.Any (p => p.Key == "Class" && p.Value == 'M')
      where i.Properties.Any (p => p.Key == "App" && p.Value == 'R')
      select new
      {
         i.ID,
         i.ParentID,
         i.Title,
         Properties = i.Properties.Select (p => new { p.Key, p.value, p.DataType })
      }

    This will give you a nicely structured output.

    Regards

    Joe
    Write LINQ queries interactively - www.linqpad.net
    Sunday, June 21, 2009 12:16 PM
    Answerer