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])?
.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");
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()})
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)