How should I correctly compose my Linq query where the condition is set to a foreign entity

Question

The following are sample data for the purpose of explaining this matter:

tblPrimary
1, John, 2011-01-03T11:12:00z
2, John, 2011-02-04T10:54:12z

tblDetail:
1, 1, 14, Dept01
2, 1, 12, Dept02
3, 2, 10, Dept03
4, 2, 17, Dept04

Using the above example table, am given a “Name” where I need to return every detail record associated with the name in tblPrimary.Name. The tblPrimary.Name is not unique so there will be duplicates but they are really different line item as they have varying date & time.

Using SQLMetal, the code gets generated so that the object handles the relationship between the two tables. Keeping this in mind, I construct the query as follows:

var q = from i in dbcontext.tblDetail
where i.tblPrimary.Name == 'John' && i.PrimaryId == i.tblPrimary.PrimaryId
select i;

Using the example data above, if I have two “John” in tblPrimary with different DateAdded, and 2 records in tblDetail for each John, the result returned by the query is:

John - 2011-01-03T11:12:00z
1, 1, 14, Dept01
2, 1, 12, Dept02
3, 2, 10, Dept03
4, 2, 17, Dept04
John - 2011-02-04T10:54:12z
1, 1, 14, Dept01
2, 1, 12, Dept02
3, 2, 10, Dept03
4, 2, 17, Dept04

What I really want it to result is this:

John - 2011-01-03T11:12:00z
1, 1, 14, Dept01
2, 1, 12, Dept02
John - 2011-02-04T10:54:12z
3, 2, 10, Dept03
4, 2, 17, Dept04

Can anyone suggest how I solve this Linq query without using Joins? I assume that the sqlmetal generated relationship between the two tables should handle the joins already for me.

Answer 1

Hmm, look at this part of the query:

i.PrimaryId == i.tblPrimary.PrimaryId

The tblPrimary property is defined in such a way that this expression is always true.

---

Can anyone suggest how I solve this Linq query without using Joins?

How about this?

var q =
   from i in  dbcontext.tblDetail 
   let j = i.tblPrimary
   select new {Detail = i, Primary = j};