none
How do I "join" tables using navigation properties? RRS feed

  • Question

  • WHAT I HAVE:

    Visual Basic 2010, .NET Framework 4.0, Entity Framework 4.0, SQL Express

    MY QUESTION:

    When I used conventional SQL, I JOINed tables using foreign keys. Now that I'm using Entity Framework, I'm using navigation properties (entities and collections thereof) to relate entities. How do I do operations in VB code using LINQ that return the same kind of informations as JOINs, if I'm relying on navigation properties?

    EXAMPLES (navigation properties underlined):

    1. 1-to-many involving 2 entities

    Parent entity < 1 : * > Child entity

    ID, other prop, Children <-> ID, other prop, Parent

    2. many-to-many involving 2 entities

    Man entity < * : * > Women entity

    ID, other prop, Wives <-> ID, other prop, Husbands

    3. many-to-many involving 3 entities

    Man entity < 1 : * > Child entity < * : 1 > Women entity

    ID, other prop, Children <-> ID, other prop, Father, Mother <-> ID, other prop, Children

    What would the LINQ queries look like?


    Robert Gustafson

    Sunday, November 24, 2013 2:30 AM

Answers

  • Hi,

    When use the Model first, we do not use the join, because we cannot determine intersection table.

    We use "Include", for the men and woman exmaple, we can write codes like below:

    Dim result = from men in db.Men.Include("Women")
                                 where men.Women.Any(women => women.Id == 1)
                                 select men

    The translated sql statement:

     SELECT 
    	[Extent1].[Id] AS [Id], 
    	[Extent1].[Name] AS [Name], 
    	[Join1].[Id] AS [Id1], 
    	[Join1].[Name] AS [Name1], 
    	CASE WHEN ([Join1].[Men_Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
    	FROM  [dbo].[Men] AS [Extent1]
    	LEFT OUTER JOIN  (SELECT [Extent2].[Men_Id] AS [Men_Id], [Extent3].[Id] AS [Id], [Extent3].[Name] AS [Name]
    		FROM  [dbo].[ManWomen] AS [Extent2]
    		INNER JOIN [dbo].[Women] AS [Extent3] ON [Extent3].[Id] = [Extent2].[Women_Id] ) AS [Join1] ON [Extent1].[Id] = [Join1].[Men_Id]
    	WHERE  EXISTS (SELECT 
    		1 AS [C1]
    		FROM [dbo].[ManWomen] AS [Extent4]
    		WHERE ([Extent1].[Id] = [Extent4].[Men_Id]) AND (1 = [Extent4].[Women_Id])

    We can see that it will join the intersection table.

    Regards.

      

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, November 26, 2013 9:56 AM
    Moderator

All replies

  • Hello,

    >>When I used conventional SQL, I JOINed tables using foreign keys

    In VB, Entity Framework provides a key word ‘JOIN’.

    Its usage is similar with the conventional SQL

    >> 1. 1-to-many involving 2 entities

    It should be like:

    Dim test = From parent in Parent_
    
           Join child in Child on parent.ID Equals child.ParentID _ into all
    
           Select all
    

    >> 2. many-to-many involving 2 entities

    For this, there will be a 3<sup>rd</sup> table named ManWomen Or WomenMan.

    It should be like:

    Dim test = From manwomen in ManWomen _
    
                           Join women in Women on manwomen.WomenID Equals child.ID_
    
                           Join man in Man on man.ID Equals manwomen.ManID _ into all
    
           Select all
    

    For the many-to-many involving 3 entities, it is similar with the second.

    For more information regarding join in LINQ using VB:

    http://msdn.microsoft.com/en-us/vstudio/bb737909

    Regards.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, November 25, 2013 7:40 AM
    Moderator
  • Hi,

    What entity framework model has you used?

    I have assumed that you used the Database First so the table would exist in designer model directly.

    If I am wrong, please let me know.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, November 25, 2013 10:26 AM
    Moderator
  • I used "Model-First". (This is a new app.) Notice also that I defined associations in terms of navigation properties, not foreign keys. (Did I not make that clear?) So how do I do the equivalent of JOINs given what I have? (Or does EF create foreign-key properties implicitly that I can determine through IntelliSense?) (And how do I determine the name of the intersection table between Man and Woman?)

    Robert Gustafson


    Monday, November 25, 2013 12:26 PM
  • I used "Model-First". (This is a new app.) Notice also that I defined associations in terms of navigation properties, not foreign keys. (Did I not make that clear?) So how do I do the equivalent of JOINs given what I have--navigation properties? Or does EF create foreign-key properties implicitly that I can determine through IntelliSense? (And how do I determine the name of the intersection table between Man and Woman?)

    Monday, November 25, 2013 12:28 PM
  • Hi,

    When use the Model first, we do not use the join, because we cannot determine intersection table.

    We use "Include", for the men and woman exmaple, we can write codes like below:

    Dim result = from men in db.Men.Include("Women")
                                 where men.Women.Any(women => women.Id == 1)
                                 select men

    The translated sql statement:

     SELECT 
    	[Extent1].[Id] AS [Id], 
    	[Extent1].[Name] AS [Name], 
    	[Join1].[Id] AS [Id1], 
    	[Join1].[Name] AS [Name1], 
    	CASE WHEN ([Join1].[Men_Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
    	FROM  [dbo].[Men] AS [Extent1]
    	LEFT OUTER JOIN  (SELECT [Extent2].[Men_Id] AS [Men_Id], [Extent3].[Id] AS [Id], [Extent3].[Name] AS [Name]
    		FROM  [dbo].[ManWomen] AS [Extent2]
    		INNER JOIN [dbo].[Women] AS [Extent3] ON [Extent3].[Id] = [Extent2].[Women_Id] ) AS [Join1] ON [Extent1].[Id] = [Join1].[Men_Id]
    	WHERE  EXISTS (SELECT 
    		1 AS [C1]
    		FROM [dbo].[ManWomen] AS [Extent4]
    		WHERE ([Extent1].[Id] = [Extent4].[Men_Id]) AND (1 = [Extent4].[Women_Id])

    We can see that it will join the intersection table.

    Regards.

      

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, November 26, 2013 9:56 AM
    Moderator