Get parent data by querying child data

Steveo1

New member
Joined
Nov 5, 2021
Messages
4
Programming Experience
3-5
I have two models
Order Model:
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.
 
Last edited:
Solution
Are you sure? The LINQ query works for me:
C#:
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 =...

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
4,029
Location
Chesapeake, VA
Programming Experience
10+
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

New member
Joined
Nov 5, 2021
Messages
4
Programming Experience
3-5
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.

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

Skydiver

Staff member
Joined
Apr 6, 2019
Messages
4,029
Location
Chesapeake, VA
Programming Experience
10+
C#:
var radio123Orders =
_db.Orders.Where(
    order =>
    order.LineItems.Any(
        lineItem =>
        lineItem.Sku == "radio123"
    )
);
 

Steveo1

New member
Joined
Nov 5, 2021
Messages
4
Programming Experience
3-5
C#:
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

Staff member
Joined
Apr 6, 2019
Messages
4,029
Location
Chesapeake, VA
Programming Experience
10+
Are you sure? The LINQ query works for me:
C#:
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:
Markdown (GitHub flavored):
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
 
Solution

Steveo1

New member
Joined
Nov 5, 2021
Messages
4
Programming Experience
3-5
Are you sure? The LINQ query works for me:
C#:
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:
Markdown (GitHub flavored):
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

Staff member
Joined
Apr 6, 2019
Messages
4,029
Location
Chesapeake, VA
Programming Experience
10+
Line 66 is essentially post #4.
 
Top Bottom