none
Manual EntitySet Association RRS feed

  • Question

  • I'm trying to implement the following join with LINQ entities:

    From [Parent]
    Join [Child] on Child.ParentID = Parent.ID and Child.TypeCode = 2

    How do I set a field in the composite key to a specific value (ChildTypeCode = 2) when defining the association?

     

    I want to load the related entity set using load options.

     

    Thanks!

    Monday, June 29, 2009 1:11 PM

All replies

  • You can use anonymous types to do a join on multiple values, such as this:

        From [Parent]
        Join [Child] on new { ParentID = Parent.ID, TypeCode = 2 }
            equals { Child.ParentID, Child.TypeCode }
    
    Monday, June 29, 2009 2:05 PM
  • I agree I can do it in a LINQ query, but I want to try and extend my Parent entity (Contact below) and have LINQ generate the join clause automatically so I can load the child entity set with: 

    options.LoadWith<Contact>(contact => contact.Addresses);
    
    
    

    Here's how I'm trying to extend the parent entity:

        public partial class Contact 
        {
            private EntitySet<AddressLink> _Addresses;
    
            partial void OnCreated()
            {
                this._Addresses = new EntitySet<AddressLink>(new Action<AddressLink>(this.attach_AddressLink), new Action<AddressLink>(this.detach_AddressLink));
            }
    ...
    • Edited by vze23c3q Monday, June 29, 2009 3:02 PM
    Monday, June 29, 2009 2:38 PM
  • I was able to extend the tool generated classes to create the association and forgetting LinkType for just a moment, the generated query is correct:

    SELECT {fieldlist}, (

        SELECT COUNT(*)

        FROM [dbo].[AddressLink] AS [t3]

        INNER JOIN [dbo].[Address] AS [t4] ON [t4].[ID] = [t3].[AddressID]

        WHERE [t3].[LinkedID] = [t0].[ID]

        ) AS [value]

    FROM [dbo].[Contact] AS [t0]

    LEFT OUTER JOIN ([dbo].[AddressLink] AS [t1]

        INNER JOIN [dbo].[Address] AS [t2] ON [t2].[ID] = [t1].[AddressID]) ON [t1].[LinkedID] = [t0].[ID] ]

    WHERE [t0].[LastName] LIKE '%'

    ORDER BY [t0].[ID], [t1].[ID], [t2].[ID]


    I just need LINQ add one more condition to the JOIN clause:
    (Is this possible?)

    SELECT {fieldlist}, (

        SELECT COUNT(*)

        FROM [dbo].[AddressLink] AS [t3]

        INNER JOIN [dbo].[Address] AS [t4] ON [t4].[ID] = [t3].[AddressID]

        WHERE [t3].[LinkedID] = [t0].[ID] AND [t3].[LinkType] = 1

        ) AS [value]

    FROM [dbo].[Contact] AS [t0]

    LEFT OUTER JOIN ([dbo].[AddressLink] AS [t1]

        INNER JOIN [dbo].[Address] AS [t2] ON [t2].[ID] = [t1].[AddressID]) ON [t1].[LinkedID] = [t0].[ID] ] AND [t1].[LinkType] = 1

    WHERE [t0].[LastName] LIKE '%'

    ORDER BY [t0].[ID], [t1].[ID], [t2].[ID]

     

     

    Monday, June 29, 2009 5:37 PM