Linqpad - Using local sequence with Linq to SQL
Options
I tried joining a local sequence with Linq to SQL and found that while the results are displayed correctly, the SQL used by Linqpad to get the results was different from what was displayed.
The
Image -> Linq - SQL using local sequence
1) Is there a reason for this difference in the number of fields selected?
2) The CONTACTS table contains around 400 records and the above sql brought back only the ones linked to the employee record. How is this possible without a where clause in the SQL query?
void Main()
{
var empList = (from a in ADDRESSES
join e in EMPLOYEES on a.EMPLOYEE_ID equals e.EMPLOYEE_ID
where e.EMPLOYEE_ID.ToString() == "1000108"
select e).ToList();
empList.Count.Dump("Employee Count: ");
CONTACTS.Count().Dump("Total number of contacts: ");
var empContacts = from e in empList
join c in CONTACTS on e.EMPLOYEE_ID equals c.EMPLOYEE_ID
select new { c.CONTACT_ID, c.EMPLOYEE_ID };
empContacts.Dump();
}
The
empContacts.Dump()
line correctly displayed two fields on the Results tab, but the SQL tab showed that the sql statement attempted to select all the fields in the table (containing 10 fields).SELECT [t0].[CONTACT_ID], [t0].[EMPLOYEE_ID], [t0].[CONTACT_TYPE], [t0].[CONTACT_DETAILS], [t0].[IS_PRIMARY_CONTACT], [t0].[VALID_FROM], [t0].[VALID_TO]
FROM [CONTACTS] AS [t0]
Image -> Linq - SQL using local sequence
1) Is there a reason for this difference in the number of fields selected?
2) The CONTACTS table contains around 400 records and the above sql brought back only the ones linked to the employee record. How is this possible without a where clause in the SQL query?
Comments
-
The final query joins with empList which you've earlier called ToList on, so you're forcing LINQ to SQL to fetch the entire table to perform the join.
Also, have you considered using association properties? It will simplify your queries considerably:
http://www.linqpad.net/WhyLINQBeatsSQL.aspx -
Thanks Joe. I am aware of Associative properties (when I was learning MVC). This however was a setup similar to an Oracle database at work, which is why I went down the route of performing joins (not sure how to use Associative properties for Oracle).
Also, thanks for developing Linqpad. Great tool. Great timesaver.