Using Database first approach in Entity framework, how to fetch the data from a single table only which has multiple foreign key relationship?

Question

I am using Database first approach in Entity framework. I have two tables i.e. Person and Role table. Person table has 3 columns

PersonId
Name
RoleId

Role table has 2 columns

RoleId
RoleName

The automated generated classes for these two tables are below along with the foreign key relationship.

public partial class Role
{
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")]
    public Role()
    {
        this.People = new HashSet<Person>();
    }

    public int RoleId { get; set; }
    public string RoleName { get; set; }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")]
    public virtual ICollection<Person> People { get; set; }
}

public partial class Person
{
    public int PersonId { get; set; }
    public string Name { get; set; }

    public virtual Role Role { get; set; }
}

I used following code to retrieve the data from Role table

    public List<Role> GetRole()
    {
        var _context= new TestDBEntities();
        List<Role> data = new List<Role>();
        data  = _context.Roles.ToList();
        return data;
    }

While retrieving the data from the Role table, all the data from the Role table was retrieved but the Person table data was also retrieved along with it. The reason behind it must be the relationship between these two tables. So, I want to fetch the data only from the Role table. Can anyone assist me on how to fetch the data from the Role table only without the data from Person?

I am using break point for debugging, so I could see the person list within the row of the Role data — S. Tmg
– S. Tmg, Commented Jul 17, 2016 at 13:33
Ivan below answers why this i happening. If you remove 'virtual' keyword from ICollection<Person>, this will not appear until you load related objects manually. — MagisterCrazy
– MagisterCrazy, Commented Jul 17, 2016 at 14:57
I solved it but I do not have to remove 'virtual' keyword from ICollection<Person>. Rather, I disabled the lazyloading in the constructor of the EntitiesClass that inherits the DbContext. public TestDBEntities() : base("name=TestDBEntities") { this.Configuration.LazyLoadingEnabled = false; } — S. Tmg
– S. Tmg, Commented Jul 18, 2016 at 5:42

Community · Accepted Answer · 2017-05-23 12:32:39Z

3

The related data is not retrieved by the shown code, but when some other code does access People property for the first time. It could be the debugger variables window or some serialization code.

This behavior is called Lazy Loading. It can be disabled in a several ways, the easiest is to remove the virtual keyword from the navigation properties. In Code First you would do that directly in the code, in the EDMX designer I guess there must be some property controlling the generated property accessor. Or using the techniques described in the Selective disabling of lazy loading with Database First method thread.

edited May 23, 2017 at 12:32

CommunityBot

11 silver badge

answered Jul 17, 2016 at 11:27

Ivan Stoev

208k17 gold badges356 silver badges376 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

S. Tmg Over a year ago

While looking at the break point, I can see the row of the Role data has the Person property (because of relationship between Person and Role table) and it contains the Person data. So the above mentioned code is fetching the Role data as well as the Person data within the row of the Role data. I'll try the technique that you have mentioned above and let u know about it.

Ivan Stoev Over a year ago

How do you see it? In a debugger, right? See the first paragraph of the answer. Actually there is no way to see if it's loaded or not because once you "look" at the property, it gets loaded - that's the side effect of the lazy loading.

S. Tmg Over a year ago

Thank you @Ivan for your answer. I solved the issue by including

public TestDBEntities()             : base("name=TestDBEntities")         {             this.Configuration.LazyLoadingEnabled = false;         }

in the constructor of the TestDBEntities class which inherits the DbContext. It worked for me. Now the Person table data can be fetched without retrieving the data from the foreign key table.

Ivan Stoev Over a year ago

You are welcome, glad that helped solving the issue. The solution you choose is one of the "several ways" I mentioned and is explained in the "Turn off lazy loading for all entities" section of the "Lazy Loading" link. So you pick the one that is most appropriate for your case.

Collectives™ on Stack Overflow

Using Database first approach in Entity framework, how to fetch the data from a single table only which has multiple foreign key relationship?

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related