Zeeshan Hirani

This blog post title might be very surprising to the viewers. I am sure people would be thinking how in the word can you access private properties or methods without using reflection. Well in your code you don't have much choice except to use reflection. However if you are debugging, than immediate window lets you access private level properties and method. I thought it was pretty slick and also meant easier and more productive debugging.  In the code below, I have an Employee class which has a private method and a private property. I created an instance of my class, than stopped the debugger in the next line, opened up the immediate window and ran the commands below. Sure enough immediate window allowed me to call the private attributes of a class although intellisence didn't seem to help any in terms of calling private stuff.

When you make changes to object, delete or insert new objects, linq to SQL is tracking the changes and based on the original values, it generates the appropriate SQL statements. DataContext exposes a method called GetChangeSet that contains all the inserted, updated and deleted objects that will be sent to the database when SubmitChanges gets called. You basically have to loop through each collection and find out what objects have been effected.  If you want to know exactly what SQL statement would be sent to the database their is a private method called GetChangeText that you can call using reflection to find out the exact SQL statement that would be sent to the database. Example below shows a change set that contains few updated objects, an inserted object and an object marked for deletion.

Linq to SQL provides deferred loading of foreign key object such as EntityRef and EntitySet so that you get the best performance and do not end up loading the entire object graph until you need it. However there are situations when you have binary or varchar(max) columns on a table which you do not want to load until you access the property. A real world example for this scenario would be like you are storing images in the product table or product table has description column which is set to varchar(max).  If all you are doing is querying the product table to bring  product collection and wont be touching description or image column than there really is no need to be loading those column ahead of time. If you are going to be using the Visual Studio designer than simply go to the property and set the Delay Loaded to true to turn on Delay Loading. On changing the setting in the properties window, Linq to SQL designer will create a private variable of type System.Data.Linq<T> where T is the type of property  like string, int etc. Here is an example where I have marked description column to be lazy loaded.

When you insert or update a record in the database, there are certain columns whose values are either generated because they are identity columns, or columns whose value is assigned by SQL server such as timestamp column. You could have triggers that update columns after an insert or update on the table. Those column values are not reflected in your entity. However linq to SQL offers different syncing options to sync properties on your entities with column values generated by the database. You can use AutoSync parameter on your columns and specify when linq to SQL should update those columns such as OnInsert, OnUpdate,Always,Default and Never. I have generated a sample class that show variety of syncing options that you can apply on your properties to sync your entities after insert and update of a record to the database.

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.

Linq to SQL support many aggregates such as Sum,Avg, Min, Max and few others but they may not execute on SQL server. Depending upon how you write your queries, you would be surprised the same syntax would actually bring the entire data from the server and execute the aggregate operations in memory. Below is an example where the entire query executes on SQL server and hence very efficient in terms of performance. While the same exact query just written in two steps causes the records to be fetched from SQL server and aggregate be performed in memory.

If you have generated your Linq to SQL classes using visual studio Designer or used sqlmetal utility, you would notice that your entity classes implement two very important interfaces for change tracking called INotifyPropertyChanging and INotifyPropertyChanged interface. These interfaces belong to System.ComponentModel namespace and were introduced in .net 3.0 runtime. Although the use of these interfaces are not mandatory for your entity classes but having them make the change tracking process efficient and less memory intensive. If your entities do not implement Notification interfaces, Linq to SQL will keep track of two copies of your objects, one with the original values when you first fetched the object and second object which has changes applied to it. When submit changes is called, Linq to SQL will do a compare of those objects and send only the properties which has changed since the first retrieval. This process which keeps tracks of two copies of the object can cause memory issues when you are managing lots of objects. Therefore Linq to SQL also provides an option where it can allow entities to to participate in change tracking service. Entities can participate in change tracking service by implementing INotifyPropertyChanging interface and INotifyPropertyChanged interface and raise propertychanging and property changed event in the setter of the property. Let's look at some code below to see how our entities can participate change notification service.

By default Linq to SQL would lazy load all your child collections like when you have an instance of customer, its Orders are not loaded. ON traversing the Orders collection,  Linq to SQL fetches the Orders for that customer. You can use LoadWith operator to eagerly load the Orders for the customer. But what if you also want to sort that Order collection when your eagerly fetching the Orders. In that case you can use AssociateWith Method and specify the column that you want to order your Orders Collection by. Here is an example that accomplishes that.