locked
EF joining tables with 0..1 relationships in Entity model RRS feed

  • Question

  • I have found a strange thing in SQL commands created by EF. I have a entity model where one entity (call it A ) has five associations to other entities(call it B1-B5), each with (A) 1 to 0..1 (Bx) relationship. When I query Object context for entity A it will always create SQL command where all tables for entities B1-B5 are joined. This is not itself such a big problem, but if I try to connect entity A to different query with Include(), I will end up with SQL command with five times more joins than intended. That is a serious performance problem for my application:-(

     

    Is there a way how to make EF stop joining these related tables?


    TomiGaspar
    Thursday, September 22, 2011 9:08 AM

Answers

  • Hi.

    I finaly found what is causing this trouble.Luckily I had more such connections, and as I tried them aswell, I found out that some are behaving in same way, and some are not. Later on I found reason. Reason is that associations that had referential constraint on them are not included in query, but entities that have no referential constraint on them are loaded with ther parental entity. I read somewhere that this is a feature of EF. But now I can control it by seting or not setting referential constraint on associations.

    Thanx everyone for replies.


    TomiGaspar
    • Marked as answer by tomigaspar Wednesday, September 28, 2011 9:00 AM
    Wednesday, September 28, 2011 9:00 AM

All replies

  • Hello,

    Regarding the performance of Include method, the performance depends on different scenarios. I suggest you check this thread http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/f8d968e1-5937-4a06-a70a-429eea1e0950/.

    Please feel free to let us know if you have any question.

    Thanks,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, September 26, 2011 3:23 AM
  • Hello,

    Thanks for reply. I read that thread you suggested, but havent found anything regarding my problem. My problem lies in Entity Framework SQL commands, that are created when including table with several other tables associated to it with 0..1 relationship. I dont want them in resulting SQL code. But it seems that this cannot be helped:-(


    TomiGaspar
    Monday, September 26, 2011 5:59 AM
  • Hello again,

    As mentioned, performance issue reply on many different scenarios. In some scenarios, specifying a query path using Include method is much faster because it didn't require more round trips between with database. But others, it maybe return too much data. To avoid returning too much data in a single query, consider paging the result of query into multiple manageable groups. For your reference, please check it here. http://msdn.microsoft.com/en-us/library/bb738702.aspx

    Thanks,


    Larcolais Gong[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, September 27, 2011 9:07 AM
  • Hi.

    This is not problem of query returning too many DB entries, its that query returns data which I dont want. And I havent found any way how to make it return only data which I want. I want to retrieve only data for entity I am querying, but instead I retrieve queried entities PLUS all entities that are associated with it via 0..1 relationship. This makes a lot of mess if I try to include any other entities, as resulting query is multiplied by number of including entities, which is not acceptable because of performance.

     

    Tomi


    TomiGaspar
    Tuesday, September 27, 2011 9:55 AM
  • If you didn't wanna load relative entities, why not did you check LoadWith method to load appropriate entities which you want to load. http://msdn.microsoft.com/en-us/library/system.data.linq.dataloadoptions.loadwith.aspx 

    Thanks,

    Werewolf,


    Just a newbie for everything.
    Wednesday, September 28, 2011 4:02 AM
  • Hi.

    I finaly found what is causing this trouble.Luckily I had more such connections, and as I tried them aswell, I found out that some are behaving in same way, and some are not. Later on I found reason. Reason is that associations that had referential constraint on them are not included in query, but entities that have no referential constraint on them are loaded with ther parental entity. I read somewhere that this is a feature of EF. But now I can control it by seting or not setting referential constraint on associations.

    Thanx everyone for replies.


    TomiGaspar
    • Marked as answer by tomigaspar Wednesday, September 28, 2011 9:00 AM
    Wednesday, September 28, 2011 9:00 AM
  • Hello Werewolf.

    I am not using Linq to SQL but Entity Framework instead. So I am also not using LoadWith method.


    TomiGaspar
    Monday, October 3, 2011 7:21 AM