C#: Left outer joins with LINQ

I always considered Left Outer Join in LINQ to be complex until today when I had to use it in my application. I googled and the first result gave a very nice explanation. The only difference between ordinary joins (inner joins) and left joins in LINQ is the use of “join into” and “DefaultIfEmpty()” expressions.

Consider this very simple query (Assuming a scenario that not all the TimesheetLines are associated with a Job)

Select TL.EntryDate, TL.Hours, J.JobName
From TimeSheetLines TL
Left Join Jobs J on TL.JobNo=J.JobNo

A LINQ query using inner join is

var lines =
    from tl in db.TimeSheetLines
    join j  in db.Jobs on tl.JobNo equals j.JobNo
    where tl.ResourceNo == resourceNo

    select new
    {
        EntryDate = tl.EntryDate,
        Hours = tl.Hours,
        Job = j.JobName
    };

And a LINQ query performing left join is

var lines =
    from tl in db.TimeSheetLines
    join j  in db.Jobs on tl.JobNo equals j.JobNo into tl_j
    where tl.ResourceNo == resourceNo

    from j in tl_j.DefaultIfEmpty()
    select new
    {
        EntryDate = tl.EntryDate,
        Hours = tl.Hours,
        Job = j.JobName
    };

Notice that the only difference is the use of “into” with the join statement followed by reselecting the result using “DefaultIfEmpty()” expression. And here’s the generated SQL from the above LINQ expression.

SELECT [t0].[EntryDate] as [EntryDate], [t0].[Hours] as [Hours], [t1].[JobName] AS [Job]
FROM [dbo].[TimeSheetLine] AS [t0]
LEFT OUTER JOIN [dbo].[Jobs] AS [t1] ON [t0].[JobNo] = [t1].[JobNo]
WHERE [t0].[ResourceNo] = @p0

Similar concept can be expanded for multiple left joins. Assuming that a TimeSheetLine will either have a JobNo or an IndirectCode, consider this SQL query:

Select TL.EntryDate, TL.Hours, J.JobName, I.IndirectName
From TimeSheetLines TL
Left Join Jobs J on TL.JobNo=J.JobNo
Left Join Indirects I on TL.IndirectCode=I.IndirectCode

The equivalent LINQ query is:

var lines =
    from tl in db.TimeSheetLines
    join j in db.Jobs      on tl.JobNo        equals j.JobNo            into tl_j
    join i in db.Indirects on tl.IndirectCode equals i.IndirectCode  into tl_i
    where tl.ResourceNo == resourceNo

    from j in tl_j.DefaultIfEmpty()
    from i in tl_i.DefaultIfEmpty()
    select new
    {
        EntryDate = tl.EntryDate,
        Hours = tl.Hours,
        Job = j.JobName,
        Indirect = i.IndirectName,
    };

And the generated SQL is:

SELECT [t0].[EntryDate] as [EntryDate], [t0].[Hours] as [Hours], [t1].[JobName] AS [Job], [t2].[IndirectName] As [Indirect]
LEFT OUTER JOIN [dbo].[Jobs] AS [t1] ON [t0].[JobNo] = [t1].[JobNo]
LEFT OUTER JOIN [dbo].[Indirects] AS [t2] ON [t0].[IndirectCode] = [t2].[IndirectCode]
WHERE [t0].[ResourceNo] = @p0

That’s all, left outer joins in LINQ are as easy as in T-SQL. Happy joining.

C#: Executing batch T-SQL Scripts containing GO statements

At times, we developers need to run SQL scripts from our .NET applications, in, say Installer Applications. This is not always easy since large SQL scripts typically contain a GO statement to separate individual batches and our ADO.NET classes under System.Data namespace do not know how to handle it. The reason is that the “GO” statement is not a native T-SQL statement but rather used by SQL Server Management Studio to terminate the batches it is sending to the server. To tackle this situation, we have the following choices:

1. Split the script on “GO” command into smaller scripts and execute those individual scripts

A very primitive solution would be to split the script on “GO” text and run the individual sub-scripts in sequence. The problem is how to get a robust split mechanism. Generally, a line break before and after the GO works fine. Here’s how to do this:

//get the script
string scriptText = GetScript();

//split the script on "GO" commands
string[] splitter = new string[] { "\r\nGO\r\n" };
string[] commandTexts = scriptText.Split(splitter,
  StringSplitOptions.RemoveEmptyEntries);
foreach (string commandText in commandTexts)
{
  //execute commandText
}

The above code can produce unnecessary splitting in some situations, thus creating an incorrect SQL Command. A more better approach would be to use Regular Expressions. Again, the problem is how to create a pattern that is robust enough to tackle all sort of scripts. Lets look into the second solution now.

2. Use the Server class from SQL Server Management Objects (SMO)

For this, we need to add the following references in our project:

• Microsoft.SqlServer.Smo
• Microsoft.SqlServer.ConnectionInfo
• Microsoft.SqlServer.Management.Sdk.Sfc

After that, we can simply execute the entire script (with all the “GO” statements) using the code below:

string connectionString, scriptText;
SqlConnection sqlConnection = new SqlConnection(connectionString);
ServerConnection svrConnection = new ServerConnection(sqlConnection);
Server server = new Server(svrConnection);
server.ConnectionContext.ExecuteNonQuery(scriptText);

This is a fairly generic and robust solution and the great thing is that it does not require any change in our original script. Hooray!!!

How to create and use Extension Methods in C#

As I promised in my previous post, here’s a simple example to create and use extension methods. Extension methods are a powerful feature introduced in Dot Net 3.0/3.5 that allow us to inject/add a custom method in any existing type (even if the type is Sealed/NotInheritable). Lets say, we want to add a new method IsPrime to the existing int (System.Int32) type, then we need to write an static method in an static class like this:

 public static class MyExtensions
    {
        public static bool IsPrime(this int integer)
        {
            //implementation here
            return true;
        }
    }
 

Now, this IsPrime method will be associated with every int type in our project, as depicted in the following screenshot:

Thats all. I just wanted to demonstrate extension methods using a very basic example; if you need more information, try msdn or google.

C# Coding Standards

I recently came across this post from Clint Edmonson. It presents a reference document for coding standards (for C# as well as VB) you may find useful.

New article: Analog clock control in C#

Visit http://www.codeproject.com/cs/miscctrl/AnalogClockControl.asp for my new article. It is a step-by-step guide on how to make a clock control in C#. The article contains much description and pictures. I hope you will like this.

Matrix and Fraction classes

I always get confused when performing row operations in a Matrix. But matrices were a great portion of our Maths-III course, as well as our Economics’ Linear programming section. Hence I needed a program which could tell me at when I was doing mistakes. The solution was simple, just to download any matrix application. But the problem was that all matrix programs I got, worked on floating point numbers and in our class problems we have to consider the exact values using fractions. I therefore needed a matrix application which could show results in a fraction format, but I didn’t found any. At that time we learned how to represent real world objects in a programming language and we did the example of a fraction class in our OOP course. I further enhanced that idea and was able to develop a fraction class and later on, a matrix using the fraction class. I incorporated all my needs and it worked fine. Then I uploaded the classes on GotDotNet<www.gotdotnet.com> and other similar sites and yes, I was able to further improve my classes with the help of user responses. I finally posted two articles on CodeProject<www.codeproject.com> and user responses were wonderful. I finally thank all of those who were helpful starting from Sir Saqib Ilyas to Jeffrey, Marc and other fellows.