CODEWITHSUNDEEP
c#winformslinqentity-frameworklinq-to-entities
rockyashkumar
rockyashkumar

Reputation: 1332

not able to get the number of products count

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(),

Upvotes: 1

Views: 262

Answers (2)

CodingWithSpike
CodingWithSpike

Reputation: 43748

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.

Upvotes: 0

Davide Piras
Davide Piras

Reputation: 44595

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)

Upvotes: 2

Related Questions