C# 7.0: Tuple Types and Tuple Literals

Basically, a tuple (Tuple in C#) is an ordered sequence, immutable, fixed-size and of heterogeneous objects, i.e., each object being of a specific type. Tuples were introduced in C# with .NET 4.0

// Implementation
private Tuple<int, string> GetData()
{
	int id = 1;
	string name = "test";
	return Tuple.Create(id, name);
}
//Usage
var tuple = GetData();
var message = $"ID is {tuple.Item1} and Name is {tuple.Item2}";

Tuples provide a quick way to group multiple values into a single result, which is very useful when used as a return of function, without the need to create parameters “ref” and / or “out “. It also facilitated with creation of composite keys to collections of type Dictionaryand eliminates the need to create structs or classes or just to fill combobox or lists.

Along with all advantages it has a major problem, tuples does not have an explicit semantic meaning. In other words, the elements in the tuples don’t have names, they have same names (Item1,Item2,Item3…ItemN). It’s hard to read and understand for teams consuming these functions.

To do better at this, C# 7.0 adds tuple types and tuple literals. Tuples still are not part of .NET framework, to use them we can add them using nuget:

PM> install-package System.ValueTuple

Successfully installed ‘System.ValueTuple 4.3.0’ to EntityFrameworkSample
Executing nuget actions took 3.85 sec
Time Elapsed: 00:00:04.5314317

//Implementation
public (int id, string name, double marks) GetData()
{
	var id = 1;
	string name = "test";
	double marks = 95.7;
	return (id, name, marks);
}
//Usage
var tuple = GetData();
var message = $"ID is {tuple.id}, name is {tuple.name} and marks are {tuple.marks}";

Now with new implementation, it looks much better and readability is also good. There is another nice addition, it fully supports async and await.

public async Task<(int id, string name, double marks)> GetData()

Tuples can also be created as:

var t = new (int sum, int count) { sum = 0, count = 0 };

Final comment, whatever changes C# has made are syntactical changes. If you check the compiled code, the tuples are referred as Item1,Items2 etc not id, name or marks.

I hope this helps someone. Any comments and suggestions are welcome.

SQL Tips: Why we need to have RIGHT OUTER JOIN in SQL

Recently I was reading some article and in comments section I found this interesting question.

Q’s: If we perform left outer join on employee and department it will return all records from left table and only matching from right. similarly, right outer join will return all the records from department and only matching from employee table. NOW… what if we swap the tables in left outer join. i.e if we perform left outer join of department and employee which will return same records as output as right outer join.

THEN THE QUESTION IS WHY DO WE HAVE RIGHT OUTER JOIN IN SQL???

Before I dive into the answer, I would love to give a brief intro about Left outer join and Right outer join.

join_left_right

For the sake of this example, lets say you have 100 students, 70 of which have enrollments. You have a total of 50 enrollments, 40 of which have at least 1 student and 10 enrollments have no student.

LEFT OUTER JOIN would be “show me all students, with their corresponding enrollments if they have one”.
This might be a general student list, or could be used to identify students with no enrollments.
Returns 100 rows

RIGHT OUTER JOIN would be “show me all enrollments, and the students assigned to them if there are any”.
This could be used to identify enrollments that have no students assigned, or enrollments that have too many students.
Returns 80 rows (list of 70 students in the 40 enrollments, plus the 10 enrollments with no student)

Lets get back to our main question,”WHY DO WE NEED RIGHT OUTER JOIN”?

Answer to the above question was also provided by some anonymous user then and there in itself.

Anything you can write as a “RIGHT OUTER” could be written as a “LEFT OUTER” by just flipping the tables, so technically “RIGHT OUTER” isn’t functionally required in order to have working code.

It was created to allow the coder to express it that way if it makes more sense to them that way. So basically it exists for readability and potential maintainability.

Having said that I rarely see anyone code a “RIGHT OUTER”, most people just automatically order their tables to be “LEFT OUTER” joins if an outer is needed.

I found it interesting, so thought to share with others also. May be it can be of some help to some one else.

SQL Tips: Parsing special characters in Queries

Recently I was working on some SSIS reports. Everything was working fine up till my queries ran on testing server where it failed during parsing of special characters in the query.

Below query worked fine  when there was no special character in the data.

DECLARE @t TABLE
(
StudentID INT,
Subjects VARCHAR(8000)
)

INSERT INTO @T VALUES 
(111,'English French,Maths,Science'),
(112,'English Spanish,English,Science')

SELECT StudentId 'Student Id',
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS 'Subject Name'
FROM
(
SELECT 
 StudentID,
 CAST('' + REPLACE(Subjects,',','') + '' AS XML) AS node
FROM @t
)XmlTable
CROSS APPLY node.nodes('/XMLRoot/RowData')m(n)

Output:

Output5.JPG

It started failing as soon as I got special characters in my data.

--(111,'English & French,Maths,Science'),
--(112,'English & Spanish,English,Science')

Msg 9421, Level 16, State 1, Line 13
XML parsing: line 1, character 28, illegal name character

This is what I made changes to my query to get the desired results. Here I am using (SELECT ... FOR XML PATH()) instead of the naked Subjects. I am letting FOR XML PATH() do all the hard work for me.

--Changed below line
CAST('' + REPLACE(Subjects,',','') + '' AS XML) AS node
--with
CAST('' + REPLACE((SELECT Subjects AS [*] FOR XML PATH('')),',','') + '' AS XML) 
AS node

Output:

output6

We can see now we are getting the results with “&” characters.

I hope this will help someone and will save some precious time.

SQL Tips – Comma separated list

Today in an interview interviewer asked me to make a query to create a comma separated list which I did easily using STUFF and XML Path. Later I realized I could do it through other ways also. I will write in this blog all possible ways I know.
Table Schema

CREATE TABLE Student(StudentId int,SubjectName varchar(100))

INSERT INTO Student VALUES(111,'English')
INSERT INTO Student VALUES(111,'Science')
INSERT INTO Student VALUES(112,'English')
INSERT INTO Student VALUES(112,'Science')
INSERT INTO Student VALUES(111,'Maths')

Query 1: Below query will pull list for each StudentId  one at a time.

SELECT STUFF((SELECT ',' + SubjectName
FROM Student SS
WHERE SS.StudentId = S.StudentId
ORDER BY SubjectName
FOR XML PATH('')), 1, 1, '') AS SubjectList
FROM Student S
GROUP BY S.StudentId

output1

Query 2: Below query will return all the subjects in one string value. Basically, in this code we are checking if the variable has been initialized yet. If it hasn’t, set it to the empty string, and append the first SubjectName (no leading comma). If it has, then append a comma, then append the SubjectName.

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' , '') + SubjectName
FROM Student
SELECT @listStr 'Subject List'

output2

Query 3: Below query will also return the same output as above query, but its implementation is different. This is also a good way but not the best way.

DECLARE @listStr VARCHAR(MAX)
SET @listStr = ''
SELECT @listStr = @listStr + SubjectName + ','
FROM Student
SELECT SUBSTRING(@listStr , 1, LEN(@listStr)-1) 'Student List'

output2

We can also use cursors to get same results but they aren’t the efficient way.

Splitting comma separated list

Suppose we have another scenario where we have a string of comma separated list and we have to return a table of it.

DECLARE @t TABLE
(
StudentID INT,
Subjects VARCHAR(8000)
)

INSERT INTO @T
SELECT  
        StudentId,
        STUFF((SELECT  ',' + SubjectName
            FROM Student SS
            WHERE  SS.StudentId = S.StudentId
            ORDER BY SubjectName
        FOR XML PATH('')), 1, 1, '') AS 'Subject List'
FROM Student S
GROUP BY S.StudentId

output3

Using below query we can split a comma separated list into a table.

Query

SELECT StudentId 'Student Id',
LTRIM(RTRIM(m.n.value('.[1]','varchar(8000)'))) AS 'Subject Name'
FROM
(
SELECT 
	StudentID,
	CAST('' + REPLACE(Subjects,',','') + '' AS XML) AS node
FROM @t
)XmlTable
CROSS APPLY node.nodes('/XMLRoot/RowData')m(n)

output4

We can use above query in UDF’s also to return a table value as output.

I hope it will help someone. Any comments or suggests are welcome.