Contextual Keywords in C#

Contextual Keywords in C#

A contextual keyword is used to provide a specific meaning in the code, but it is not a reserved word in C#. This will give some understanding on different contextual keywords and make you comfortable while using those...

yield:
Used in an iterator block to return a value to the enumerator object or to signal the end of iteration.


Use of this construct is vital for LINQ. Yield return allows one enumerable function to be implemented in terms of another. It allows us to write functions that return collections that exhibit lazy behavior. This allows LINQ and LINQ to XML to delay execution of queries until the latest possible moment. it allows queries to be implemented in such a way that LINQ and LINQ to XML do not need to assemble massive intermediate results of queries. Without the avoidance of intermediate results of queries, the system would rapidly become unwieldy and unworkable.

The following two small programs demonstrate the difference in implementing a collection via the IEnumerable interface, and using yield return in an iterator block.

With this first example, you can see that there is a lot of plumbing that you have to write. You have to implement a class that derives from IEnumerable, and another class that derives from IEnumerator. The GetEnumerator() method in MyListOfStrings returns an instance of the class that derives from IEnumerator. But the end result is that you can iterate through the collection using foreach.

public class MyListOfStrings : IEnumerable
{
private string[] _strings;
public MyListOfStrings(string[] sArray)
{
_strings = new string[sArray.Length];

for (int i = 0; i < sArray.Length; i++)
{
_strings[i] = sArray[i];
}
}

public IEnumerator GetEnumerator()
{
return new StringEnum(_strings);
}
}

public class StringEnum : IEnumerator
{
public string[] _strings;

// Enumerators are positioned before the first element
// until the first MoveNext() call.
int position = -1;

public StringEnum(string[] list)
{
_strings = list;
}

public bool MoveNext()
{
position++;
return (position < _strings.Length);
}

public void Reset()
{
position = -1;
}

public object Current
{
get
{
try
{
Console.WriteLine("about to return {0}", _strings[position]);
return _strings[position];
}
catch (IndexOutOfRangeException)
{
throw new InvalidOperationException();
}
}
}
}

class Program
{
static void Main(string[] args)
{
string[] sa = new[] {
"aaa",
"bbb",
"ccc"
};

MyListOfStrings p = new MyListOfStrings(sa);

foreach (string s in p)
Console.WriteLine(s);
}
}

Using the yield return keywords, the equivalent in functionality is as follows. This code is attached to this page:

class Program
{
public static IEnumerable MyListOfStrings(string[] sa)
{
foreach (var s in sa)
{
Console.WriteLine("about to yield return");
yield return s;
}
}

static void Main(string[] args)
{
string[] sa = new[] {
"aaa",
"bbb",
"ccc"
};

foreach (string s in MyListOfStrings(sa))
Console.WriteLine(s);
}
}

As you can see, this is significantly easier.

This isn't as magic as it looks. When you use the yield contextual keyword, what happens is that the compiler automatically generates an enumerator class that keeps the current state of the iteration. This class has four potential states: before, running, suspended, and after. This class has Reset and MoveNext methods, and a Current property. When you iterate through a collection that is implemented using yield return, you are moving from item to item in the enumerator using the MoveNext method. The implementation of iterator blocks is fairly involved. A technical discussion of iterator blocks can be found in the C# specifications.

Yield return is very important when implementing our own query operators (which we will want to do sometimes).

There is no counterpart to the yield keyword in Visual Basic 9.0, so if you are implementing a query operator in Visual Basic 9.0, you must use the approach where you implement IEnumerable and IEnumerator.

One of the important design philosophies about the LINQ and LINQ to XML technologies is that they should not break existing programs. Adding new keywords will break existing programs if the programs happen to use the keyword in a context that would be invalid. Therefore, some keywords are added to the language as contextual keywords. This means that when the keyword is encountered at specific places in the program, it is interpreted as a keyword, whereas when the keyword is encountered elsewhere, it may be interpreted as an identifier. Yield is one of these keywords. When it is encountered before a return or break keyword, it is interpreted by the compiler as appropriate, and the new semantics are applied. If the program was written in C# 1.0 or 1.1, and it contained an identifier named yield, then the identifier continues to be parsed correctly by the compiler, and the program is not made invalid by the language extensions.

Extension Methods

Extension methods are special methods that, while they are not part of a data type, you can call them as though they were part of the data type. Extension methods are a feature of C# 3.0.

Writing Extension Methods
To write an extension method, you do the following:

· Define a public static class.
· Define a public static method in the class where the first argument is the data type for which you want the extension method.
· Use the this keyword on the first argument to your public static method. The this keyword denotes the method as an extension method.

This special syntax is actually simply a natural extension of what you do normally when you want to make a utility method for a class that you don't want to (or can't) extend. A common pattern when you want to write a utility method is to define a static method that takes an instance of the class as the first parameter. This is exactly what an extension method is - the only difference is that you can then write a call to the method as though it were part of the class, and the compiler will find and bind to the extension method.

Note that the following class does not define a PrintIt method:

public class MyClass
{
public int IntField;
public MyClass(int arg)
{
IntField = arg;
}
}

This static class defines an extension method for the MyClass class:

public static class MyClassExtension
{
public static void PrintIt(this MyClass arg)
{
Console.WriteLine("IntField:{0}", arg.IntField);
}
}

You can now call the PrintIt method as though it were part of the class.

MyClass mc = new MyClass(10);
mc.PrintIt();

When you run this code, it outputs:

IntField:10

But the really cool thing is that you can write extension methods for a type that in and of itself, can't contain methods. The most important example of this is that you can write extension methods for interfaces. You could also write an extension method for an abstract class.

You can define extension methods for parameterized types as well as non-parameterized types. The standard query operators are almost all extension methods that are defined on IEnumerable.

Note that you can define your own extension methods for IEnumerable. When there isn't a standard query operator that does exactly what you want, you can write your own extension method. This is a powerful technique that adds expressiveness to your code.

When you are writing pure functional code, extension methods are important. There are times that writing extension methods on IEnumerable is the way that you want do things. We'll be using this technique sometimes when writing FP code.

Extension Methods are Vital for LINQ
Extension methods are an integral part of LINQ. Consider the following code that contains a LINQ query expression:

int[] source = new[] { 3, 6, 4, 8, 9, 5, 3, 1, 7, 0 };

IEnumerable query =
from i in source
where i >= 5
select i * 2;

foreach (var i in query)
Console.WriteLine(i);

This code is functionally identical to this:

int[] source = new[] { 3, 6, 4, 8, 9, 5, 3, 1, 7, 0 };

IEnumerable query =
source.Where(i => i >= 5)
.Select(i => i * 2);

foreach (var i in query)
Console.WriteLine(i);

In fact, you can see that there is a direct translation from the query expression to the same query that is expressed in method notation. I believe that in early versions of the C# 3.0 compiler, this translation was implemented almost like a macro. But in any case, queries that are implemented via method syntax rely, of course, on the extension methods that are included with the .NET framework, and hence query expressions also rely on them. It is the ability to write extension methods for generic interfaces that enables queries.

Lambda Expressions

A lambda expression is an anonymous function that can contain expressions and statements, and can be used to create delegates or expression tree types.
A lambda anonymous methods, that allows you to declare your method code inline instead of with a delegate function.

All lambda expressions use the lambda operator =>, which is read as "goes to". The left side of the lambda operator specifies the input parameters (if any) and the right side holds the expression or statement block. The lambda expression x => x * x is read "x goes to x times x."

(int x) => x + 1 // explicitly typed parameter
(y,z) => return y * z; // implicitly typed parameter

Note:
1. Lambdas are not allowed on the left side of the is or as operator.

To show lambda expressions in context, consider the problem where you have an array with 10 digits in it, and you want to filter for all digits greater than 5. In this case, you can use the Where extension method, passing a lambda expression as an argument to the Where method:

int[] source = new[] { 3, 8, 4, 6, 1, 7, 9, 2, 4, 8 };

foreach (int i in source.Where(x => x > 5))
Console.WriteLine(i);

First, a quick review of delegates:

Defining, Creating, and Using a Delegate
In C#, a delegate is a data structure that refers to either a static method, or an object and an instance method of its class. When you initialize a delegate, you initialize it with either a static method, or a class instance and an instance method.

The following code shows the definition of a delegate and a method that can be used to initialize the delegate:

// Defines a delegate that takes an int and returns an int
public delegate int ChangeInt(int x);

// Define a method to which the delegate can point
static public int DoubleIt(int x)
{
return x * 2;
}

Now, you can create and initialize an instance of the delegate, and then call it:

ChangeInt myDelegate = new ChangeInt(DelegateSample.DoubleIt);
Console.WriteLine("{0}", myDelegate(5));

This, as you would expect, writes 10 to the console.

Using an Anonymous Method
With C# 2.0, anonymous methods allow you to write a method and initialize a delegate in place:

ChangeInt myDelegate = new ChangeInt(
delegate(int x)
{
return x * 2;
}
);
Console.WriteLine("{0}", myDelegate(5));

Using a Lambda Expression
With Lambda expressions, the syntax gets even terser:

ChangeInt myDelegate = x => x * 2;
Console.WriteLine("{0}", myDelegate(5));

This lambda expression is an anonymous method that takes one argument x, and returns x * 2. In this case, the type of x and the type that the lambda returns are inferred from the type of the delegate to which the lambda is assigned.

If you wanted to, you could have specified the type of the argument, as follows:

ChangeInt myDelegate = (int x) => x * 2;
Console.WriteLine("{0}", myDelegate(5));

Using a Lambda with Two Arguments
When using the Standard Query Operators, on occasion, you need to write a lambda expression that takes two arguments.

If you have a delegate that takes two arguments:

// Defines a delegate that takes two ints and returns an int
public delegate int MultiplyInts(int arg, int arg2);

You can declare and initialize a delegate:

MultiplyInts myDelegate = (a, b) => a * b;
Console.WriteLine("{0}", myDelegate(5, 2));

Statement Lambda Expressions
You can write a more complicated lambda expression using statements, enclosing the statements in braces. If you use this syntax, you must use the return statement, unless the lambda returns void:

int[] source = new[] { 3, 8, 4, 6, 1, 7, 9, 2, 4, 8 };

foreach (int i in source.Where(
x =>
{
if (x <= 3)
return true;
else if (x >= 7)
return true;
return false;
}
))
Console.WriteLine(i);

Sometimes developers wonder how to pronounce the => token.

If the lambda expression is a predicate, expressing some condition: c => c.State == "WA" then the => can be spoken as "such that". In this example, you could say "c such that c dot state equals Washington". If the lambda expression is a projection, returning a new type: c => new XElement("CustomerID", c.CustomerID); then the => can be spoken as "becomes". In the above example, you could say "c becomes new XElement with a name of CustomerID and its value is c dot CustomerID". Or "maps to", or "evaluate to", as suggested in the comments below. But most often, I just say "arrow". J

A quick note: predicates are simply boolean expressions that are passed to some method that will use the boolean expression to filter something. A lambda expression used for projection takes one type, and returns a different type. More on both of these concepts later.

The Func Delegate Types
The framework defines a number of parameterized delegate types:

public delegate TR Func();
public delegate TR Func(T0 a0);
public delegate TR Func(T0 a0, T1 a1);
public delegate TR Func(T0 a0, T1 a1, T2 a2);
public delegate TR Func(T0 a0, T1 a1, T2 a2, T3 a3);

In the above delegate types, notice that if there is only one type parameter, it is the return type of the delegate. If there are two type parameters, the first type parameter is the type of the one and only argument, and the second type is the return type of the delegate, and so on. Many of the standard query operators (which are just methods that you call) take as an argument a delegate of one of these types.

Expression Trees
Lambda expressions can also be used as expression trees. This is an interesting topic, but is not part of this discussion on writing pure functional transformations.

InternalsVisibleToAttribute (C#)

Today I am going to give an idea on internal visible attribute which was introduced in .NET 2.0. This feature is better than accessing non-public member using reflection. Before starting this topic it is better to take a look on the different access modifier available in C# which will give us a clear picture on this.

Access Modifiers:
-Public
The type or member can be accessed by any other code in the same assembly or another assembly that references it.

-Private
The type or member can only be accessed by code in the same class or struct.

-Protected
The type or member can only be accessed by code in the same class or struct, or in a derived class.

-Internal
The type or member can be accessed by any code in the same assembly, but not from another assembly.

-Protected Internal
The type or member can be accessed by any code in the same assembly, or by any derived class in another assembly.

Friend Assemblies:

The friend assemblies feature allows you to access internal members; private types and private members will remain inaccessible. To give an assembly (assembly B) access to another assembly's (assembly A's) internal types and members, use the InternalsVisibleToAttribute attribute in assembly A.

It is better to give an example to understand better:
1. Add a class libarary (with name 'A')
Code will looks:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace A
{

public class Class1
{
private int num1 = 1;
public int num2 = 2;
protected int num3 = 3;
internal protected int num4 = 4;
int num5 = 5;
}
}

2. Add another console application (With name 'B') bellow
3. Add the project reference 'A' to project 'B' and add the below code to program.cs file
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using A;

namespace B
{
class Program
{
static void Main(string[] args)
{
Class1 obj = new Class1();
int n1 = obj.num1; //Compiler error
int n2 = obj.num2; //No error
int n3 = obj.num3; //Compiler error
int n4 = obj.num4; //Compiler error
int n5 = obj.num5; //Compiler error
}
}
}

3. Compile the both the project and watch carefully. We are able to access only num2 as its access modifier is public. Don't worry we will do some this which will allow to access num4
4. Add the below code line to AssemblyInfo.cs on project 'A'. which will tell the 'A' to allow 'B' to access his protected internal members
[assembly: InternalsVisibleTo("B")]
5. Now recompile the project and you will see:
int n1 = obj.num1; //Compiler error
int n2 = obj.num2; //No error
int n3 = obj.num3; //Compiler error
int n4 = obj.num4; //No error
int n5 = obj.num5; //Compiler error



We can also provide the public token key whiling specifying the assembly name to the assemblyinfo.cs. To do this you may need following:

This example shows how to make internal types and members available for assemblies that have strong names.

To generate the keyfile and display public key, use the following sequence of sn.exe commands (for more information, see Strong Name Tool (Sn.exe)):

sn -k friend_assemblies.snk // Generate strong name key

sn -p friend_assemblies.snk key.publickey // Extract public key from key.snk into key.publickey

sn -tp key.publickey // Display public key stored in file'key.publickey

Pass the keyfile to the compiler with /keyfile.

Example:
[assembly:InternalsVisibleTo("B, PublicKey=002400000480000094…")]

Enjoy..:):)

Full-Text Search (SQL Server)

SQL Server 2008 provides the functionality for applications and users to issue full-text queries against character-based data in SQL Server tables. Before full-text queries can be run on a given table, the database administrator must create a full-text index on the table. The full-text index includes one or more character-based columns in the table. These columns can have any of the following data types: char, varchar, nchar, nvarchar, text, ntext, image, xml, varbinary, or varbinary(max). Each full-text index indexes one or more columns from the base table, and each column can have a specific language. Beginning in SQL Server 2008, full-text search supports more than 50 diverse languages, such as English, Spanish, Chinese, Japanese, Arabic, Bengali, and Hindi. For a complete list of the supported full-text languages, see sys.fulltext_languages (Transact-SQL).

For each supported language, SQL Server provides language-specific linguistic components, including a word breaker and stemmer and an empty thesaurus file. For each full-text language, SQL Server also provides a file in which you can optionally define language-specific synonyms to extend the scope of search queries (a thesaurus file). In addition, beginning in SQL Server 2008, a system stoplist is provided. To support specific language or business scenarios, you can alter the system stoplist by adding and removing stopwords (also known as noise words), and you can create additional stoplists as needed.

For writing full-text queries, SQL Server provides a set of full-text predicates (CONTAINS and FREETEXT) and rowset-valued functions (CONTAINSTABLE and FREETEXTTABLE). Using these, applications and users can perform a variety of types of full-text searches, such as searching on a single word or phrase (and optionally ranking the result set), searching on a word or phrase close to another word or phrase, or searching on synonymous forms of a specific word.

Note: Full-text search is an optional component of the SQL Server Database Engine. For more information, see Installing SQL Server 2008.

How to Configure a Database for Full-Text Searching
For any scenario, a database administrator performs the following basic steps to configure table columns in a database for full-text search:

1. Create a full-text catalog.

2. On each table that you want to search, create a full-text index by:

a. Identify each text columns that you want to include in the full-text index.

b. If a given column contains documents stored as binary data (varbinary, varbinary(max), or image data), you must specify a table column (the type column) that identifies the type of each document in the column being indexed.

c. Specify the language that you want full-text search to use on the documents in the column.

d. Choose the change-tracking mechanism that you want to use on the full-text index to track changes in the base table and its columns.

Full-text search supports multiple languages through the use of the following linguistic components: word breakers and stemmers, stoplists that contain stopwords (also known as noise words), and thesaurus files. Thesaurus files and, in some cases, stoplists require configuration by a database administrator. A given thesaurus file supports all full-text indexes that use the corresponding language, and a given stoplist can be associated with as many full-text indexes as you want.

After the columns have been added to a full-text index, applications and users can run full-text queries on the text in the columns. These queries can search for any of the following:

1. One or more specific words or phrases (simple term)

2. A word or a phrase where the words begin with specified text (prefix term)

3. Inflectional forms of a specific word (generation term)

4. A word or phrase close to another word or phrase (proximity term)

5. Synonymous forms of a specific word (thesaurus)

6. Words or phrases using weighted values (weighted term)

Full-text queries all use a small set of Transact-SQL predicates (CONTAINS and FREETEXT) and functions (CONTAINSTABLE and FREETEXTTABLE). However, the search goals of a given business scenario influence the exact structure of the full-text queries;

for example:

a. e-business—searching for a product on a website:
SELECT product_id FROM products WHERE CONTAINS(product_description, ”Snap Happy 100EZ” OR FORMSOF(THESAURUS,’Snap Happy’) OR ‘100EZ’) AND product_cost<200

b. Recruitment scenario—searching for job candidates that have experience working with SQL Server:
SELECT candidate_name,SSN FROM candidates WHERE CONTAINS(candidate_resume,”SQL Server”) AND candidate_division =DBA

Partitioned Views in SQL Server

Partitioned views allow the data in a large table to be split into smaller member tables. The data is partitioned between the member tables based on ranges of data values in one of the columns. The data ranges for each member table are defined in a CHECK constraint specified on the partitioning column. A view that uses UNION ALL to combine selects of all the member tables into a single result set is then defined. When SELECT statements referencing the view specify a search condition on the partition column, the query optimizer uses the CHECK constraint definitions to determine which member table contains the rows.

For example, a sales table that records sales for 1998 has been partitioned into 12 member tables, one for each month. Each member table has a constraint defined on the OrderMonth column:

CREATE TABLE May1998sales
(OrderID INT,
CustomerID INT NOT NULL,
OrderDate DATETIME NULL
CHECK (DATEPART(yy, OrderDate) = 1998),
OrderMonth INT
CHECK (OrderMonth = 5),
DeliveryDate DATETIME NULL
CHECK(DATEPART(mm, DeliveryDate) = 5)
CONSTRAINT OrderIDMonth PRIMARY KEY(OrderID, OrderMonth)
)

The application that populates May1998sales must make sure that all rows have 5 in the OrderMonth column and that the order date specifies a date in May 1998. This is enforced by the constraints defined on the table.

A view is then defined that uses UNION ALL to select the data from all 12 member tables as a single result set:

CREATE VIEW Year1998Sales
AS
SELECT * FROM Jan1998Sales
UNION ALL
SELECT * FROM Feb1998Sales
UNION ALL
SELECT * FROM Mar1998Sales
UNION ALL
SELECT * FROM Apr1998Sales
UNION ALL
SELECT * FROM May1998Sales
UNION ALL
SELECT * FROM Jun1998Sales
UNION ALL
SELECT * FROM Jul1998Sales
UNION ALL
SELECT * FROM Aug1998Sales
UNION ALL
SELECT * FROM Sep1998Sales
UNION ALL
SELECT * FROM Oct1998Sales
UNION ALL
SELECT * FROM Nov1998Sales
UNION ALL
SELECT * FROM Dec1998Sales

For example, the following SELECT statement queries for information about specific months.

SELECT *
FROM Year1998Sales
WHERE OrderMonth IN (5,6) AND CustomerID = 64892

The SQL Server query optimizer recognizes that the search condition in this SELECT statement references only rows in the May1998Sales and Jun1998Sales tables. Therefore, it limits its search to those tables.

To perform updates on a partitioned view, the partitioning column must be a part of the primary key of the base table. If a view is not updatable, you can create an INSTEAD OF trigger on the view that allows updates. You should design error handling into the trigger to make sure that no duplicate rows are inserted. For an example of an INSTEAD OF trigger designed on a view, see Designing INSTEAD OF Triggers.

CHECK constraints are not needed for the partitioned view to return the correct results. However, if the CHECK constraints have not been defined, the query optimizer must search all the tables instead of only those that cover the search condition on the partitioning column. Without the CHECK constraints, the view operates like any other view with UNION ALL. The query optimizer cannot make any assumptions about the values stored in different tables and it cannot skip searching the tables that participate in the view definition.

If all the member tables referenced by a partitioned view are on the same server, the view is a local partitioned view. If the member tables are on multiple servers, the view is a distributed partitioned view. Distributed partitioned views can be used to spread the database processing load of a system across a group of servers. For more information, see Federated Database Servers.

Partitioned views make it easier to maintain the member tables independently. For example, you can do the following at the end of a period:

1. The definition of the partitioned view for current results can be changed to add the newest period and drop the oldest period.

2. The definition of the partitioned view for past results can be changed to add the period just dropped from the current results view. The past results view can also be updated to remove and archive the oldest period it covers.

When you insert data into the partitioned views, the sp_executesql system stored procedure can be used to create INSERT statements with execution plans that have a significant chance of being reused in systems with many concurrent users.

Note:Bulk importing into a partitioned view is unsupported by both the bcp command and the BULK INSERT and INSERT ... SELECT * FROM OPENROWSET(BULK...) statements. However, you can insert multiple rows into a partitioned view by using an INSERT statement

Common Table Expressions (Recursive Queries)

A common table expression (CTE) provides the significant advantage of being able to reference itself, thereby creating a recursive CTE. A recursive CTE is one in which an initial CTE is repeatedly executed to return subsets of data until the complete result set is obtained.

A query is referred to as a recursive query when it references a recursive CTE. Returning hierarchical data is a common use of recursive queries, for example: Displaying employees in an organizational chart, or data in a bill of materials scenario in which a parent product has one or more components and those components may, in turn, have subcomponents or may be components of other parents.

A recursive CTE can greatly simplify the code required to run a recursive query within a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. In earlier versions of SQL Server, a recursive query usually requires using temporary tables, cursors, and logic to control the flow of the recursive steps. For more information about common table expressions, see Using Common Table Expressions.


Structure of a Recursive CTE
The structure of the recursive CTE in Transact-SQL is similar to recursive routines in other programming languages. Although a recursive routine in other languages returns a scalar value, a recursive CTE can return multiple rows.

A recursive CTE consists of three elements:

1. Invocation of the routine.
The first invocation of the recursive CTE consists of one or more CTE_query_definitions joined by UNION ALL, UNION, EXCEPT, or INTERSECT operators. Because these query definitions form the base result set of the CTE structure, they are referred to as anchor members.

CTE_query_definitions are considered anchor members unless they reference the CTE itself. All anchor-member query definitions must be positioned before the first recursive member definition, and a UNION ALL operator must be used to join the last anchor member with the first recursive member.

2. Recursive invocation of the routine.
The recursive invocation includes one or more CTE_query_definitions joined by UNION ALL operators that reference the CTE itself. These query definitions are referred to as recursive members.

3. Termination check.
The termination check is implicit; recursion stops when no rows are returned from the previous invocation.

Example
The following example shows the semantics of the recursive CTE structure by returning a hierarchical list of employees, starting with the highest ranking employee, in the Adventure Works Cycles company. The statement that executes the CTE limits the result set to employees in the Research and Development Group. A walkthrough of the code execution follows the example.

USE AdventureWorks;
GO
WITH DirectReports (ManagerID, EmployeeID, Title, DeptID, Level)
AS
(
-- Anchor member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
0 AS Level
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
WHERE ManagerID IS NULL
UNION ALL
-- Recursive member definition
SELECT e.ManagerID, e.EmployeeID, e.Title, edh.DepartmentID,
Level + 1
FROM HumanResources.Employee AS e
INNER JOIN HumanResources.EmployeeDepartmentHistory AS edh
ON e.EmployeeID = edh.EmployeeID AND edh.EndDate IS NULL
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)
-- Statement that executes the CTE
SELECT ManagerID, EmployeeID, Title, Level
FROM DirectReports
INNER JOIN HumanResources.Department AS dp
ON DirectReports.DeptID = dp.DepartmentID
WHERE dp.GroupName = N'Research and Development' OR Level = 0;
GO