Grouping and summing values for food takeout

Question

I have 4 properties which I calculate using LINQ:

public decimal TotalEatin { get; set; }
public int EatinAmount { get; set; }

public decimal TotalTakeout { get; set; }
public int TakeoutAmount { get; set; }

The way I generate the values are done correctly this way, but I think the code is a bit cumbersome because of the repeated iteration that is needed.

Because I need to generate the values from different DataTables, I have no idea how I can improve this using one "large" query. My knowledge of LINQ / SQL-like syntax is simply way too low.

Is it somehow possible to put these properties in a separate class, and use proper LINQ to generate one instance of this class with the right property values?

TotalEatin = Data.kassaOrders.AsEnumerable().Where(x => x.Field<bool>("btwticket") && !x.Field<bool>("terugname") && !x.Field<bool>("teruggenomen"))
    .SelectMany(x => Data.orderDetails.AsEnumerable().Where(d => d.Field<int>("orderid") == x.Field<int>("serial") && !d.Field<bool>("takeout")))
    .Sum(x => x.Field<decimal>("price"));

TotalEatin += Data.kassaOrders.AsEnumerable().Where(x => x.Field<bool>("btwticket") && !x.Field<bool>("terugname") && !x.Field<bool>("teruggenomen"))
    .SelectMany(x => Data.orderDetails.AsEnumerable().Where(d => d.Field<int>("orderid") == x.Field<int>("serial") && !d.Field<bool>("takeout")))
    .SelectMany(x => Data.MenuDetails.AsEnumerable().Where(d => d.Field<int>("orderid") == x.Field<int>("orderid")))
    .Sum(x => x.Field<decimal>("prijs"));

EatinAmount = Data.kassaOrders.AsEnumerable().Where(x => x.Field<bool>("btwticket") && !x.Field<bool>("terugname") && !x.Field<bool>("teruggenomen"))
    .SelectMany(x => Data.orderDetails.AsEnumerable().Where(d => d.Field<int>("orderid") == x.Field<int>("serial") && !d.Field<bool>("takeout")))
    .GroupBy(x => new { takeout = x.Field<bool>("takeout"), orderid = x.Field<int>("orderid") }).Distinct().Count();

TotalTakeout = Data.kassaOrders.AsEnumerable().Where(x => x.Field<bool>("btwticket") && !x.Field<bool>("terugname") && !x.Field<bool>("teruggenomen"))
    .SelectMany(x => Data.orderDetails.AsEnumerable().Where(d => d.Field<int>("orderid") == x.Field<int>("serial") && d.Field<bool>("takeout")))
    .Sum(x => x.Field<decimal>("price"));

TotalTakeout += Data.kassaOrders.AsEnumerable().Where(x => x.Field<bool>("btwticket") && !x.Field<bool>("terugname") && !x.Field<bool>("teruggenomen"))
    .SelectMany(x => Data.orderDetails.AsEnumerable().Where(d => d.Field<int>("orderid") == x.Field<int>("serial") && d.Field<bool>("takeout")))
    .SelectMany(x => Data.MenuDetails.AsEnumerable().Where(d => d.Field<int>("orderid") == x.Field<int>("orderid")))
    .Sum(x => x.Field<decimal>("prijs"));

TakeoutAmount = Data.kassaOrders.AsEnumerable().Where(x => x.Field<bool>("btwticket") && !x.Field<bool>("terugname") && !x.Field<bool>("teruggenomen"))
    .SelectMany(x => Data.orderDetails.AsEnumerable().Where(d => d.Field<int>("orderid") == x.Field<int>("serial") && d.Field<bool>("takeout")))
    .GroupBy(x => new { takeout = x.Field<bool>("takeout"), orderid = x.Field<int>("orderid") }).Distinct().Count();

Answer 1

Scope

If all you need is to calculate some values from a set of data tables, it would suffice to use LINQ. However, if you prefer to provide an API with common functionality concerning orders and order details, you should really create your own domain classes and map the data tables to your domain classes. For the sake of this question, I would opt for using LINQ directly on the data tables.

Review

You are iterating your 3 data tables kassaOrders, orderDetails and menuDetails over and over again with each order detail having a link to a kassa order and each menu detail having a link to a kassa order. We can easily optimize performance and reduce code redundancy by caching these tables in lookup collections.

Lookup Tables

Start by caching the kassa orders in a dictionary by order id (called serial in the orders). This way, fast lookup is possible to join the details.

Data.kassaOrders.AsEnumerable()
    .Where(x => x.Field<bool>("btwticket") && 
          !x.Field<bool>("terugname") && !x.Field<bool>("teruggenomen"))

 var orders = Data.kassaOrders
    .AsEnumerable()
    .Where(x => 
         x.Field<bool>("btwticket") && 
        !x.Field<bool>("terugname") && 
        !x.Field<bool>("teruggenomen"))
    .ToDictionary(order => order.Field<int>("serial"));

We can then use lookup tables - which are dictionaries that store a sequence of values instead of a value - to cache the order and menu details by order id. This code shows how to do it for eating in, equivalent LINQ could be written for take out.

Data.orderDetails.AsEnumerable()
   .Where(d => d.Field<int>("orderid") == x.Field<int>("serial") 
       && !d.Field<bool>("takeout"))

 var orderDetailsEatIn = Data.orderDetails
    .Where(order => !order.Field<bool>("takeout"))
    .ToLookup(detail => detail.Field<int>("orderid"));

and

Data.MenuDetails.AsEnumerable()
   .Where(d => d.Field<int>("orderid") == x.Field<int>("orderid"))

var menuDetailsEatIn = Data.MenuDetails
   .Where(order => !order.Field<bool>("takeout"))
   .ToLookup(detail => detail.Field<int>("orderid"));

Calculate Amount

You're calculating the amount using a peculiar GroupBy. There is no reasong to group by the take out variable, because you filter on a specific value right before !d.Field<bool>("takeout"). The call to Distinct also doesn't make sense since details are already grouped by key and keys are distinct anyway.

GroupBy(x => new { 
    takeout = x.Field<bool>("takeout"), 
    orderid = x.Field<int> ("orderid") }).Distinct().Count();

Using our lookup tables, we can easily calculate the amount as below. To stay within your spec, we count the number of orders that have at least one order detail available. Note that an empty collection is returned orderDetailsEatIn[key] is the key is not present in an ILookup.

 var EatinAmount = orders
    .Select(order => orderDetailsEatIn[order.Key])
    .Count(details => details.Any());

Calculating Total

Your spec is a bit strange to me. I would expect Order 1-->* OrderDetail 1-->* MenuDetail. Instead the cardinality is Order 1-->* OrderDetail, Order 1-->* MenuDetail with the price for each menu detail multiplied by the number of order details to calculate the subtotal by menu detail.

We could calculate the subtotal for the order details as follows:

var subtotalOrderDetailEatIn = orders
    .SelectMany(order => orderDetailsEatIn[order.Key])
    .Select(detail => detail.Field<decimal>("price"))
    .DefaultIfEmpty(0).Sum();

and the subtotal for menu details:

var subtotalMenuDetailEatIn = orders
    .SelectMany(order => orderDetailsEatIn[order.Key]
        .SelectMany(detail => menuDetailsEatIn[order.Key]))
    .Select(detail => detail.Field<decimal>("prijs"))
    .DefaultIfEmpty(0).Sum();

yielding a grant total of:

var TotalEatin = subtotalOrderDetailEatIn + subtotalMenuDetailEatIn;

Note that this is just one possible way to use LINQ to calculate these values from data tables. Other ways could include:

• Create intermediate classes, value tuples or domain classes to store strongly typed properties to facilitate querying readability.
• A more advanced LINQ approach using GroupJoin to build complex classes given several flattened collections and join selectors.

It's really up to you to see how comfortable you are exploring the realms of LINQ.