not able to get the number of products count

Question

I have product table with columns

 product_id (p.k)
 product_name
 product_description
 product_price
 category_id

and I have set the properties for product_id like this...(primary key , not null,A.I)

I am trying to represent the number of same products in datagrid view as a column like this

product_name     product_description         stock available        product price

    a                good product                2  (a+a)                  300

    b                bad product                 3   (b+b+b)                  400

by the following method....

var stockavailable = dbcontext.products
   .GroupBy(x => x.product_Id)
   .Select(a => new
                {
                    productid = a.Key,
                    productnam = a.FirstOrDefault().product_Name,
                    productdescr = a.FirstOrDefault().product_Description,
                    stockavailable = a.LongCount(),
                    productprice = a.FirstOrDefault().product_Price
                });

bindingsource.DataSource = stockavailable;
datagridview1.DataSource = bindingsource;

But it does not show the number of products even if there are two same products. with different product_id 's would any one pls help on this...

EDIT :
Can i do like this.....

  stockavailable = a.select(x=>x.product_id).Distinct().Count() 
    Instead of this stockavailable = a.LongCount(),

would any one pls help me on this... – rockyashkumar Sep 03 '11 at 11:07 — rockyashkumar, Sep 03 '11 at 11:07

score 2 · Answer 1 · edited May 23 '17 at 11:47

2

what you are missing is to do a count on number of rows with same product_id

In SQL after you do a group by you can use aggregate functions like sum, count, max, min and so on, in your case you do a group by but forgot to insert a count.

see here this very similar question for syntax on how to use the count in LINQ:

LINQ to SQL using GROUP BY and COUNT(DISTINCT)

edited May 23 '17 at 11:47

Community

1
1

answered Sep 03 '11 at 10:39

Davide Piras

43,984
10
98
147

score 0 · Accepted Answer · answered Sep 03 '11 at 12:28

0

If product_id is the primary key, then wouldn't it be distinct for every entry? No 2 rows would have the same product_id, right? If that is true, then GroupBy(x => x.product_id) won't do any real grouping. Every row will be its own group.

I think what you want is something closer to:

var stockavailable = dbcontext.products
   .GroupBy(x => new {x.product_Name, x.product_Description, x.product_Price })
   .Select(a => new
                {
                    productnam = a.Key.product_Name,
                    productdescr = a.Key.product_Description,
                    stockavailable = a.LongCount(),
                    productprice = a.Key().product_Price
                });

Also, wouldn't it have been easier to just add a product_Quantity column? The way your table is set up now, couldn't you have:

id  |  name  |  desc  |  price
1      a        good     350
2      a        good     300
3      b        bad      400

So there are 2 of the same product with different prices.

answered Sep 03 '11 at 12:28

CodingWithSpike

42,906
18
101
138

Many thanks.. but this is not what i am asking ..... i need to represent the datagrid view with products avaialable (stock available).....number of products who have with same Name.... like i have mentioned sample datagrid view in my question.... – rockyashkumar Sep 03 '11 at 12:35
I don't think anyone knows what it is you actually want then. Can you express what you want as a regular SQL query? If you want things grouped by the same product_name, then just change the grouping I provided to `.GroupBy(x.product_name)` instead. – CodingWithSpike Sep 03 '11 at 16:05
I have already specified in my question(sample datagrid view) i want exactly same like that.... – rockyashkumar Sep 03 '11 at 16:13
Which is exactly (i think) what I gave you... you should be grouping by `Product_Name` not `Product_Id`. I don't understand how my and Davide's answers aren't working for you. What DO you get when you try either of our solutions, and how is that different from what you wanted? – CodingWithSpike Sep 04 '11 at 00:29

not able to get the number of products count

2 Answers2