How to retrieve value using LINQ in MVC

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

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 me 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.

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.

Hi Jignesh, Thank you for your reply
I have some doubts here,till 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 2,3 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

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.