Linq to Entity Framework (v1)

Question

Hi -

I'm currently working on a solution, where there are the concepts of users, work queues and work items. At a basic level a user has a series of work queues assigned to them, the work queue containing the work that is to be done.

So - at the moment we have a basic Linq query that returns all of the queues, but I was hoping to restrict that query to improve performance - based on the current query it pulls back over 94,000 records for a maximum of 127 work queues. The reason for this is that in this linq query we include the WorkItems, so that we can (later on) count the number of work items in the queue. I have extended the WorkQueue object to inclde a LinqQuery to count the number of records - which I hoped would work, but that fails to work unless the initial query includes the workitems.

This is basically to be databound using a master / detail type interface, and I'm somewhat concerned that (based on the Trace String generated from LINQ) that there is a huge amount of data produced which will affect performance.

Historically I would have used straight SQL binding - but the previous developer had decided to use EF for this.. I'm also hampered by the fact that we're still using version 1 of the Entity Framework Modelling system.

I have considered replacing the query with a Stored Procedure call - but that doesn't seem to work so well either - giving me an additional NULL record. I've tried using LinqPad - and that sort of works, but as soon as you want to include things, the syntax goes to pot.

Here's what I have so far...

Here's the Linq Query :

QueueTable.DataSource = From queue In objImageViewerContext.WorkQueues().Include("WorkItems") _
                                        .Where(Function(i) i.Scan_Type = Constants.Work_Queue_Type_Front_End) _
                                        Order By (queue.WorkItems.Count > 0) Descending, queue.Name Ascending

Here's the SQL that's generated...

SELECT     WorkQueue_ID, Name, Work_Type, Functional_Area, Process, Text_Code, Barcode, Scan_Type, SLA_Minutes, Medical_Indicator, IsTeamQueue, 
                      C2 AS C1, Role_ID, C4 AS C2, C3, WorkItem_ID, Participant, Last_Action, Last_Modified, Date_Added, Modified_By, Is_Urgent_Action, Image_ID, 
                      Queue_ID
FROM         (SELECT     CASE WHEN ([Project2].[C1] > 0) THEN CAST(1 AS bit) WHEN (NOT ([Project2].[C2] > 0)) THEN CAST(0 AS bit) END AS C1, 
                                              Project2.WorkQueue_ID, Project2.Name, Project2.Work_Type, Project2.Functional_Area, Project2.Process, Project2.Text_Code, 
                                              Project2.Barcode, Project2.Scan_Type, Project2.SLA_Minutes, Project2.Medical_Indicator, Project2.IsTeamQueue, Project2.Role_ID, 
                                              1 AS C2, Extent4.WorkItem_ID, Extent4.Image_ID, Extent4.Queue_ID, Extent4.Participant, Extent4.Last_Action, Extent4.Last_Modified, 
                                              Extent4.Date_Added, Extent4.Modified_By, Extent4.Is_Urgent_Action, CASE WHEN ([Extent4].[WorkItem_ID] IS NULL) THEN CAST(NULL 
                                              AS int) ELSE 1 END AS C3, CASE WHEN ([Extent4].[WorkItem_ID] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS C4
                       FROM          (SELECT     WorkQueue_ID, Name, Work_Type, Functional_Area, Process, Text_Code, Barcode, Scan_Type, SLA_Minutes, 
                                                                      Medical_Indicator, IsTeamQueue, Role_ID, C1,
                                                                          (SELECT     COUNT(CAST(1 AS bit)) AS A1
                                                                            FROM          WorkItems AS Extent3
                                                                            WHERE      (Project1.WorkQueue_ID = Queue_ID)) AS C2
                                               FROM          (SELECT     WorkQueue_ID, Name, Work_Type, Functional_Area, Process, Text_Code, Barcode, Scan_Type, SLA_Minutes, 
                                                                                              Medical_Indicator, IsTeamQueue, Role_ID,
                                                                                                  (SELECT     COUNT(CAST(1 AS bit)) AS A1
                                                                                                    FROM          WorkItems AS Extent2
                                                                                                    WHERE      (Extent1.WorkQueue_ID = Queue_ID)) AS C1
                                                                       FROM          WorkQueues AS Extent1
                                                                       WHERE      (N'Front End' = Scan_Type)) AS Project1) AS Project2 LEFT OUTER JOIN
                                              WorkItems AS Extent4 ON Project2.WorkQueue_ID = Extent4.Queue_ID) AS Project3

It's so frustrating because I know I can write the SQL to accomplish what I want, I just don't seem to be able to get Linq to work.

Answer 1

You can start to optimize this by using a .Select() to only retrieve the columns you care about and possibly use paging with .Skip().Take()

Also, instead of grabbing all of the WorkItems why don't you use a select statement such as:

.Select(i => new { Count = i.WorkItems.Count() })

and get rid of .Include("WorkItems") if all you need is a count of WorkItems.

Sorry I'm using C#, I never write in VB but trust you understand what I'm trying to say