none
ObjectContext relationship span RRS feed

  • Question

  • Hi,

    I am using EF 4.1 and independent associations on my entities. I understand relationship spans on 0..1 relations, but i just want to know how do i disable this when loading certain entities (without using the No tracking option), because the performance implications of a left outer join to pull the other side of the relationship is too high. I tried to disable LazyLoading , just before i load the entity , but it still generates a Left outer join in the query.

    Ramana

     

     

    Tuesday, November 8, 2011 12:04 PM

All replies

  • Could you show us the code? 

    At least the LINQ query and the SQL. 


    ----------------------------------
    http://jendaperl.blogspot.com
    A Perl developer in the world of C#
    Tuesday, November 8, 2011 12:25 PM
  • The LINQ looks like this:

     

    EntityManager.BillTo.Include("BillToContact")
    
    .Where(bt => bt.BillToCode == billToCode && bt.Client.ID == clientCode).FirstOrDefault()
    

     


    Which generates the following SQL (changes selects to * to make it easier to read)

    Execute: SELECT 
    "Limit1".C2 AS C1, 
    FROM ( SELECT 
    	FROM        BILLTO "Extent1"
    	INNER JOIN CLIENT "Extent2" ON "Extent1".CLIENT_FK = "Extent2".OBJECTID
    	LEFT OUTER JOIN  (SELECT 
    		*
    		FROM     PARTY "Extent3"
    		LEFT OUTER JOIN CLIENT "Extent4" ON ("Extent4".PRIMARYCONTACT_FK IS NOT NULL) AND ("Extent3".OBJECTID = "Extent4".PRIMARYCONTACT_FK)
    		LEFT OUTER JOIN BILLTO "Extent5" ON "Extent3".OBJECTID = "Extent5".BILLTOCONTACT_FK
    		LEFT OUTER JOIN DISPUTE "Extent6" ON ("Extent6".REVIEWER_FK IS NOT NULL) AND ("Extent3".OBJECTID = "Extent6".REVIEWER_FK)
    		LEFT OUTER JOIN DISPUTE "Extent7" ON ("Extent7".DISPUTEATTORNEY_FK IS NOT NULL) AND ("Extent3".OBJECTID = "Extent7".DISPUTEATTORNEY_FK) ) "Join5" ON "Extent1".BILLTOCONTACT_FK = "Join5".OBJECTID1
    	LEFT OUTER JOIN  (SELECT 
    		*
    		FROM     PARTY "Extent8"
    		LEFT OUTER JOIN CLIENT "Extent9" ON ("Extent9".PRIMARYCONTACT_FK IS NOT NULL) AND ("Extent8".OBJECTID = "Extent9".PRIMARYCONTACT_FK)
    		LEFT OUTER JOIN BILLTO "Extent10" ON "Extent8".OBJECTID = "Extent10".BILLTOCONTACT_FK
    		LEFT OUTER JOIN DISPUTE "Extent11" ON ("Extent11".REVIEWER_FK IS NOT NULL) AND ("Extent8".OBJECTID = "Extent11".REVIEWER_FK)
    		LEFT OUTER JOIN DISPUTE "Extent12" ON ("Extent12".DISPUTEATTORNEY_FK IS NOT NULL) AND ("Extent8".OBJECTID = "Extent12".DISPUTEATTORNEY_FK) ) "Join10" ON "Extent1".BILLTOCONTACT_FK = "Join10".OBJECTID3
    	LEFT OUTER JOIN  (SELECT 
    		*
    		FROM     PARTY "Extent13"
    		LEFT OUTER JOIN CLIENT "Extent14" ON ("Extent14".PRIMARYCONTACT_FK IS NOT NULL) AND ("Extent13".OBJECTID = "Extent14".PRIMARYCONTACT_FK)
    		LEFT OUTER JOIN BILLTO "Extent15" ON "Extent13".OBJECTID = "Extent15".BILLTOCONTACT_FK
    		LEFT OUTER JOIN DISPUTE "Extent16" ON ("Extent16".REVIEWER_FK IS NOT NULL) AND ("Extent13".OBJECTID = "Extent16".REVIEWER_FK)
    		LEFT OUTER JOIN DISPUTE "Extent17" ON ("Extent17".DISPUTEATTORNEY_FK IS NOT NULL) AND ("Extent13".OBJECTID = "Extent17".DISPUTEATTORNEY_FK) ) "Join15" ON "Extent1".BILLTOCONTACT_FK = "Join15".OBJECTID4
    	LEFT OUTER JOIN  (SELECT 
    		*
    		FROM     PARTY "Extent18"
    		LEFT OUTER JOIN CLIENT "Extent19" ON ("Extent19".PRIMARYCONTACT_FK IS NOT NULL) AND ("Extent18".OBJECTID = "Extent19".PRIMARYCONTACT_FK)
    		LEFT OUTER JOIN BILLTO "Extent20" ON "Extent18".OBJECTID = "Extent20".BILLTOCONTACT_FK
    		LEFT OUTER JOIN DISPUTE "Extent21" ON ("Extent21".REVIEWER_FK IS NOT NULL) AND ("Extent18".OBJECTID = "Extent21".REVIEWER_FK)
    		LEFT OUTER JOIN DISPUTE "Extent22" ON ("Extent22".DISPUTEATTORNEY_FK IS NOT NULL) AND ("Extent18".OBJECTID = "Extent22".DISPUTEATTORNEY_FK) ) "Join20" ON "Extent1".BILLTOCONTACT_FK = "Join20".OBJECTID6
    	LEFT OUTER JOIN  (SELECT 
    		*
    		FROM     PARTY "Extent23"
    		LEFT OUTER JOIN CLIENT "Extent24" ON ("Extent24".PRIMARYCONTACT_FK IS NOT NULL) AND ("Extent23".OBJECTID = "Extent24".PRIMARYCONTACT_FK)
    		LEFT OUTER JOIN BILLTO "Extent25" ON "Extent23".OBJECTID = "Extent25".BILLTOCONTACT_FK
    		LEFT OUTER JOIN DISPUTE "Extent26" ON ("Extent26".REVIEWER_FK IS NOT NULL) AND ("Extent23".OBJECTID = "Extent26".REVIEWER_FK)
    		LEFT OUTER JOIN DISPUTE "Extent27" ON ("Extent27".DISPUTEATTORNEY_FK IS NOT NULL) AND ("Extent23".OBJECTID = "Extent27".DISPUTEATTORNEY_FK) ) "Join25" ON "Extent1".BILLTOCONTACT_FK = "Join25".OBJECTID8
    	LEFT OUTER JOIN  (SELECT 
    		*
    		FROM     PARTY "Extent28"
    		LEFT OUTER JOIN CLIENT "Extent29" ON ("Extent29".PRIMARYCONTACT_FK IS NOT NULL) AND ("Extent28".OBJECTID = "Extent29".PRIMARYCONTACT_FK)
    		LEFT OUTER JOIN BILLTO "Extent30" ON "Extent28".OBJECTID = "Extent30".BILLTOCONTACT_FK
    		LEFT OUTER JOIN DISPUTE "Extent31" ON ("Extent31".REVIEWER_FK IS NOT NULL) AND ("Extent28".OBJECTID = "Extent31".REVIEWER_FK)
    		LEFT OUTER JOIN DISPUTE "Extent32" ON ("Extent32".DISPUTEATTORNEY_FK IS NOT NULL) AND ("Extent28".OBJECTID = "Extent32".DISPUTEATTORNEY_FK) ) "Join30" ON "Extent1".BILLTOCONTACT_FK = "Join30".OBJECTID10
    	WHERE (("Extent1".BILLTOCODE = :p__linq__0) AND ("Extent2".ID = :p__linq__1)) AND ROWNUM <= 1
    )  "Limit1"
    

     

    The query without the include looks like this, so it's that property that's pulling in the extra tables.

     

    SELECT 
    *
    FROM ( SELECT 
    	*
    	FROM  BILLTO "Extent1"
    	INNER JOIN CLIENT "Extent2" ON "Extent1".CLIENT_FK = "Extent2".OBJECTID
    	WHERE (("Extent1".BILLTOCODE = :p__linq__0) AND ("Extent2".ID = :p__linq__1)) AND ROWNUM <= 1
    )  "Limit1"
    


     

     

    if we remove the include and load the BillToContact (Which is the party table) either through lazy loading or the Load statement, it still pulls in the additional data / joins when we access the property. We need to update information on the BillToContact and save; but we don't want all of the additional data its pulling in about the dispute, and the other BillTo's and clients that the Contact is associated with.  

     

    We have this problem with other objects / relationships so unfortunately it's not feasible to change all of them to no-tracking and attach each of them and their sub objects individually as we want to track them.





    • Edited by Ed Frey Wednesday, November 9, 2011 3:56 PM
    Wednesday, November 9, 2011 3:50 PM
  • Any response on this?
    Tuesday, November 15, 2011 9:54 PM