Linqpad - Using local sequence with Linq to SQL

denwiz · September 2014

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.


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?

JoeAlbahari · September 2014

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

denwiz · September 2014

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.

Linqpad - Using local sequence with Linq to SQL

Comments

Categories