none
Can this LINQ to Entities query be optimized? RRS feed

  • Question

  • I have the following entities:

        Public Class Player
            
            Public Property ID As Long
            Public Property Name As String
    
            Public Overridable Property ListParticipations As ICollection(Of PlayerInList)
        
        End Class
        Public Class PlayerList
        
            Public Property ID As Long
            Public Property Name as String
            
            Public Overridable Property PlayerParticipations As ICollection(Of PlayerInList)
        
        End Class
       Public Class PlayerInList
           'ID=PlayerID - PlayersListID'
        
            Public Property PlayerID As Long
            Public Property ListID As Long
        
            Public Overridable Property Player As Player
            Public Overridable Property List As PlayerList
        
        End Class


    To summarize: a many to many relationship between `Player` and `PlayerList` with an explicitly defined junction that has a composite ID from the the entities it joins.

    I need a query that gets all the `Players` that participate in a certain `PlayerList`, but from these `Players` I want to know in which other lists they participate.

    I have come up with two queries, both of which take nearly 2 seconds to draw 4500 `Players` from a LocalDb database with in total 12000 `Players`. This strikes me as too slow. Can the LINQ below be optimized?

    QUERY STARTING FROM THE PLAYERS TABLE: 

       players = (From player In db.Players.Include(Function(p) p.ListParticipations)
                   Where player.ListParticipations.Any(Function(p) p.ListID = listID)
                   Select player).ToList

    QUERY STARTING FROM THE PLAYERSINLISTS TABLE USING PROJECTION: 

       Dim anons = (From pil In db.PlayersInLists
                     Where pil.ListID = listID
                     Select New With {.Player = pil.Player, .Lists = pil.Player.ListParticipations}).ToList
        
        players = New List(Of Player)
        For Each item In anons
            Dim newPlayer As Player
            newPlayer = item.Player
            newPlayer.ListParticipations = item.Lists
            players.Add(newPlayer)
        Next





    • Edited by Aner Wednesday, July 31, 2013 10:52 AM
    Wednesday, July 31, 2013 10:52 AM

Answers

  • Hi Aner,

    From your description, I notice the issue you are experiencing is that the performance of Entity Framework.

    I would like to quote some information from the help document for you to improve performance of Entity Framework.

    Let's refer to Performance Considerations.

    I agree with Magnus to use raw SQL or Stored Procedures if you find out that the sql created by Entity Framework was too complicated.

    I hope this will help resolve your problem. If anything is unclear, please free feel to let us know.

    Best Regards.

    Thursday, August 1, 2013 9:43 AM
    Moderator

All replies

  • For advanced queries I would recommend you to use raw SQL or stored procedures to fetch the data. Please refer to this article on MSDN for more information about your options and some code samples: http://msdn.microsoft.com/en-US/data/jj592907

    Wednesday, July 31, 2013 11:17 AM
  • Hi Aner,

    From your description, I notice the issue you are experiencing is that the performance of Entity Framework.

    I would like to quote some information from the help document for you to improve performance of Entity Framework.

    Let's refer to Performance Considerations.

    I agree with Magnus to use raw SQL or Stored Procedures if you find out that the sql created by Entity Framework was too complicated.

    I hope this will help resolve your problem. If anything is unclear, please free feel to let us know.

    Best Regards.

    Thursday, August 1, 2013 9:43 AM
    Moderator