2

I have a query that looks like this: it takes a list of IDs (ThelistOfIDs) as parameter and I'm grouping for a count.

var TheCounter = (from l in MyDC.SomeTable
                  where ThelistOfIDs.Contains(l.ID)
                  group l by l.Status into groups
                  select new Counter()
                  {
                      CountOnes = (from g in groups
                                   where g.Status == 1
                                   select g).Count(),

                      CountTwos = (from g in groups
                                   where g.Status == 2
                                   select g).Count(),
                  }).Single();

And basically, I don't understand why I'm getting the error. I don't want to brring back the entore collection from the DB and do the count in linq-to-object; I want to do the count in the DB and bring back the result.

Improve this question
5
  • @Krizz: "sequence contains more than one element" Commented Apr 8, 2012 at 14:33
  • How many groups do you expect? The query looks to return one element per Status Commented Apr 8, 2012 at 14:36
  • I'm grouping the sequence by Status in the variable groups and I'm loading the results in one object model. There is only one Counter. Commented Apr 8, 2012 at 14:38
  • @frenchi no, that query says one Counter per group - but re-using "groups" internally. Commented Apr 8, 2012 at 14:39
  • @MarcGravell: Ok, I want one Counter that holds the count of each group Commented Apr 8, 2012 at 14:40

3 Answers 3

Reset to default
2

I have not put your query into my IDE or compiled with C#, but I guess the problem is that groups in your query is IGrouping<Tkey, Telm> and not IQueryable<Tkey> (where Tkey is type of l.Status and Telm is type of l).

I think you got confused with the use of grouping operator.

What you want to get is I guess:

var queryByStatus = from l in MyDC.SomeTable
                    where ThelistOfIDs.Contains(l.ID)
                    group l by l.Status;

var counter = new Counter() 
              {
                  CountOnes = queryByStatus.Where(l => l.Key == 1).Count(),
                  CountTwos = queryByStatus.Where(l => l.Key == 2).Count(),
              };

EDIT:

Alternative query, to obtain the same, moving all operation on DB into the original query so that DB is queried only once.

var queryCountByStatus = from l in MyDC.SomeTable
                    where ThelistOfIDs.Contains(l.ID)
                    group l by l.Status into r
                    select new { status = r.Key, count = r.Count() };

 var countByStatus = queryCountByStatus.ToList();

 var counter = new Counter() 
               {
                    CountOnes = countByStatus.FirstOrDefault(l => l.status == 1).count,
                    CountTwos = countByStatus.FirstOrDefault(l => l.status == 2).count,
               };

Note:

The query in my edit section queries the DB once only and mapping Status -> Count is returned.

Note that in my original query there were two calls to DB needed only - both of which returned single number - one for CountOnes, one for CountTwos.

In the edit query, one query is done which return table { { 1, CountOnes}, {2, CountTwos } }. The other lines are just to convert the result - which is set of items - into single object having certain objects as properties and is done physically on these two values.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

@frenchie - why would you want that?
Because I want to do the counting in the DB and return just the count, not the whole collection.
@frenchie, take a look at my edit. The whole collection is not returned. Remember - query is executed when it's started to enumerate, not when you type ; after the query's "statement" as you called it.
Ok, the query you're suggesting returns the count in an anonymous type. Why can't I write the query without the anonymous type and go straight into my object model?
There's a problem: sometimes there are no elements with a certain status. So "countByStatus.Single(l => l.status == 1)" fails if there are no elements with status == 1.
|
1

You are grouping by Status, and then projecting from that group - but you will still have one row per unique Status (===group).

So: I propose that you don't have exactly one unique Status.

Improve this answer

6 Comments

right, all the IDs have 2 statuses, either status == 1 or status == 2
@frenchie yes, but your starting point is still from the grouping - so 2 rows. For each of those you then perform some calculations over the "groups" data.
@frenchi I'd ToList the groups and post-process it from there
But that would mean that I'd have to bring back the entire collection from the DB. I'm looking to do the counting in the DB and return just the counts.
@frenchie then select just new {grp.Key, Count= grp.Count() } and ToList that. Or write the TSQL you want , and use ExecuteQuery
|
0

This might be what you're looking for to get...
(it's for users table I had but should be the same) 

var statuscounts = (from u in db.Users
                    where u.UserStatus > 0
                    group u by u.UserStatus into groups
                    select new { Status = groups.Key, Count = groups.Count() });

// do this to iterate and pump into a Counter at will
foreach (var g in statuscounts)
    Console.WriteLine("{0}, {1}", g.Status, g.Count);

...or even something like this...

var counter = statuscounts.AsEnumerable()
                    .Aggregate(new Counter(), (c, a) => {
                            switch (a.Status)
                            {
                                case 1: c.CountOfOnes = a.Count; return c;
                                case 2: c.CountOfTwos = a.Count; return c;
                                case 3: c.CountOfThrees = a.Count; return c;
                                default: c.CountOfOthers = a.Count; return c;
                            }});

...point is that if you're grouping already you should use the grouping result, it's of type IGrouping<out TKey, out TElement> where the key is your status and it's IEnumerable<> or your records.

hope this helps

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.