Jaima Joseph

Jaima Joseph

  • NA
  • 94
  • 108k

How to retrieve value using LINQ in MVC

Mar 26 2014 6:03 AM

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

Answers (6)

0
Jignesh Trivedi

Jignesh Trivedi

  • 0
  • 62.3k
  • 46m
Mar 27 2014 5:31 AM

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.

Accepted Answer
1
Jaima Joseph

Jaima Joseph

  • 0
  • 94
  • 108k
Mar 27 2014 7:04 AM


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


0
Jaima Joseph

Jaima Joseph

  • 0
  • 94
  • 108k
Mar 27 2014 5:20 AM


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.

0
Jignesh Trivedi

Jignesh Trivedi

  • 0
  • 62.3k
  • 46m
Mar 27 2014 2:33 AM
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.

0
Jaima Joseph

Jaima Joseph

  • 0
  • 94
  • 108k
Mar 27 2014 1:46 AM


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

0
Jignesh Trivedi

Jignesh Trivedi

  • 0
  • 62.3k
  • 46m
Mar 26 2014 7:32 AM

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.