Zeeshan Hirani

With Vb, building an XML Document on the fly from a list in the database is very simple. Vb supports the concept of embedded expressions which is similar to asp.net concept of embedding code expressions in aspx page. This allows you to build you XML file as it should look when its opened. XML literals also allows you to replace sections of the file with data from anything that is IEnumerable. Example below builds an XML document from a list in the database.

This is a cool trick that I discovered in Vb when working with XML. Vb has full support for XML in the language so much so that you can type in XML write in the code and IDE would do color highlighting to differentiate it from the code in the page. It would also do correct indenting of the code so XML is more readable. By default when you type in XML in Vb.net code, it is inferred as XElement. Below example shows this behavior.

If you have been making use of DataLoadOptions to eagerly load 1 to 1 entity or 1 to many entities such as child collections; you have to make sure that you apply all these operations before assigning the DataLoadOptions to the DataContext. For example, when you use LoadWith method to Load Oders for a customer, you need to ensure that you call LoadWith method before you assign DataLoadOptions variable to DataContext. Any changes you make to the DataLoadOptions after it has been assigned will result in runtime exception by the compiler. Example below shows the exception raised by the compiler in an correct usage of the datacontext.

From the title of the blog posting, readers must be thinking, I have already given away control of writing SQL to Linq providers. Now do I get to loose control over writing my Linq queries?  Well in reality not. What I meant is Linq provider analyzes the queries and if it finds repetitive filters or duplicate checks or even for that matter finds that a query can be written efficiently based on the data available at runtime which was not be available during compile time, Linq providers will fix the query and translate the fixed version of the query to SQL to be sent to the database.  Below is an example that demonstrates Linq provider's ability to modify Linq queries for better performance and less redundancy.

When you use Linq to SQL to apply filter, orderby or any other operator, Linq provider translates the query to SQL and sends it to the database. When the query is send to the database, SQL server determines if it can use an existing query plan for this query or create a new query plan. One of interesting point I discovered is, Linq to SQL query plans do not get reused. For instance if I write a Linq query to find customers with Contact Title of Sales Agent, SQL server would generate an query plan which you can see in syscacheobjects table. When I run the query again with a different Contact Title say, Accounting Manager, SQL server, instead of reusing the same query plan generates a new query plan. The problem with this is, dynamic queries have limited caching buffer and once it reaches it max size allocated for the dynamic query buffer, it would start to flush out the old query plans. Since this process would be happening so fast, that it would hard to reuse the same query plan again resulting in a new query plan being created every time. Secondly if you look at syscacheobjects table, there is another column called usecounts which tells how many times this query plan has been used. Most of the time with Linq to SQL query the usecounts never goes past one. Below is an example that shows different queries being send to the database using Linq to SQL.

Linq to SQL supports single table inheritance. So if you have an employee table, you can store both Salaried and Hourly Employee in a single table. To identify which employee is what, you can use discriminator column. To map a table that contains multiple derived classes in Linq to SQL, you have to do 3 things. For each derived class, you have to specify what discriminator column to use. Secondly you have specify what value in the discriminator column identifies a particular derived class. Third you have to specify what should be the default derived class in the case where database does not define a value for the discriminator column. In the example below I have an employee class which has two derived classes, SalariedEmployee and HourlyEmployee.

When a call is made to a stored procedure or a function that returns a scalar value,  linq to SQL designer generated code calls ExecuteMethodCall which returns an object that implements of  IExecuteResult. IExecuteResult interface has one method and one property. Below is the definition for IExecuteResult interface.

If you drag stored procedures or functions on linq to SQL designer, the code generated to uses ExecuteMethodCall or CreateMethodQuery.