Weird problem with my Linq to Sql query and manually adding some JOIN statements.

Question

Hi folks,

my Linq To Sql model contains some entites that cause the generated Sql code to be LEFT OUTER JOINS. this is correct, because of how the tables are modelled.

BUT, for a particular query, I actually know the results will always exist in the child table. As such, I was hoping to change the SQL from a LEFT OUTER JOIN to an INNER JOIN.

Ok. so i thought i might MANUALLY specify the joins. eg.

from q in db.Foo
join a in db.Bar on q.Id equals a.Id
select q

Now, when I check out the sql statemement the Linq generates, it contains BOTH the INNER JOIN (which i made, above) and the LEFT OUTER JOIN!

How can i override the default join behavior so only what I define in my linq statement, is what is used/goes/is the law?

cheers :)

Answer 1

With the sample query you have posted above it should not do any joins besides a single join between Foo and Bar.

Are you by any chance doing something along the lines of:

from q in db.Foo
join a in db.Bar on q.Id equals a.Id
select new { q.SomeField, q.Bar.SomeOtherField }

If so, change that to:

from q in db.Foo
join a in db.Bar on q.Id equals a.Id
select new { q.SomeField, a.SomeOtherField }