'int' does not contain a definition for 'ToList'

Question

So I'm trying to run a Linq query that's analogous to the SQL query:

SELECT COUNT(*) 
FROM table 
WHERE ww >= [wwStartSelected] 
    AND ww <= [wwEndSelected] 
    AND manager='[managerName]' 
    AND status='Done'
GROUP BY ww; 

To get a count of the number of rows (tasks) within the given ww range that are marked as done under a particular manager and grouped by ww. I've tried to create a LINQ query that would return something similar (wwStartSelected && wwEndSelected are global vars):

protected List<int> getManagerDoneCount(string managerName)
{
    using (var context = new InfoDBContext())
    {
        List<int> managerDoneCount = context.InfoSet.Where(x => x.ww >= wwStartSelected && x.ww <= wwEndSelected && x.manager == managerName && x.status == "Done").GroupBy(x => x.ww).Count().ToList();

        return managerDoneCount;
    }
}

This query would then feed into a chart:

var testChart = new Chart(width: 600, height: 400)
    .AddTitle("Test")
    .AddSeries(
        name: "Done",
        chartType: "StackedColumn100",
        xValue: new[] { WWList },
        yValues: new[] { getManagerDoneCount("someManager") })

However I'm running into an issue with my Linq line and it says:

'int' does not contain a definition for 'ToList' and no extension method 'ToList' accepting a first argument of type 'int' could be found (are you missing a using directive or an assembly reference?)

Is there way to get fix this easily or do I have to convert to string, then convert back to int for the chart series (the latter of which seems a bit silly [but if so, best way to do so])?

Solution

.Count().ToList()

You're asking it to count the items in the list (which results in a number) and then convert that single number into a list, which makes no sense.

Either return a list and count it later (omit the .Count()) or, change the method to return an int not a List<int> and omit the .ToList()

protected int getManagerDoneCount(string managerName)
{
    using (var context = new InfoDBContext())
    {
        int managerDoneCount = context.InfoSet
                .Where(x => x.ww >= wwStartSelected &&
                            x.ww <= wwEndSelected && 
                            x.manager == managerName &&
                            x.status == "Done")
                .GroupBy(x => x.ww)
                .Count();

        return managerDoneCount;
    }
}

As an aside, to save you writing hundreds of these methods, you can pass the Where clause in as a parameter...

using System.Linq.Expressions;
protected int getManagerCount(string managerName, Expression<Info> predicate)
{
    using (var context = new InfoDBContext())
    {
        int managerDoneCount = context.InfoSet
                .Where(predicate)
                .GroupBy(x => x.ww)
                .Count();

        return managerDoneCount;
    }
}

Then call it like this...

var count = getManagerCount("...", x => x.ww >= wwStartSelected &&
                                        x.ww <= wwEndSelected && 
                                        x.manager == managerName &&
                                        x.status == "Done");

Edit Re: Comments

To return a count of each group, List<int> is a bad idea as you aren't ordering the groups so the counts will be in an undefined order. The ideal solution is to have a class that has an appropriate Key and Count property, but to simplify the example, I'll use a Tuple.

//I'm assuming `ww` is an `int`, change the first type parameter of the tuple as appropriate
List<Tuple<int, int>> counts = context.InfoSet
                .Where(x => x.ww >= wwStartSelected &&
                            x.ww <= wwEndSelected && 
                            x.manager == managerName &&
                            x.status == "Done")
                .GroupBy(x => x.ww)
                .Select(x => new Tuple<int, int>(x.Key, x.Count())).ToList(); 

Note that after you've done the group, the next Select is against the group, which has a Key property for the thing you've grouped on and a lot of aggregate methods for counting, summing, etc..

If you really just want a list of ints, change the last Select to be...

.Select(x => x.Count())

If you weren't passing it out of the method, I'd just use an anonymous class...

.Select(x => new {Ww = x.Key, Count = x.Count()})

But that's no use in a method signature. If you created a CountResult class with Ww and Count properties...

.Select(x => new CountResult{Ww = x.Key, Count = x.Count()})

Edit Re: Comments #2

Linq-To-Entities builds an expression tree which is executed against SQL server, whereas Linq-To-Objects runs in-memory on the client and has more features (as it doesn't need to work out equivalent SQL). In this case, when it gets results from SQL it creates a special proxy class that looks/behaves the same as your entities but handle additional things like tracking which properties have changed. Because of this, you can only use classes which can be constructed (with a parameterless constructor) and then have their properties set (and tracked).

(Although you didn't specify Linq-To-Entities, it's obvious from your question so I should've caught this).

LINQ doesn't deal in lists, but IQueryables, which support lazy evaluation.

Eg If you do...

var a = dbcontext.SomeSet.Where(x => true); //You could omit the Where entirely, just for illustration purposes
var b = a.Where(x => x.Id < 100);
var c = b.ToList();

The query is only executed on the last line and at most 100 records will be returned by the database. a and b are both IQueryable<SomeSet> and "just" contain the expression tree (basically a hierarchy representing the constrains/operations applied so far).

So, to be able to use parameterised constructors / other Linq-To-Object features, we can force the evaluation ...

List<Tuple<int, int>> counts = context.InfoSet
                .Where(x => x.ww >= wwStartSelected &&
                            x.ww <= wwEndSelected && 
                            x.manager == managerName &&
                            x.status == "Done")
                .GroupBy(x => x.ww)
                .ToList() // <<<< Force execution of SQL query
                .Select(x => new Tuple<int, int>(x.Key, x.Count())).ToList(); 

Which should allow you to use constructors, should you wish.

That said, getting a zero count is difficult - the same as it would be getting it from a database (if you group by a field, it doesn't show any 0 counts). There are a number of ways to approach this and which one you use depends on how much data you're playing with. All of them require some way of defining all possible values. I'll use a List<string> as it works well with LINQ

You could, for example get a list of all values and run a different count for each. This is easy but requires multiple queries. If there are lots of groups, it might be expensive...

var groups = new List<string> {"ww1", "ww2", ...};
var counts = groups.Select(g => context.InfoSet.Where(x => x.ww == g && 
                                          x.manager == managerName &&
                                          x.status == "Done").Count());

(This will only return counts, but in the same order as your groups list. As before, you can Select anything you like, including a CountResult...)

var counts = groups.Select(g => new CountResult { 
        Ww = g,
        Count = context.InfoSet.Where(x => x.ww == g && 
                                          x.manager == managerName &&
                                          x.status == "Done").Count();
    });

Alternatively, you can run the query you were doing previously and add the missing groups with a count of 0. This has the benefit of running a single SQL query and letting the database do all the heavy lifting (Ok, handling a few counts isn't too expensive but it's worth bearing in mind for other problems - you don't want to get the whole DB table in memory and do the processing there!).

var groups = new List<string> {"ww1", "ww2", ...};
var previousQuery = ... //(I'll assume a List<CountResult> but tuple would work the same)
var finalList = previousQuery.Concat(
                    groups.Where(g => ! previousQuery.Exists(p => p.Ww == g))
                          .Select(g => new CountResult {Ww=g, Count=0})
                );

In short, take the previous results set, and concatenate (join) it with the result of; (take a list of all groups, remove those already in set 1, for the remainder create a new object with the appropriate ww and a count of 0)

Comments

[Kurt Wagner]

However I want to return a count List (for example return {1, 3, 2}) that's grouped by ww {37, 38, 39}. So 1 row marked as done in ww37, 3 in ww38, etc. Wouldn't it be best to just use your suggested query code in a foreach loop to iterate through the existing WWList, use the WWList values in an additional where clause and append to an int List at the end of each loop and then return that int List?

[Basic]

Ah, ok, I misunderstood. Your example SQL would count the number of groups are you're not selecting any fields. Give me a sec...

[Basic]

NB: Sorry, I missed a .ToList(). Fixed in the last full example

[Kurt Wagner]

Thanks, I didn't need to use Tuples, but the in-depth explanation was really useful given I'm relatively new to Linq! :) Sorry for the SQL confusion. :S Just for code completeness you might want to fix the missing variable at List<Tuple<int, int>> = context.InfoSet, but again immense thanks for the detail.

[Basic]

No worries, glad I could help. It took me a while to get my head around Grouping, mostly because the other (non-fluent) syntax was SQL-Like but very counter-intuitive (at least for me). Once I realised what it was actually doing, it became obvious (and also sold me on the fluent syntax). Yeah, that's a fairly crucial missing variable... Fixed, thanks

[Kurt Wagner]

I had some issues where using int would cause it not to append to the list if there was a count of 0 rather than appending 0, which is not what I want...I want 0's if there's a count of 0. So I switched to using your tuple LINQ, but it's giving the exception: "Only parameterless constructors and initializers are supported in LINQ to Entities". I tried using static variables (38 for starting WW, 39 for ending WW, and "testManager1" for manager) and got the same issue.

[Basic]

Sorry, Busy day... The issue is that LINQ-To-Entities runs directly on the SQL server, LINQ-To-Objects (which supports more) runs in-memory on the client. I'll add some details

[Basic]

Try that, hopefully it should illustrate what you need to do. Apologies but I'm not near an IDE so am writing this all from memory = there may be typos/bugs