Salim Fayad

This is my first project on LINQ, and I got surprised on how tuff it is to build join queries. But the trick is to understand how the LINQ to Entities work.

I will discuss in this blog some of the complicated queries that I built and how I built them.

But let's first understand how the LINQ to Entities works: each entity contains a set of properties, and a set of entity objects if it is linked to other tables. So, it is already linked together.

For example

A City might contain many hubs, and a hub can be for many cities. The relation between city and hub is many-to-many.

First let's get all the hubs for a specific city, to write it in LINQ:

The trick was in the second line:

            from ch in h.CityHubs

As you can see, cities and hubs are already linked in the Entity Data Model, so, no need to write a "join" query. But to reference it, you can use the from on the referenced Hubs which is h.

Now, let's do something more challenging. We want to get all the hubs that are not for a specific city. In T-SQL, we use nested query and the "not in" as following:

This is one way to write it in T-SQL, but there is another way:

There is no "not in" operator in LINQ, so we're going to transform the second T-SQL to LINQ as following:

As you can see, you can do a Cross Join in LINQ using many times "from" operator

Now, another more sophisticated query:

Each flight has two Hubs, one arrival and the other one is the departure. The query has to get the available flights for the selected arrival and departure directorates.

In T-SQL, it can be written as following:

In LINQ, it is written as following:

There are plenty of tools to compare database schemas. But why using them while SQL Server provides everything for you through System Views (check my blog How to check the schema of your Database through queries)? This blog shows you how to do that.

In this blog, we will refer to the source database as "Source", and the destination database as "Destination". They are considered in the examples as on the same server.

For each comparison, we will be checking:

1. The new objects
2. The objects that need to be deleted (not present in the Source database but present in the Destination database)
3. The modified objects

First of all, we will compare the objects in the databases in order to find out what are the new objects and the objects that need to be deleted. To do that, we will use the sys.objects system view. It shows all the objects in the database, along with their object_id which is used to identify the objects in other system views:

Next, we will check all the columns to see the new columns to be added, the old ones to be deleted, and the modified ones. We will use the INFORMATION_SCHEMA.COLUMNS system view:

The most difficult part is to find out the updated stored procedures, user defined functions, and views. We will use the sys.sql_module system view to checkout the script of these objects. But a single space character might make 2 SPs different. We will use the DIFFERENCE function to reduce such discrepencies and focus on the script itself:

I included the different parts in one stored procedure that produces a table with all the differences between the two databases:

The output table has the following columns:

1. Name: the name of the object
2. Type: the type of the object
3. Value: the value of the object. For the columns, it will include its types, while for the SPs, Views and UDFs, it will include its scripts
4. Action: one of the 3 possible actions: Create, Delete or Update

In my previous post, How to check the schema of your database through queries, I talked about how to check the schema of the database. In this post, we are going to talk about how to use that to build a Search Engine for the Database, to search in all your database for a specific keyword. The output will be a table with the following fields:

1. Table: The table that contains this keyword
2. Column: The column that contains this keyword
3. Result: The whole value stored in the column that satisfies the search criteria

This will be a stored procedure:

-- Displaying the result
SELECT *
FROM #Result

How to convert rows into columns? We're going to see how to do that using query, and there is the "Pivot" function in SQL Server 2005.

The easiest way is when you know the columns which are fixed. But most of the times, you want to do that in general not for specific columns.

Using Query:

If you have fixed columns, then all you will be doing is an “if-else” field that holds the column names and set them into different columns:

Example: We have 2 tables: LKUP_Subject which is a lookup table that holds the subject names, and the Student_Subject which contains the student grades for the different subject.

We are going to build the query having fixed columns:

1. We use the "if-else" functionality in the query to put them in the defined columns (in SQL, it is "case"):

SELECT StudentId,
 (CASE SubjectId WHEN 24 THEN ISNULL(Grade, 0) END) AS Physics,
 (CASE SubjectId WHEN 25 THEN ISNULL(Grade, 0) END) AS Chemistry,
 (CASE SubjectId WHEN 26 THEN ISNULL(Grade, 0) END) As Math,
 (CASE SubjectId WHEN 28 THEN ISNULL(Grade, 0) END) AS English
FROM Student_Subject

              

 

2.       Then we use the “SUM” function to merge the results in 1 row like we want:

SELECT StudentId,
 SUM(Physics) AS Physics,
 SUM(Chemistry) As Chemistry,
 SUM(Math) AS Math,
 SUM(English) As English
FROM
 (SELECT StudentId,
  (CASE SubjectId WHEN 24 THEN ISNULL(Grade, 0) END) AS Physics,
  (CASE SubjectId WHEN 25 THEN ISNULL(Grade, 0) END) AS Chemistry,
  (CASE SubjectId WHEN 26 THEN ISNULL(Grade, 0) END) As Math,
  (CASE SubjectId WHEN 28 THEN ISNULL(Grade, 0) END) AS English
 FROM Student_Subject) s
GROUP BY StudentId

                

 

Now, we will build it dynamically using cursor (you can do it using temporary tables to do the same functionality for performance reasons):

 

DECLARE Cur CURSOR FOR
      SELECT DISTINCT id, '[' + Description_En + ']' AS Description_En
      FROM LKUP_Subject

DECLARE @SubjectName NVARCHAR(MAX),
            @SubjectId INT,
   @Sum NVARCHAR(MAX), -- The SUM part of the query
            @Select NVARCHAR(MAX), -- The inner query
   @Sql NVARCHAR(MAX) -- The total sql statement

SET @Select = ''
SET @Sum = ''
SET @Sql = ''

OPEN Cur

FETCH NEXT FROM Cur INTO @SubjectId, @SubjectName
WHILE @@FETCH_STATUS = 0
BEGIN
   SET @Sum = @Sum + 'SUM(' + @SubjectName + ') AS ' + @SubjectName + ','
      SET @Select = @Select + '(CASE WHEN SubjectId = ' + CONVERT(NVARCHAR(10), @SubjectId) + ' THEN Grade END) AS ' + @SubjectName + ','
      FETCH NEXT FROM Cur INTO @SubjectId, @SubjectName

END

CLOSE Cur
DEALLOCATE Cur

IF RIGHT(@Select, 1) = ','
      SET @Select = STUFF(@Select, LEN(@Select), 1, ' FROM Student_Subject')

IF RIGHT(@Sum, 1) = ','
 SET @Sum = STUFF(@Sum, LEN(@Sum), 1, '')

SET @Sql = 'SELECT StudentId, ' + @Sum + ' FROM (SELECT StudentId, ' + @Select + ') s GROUP BY StudentId'

EXEC sp_executesql @Sql

                            

Using Pivot:

In SQL Server 2005, there is a new feature that does all of this in a single step: PIVOT

In fixed columns, here is how we use it:

SELECT StudentId, Physics, Chemistry, Math, English
FROM
 (SELECT StudentId, Grade, Description_En
 FROM LKUP_Subject
  INNER JOIN Student_Subject
   ON LKUP_Subject.Id = Student_Subject.SubjectId) S
 PIVOT
 (
  SUM (Grade)
  FOR Description_En IN
  (Physics, Chemistry, Math, English)) AS pvt
 
 

Note: that you should use an aggreate function like the SUM (for the same reason as you should use an aggreate function when using the query to transform Rows to Columns)

 

As for how to do it dynamically:

DECLARE Cur CURSOR FOR
  SELECT DISTINCT Description_En
  FROM LKUP_Subject

DECLARE @Temp NVARCHAR(MAX),
  @AllSubjects NVARCHAR(MAX),
  @SubjectQuery NVARCHAR(MAX)

SET @AllSubjects = ''

OPEN Cur
-- Getting all the subjects
FETCH NEXT FROM Cur INTO @Temp
WHILE @@FETCH_STATUS = 0
BEGIN
 SET @AllSubjects = @AllSubjects + '[' + @Temp + '],'
 FETCH NEXT FROM Cur INTO @Temp
END

CLOSE Cur
DEALLOCATE Cur

SET @AllSubjects = SUBSTRING(@AllSubjects, 0, LEN(@AllSubjects))

-- Building the pivot query
SET @SubjectQuery = 'SELECT StudentId, ' + @AllSubjects + '
FROM
(SELECT StudentId, Grade, Description_En
FROM LKUP_Subject
 INNER JOIN Student_Subject
  ON LKUP_Subject.Id = Student_Subject.SubjectId) S
PIVOT
(
SUM (Grade)
FOR Description_En IN
(' + @AllSubjects + ')) AS pvt'

EXEC sp_executesql @SubjectQuery

 

One nice thing is to be able to check and to query the schema of your database through writing simple queries and not through code.

For example, if you want to change a table structure or delete it, you want to find all the SPs and Views that use this table. Another example, if you want to check for schema changes between 2 databases, you can do that using simple queries. Another example, if you want to get the script of stored procedure of view using queries (for example writing a T-SQL to get you the script of some stored procedures)

 We are going to talk in this blog on 4:

1. INFORMATION_SCHEMA.TABLES: This is a system view that allows to see all the tables in your database

2. INFORMATION_SCHEMA.COLUMNS: This is also a system view that allows to see all the columns along with their metadata in your database

3. INFORMATION_SCHEMA.ROUTINES: This is also a system view that will get you all the stored procedures in your database

4. sys.sql_modules: This is a system table that stores the script of the stored procedures in your database.

         Example: SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('dbo.InsertStudent');

         This will get you the script of the stored procedure "InsertStudent"