Use of custom expression in LINQ leads to a query for each use

I have the following p开发者_运维百科roblem: In our database we record helpdesk tickets and we book hours under tickets. Between those is a visit report. So it is: ticket => visitreport => hours.

Hours have a certain 'kind' which is not determined by a type indicator in the hour record, but compiled by checking various properties of an hour. For example, an hour which has a customer but is not a service hour is always an invoice hour.

Last thing I want is that the definitions of those 'kinds' roam everywhere in the code. They must be at one place. Second, I want to be able to calculate totals of hours from various collections of hours. For example, a flattened collection of tickets with a certain date and a certain customer. Or all registrations which are marked as 'solution'.

I have decided to use a 'layered' database access approach. The same functions may provide data for screen representation but also for a report in .pdf . So the first step gathers all relevant data. That can be used for .pdf creation, but also for screen representation. In that case, it must be paged and ordered in a second step. That way I don't need separate queries which basically use the same data.

The amount of data may be large, like the creation of year totals. So the data from the first step should be queryable, not enumerable. To ensure I stay queryable even when I add the summation of hours in the results, I made the following function:

    public static decimal TreeHours(this IEnumerable<Uren> h, FactHourType ht)
{
    IQueryable<Uren> hours = h.AsQueryable();
    ParameterExpression pe = Expression.Parameter(typeof(Uren), "Uren");
    Expression left = Expression.Property(pe, typeof(Uren).GetProperty("IsOsab"));
    Expression right = Expression.Constant(true, typeof(Boolean));
    Expression isOsab = Expression.Equal(Expression.Convert(left, typeof(Boolean)), Expression.Convert(right, typeof(Boolean)));

    left = Expression.Property(pe, typeof(Uren).GetProperty("IsKlant"));
    right = Expression.Constant(true, typeof(Boolean));
    Expression isCustomer = Expression.Equal(Expression.Convert(left, typeof(Boolean)), Expression.Convert(right, typeof(Boolean)));
    Expression notOsab;
    Expression notCustomer;
    Expression final;
    switch (ht)
    {
        case FactHourType.Invoice:
            notOsab = Expression.Not(isOsab);
            final = Expression.And(notOsab, isCustomer);
            break;
        case FactHourType.NotInvoice:
            notOsab = Expression.Not(isOsab);
            notCustomer = Expression.Not(isCustomer);
            final = Expression.And(notOsab, notCustomer);
            break;
        case FactHourType.OSAB:
            final = Expression.And(isOsab, isCustomer);
            break;
        case FactHourType.OsabInvoice:
            final = Expression.Equal(isCustomer, Expression.Constant(true, typeof(Boolean)));
            break;
        case FactHourType.Total:
            final = Expression.Constant(true, typeof(Boolean));
            break;
        default:
            throw new Exception("");
    }
    MethodCallExpression whereCallExpression = Expression.Call(
typeof(Queryable),
"Where",
new Type[] { hours.ElementType },
hours.Expression,
Expression.Lambda<Func<Uren, bool>>(final, new ParameterExpression[] { pe })
);
    IQueryable<Uren> result = hours.Provider.CreateQuery<Uren>(whereCallExpression);
    return result.Sum(u => u.Uren1);

}

The idea behind this function is that it should remain queryable so that I don't switch a shipload of data to enumerable.

I managed to stay queryable until the end. In step 1 I gather the raw data. In step 2 I order the data and subsequently I page it. In step 3 the data is converted to JSon and sent to the client. It totals hours by ticket.

The problem is: I get one query for the hours for each ticket. That's hundreds of queries! That's too much...

I tried the following approach:

DataLoadOptions options = new DataLoadOptions();
options.LoadWith<Ticket>(t => t.Bezoekrapport);
options.LoadWith<Bezoekrapport>(b => b.Urens);
dc.LoadOptions = options;

Bezoekrapport is simply Dutch for 'visitreport'. When I look at the query which retrieves the tickets, I see it joins the Bezoekrapport/visitreport but not the hours which are attached to it.

A second approach I have used is manually joining the hours in LINQ, but that does not work as well.

I must do something wrong. What is the best approach here?

The following code snippets are how I retrieve the data. Upon calling toList() on strHours in the last step, I get a hailstorm of queries. I've been trying for two days to work around it but it just doesn't work... Something must be wrong in my approach or in the function TreeHours.

Step 1:

IQueryable<RelationHoursTicketItem> HoursByTicket =
            from Ticket t in allTickets
            let RemarkSolved = t.TicketOpmerkings.SingleOrDefault(tr => tr.IsOplossing)
            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 = RemarkSolved == null ? (DateTime?)null :                                                                                              RemarkSolved.Datum,
                           Ticket = t,
                           Relatie = t.Relatie,
                           HoursOsab = hours.TreeHours(FactHourType.OSAB),
                           HoursInvoice = hours.TreeHours(FactHourType.Invoice),
                           HoursNonInvoice = hours.TreeHours(FactHourType.NotInvoice),
                           HoursOsabInvoice = hours.TreeHours(FactHourType.OsabInvoice),
                           TicketNr = t.Id,
                           TicketName = t.Titel,
                           TicketCategorie = t.TicketCategorie,
                           TicketPriority = t.TicketPrioriteit,
                           TicketRemark = RemarkSolved
                       };

Step 2

        sort = sort ?? "TicketNr";
        IQueryable<RelationHoursTicketItem> hoursByTicket = GetRelationHours(relation,     dateFrom, dateTo, withBranches);
        IOrderedQueryable<RelationHoursTicketItem> orderedResults;

        if (dir == "ASC")
        {
            orderedResults = hoursByTicket.OrderBy(sort);
}
        else
        {
            orderedResults = hoursByTicket.OrderByDescending(sort);
        }
        IEnumerable<RelationHoursTicketItem> pagedResults = orderedResults.Skip(start ?? 0).Take(limit ?? 25);
        records = hoursByTicket.Count();
        return pagedResults;

Step 3:

 IEnumerable<RelationHoursTicketItem> hours = _hourReportService.GetRelationReportHours(relation, dateFrom, dateTo, metFilialen, start, limit, dir, sort, out records);

        var strHours = hours.Select(h => new
               {
                   h.TicketNr,
                   h.TicketName,
                   RelationName = h.Relatie.Naam,
                   h.Date,
                   TicketPriority = h.TicketPriority.Naam,
                   h.DateSolved,
                   TicketCategorie = h.TicketCategorie == null ? "" : h.TicketCategorie.Naam,
                   TicketRemark = h.TicketRemark == null ? "" : h.TicketRemark.Opmerking,
                   h.HoursOsab,
                   h.HoursInvoice,
                   h.HoursNonInvoice,
                   h.HoursOsabInvoice
               });

I don't think your TreeHours extension method can be converted to SQL by LINQ in one go. So are evaluated on execution of each constructor of the row, causing a 4 calls to the database in this case per row.

I would simplfy your LINQ query to return you the raw data from SQL, using a simple JOIN to get all tickets and there hours. I would then group and filter the Hours by type in memory. Otherwise, if you really need to perform your operations in SQL then look at the CompiledQuery.Compile method. This should be able to handle not making a query per row. I'm not sure you'd get the switch in there but you may be able to convert it using the ?: operator.