CODEWITHSUNDEEP
entity-framework
barteloma
barteloma

Reputation: 6845

Entity Framework many to many query performance

I have many-to-many relationship in my database. And I am sending a query with Entity Framework.

My classes are Student, Course and StudentCourse.

    public IHttpActionResult Get()
    {
        var data = context.Students;

        return Ok(data);
    }

I have created an query interceptor to see the queries sent to the database.

    Intercepted on: ReaderExecuted :-  IsAsync: False, Command Text: SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Name] AS [Name]
        FROM [dbo].[Student] AS [Extent1] 
    Intercepted on: ReaderExecuted :-  IsAsync: False, Command Text: SELECT 
        [Extent1].[Id] AS [Id], 
        [Extent1].[Name] AS [Name]
        FROM [dbo].[Student] AS [Extent1] 
    Intercepted on: ReaderExecuted :-  IsAsync: False, Command Text: SELECT 
        [Extent2].[Id] AS [Id], 
        [Extent2].[Name] AS [Name]
        FROM  [dbo].[Course] AS [Extent1]
        INNER JOIN [dbo].[Courses] AS [Extent2] ON [Extent1].[CourseId] = [Extent2].[Id]
        WHERE [Extent1].[CourseId] = @EntityKeyValue1 
    Intercepted on: ReaderExecuted :-  IsAsync: False, Command Text: SELECT 
        [Extent2].[Id] AS [Id], 
        [Extent2].[Name] AS [Name]
        FROM  [dbo].[Course] AS [Extent1]
        INNER JOIN [dbo].[Courses] AS [Extent2] ON [Extent1].[CourseId] = [Extent2].[Id]
        WHERE [Extent1].[StudentId] = @EntityKeyValue1 
....
...
...

It send 14 queries to get relational data from server. Is this a performance problem for multiple rows of data?

Upvotes: 0

Views: 49

Answers (1)

Akos Nagy
Akos Nagy

Reputation: 4350

I think you're seeing what is called lazy-loading. Basically what happens is that a query is issued to the db to get all the Students. Then, when the response is sent, the Students collection is traversed and the StudentCourses are loaded to every Student, one query at a time. Then, when the StudentCourses are sent back in every Student, the Course property is loaded (again, one at a time) and is sent back.

You should try disabling lazy-loading and using eager loading to load the StudentCourses and Courses together with the Students. Something like this:

context.Students.Include(s=>s.StudentCourses).Include(s=>s.StudentCourses.Select(sc=>sc.Course)

Upvotes: 2

Related Questions