Simplifying a Linq query by using Include instead of Join

Question

I've been asked to simplify the following Linq query:

var orders = db.Orders
    .Join(db.Shipments,
        o => o.OrderID,
        s => s.OrderID,
        (o, s) => new { Order = o, Shipment = s })
    .Join(db.LineItems,
        s => s.Shipment.ShipmentID,
        l => l.ShipmentID,
        (s, l) => new { Order = s.Order, Shipment = s.Shipment, LineItem = l })
    .Join(db.StatusTypes,
        s => s.Shipment.StatusTypeID,
        st => st.StatusTypeID,
        (s, st) => new { Order = s.Order, Shipment = s.Shipment, LineItem = s.LineItem, Description = st.ExternalDescription })
    .Where(x => x.Order.AccountID == accountId)
    .GroupBy(x => x.Order.OrderNumber)
    .ToList()
    .Select(
        x => new OrderStatusViewModel
        {
            Date = x.Max(y => y.Order.Created),
            OrderNumber = x.Key,
            Cost = x.Sum(y => y.LineItem.UnitPrice).ToString(),
            Status = x.Max(y => y.Description)
        }
    );

By replacing the Joins with Includes. I've searched around, and I've discovered that Joins and Includes are somewhat equivalent. But I can't figure out how to convert this query to one that uses includes instead of joins. Is it actually less code and simpler to use includes instead of joins here?

You could use `Zip` instead of `Join` aswell – Greggz Jan 18 '18 at 10:08 — Greggz, Jan 18 '18 at 10:08

lucky · Accepted Answer · 2018-01-18T10:50:21.320

1

I strongly suggest you to use navigation properties instead of manual builded joins. It will be more efficent and controlable. Read this article.

If you would convert your query to navigation property form by using Include, it would seem like that;

var orders = db.Orders
    .Include(x => x.Shipments)
    .Include(x => x.Shipments.Select(y => y.LineItems))
    .Include(x => x.Shipments.Select(y => y.StatusType))
    .Where(x => x.Order.AccountID == accountId)
    .GroupBy(x => x.Order.OrderNumber)
    .ToList()
    .Select(
        x => new OrderStatusViewModel
        {
            Date = x.Max(y => y.Created),
            OrderNumber = x.Key,
            Cost = x.LineItems.Sum(k => k.UnitPrice),
            Status = x.Max(y => y.Description)
        }
    );

But, as I said, you should define the navigation properties for entities first.

edited Jan 18 '18 at 10:50

answered Jan 18 '18 at 10:41

lucky

12,734
4
24
46

The entities do have navigation properties, but how does that work with the Include method in this context? The example you describe doesn't even compile. Include only accepts strings. Plus, how does the GoupBy even work with Includes? – NomenNescio Jan 18 '18 at 11:25
Which version are you using ? – lucky Jan 18 '18 at 11:31
The one that's supplied with the `.NET Framework 4.5.2` – NomenNescio Jan 18 '18 at 11:34
You should import "using System.Data.Entity;" then. – lucky Jan 18 '18 at 11:42

Jophy job · Answer 2 · 2018-01-18T10:51:05.240

var orders = db.Orders
.Include("Shipments")
.Include("Shipments.LineItems")
.Include("Shipments.StatusTypes")
.Where(x => x.Order.AccountID == accountId)
.GroupBy(x => x.Order.OrderNumber)
.ToList()
.Select(
    x => new OrderStatusViewModel
    {
        Date = x.Max(y => y.Order.Created),
        OrderNumber = x.Key,
        Cost = x.Sum(y => y.LineItem.UnitPrice).ToString(),
        Status = x.Max(y => y.Description)
    }
);

i not tested above code ,just try

ObjectQuery.Include Method (String)

Simplifying a Linq query by using Include instead of Join

2 Answers2