Salim Fayad

LINQ to Entities – Join Queries

The Eagle — Tue, 08 Jul 2008 21:08:00 GMT

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:

from h in db.Hubs
from ch in h.CityHubswhere ch.Cities.CityID == 1
select h;

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:

SELECT *
FROM Hubs h
WHERE h.HubId NOT IN (SELECT HubID
FROM CityHubs
WHERE CityID = 1)

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

SELECT h.*
FROM Hubs h,
Common.CityHubs ch
WHERE ch.CityID = 1
AND ch.HubId != h.HubId

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

from h in db.Hubs
from ch in db.CityHubs
where ch.Cities.CityID == 1 && h.HubID != ch.Hubs.HubID
select h;

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:

SELECT f.*
FROM flights f
      INNER JOIN Hubs sh
            ON f.HubFrom = sh.HubId
      INNER JOIN CityHubs sch
            ON sh.HubId = sch.HubId
      INNER JOIN Cities sc
            ON sch.CityId = sc.CityId
      INNER JOIN Directorates sd
            ON sc.CityId = sd.CityId
      INNER JOIN Hubs dh
            ON f.HubTo = dh.HubId
      INNER JOIN CityHubs dch
            ON dh.HubId = dch.HubId
      INNER JOIN Cities dc
            ON dch.CityId = dc.CityId
      INNER JOIN Directorates dd
            ON dd.CityId = dc.CityId
WHERE sd.DirectorateID = @sourceDirectorateID
      AND dd.DirectorateId = @destinationDirectorateID

In LINQ, it is written as following:

from f in db.Flights
from fc in f.Hubs.Cities
from fd in fc.Directorates
from tc in f.Hubs1.Cities
from td in tc.Directorates
where fd.DirectorateID == sourceDirectorateID
&& td.DirectorateID == destinationDirectorateID
select f;

Enjoy them J

Database Schema Comparison Using Simple Queries

The Eagle — Tue, 01 Apr 2008 20:56:00 GMT

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:

The new objects
The objects that need to be deleted (not present in the Source database but present in the Destination database)
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:

-- Objects to be created

SELECT name, type_desc

FROM Source.sys.objects

WHERE name NOT IN (SELECT name FROM Destination.sys.objects)

-- Objects to be deleted

SELECT name, type_desc

FROM Destination.sys.objects

WHERE name NOT IN (SELECT name FROM Source.sys.objects)

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:

-- Columns to be created

SELECT TABLE_SCHEMA + '.' + TABLE_NAME + '.' + COLUMN_NAME,

DATA_TYPE + ISNULL('(' + (CASE WHEN CONVERT(varchar, CHARACTER_MAXIMUM_LENGTH) = '-1' THEN 'MAX' ELSE CONVERT(varchar, CHARACTER_MAXIMUM_LENGTH) END ) + ')' , ISNULL('(' + CONVERT(varchar, NUMERIC_PRECISION) + ', ' + CONVERT(varchar, NUMERIC_SCALE) + ')', '')) + (CASE WHEN IS_NULLABLE = 'YES' THEN ', null' ELSE ', not null' END)

FROM Source.INFORMATION_SCHEMA.COLUMNS source

WHERE COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM Destination.INFORMATION_SCHEMA.COLUMNS destination

WHERE source.TABLE_NAME = destination.TABLE_NAME)

AND TABLE_NAME IN (SELECT TABLE_NAME

FROM Destination.INFORMATION_SCHEMA.TABLES)

-- Columns to be removed

SELECT TABLE_SCHEMA + '.' + TABLE_NAME + '.' + COLUMN_NAME,

FROM Destination.INFORMATION_SCHEMA.COLUMNS destination

WHERE COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM Source.INFORMATION_SCHEMA.COLUMNS source

WHERE source.TABLE_NAME = destination.TABLE_NAME)

AND TABLE_NAME IN (SELECT TABLE_NAME

FROM Source.INFORMATION_SCHEMA.TABLES)

-- Columns to be modified

SELECT source.TABLE_SCHEMA + '.' + source.TABLE_NAME + '.' + source.COLUMN_NAME,

source.DATA_TYPE + ISNULL('(' + (CASE WHEN CONVERT(varchar, source.CHARACTER_MAXIMUM_LENGTH) = '-1' THEN 'MAX' ELSE CONVERT(varchar, source.CHARACTER_MAXIMUM_LENGTH) END ) + ')' , ISNULL('(' + CONVERT(varchar, source.NUMERIC_PRECISION) + ', ' + CONVERT(varchar, source.NUMERIC_SCALE) + ')', '')) + (CASE WHEN source.IS_NULLABLE = 'YES' THEN ', null' ELSE ', not null' END),

FROM Source.INFORMATION_SCHEMA.COLUMNS source

INNER JOIN Destination.INFORMATION_SCHEMA.COLUMNS destination

ON source.TABLE_NAME = destination.TABLE_NAME

AND source.COLUMN_NAME = destination.COLUMN_NAME

WHERE source.COLUMN_DEFAULT <> destination.COLUMN_DEFAULT

OR source.DATA_TYPE <> destination.DATA_TYPE

OR source.CHARACTER_MAXIMUM_LENGTH <> destination.CHARACTER_MAXIMUM_LENGTH OR source.NUMERIC_PRECISION <> destination.NUMERIC_PRECISION

OR source.NUMERIC_SCALE <> destination.NUMERIC_SCALE

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:

-- Changes in the SPs, Views and UDFs

SELECT o.name,

m.definition

FROM Source.sys.sql_modules m

INNER JOIN Source.sys.objects o ON m.object_id = o.object_id

INNER JOIN Destination.sys.sql_modules m2 INNER JOIN Destination.sys.objects o2 ON m2.object_id = o2.object_id ON o.name = o2.name

WHERE DIFFERENCE(m.definition, m2.definition) < 4

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

CREATE PROCEDURE [dbo].[CompareSchema]

CREATE TABLE #Changes

(

[Name] nvarchar(256),

[Type] nvarchar(256),

[Value] nvarchar(MAX),

[Action] nvarchar(256)

)

-- Objects to be created

INSERT INTO #Changes([Name], [Type], [Action])

SELECT name, type_desc, 'Create'

FROM Source.sys.objects

WHERE name NOT IN (SELECT name

FROM Destination.sys.objects)

ORDER BY type_desc, name

-- Objects to be removed

INSERT INTO #Changes([Name], [Type], [Action])

SELECT name, type_desc, 'Delete'

FROM Destination.sys.objects

WHERE name NOT IN (SELECT name

FROM Source.sys.objects)

-- Columns to be created

INSERT INTO #Changes([Name], [Type], [Value], [Action])

SELECT TABLE_SCHEMA + '.' + TABLE_NAME + '.' + COLUMN_NAME,

'COLUMN',

'Create'

FROM Source.INFORMATION_SCHEMA.COLUMNS source

WHERE COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM Destination.INFORMATION_SCHEMA.COLUMNS destination

WHERE source.TABLE_NAME = destination.TABLE_NAME)

AND TABLE_NAME IN (SELECT TABLE_NAME

FROM Destination.INFORMATION_SCHEMA.TABLES)

-- Columns to be removed

INSERT INTO #Changes([Name], [Type], [Value], [Action])

SELECT TABLE_SCHEMA + '.' + TABLE_NAME + '.' + COLUMN_NAME,

'COLUMN',

'Delete'

FROM Destination.INFORMATION_SCHEMA.COLUMNS destination

WHERE COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM Source.INFORMATION_SCHEMA.COLUMNS source

WHERE source.TABLE_NAME = destination.TABLE_NAME)

AND TABLE_NAME IN (SELECT TABLE_NAME

FROM Source.INFORMATION_SCHEMA.TABLES)

-- Columns to be modified

INSERT INTO #Changes([Name], [Type], [Value], [Action])

SELECT source.TABLE_SCHEMA + '.' + source.TABLE_NAME + '.' + source.COLUMN_NAME,

'COLUMN',

'Update'

FROM Source.INFORMATION_SCHEMA.COLUMNS source

INNER JOIN Destination.INFORMATION_SCHEMA.COLUMNS destination

ON source.TABLE_NAME = destination.TABLE_NAME

AND source.COLUMN_NAME = destination.COLUMN_NAME

WHERE source.COLUMN_DEFAULT <> destination.COLUMN_DEFAULT

OR source.DATA_TYPE <> destination.DATA_TYPE

OR source.CHARACTER_MAXIMUM_LENGTH <> destination.CHARACTER_MAXIMUM_LENGTH OR source.NUMERIC_PRECISION <> destination.NUMERIC_PRECISION

OR source.NUMERIC_SCALE <> destination.NUMERIC_SCALE

-- Changes in the SPs, Views and UDFs

INSERT INTO #Changes([Name], [Type], [Value], [Action])

SELECT o.name,

'SQL_STORED_PROCEDURE',

m.definition,

'Update'

FROM Source.sys.sql_modules m INNER JOIN Source.sys.objects o

ON m.object_id = o.object_id

INNER JOIN Destination.sys.sql_modules m2 INNER JOIN Destination.sys.objects o2

ON m2.object_id = o2.object_id

ON o.name = o2.name

WHERE DIFFERENCE(m.definition, m2.definition) < 4

SELECT *

FROM #Changes

The output table has the following columns:

Name: the name of the object
Type: the type of the object
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
Action: one of the 3 possible actions: Create, Delete or Update

Building Search Engine for the Database

The Eagle — Fri, 29 Feb 2008 15:03:00 GMT

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:

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

This will be a stored procedure:

CREATE PROCEDURE [dbo].[SearchAll]
@KeyWord NVARCHAR(MAX)
AS

-- Getting all the columns in the #AllColumns temporary table
SELECT TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE
INTO #AllColumns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE <> 'image'
AND DATA_TYPE <> 'timestamp'

-- #Result temporary table holds the result of the search
CREATE TABLE #Result
(
[Table] nvarchar(MAX),
[Column] nvarchar(MAX),
Result nvarchar(MAX)
)

DECLARE @TABLE_SCHEMA NVARCHAR(MAX), -- Temporary variable that holds the TABLE_SCHEMA value
@TABLE_NAME NVARCHAR(MAX), -- Temporary variable that holds the TABLE_NAME value
@COLUMN_NAME NVARCHAR(MAX), -- Temporary variable that holds the COLUMN_NAME value

@DATA_TYPE NVARCHAR(MAX), -- Temporary variable that holds the DATA_TYPE value
@RESULT NVARCHAR(MAX), -- Temporary variable that holds the value in the column that contains the keyword
@Query NVARCHAR(MAX) -- The query of the search

-- Looping in each column (Same functionality of the cursor, but better performance)
WHILE EXISTS(SELECT TABLE_SCHEMA FROM #AllColumns)
BEGIN
-- Getting single row in the #AllColumns table
SELECT @TABLE_SCHEMA = TABLE_SCHEMA,
  @TABLE_NAME = TABLE_NAME,
  @COLUMN_NAME = COLUMN_NAME,
  @DATA_TYPE = DATA_TYPE
FROM #AllColumns

-- Building the insert query to the #Result table if the keyword exists in the column @COLUMN_NAME
SET @Query = 'INSERT INTO #Result([Table], [Column], Result) '
     + 'SELECT ''' + @TABLE_NAME + ''', '
     + '''' + @COLUMN_NAME + ''', '
     + 'CONVERT(nvarchar(MAX), [' + @COLUMN_NAME + ']) '
     + 'FROM [' + @TABLE_SCHEMA + '].[' + @TABLE_NAME + '] '

IF @DATA_TYPE = 'xml' OR @DATA_TYPE = 'sql_variant'
BEGIN
SET @Query = @Query + 'WHERE CONVERT(nvarchar(MAX), [' + @COLUMN_NAME + ']) LIKE ''%' + @KeyWord + '%'''
END
ELSE
SET @Query = @Query + 'WHERE [' + @COLUMN_NAME + '] LIKE ''%' + @KeyWord + '%'''

--Execute the query
EXEC sp_executesql @Query

-- Delete the selected row from the #AllColumns table
DELETE FROM #AllColumns
WHERE TABLE_SCHEMA = @TABLE_SCHEMA
AND TABLE_NAME = @TABLE_NAME
AND COLUMN_NAME = @COLUMN_NAME
END

-- Displaying the result
SELECT *
FROM #Result

Rows to Columns

The Eagle — Wed, 30 Jan 2008 17:36:00 GMT

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:

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

How to check the schema of your Database through queries

The Eagle — Wed, 23 Jan 2008 13:29:00 GMT

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"