Get parent data by querying child data

[Steveo1]

I have two models

Public Class Orders
{
    public int id{ get; set; }
    public string OrderNumber{ get; set; }
    public Int Status{ get; set; }
    public datetime OrderDate{ get; set; }
    public List<LineItem> LineItems { get; set; }
}

Public Class LineItem
{
    public int Id { get; set; }
        public int OrderId { get; set; }
        public int ProductId { get; set; }
        public string Sku { get; set; }
        public string Name { get; set; }
        public int QtyOrdered { get; set; }
        public int QtyLeftToShip { get; set; }
        public int QtyShipped { get; set; }    
        public double Price { get; set; }
        public double Tax { get; set; }
}

So when I use linq to return the data for orders it has a list of Lineitems for each order embedded
I need to be able to return the order information by searching the sku name.
So say I need to see the order information for the Lineitem with the sku radio123

I am not sure how to write the link query to search Lineitems and return a subset of data based on the sku name.

Any Help would be appreciated.

 

[Skydiver]

I don't use EF anymore after several attempts a few years ago but running into bugs and issues when trying anything more complex than just simple toy programs. As I recall, the Order.id on line 3 maps to LineItem.OrderId on line 13 by the conventions used by EF. So armed with that knowledge, you can always find your way back to the parent Order.

 

[Steveo1]

If I was using T-SQL I would use something like this on the two tables. But I don't know how to translate this.

SELECT OrderNumber, Status, Orderdate
FROM [Order]
Where Id in(Select OrderId From [LineItem] WHERE (Sku = 'radio123'))

 

[Skydiver]

var radio123Orders =
_db.Orders.Where(
    order =>
    order.LineItems.Any(
        lineItem =>
        lineItem.Sku == "radio123"
    )
);

 

[Steveo1]

var radio123Orders =
_db.Orders.Where(
    order =>
    order.LineItems.Any(
        lineItem =>
        lineItem.Sku == "radio123"
    )
);

No luck with this. It does not return any data. I can't figure out why this is so hard to do. It seems like it should be really simple

 

[Skydiver]

Are you sure? The LINQ query works for me:

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Xml;

namespace SimpleCS
{
    class Order
    {
        public string OrderNumber { get; set; }
        public List<LineItem> LineItems { get; set; }

        public override string ToString()
        {
            var items = String.Join(",", LineItems);
            return $"OrderNumber = {OrderNumber}, LineItems = {items}";
        }
    }

    class LineItem
    {
        public string Sku { get; set; }

        public override string ToString()
            => $"Sku = {Sku}";
    }

    class Program
    {
        static void Main(string[] args)
        {
            var allOrders = new List<Order>
            {
                new Order
                {
                    OrderNumber = "OrderWithoutRadio",
                    LineItems = new List<LineItem>
                    {
                        new LineItem { Sku = "seatwarmer123" },
                        new LineItem { Sku = "cupholder2" },
                        new LineItem { Sku = "standardAC" },
                    }
                },
                new Order
                {
                    OrderNumber = "OrderWithRadio1",
                    LineItems = new List<LineItem>
                    {
                        new LineItem { Sku = "seatwarmer123" },
                        new LineItem { Sku = "radio123" },
                        new LineItem { Sku = "cupholder1" },
                    }
                },
                new Order
                {
                    OrderNumber = "OrderWithRadio2",
                    LineItems = new List<LineItem>
                    {
                        new LineItem { Sku = "seatwarmer456" },
                        new LineItem { Sku = "enhancedAC" },
                        new LineItem { Sku = "radio123" },
                    }
                },
            };
            var radioOrders = allOrders.Where(order => order.LineItems.Any(li => li.Sku == "radio123"));

            ShowOrders("allOrders:", allOrders);
            Console.WriteLine();
            ShowOrders("radioOrders:", radioOrders);

            void ShowOrders(string caption, IEnumerable<Order> orders)
            {
                Console.WriteLine(caption);
                foreach (var order in orders)
                    Console.WriteLine(order);
            }
        }
    }
}

Producing the following output:

allOrders:
OrderNumber = OrderWithoutRadio, LineItems = Sku = seatwarmer123,Sku = cupholder2,Sku = standardAC
OrderNumber = OrderWithRadio1, LineItems = Sku = seatwarmer123,Sku = radio123,Sku = cupholder1
OrderNumber = OrderWithRadio2, LineItems = Sku = seatwarmer456,Sku = enhancedAC,Sku = radio123

radioOrders:
OrderNumber = OrderWithRadio1, LineItems = Sku = seatwarmer123,Sku = radio123,Sku = cupholder1
OrderNumber = OrderWithRadio2, LineItems = Sku = seatwarmer456,Sku = enhancedAC,Sku = radio123

 

[Steveo1]

Are you sure? The LINQ query works for me:

using System;
using System.Collections.Generic;
using System.Data.Common;
using System.Linq;
using System.Xml;

namespace SimpleCS
{
    class Order
    {
        public string OrderNumber { get; set; }
        public List<LineItem> LineItems { get; set; }

        public override string ToString()
        {
            var items = String.Join(",", LineItems);
            return $"OrderNumber = {OrderNumber}, LineItems = {items}";
        }
    }

    class LineItem
    {
        public string Sku { get; set; }

        public override string ToString()
            => $"Sku = {Sku}";
    }

    class Program
    {
        static void Main(string[] args)
        {
            var allOrders = new List<Order>
            {
                new Order
                {
                    OrderNumber = "OrderWithoutRadio",
                    LineItems = new List<LineItem>
                    {
                        new LineItem { Sku = "seatwarmer123" },
                        new LineItem { Sku = "cupholder2" },
                        new LineItem { Sku = "standardAC" },
                    }
                },
                new Order
                {
                    OrderNumber = "OrderWithRadio1",
                    LineItems = new List<LineItem>
                    {
                        new LineItem { Sku = "seatwarmer123" },
                        new LineItem { Sku = "radio123" },
                        new LineItem { Sku = "cupholder1" },
                    }
                },
                new Order
                {
                    OrderNumber = "OrderWithRadio2",
                    LineItems = new List<LineItem>
                    {
                        new LineItem { Sku = "seatwarmer456" },
                        new LineItem { Sku = "enhancedAC" },
                        new LineItem { Sku = "radio123" },
                    }
                },
            };
            var radioOrders = allOrders.Where(order => order.LineItems.Any(li => li.Sku == "radio123"));

            ShowOrders("allOrders:", allOrders);
            Console.WriteLine();
            ShowOrders("radioOrders:", radioOrders);

            void ShowOrders(string caption, IEnumerable<Order> orders)
            {
                Console.WriteLine(caption);
                foreach (var order in orders)
                    Console.WriteLine(order);
            }
        }
    }
}

Producing the following output:

allOrders:
OrderNumber = OrderWithoutRadio, LineItems = Sku = seatwarmer123,Sku = cupholder2,Sku = standardAC
OrderNumber = OrderWithRadio1, LineItems = Sku = seatwarmer123,Sku = radio123,Sku = cupholder1
OrderNumber = OrderWithRadio2, LineItems = Sku = seatwarmer456,Sku = enhancedAC,Sku = radio123

radioOrders:
OrderNumber = OrderWithRadio1, LineItems = Sku = seatwarmer123,Sku = radio123,Sku = cupholder1
OrderNumber = OrderWithRadio2, LineItems = Sku = seatwarmer456,Sku = enhancedAC,Sku = radio123

Works great thanks!

 

[Skydiver]

Line 66 is essentially post #4.