Entityset turns to IEnumerable at an early stage

Question

I have the following issue: I want to display Tickets from a helpdesk system in a neat overview. I also want to display summaries of the data, like groupings by priority and category. On top of that, the same data must be turned into a .pdf document.

I retrieve the data in several steps. Step one is raw data which is the base for the .pdf, the summaries and the actual overview. That way, I ensure they take the same data. Upon creating the .pdf I directly read from the data. The summaries are made by flattening the data. The overview is created by taking a page of 25 records from the data and putting the fields in a grid.

The problem is

IQueryable<RelationHoursTicketItem> HoursByTicket = 
            from Ticket t in allTickets
            let hours = t.Bezoekrapport.Urens.
                Where(h =>
                    (dateFrom == null || h.Datum >= dateFrom)
                    && (dateTo == null || h.Datum <= dateTo)
                    && h.Uren1 > 0)
            select new RelationHoursTicketItem
             {
                 Date = t.DatumCreatie,
                 DateSolved = new DateTime(2012, 11, 11),
                 Ticket = t,
                 Relatie = t.Relatie,
                 TicketNr = t.Id,
                 TicketName = t.Titel,
                 TicketCategorie = t.TicketCategorie,
                 TicketPriority = t.TicketPrioriteit,
                 TicketRemark = t.TicketOpmerkings.SingleOrDefault(tr => tr.IsOplossing)
             };

The problem is in TicketRemark. As soon as I execute the data, I get tens of subqueries since the TicketOpmerkings - Dutch for TicketRemarks - are retrieved one by one. The original intention was to create an IQueryable which would serve as base for all other queries. I have gone through great lengths to achieve this, creating custom expressions to calculate certain fields which I cut from this example.

What is the best possible approach? Writing expressions for all those links like t.TicketOpmerkingen? It would remove some of the best functionality of LINQ. Solutions I saw so far include manually creating Expressions, but then I can just as well forsake this approach and make several queries, each for its specific purpose. Then I forsake the neat 'layered' approach of LINQ and IQueryable.

Anybody some experience with this?

score 2 · Accepted Answer · edited May 23 '17 at 12:12

2

What you are experiencing is known as the n+1 problem. It occurs when you retrieve entities using an ORM, using a Deferred/Lazy Loading capability.

You retrieve entities that are linked to other entities. When you attempt to navigate to the second set of entities, the ORM/Framework has not currently loaded the entity. So it queries the entity for you, allowing you to navigate as intended.

There are several ways to resolve this. Here are some articles regarding Data Load Options:

If you are using Linq to Sql:

MSDN for DataLoadOptions

A small article explaining the usage of DataLoadOptions

For Entity Framework:

MSDN for ObjectQuery(Of T).Include

An article describing eager loading techniques

Hope that helps.

edited May 23 '17 at 12:12

Community

1
1

answered Jul 08 '11 at 11:14

Smudge202

4,689
2
26
44

I have managed to fix it with LoadWith, I simply retrieve the required tickets after I set: options.LoadWith(t => t.TicketOpmerkings); I run into a different problem later: I cannot use order by on the sql, but I can just order the enumerable later. – Paul77 Jul 08 '11 at 13:40
Excellent. There are some downsides to using load options in L2S - for one, you can only set the load options for a context **before** you run a query on the context - this can cause issues but hopefully not for you. Happy coding =) – Smudge202 Jul 08 '11 at 13:41
I found out quickly, I just create a separate datacontext for the tickets. That means I run into trouble with editing/updating later but currently all I need is retrieving a large load of connected data. Previous query sessions could take _minutes_! – Paul77 Jul 08 '11 at 13:43
Why can't you use order by - what happens? I hadn't hit that problem before. Perhaps start a new question for the new issue? – Smudge202 Jul 08 '11 at 13:43
And yes, I do the same thing regarding a seperate context. It's not an ideal practice but generally I try to ensure that I only return/expose entities not derived from the context. I'm actively working on a solution to that, if I find one I'll drop a note in here for you. – Smudge202 Jul 08 '11 at 13:44

Entityset turns to IEnumerable at an early stage

1 Answers1