CODEWITHSUNDEEP
c#linq-to-sql
frenchie
frenchie

Reputation: 51927

linq-to-sql getting sequence contains more than one element

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.

Upvotes: 2

Views: 3096

Answers (3)

Krizz
Krizz

Reputation: 11542

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.

Upvotes: 2

NSGaga
NSGaga

Reputation: 14302

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

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1062502

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.

Upvotes: 1

Related Questions