Nested .Select() inside of .Where()

Question

I have a many-to-many relationship between tables of Games and Genres. During an analysis, i need to get items from Games that match specific criteria.

The problem is, to check for this criteria, i need to analyse genres of this specific game. And linq won't let me do it.

My request now looks like this:

var result = GDB.Games.Where((g)=>
  g.GamesToGenres.Select((gtg)=>
    (weights.ContainsKey(gtg.Genre.Name) ? weights[gtg.Genre.Name]:0.0)
  ).Sum() > Threshhold
).ToArray();

When I execute it, I receive SQL exception

Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Is there a workaround? How can i perform such Select inside of Where?

EDIT: weights is a Dictionary<string, double>.

EDIT: I was playing with lambdas, and found out a strange thing in their behaviour: this code won't work, throwing nvarchar to float conversion exception:

Func<string, double> getW = (name) => 1;
var t = GDB.Games.Where((g)=>
  g.GamesToGenres.Select((gtg)=>
    getW(gtg.Genre.Name)
  ).Sum() > Threshhold
).ToArray();

but this one will work nicely:

var t = GDB.Games.Where((g)=>
  g.GamesToGenres.Select((gtg)=>
    1
  ).Sum() > Threshhold
).ToArray();

This leads me to conclusion that linq lambdas are not usual lambdas. What's wrong with them, then? What are their limitations? What i can and what i can't do inside of them? Why is it ok for me to place a .select call inside of lambda, but not my own call of getW?

RESOLVED. See the answer below. Long story short, C# can't into clojures unless explicitly told so. If anyone knows better answer, i am still confused.

Answer 1

Your problem is you're trying to select something form the dictionary weights that exists in your application and not in your DB. If it was the result of a query to your DB, use the query.Single(...) in its place