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

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 开发者_Python百科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 ..

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();

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.

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.