SQL query to linq (linq to entities)

Question

I'm new in c# and linq, i need to make query in link to entities. for starters, I managed to write the entire query in SQL, and now i didn't sucsses to create this query in linq.

SELECT s.store_name, p.product_name, p.product_id, p.list_price, SUB1.quantity_orders, st.quantity
FROM(
        SELECT store_id, product_id, SUM(oi.quantity) quantity_orders
        FROM orders as o inner join order_items as oi on o.order_id = oi.order_id
        WHERE o.order_status != 4 and o.order_status != 3
        GROUP BY store_id, product_id) AS SUB1
            join stores s on s.store_id = SUB1.store_id
            join products p on p.product_id = SUB1.product_id
            join stocks st on st.store_id = SUB1.store_id and st.product_id = SUB1.product_id
WHERE SUB1.quantity_orders > 
(select s.quantity
from stocks as s
where s.store_id = SUB1.store_id and s.product_id = SUB1.product_id)

Thanks.

Answer 1

I simulated with classes to get syntax correct.

    class Program
    {
        static void Main(string[] args)
        {
            Context context = new Context();

            var orderQuery = (from o in context.orders.Where(x => (x.order_status != 4) && (x.order_status != 3))
                         join oi in context.order_items on o.order_id equals oi.order_id
                         select new { order = o, order_id = oi }
                         ).ToList();

            var groups = orderQuery.GroupBy(x => new { product_id = x.order.product_id, store_id = x.order.store_id }).ToList();

            var results = groups.SelectMany(x => context.stock.Where(y => (x.Key.product_id == y.product_id) && (x.Key.store_id == y.store_id) && (x.Sum(z => z.order_id.quantity) > y.quantity)) 
                .Select(y => new { 
                    store_id = x.Key.store_id, 
                    product_id = x.Key.product_id, 
                    order_quantity = x.Sum(z => z.order_id.quantity), 
                    store_quantity = y.quantity,
                    product = context.products.Where(z => z.product_id == x.Key.product_id).FirstOrDefault()
                }).Select(y => new {
                    store_id = y.store_id, 
                    product_id = y.product_id, 
                    order_quantity = y.order_quantity, 
                    store_quantity = y.store_quantity,
                    product_name = y.product.name,
                    price = y.product.list_price
                })).ToList();
        }
    }
    public class Context
    {
        public List<Stock> stock { get; set; }
        public List<Order> orders { get; set; }
        public List<Product> products { get; set; }
        public List<Order_Items> order_items { get; set; }
        public List<Store> stores { get; set; }
    }
    public class Stock
    {
        public int store_id { get; set; }
        public int product_id { get; set; }
        public int quantity { get;  set; }
    }
    public class Order
    {
        public int order_status { get; set; }
        public int store_id { get;set ;}
        public int order_id { get; set; }
        public int product_id { get; set; }
    }
    public class Order_Items
    {
        public int order_id { get; set; }
        public int quantity { get; set; }
    }
    public class Product
    {
        public int product_id { get; set; }
        public string name { get; set; }
        public decimal list_price { get; set; }
    }
    public class Store
    {
        public int store_id { get; set; }
        public string store_name { get; set; }
    }

---

Here is second version :

   class Program
    {
        static void Main(string[] args)
        {
            Context context = new Context();

            var orderQuery = (from o in context.orders.Where(x => (x.order_status != 4) && (x.order_status != 3))
                         join oi in context.order_items on o.order_id equals oi.order_id
                         select new { order = o, order_id = oi }
                         ).ToList();

            var groups = orderQuery.GroupBy(x => new { product_id = x.order.product_id, store_id = x.order.store_id }).ToList();

            var productStock = (from s in context.stock
                                join p in context.products on s.product_id equals p.product_id
                                select new { store_id = s.store_id, product_id = s.product_id, quantity = s.quantity, name = p.name, price = p.list_price }
                               ).ToList();

            var results = groups.SelectMany(x => productStock.Where(y => (x.Key.product_id == y.product_id) && (x.Key.store_id == y.store_id) && (x.Sum(z => z.order_id.quantity) > y.quantity))
                .Select(y => new {
                    store_id = x.Key.store_id, 
                    product_id = x.Key.product_id, 
                    order_quantity = x.Sum(z => z.order_id.quantity), 
                    store_quantity = y.quantity,
                    product_name = y.name,
                    price = y.price
                }).ToList()).ToList();
        }
    }
    public class Context
    {
        public List<Stock> stock { get; set; }
        public List<Order> orders { get; set; }
        public List<Product> products { get; set; }
        public List<Order_Items> order_items { get; set; }
        public List<Store> stores { get; set; }
    }
    public class Stock
    {
        public int store_id { get; set; }
        public int product_id { get; set; }
        public int quantity { get;  set; }
    }
    public class Order
    {
        public int order_status { get; set; }
        public int store_id { get;set ;}
        public int order_id { get; set; }
        public int product_id { get; set; }
    }
    public class Order_Items
    {
        public int order_id { get; set; }
        public int quantity { get; set; }
    }
    public class Product
    {
        public int product_id { get; set; }
        public string name { get; set; }
        public decimal list_price { get; set; }
    }
    public class Store
    {
        public int store_id { get; set; }
        public string store_name { get; set; }
    }