none
Product and Stocks LINQ query help RRS feed

  • Question

  • I'm using EF6 in my project.

    I have to entities, Product, and Stocks.

    Product entity contains the product that we manage, and Stocks contains the number of products (stocks) that each our branches managed.

    I need a LINQ query to join the two entities and calculate (sum) the total quantity (property) stock of each product.

    Thanks,

    Benjie


    Benjamin Fallar III

    Wednesday, June 28, 2017 4:36 PM

All replies

  • I'll assume that you know how to use T-TSQL

    http://www.dotnettricks.com/learn/linq/sql-joins-with-csharp-linq

    You can play and figure it out.

    https://www.linqpad.net/

    You are talking Linq 101 stuff here that you should be able to figure out on your own, like the Sum() aggregate and maybe get a good book on Linq.

    https://code.msdn.microsoft.com/101-LINQ-Samples-3fb9811b

    Wednesday, June 28, 2017 7:17 PM
  • I decided to use T-SQL and CTE for my query.

    Benjamin Fallar III

    Thursday, June 29, 2017 3:53 AM
  • Hi Benjamin,

    Based on your description, it seems that you want to use inner join with group by, please refer to the following LINQ if you use entity framework.

      using (var context = new YourDbContext())
                {
    
                    var total = from p in context.Products
                                join s in context.Stocks on p.ProductId equals s.ProductId
                                group s by new { p.ProductName, p.ProductPrice } into g
                                select new
                                {
                                    ProductName = p.ProductName,
                                    ProductPrice = p.ProductPrice,
                                    Amount = g.Sum(s => s.Quantity)
                                };
    
                    //var query = db.Offices.ToList().Where(t => regEx.IsMatch(t.Agency));
    
                    
                }

    Best regards,

    Cole Wu


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 30, 2017 5:25 AM
    Moderator