none
Select joined rows based on subquery RRS feed

  • Question

  • I'm new to LINQ and I have a simple query to convert to FROM SQL to LINQ. I've tried several variations using LinqPad but I just can't wrap my head around it. 

    I have two tables, a document table and a tracking table. The two tables are linked to each other via a document id. For each document record there can be many tracking records. In other words ... a very typical one-to-many situation. All I want to do is join the two tables where one of the tracking records for a particular document has a certain status. In normal SQL, I would do something like this:

    select * 
    from MDLServiceDocument msd
    inner join MDLServiceTracking mst 
      on msd.MDLServiceDocId = mst.MDLServiceDocId
    where exists 
      (
        select mst2.MDLServiceDocId 
        from  MDLServiceTracking mst2 
        where mst2.MDLServiceDocId = msd.MDLServiceDocId 
          and mst2.MDLServiceStatusId = 100
      )
    order by msd.MDLServiceDocId
    

    The above query will return all joined rows where there is at least one tracking record with a status of 100 for a document. How do I do that in LINQ?

    Thanks!

    Tuesday, March 15, 2011 7:35 PM

Answers

  • Hi PTOPIA;

    Try the following query. If I understan it this should give you what you want.

    DataContext context = new DataContext();
    
    var result = from msd in context.MDLServiceDocument
           where msd.MDLServiceTracking.Any( mst2 => mst2.MDLServiceStatusId == 100 )
           orderby msd.MDLServiceDocId
           select msd;
    
    Fernando
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by PTOPIA Thursday, March 17, 2011 1:12 PM
    Tuesday, March 15, 2011 9:40 PM

All replies

  • Hi PTOPIA;

    Try the following query. If I understan it this should give you what you want.

    DataContext context = new DataContext();
    
    var result = from msd in context.MDLServiceDocument
           where msd.MDLServiceTracking.Any( mst2 => mst2.MDLServiceStatusId == 100 )
           orderby msd.MDLServiceDocId
           select msd;
    
    Fernando
    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    • Marked as answer by PTOPIA Thursday, March 17, 2011 1:12 PM
    Tuesday, March 15, 2011 9:40 PM
  • Thank you Fernando, that got me much closer to where I was before, and with a lot less code than what I had before too! Looking at the SQL in LINQPad shows that the SQL generated is very close to what I had in my first post, complete with the EXISTS clause:

    SELECT 
    [Extent1].[MDLServiceDocId] AS [MDLServiceDocId], 
    [Extent1].[CustId] AS [CustId], 
    [Extent1].[CustDocId] AS [CustDocId], 
    [Extent1].[CustOrgId] AS [CustOrgId], 
    [Extent1].[CustDocFormat] AS [CustDocFormat], 
    [Extent1].[CustDocVersion] AS [CustDocVersion], 
    [Extent1].[DocumentContent] AS [DocumentContent], 
    [Extent1].[DateLoaded] AS [DateLoaded]
    FROM [dbo].[MDLServiceDocument] AS [Extent1]
    WHERE EXISTS (SELECT 
    	1 AS [C1]
    	FROM [dbo].[MDLServiceTracking] AS [Extent2]
    	WHERE ([Extent1].[MDLServiceDocId] = [Extent2].[MDLServiceDocId]) AND (100 = [Extent2].[MDLServiceStatusId])
    )
    ORDER BY [Extent1].[MDLServiceDocId] ASC
    

    The one thing I would love to understand a little better is how LINQ knows to generate this part of the SQL (in bold ):


        FROM [dbo].[MDLServiceTracking] AS [Extent2]
        WHERE ([Extent1].[MDLServiceDocId] = [Extent2].[MDLServiceDocId]) AND (100 = [Extent2].[MDLServiceStatusId])

    How does it know to add "[Extent1].[MDLServiceDocId] = [Extent2].[MDLServiceDocId]" to that WHERE clause? Is it because of the foreign key I setup in the DB?

    So muuch to learn! :)


    Thursday, March 17, 2011 1:12 PM
  • Hi PTOPIA;

    From my understanding of it and I am not 100% sure is this, in the where clause it tales MDLServiceDocument instance msd and uses that which is [Extent1] and uses the entity set collection property MDLServiceTracking which is [Extent2] and checks to see if any [Extent1].MDLServiceStatusId exist in [Extent2], which gives [Extent1].[MDLServiceDocId] = [Extent2].[MDLServiceDocId]. But like I said this is my understanding of it.

    Fernando


    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, March 23, 2011 4:11 PM