How to retrieve value using LINQ in MVC

Question

Hii Please Any Help?? I have two tables where one is having some ids(primary key) and in the second table each of these id can have zero or more records. And each of these records contains some price and quantity values .I want to show the IDs in the first table and the sum of product of price and quantity in second table for the corresponding records in first table in my view in my MVC application. Ie. in my view I want to show id and its value (sum of product of all the records value in second table ).How two code the linq query for this in mvc?? Please help....

Jignesh Trivedi · Answer

hi sorry it my mistake Instead of grp you must use final var query = from t0 in db.POCMasters join grp in (from t1 in db.POCMasters join t2 in db.POCLists on t1.POCId equals t2.POCId into temp from t3 in temp.DefaultIfEmpty() group t1 by t1.POCId into groups select new { POCId = groups.Key totalquantity = groups.Sum(x => x.POCList.Quantity) //totalprice = groups.Sum(x => x.POCList.Price) }) on t0.POCId equals grp.POCId into dummy from final in dummy.DefaultIfEmpty() select new POCGenerateViewModel { POCId = t0.POCId Reordervalue = t0.POCList.Quantity price = t0.POCList.Price totalprice = final.totalprice totalqty = final.totalquantity }; hope this will help you.

Jaima Joseph · Answer

Hi Jignesh Thank you...........thank you so much.............you helped me a lot and it is working fine

Jaima Joseph · Answer

Hi Jignesh I tried your code and I am getting some error at the last two statements ie totalprice=grp.tatalprice totalqty = grp.totalquantity the error showing is 'grp does not exist in the current context'. Thank you very much for your help... I tried this and it is showing null value Here is my controller code where I used your logic var query = from t0 in db.POCMasters join grp in (from t1 in db.POCMasters join t2 in db.POCLists on t1.POCId equals t2.POCId into temp from t3 in temp.DefaultIfEmpty() group t1 by t1.POCId into groups select new { POCId = groups.Key totalquantity = groups.Sum(x => x.POCList.Quantity) //totalprice = groups.Sum(x => x.POCList.Price) }) on t0.POCId equals grp.POCId into dummy from final in dummy.DefaultIfEmpty() select new POCGenerateViewModel { POCId = t0.POCId Reordervalue = t0.POCList.Quantity //price = t0.POCList.Price //totalprice = grp.totalprice //totalqty = grp.totalquantity }; return View(query); Please check and tell m e where I am doing mistake please.. Actually right now I am not considering Price because there is an issue in its data type as I store it as a string data and that's why right now I am not able to do the Price * Quantity operation. Still I am trying to show atleast the sum of Quantities for each Id in my view. Please help if possible. Thanks in advance.

Jignesh Trivedi · Answer

hi not sure whether the for loop in side the LINQ query works or not... you can join two queries in to one like var query = from t0 in context.table1 join grp in ( from t1 in context.table1 join t2 in context.table2 on t1.id equals t2.id into temp from t3 in temp.DefaultIfEmpty() group t1 by t1.id into groups select new BestSeller { Id = g.Key totalquantity = g.Sum(x => x.QTY)  totalprice = g.Sum(x => x.price)  }) on t0.id equals grp.id into dummy from final in dummy.DefaultIfEmpty() select new { id = t0.id qty = t0.qty price = to.price totalprice = grp.totalprice totalqty = grp.totalquantity }; hope this will help you.

Jaima Joseph · Answer

Hi Jignesh Thank you for your reply I have some doubts heretill the grouping its clear for me Below given set of code is not clear select new BestSeller { Id = g.Key totalquantity = g.Sum(x => x.QTY)  totalprice = g.Sum(x => x.price)  }; you are taking the sum of price and quantity separately But I want to find the product of Price and quantity first for each row and then find the sum of these rows and send that sum to view along with its corresponding ID Can I use a foreach loop inside a LINQ query? Again each row in ie id in first table will be having 23 records at least and each of these records in second table is having columns price and quantity and I have to find the product in each row and the sum of these products in all the rows having that particular id and show the id and its corresponding sum of product value in my view

Jignesh Trivedi · Answer

Hi Create custom model for showing product. It may containing three properties Id and sum of price sum of the quentity. Create view with strongly type model which is create using above steps. In controller Write linq group by query to retreive the record. like var query = from t1 in context.table1 join t2 in context.table2 on t1.id equals t2.id into temp from t3 in temp.DefaultIfEmpty() group t1 by t1.id into groups select new BestSeller { Id = g.Key totalquantity = g.Sum(x => x.QTY)  totalprice = g.Sum(x => x.price)  }; assign the result of the query to view's model. let me know if you any quety on above steps. hope this will help you.