LINQ and Left Outer Joins

Left outer joins in LINQ are not so obvious in comparison to plain old SQL.
The following shows how an out join can be costucted in LINQ

var query = (from e in dc.GetTable<Employee>()

join ea in dc.GetTable<EmployeeAddress>()on e.Id equals ea.EmployeeId into tempAddresses

from addresses in tempAddresses.DefaultIfEmpty()

select new { e.FirstName, e.LastName, addresses.State, addresses.Town});

The SQL equivalent would be:

select [t0].[FirstName], [t0].[LastName], [t1].[State] as[State], [t1].[Town]as [Town]

from [dbo].[Employee] as  [t0

Left outer join  [dbo].[EmployeeAddress]as[t1] on[t0].[Id] = [t1].[PersonID]

Get Recently Ran Query - SQL SERVER 2005

Have you ever wanted to see what queries are running in SQL server on the fly? Yes? Well everyone’s favourite SQL server guru Pinalkumar Dave has once again provided the perfect solution with the follow simple SQL script.

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

One thing it doesn’t seem to do is recover last run quires where the database connection/session has finished. Now, Id like to see that as it whould have saved me from the silly mistake of not saving my work :(.

Pass DataContext to Silverlight IValueConverter

When passing DataContext to a Silverlight IValueConverter the following method is not supported:

<TextBox Text="{Binding Detail,
Mode=TwoWay, NotifyOnValidationError=True,  ValidatesOnExceptions=True}" 
IsEnabled={Binding ,Converter="{StaticResource contactDetailsColorConverter}} >

</TextBox>

Instead you need to translate the the above out to its expanded tag version

<TextBox Text="{Binding Detail, Mode=TwoWay, NotifyOnValidationError=True, ValidatesOnExceptions=True}"  >

<TextBox.IsEnabled>

<Binding Converter="{StaticResource contactDetailsColorConverter}"/>

</TextBox.IsEnabled>

</TextBox>

SSRS - Use URL to Render to Excel

So many times have I had to render a SSRS report to excel from an application? The easiest way to do this I have found is to use the URL of the report to set the output format.

This can be done by appending "&rs:Format=Excel" to the end of the reports URL string. For example:

http://servername/ReportServer?foldername/reportname&rs:Format=Excel

If however I needed to pass a parameter to the report from a calling application I can simply add the parameter as a url parameter as well. The following example shows who to pass a parameter called "eventID" to the SSRS report and render the output to Excel format:

http://servername/ReportServer?foldername/reportname&rs:Format=Excell&EventId=23