.NET Linq to SQL: select average with where-clause

Question

Hi,

I'have a table with movies, which has an integer attribute called ranking. I'd now like to retrieve the average ranking from all movies containing a certain actor.

Dim q2 = (From p In dc.Movies Where p.actlist.Contains(actor.name) Select p.ranking).Average()

This query doesn't work - Overload resolution failed because no accessible "Average" accepts this number of arguments. Without the Where-Clause it runs fine.

How to combine the Average function with Where-clause? I couldn't find any helpful example on MSDN and the internet ..

Answer 1

Possibly no results so it can give no average; try

Dim q2 = (From p In dc.Movies Where p.actlist.Contains(actor.name) Select p.ranking).Count() == 0 ? 0 : (From p In dc.Movies Where p.actlist.Contains(actor.name) Select p.ranking).Average();

Answer 2

Can you try the following code:

Dim avgRanking = (From m in dc.Movies _
                  Where m.actlist.Contains(actor.Name) _
                  Select m.ranking).Average(Function(a) Convert.ToInt32(a))

And this should also work:

Dim avgRanking = (From m in dc.Movies _
                  Where m.actlist.Contains(actor.Name) _
                  Select CInt(m.ranking)).Average()

The problem is the type of Movie.ranking. The result of your query is and IEnumerable of tinyint. There is no overload of Average for this.

Answer 3

I've figured out by myself:

Dim q3 = (From a In dc.Movies Where a.actlist.Contains(actor.name) Select a).Average(Function(r) r.ranking)

But this one throws an execption if an actor isn't in any movie. So I had to put it in try..cast.