ExecuteQuery to execute dynamic SQL in Linq To SQL

If you find that a particular query is hard to represent as a Linq query, you can consider other options such as writing your own SQL statements inside of a stored procedure or a function and than calling the stored procedure and function using Linq to SQL. You can also write dynamic SQL statements using the helper methods provided on DataContext. I am sure some of you may be thinking how is that new or different from ADO.net which always allowed executing dynamic SQL and getting a datareader or dataset back. Well you can achieve the same results by making use of ExecuteQuery<T> method available on the DataContext. ExecuteQuery method on DataContext does a lot more than simply executing a dynamic SQL. First it supports the concept of parameterized queries. You provide parameter like you provide parameters to string.format using placeholders which Linq to SQL converts it to a parameterized query. In traditional ado.net when you execute dynamic SQL, you either get a datareader or dataset. It had always been left to the developer to convert the datareader to customized business object. However ExecuteQuery takes in a generic type that tells the Linq to SQL that a particular SQL statement would return a strongly typed instance of the class passed in as a generic type. You no longer have to worry about converting datareader to object. There are some restriction that you must follow in order to accomplish proper mapping of your columns to properties on your object. First your query columns must contain the column that is defined as the primary key. Second the column names returned by the query should match with the property names defined on your entity class. If the column names do not match than you can use alias in t-SQL to match with the property name defined on your entity. Further more, Linq to SQL does not require that you return all the columns that map to all the properties on your entity. If your query is missing some column, Linq to SQL would assign default value to your property.  Below is an example that illustrates some of this behavior.

In the above code, I am using placeholder to create parameterized query. Since the query is parameterized, I am not using a single quote to pass Seattle filter to my shipCity. In the query, I am only returning customerid and companyname from the customers table. Since Linq to SQL does not require that you bring all the column, only columns that match with the property name gets mapped. Therefore when I print the ContactName its value defaults to null because we did not pass ContactName in our query.