How can I get the count in linq?

Question

I have table called products with columns:

productid ,
productname,
productprice
categoryid

My problem is I want to get the number of products depending on product name along with details. I want to show the data in DataGridView. How can I know the number of products for a single product name like below?

productid        productname          productavailable        productprice
--------------------------------------------------------------------------
1                product A            2 products(product A)   100
2                product B            5 Products(product B)   200

Like the above table I have to display in DataGridView. I am using LINQ and C# and my DbContext name is tsgdbcontext.

Are you really using entity-framework and linq-to-sql at the same time? They are two different frameworks from MS. — xanatos, Sep 10 '11 at 15:46
Question is a little confusing, is "productavailable" a column in your products table? Is it a count? — marr75, Sep 10 '11 at 15:47
@marr75 productavaialable not a column in database but it is column in datagrid view for finding the number of products...... — Glory Raj, Sep 10 '11 at 15:49
He can use linq and entity framework at the same time... LINQ != LINQ to SQL. LINQ is just the set of extensions to the framework. — marr75, Sep 10 '11 at 15:49
@xanatos I am sorry I am using entity framework unexpectedly i have tagged linq-to-sql ... — Glory Raj, Sep 10 '11 at 15:49
Seems like you need to add a column that tracks the count of products available to the database then. I don't know how we would tell you where your own data is. — marr75, Sep 10 '11 at 15:49
@xanatos Oh I see, you meant the tags, not the question. Good find. — marr75, Sep 10 '11 at 15:50
@marr75 my problem is how can i find the number of products depends upon the product name and product id ...how can i calculate using entities... — Glory Raj, Sep 10 '11 at 15:52

score 41 · Accepted Answer · edited Sep 10 '11 at 16:14

41

Use GroupBy with a key that contains your grouping properties. Then select out the key properties along with the count of each from the grouping.

var query = tsgdbcontext.Products
                        .GroupBy(p => new {
                            p.ProductId,
                            p.ProductName,
                            p.ProductPrice
                         })
                        .Select(g => new {
                            g.Key.ProductId,
                            g.Key.ProductName,
                            g.Key.ProductPrice,
                            Available = g.Count()
                        });

edited Sep 10 '11 at 16:14

Yuck

49,664
13
105
135

answered Sep 10 '11 at 15:53

tvanfosson

524,688
99
697
795

one request would you pls take a look at this question , i have tried but dint get any idea how to do this... http://stackoverflow.com/questions/7370839/how-can-i-get-the-membership-sold-count – Glory Raj Sep 10 '11 at 16:01

blins · Answer 2 · 2011-09-10T16:10:08.807

30

Not sure I am understanding exactly + making some assumptions but here is an example linq query that produces a count based on some arbitrary selection criteria (id=2 and price greater than 100)...

int count = (from p in tsgdbcontext.Products
             where p.productid == 2 && p.productprice > 100
             select p).Count();

edited Sep 10 '11 at 16:10

answered Sep 10 '11 at 16:01

blins

2,515
21
32

1

Doesn't this retrieve the whole dataset from the DB as apposed to a dedicated COUNT expression that only gets the number of elements from the DB? – Oszkar Sep 09 '15 at 07:18
@Oszkar No, it doesn't. `COUNT(1)` will be added to the query sent to the DB. – Kautsky Lozano May 18 '16 at 10:14
What if no Products are available, does this return 0 or does it throw a null exception? – Hp93 Mar 22 '18 at 15:58
Presuming that `Products` itself is not null then no (empty is fine). If `Products` contains null values then it would throw inside the `where` when p is null and `p.productid` is accessed. – blins Mar 23 '18 at 00:56
Yes, I've just tested, it returns 0. But `.Sum()` of empty list throws exception. – Hp93 Mar 23 '18 at 03:52

How can I get the count in linq?

2 Answers2

Linked

Related