Linq-to-sql join/where?

Question

I've the following table structures

Users id

Types id isBool

UsersTypes userid types

I want to select all the UserTypes based on id and isBool.

I tried this query

var q = from usertype in usertypes
        from type in types
        where type.isBool == false
        where userstypes.user == id
        select usertype;

But this did not work as expected. My questions are:

1. Why?
2. Is there any difference in using the join on syntax vs where, where vs where cond1 && cond2? My understanding is query optimizer will optimize.
3. Is there any difference in using where cond1 == var1 && cond2 == var2 with and without the parenthesis? This seems peculiar that it is possible to build this without parenthesis
4. What type of query do I need in this case? I can see that I could do a subquery or use a group but not 100% sure if it is required. An example might be helpful. I'm thinking a subquery may be required in this case.

Answer 1

Your query doesn't join those two tables on any common field:

var q = from u in usertypes
        join t in types on u.typeid equals t.id
        where t.isBool == false && usertypes.user == id
        select u;

There are differences between join and where clauses, depending on how they're used. Either way, using a join is preferred because LINQ-to-SQL will generate an inner join rather than a hash cross join (and then filtering based on the where clause).

You don't need the parenthesis. You can include them though since they do help readability in some cases.

Answer 2

var q = from usertype in usertypes 
        from type in types
        where type.isBool == false 
        where usertype.user == id 
        where usertype.typeid = type.id //join criteria
        select usertype;