SQL to LINQ Cheat Sheet

If you are already working with SQL and are familiar with SQL queries then you may find you at time are thinking of converting SQL syntax to LINQ syntax when writing LINQ. Following cheat sheet should help you with some of the common queries

SQL	LINQ	Lambda
SELECT * FROM HumanResources.Employee	from e in Employees select e	Employees .Select (e => e)
SELECT e.LoginID, e.JobTitle FROM HumanResources.Employee AS e	from e in Employees select new {e.LoginID, e.JobTitle}	Employees .Select ( e => new { LoginID = e.LoginID, JobTitle = e.JobTitle } )
SELECT e.LoginID AS ID, e.JobTitle AS Title FROM HumanResources.Employee AS e	from e in Employees select new {ID = e.LoginID, Title = e.JobTitle}	Employees .Select ( e => new { ID = e.LoginID, Title = e.JobTitle } )
SELECT DISTINCT e.JobTitle FROM HumanResources.Employee AS e	(from e in Employees select e.JobTitle).Distinct()	Employees .Select (e => e.JobTitle) .Distinct ()
SELECT e.* FROM HumanResources.Employee AS e WHERE e.LoginID = ‘test’	from e in Employees where e.LoginID == "test" select e	Employees .Where (e => (e.LoginID == "test"))
SELECT e.* FROM HumanResources.Employee AS e WHERE e.LoginID = ‘test’ AND e.SalariedFlag = 1	from e in Employees where e.LoginID == "test" && e.SalariedFlag select e	Employees .Where (e => ((e.LoginID == "test") && e.SalariedFlag))
SELECT e.* FROM HumanResources.Employee AS e WHERE e.VacationHours >= 2 AND e.VacationHours <= 10	from e in Employees where e.VacationHours >= 2 && e.VacationHours <= 10 select e	Employees .Where (e => (((Int32)(e.VacationHours) >= 2) && ((Int32)(e.VacationHours) <= 10)))
SELECT e.* FROM HumanResources.Employee AS e ORDER BY e.NationalIDNumber	from e in Employees orderby e.NationalIDNumber select e	Employees .OrderBy (e => e.NationalIDNumber)
SELECT e.* FROM HumanResources.Employee AS e ORDER BY e.HireDate DESC, e.NationalIDNumber	from e in Employees orderby e.HireDate descending, e.NationalIDNumber select e	Employees .OrderByDescending (e => e.HireDate) .ThenBy (e => e.NationalIDNumber)
SELECT e.* FROM HumanResources.Employee AS e WHERE e.JobTitle LIKE ‘Vice%’ OR SUBSTRING(e.JobTitle, 0, 3) = ‘Pro’	from e in Employees where e.JobTitle.StartsWith("Vice") || e.JobTitle.Substring(0, 3) == "Pro" select e	Employees .Where (e => (e.JobTitle.StartsWith ("Vice") || (e.JobTitle.Substring (0, 3) == "Pro")))
SELECT SUM(e.VacationHours) FROM HumanResources.Employee AS e		Employees.Sum(e => e.VacationHours);
SELECT COUNT(*) FROM HumanResources.Employee AS e		Employees.Count();
SELECT SUM(e.VacationHours) AS TotalVacations, e.JobTitle FROM HumanResources.Employee AS e GROUP BY e.JobTitle	from e in Employees group e by e.JobTitle into g select new {JobTitle = g.Key, TotalVacations = g.Sum(e => e.VacationHours)}	Employees .GroupBy (e => e.JobTitle) .Select ( g => new { JobTitle = g.Key, TotalVacations = g.Sum (e => (Int32)(e.VacationHours)) } )
SELECT e.JobTitle, SUM(e.VacationHours) AS TotalVacations FROM HumanResources.Employee AS e GROUP BY e.JobTitle HAVING e.COUNT(*) > 2	from e in Employees group e by e.JobTitle into g where g.Count() > 2 select new {JobTitle = g.Key, TotalVacations = g.Sum(e => e.VacationHours)}	Employees .GroupBy (e => e.JobTitle) .Where (g => (g.Count () > 2)) .Select ( g => new { JobTitle = g.Key, TotalVacations = g.Sum (e => (Int32)(e.VacationHours)) } )
SELECT * FROM Production.Product AS p, Production.ProductReview AS pr	from p in Products from pr in ProductReviews select new {p, pr}	Products .SelectMany ( p => ProductReviews, (p, pr) => new { p = p, pr = pr } )
SELECT * FROM Production.Product AS p INNER JOIN Production.ProductReview AS pr ON p.ProductID = pr.ProductID	from p in Products join pr in ProductReviews on p.ProductID equals pr.ProductID select new {p, pr}	Products .Join ( ProductReviews, p => p.ProductID, pr => pr.ProductID, (p, pr) => new { p = p, pr = pr } )
SELECT * FROM Production.Product AS p INNER JOIN Production.ProductCostHistory AS pch ON p.ProductID = pch.ProductID AND p.SellStartDate = pch.StartDate	from p in Products join pch in ProductCostHistories on new {p.ProductID, StartDate = p.SellStartDate} equals new {pch.ProductID, StartDate = pch.StartDate} select new {p, pch}	Products .Join ( ProductCostHistories, p => new { ProductID = p.ProductID, StartDate = p.SellStartDate }, pch => new { ProductID = pch.ProductID, StartDate = pch.StartDate }, (p, pch) => new { p = p, pch = pch } )
SELECT * FROM Production.Product AS p LEFT OUTER JOIN Production.ProductReview AS pr ON p.ProductID = pr.ProductID	from p in Products join pr in ProductReviews on p.ProductID equals pr.ProductID into prodrev select new {p, prodrev}	Products .GroupJoin ( ProductReviews, p => p.ProductID, pr => pr.ProductID, (p, prodrev) => new { p = p, prodrev = prodrev } )
SELECT p.ProductID AS ID FROM Production.Product AS p UNION SELECT pr.ProductReviewID FROM Production.ProductReview AS pr	(from p in Products select new {ID = p.ProductID}).Union( from pr in ProductReviews select new {ID = pr.ProductReviewID})	Products .Select ( p => new { ID = p.ProductID } ) .Union ( ProductReviews .Select ( pr => new { ID = pr.ProductReviewID } ) )
SELECT TOP (10) * FROM Production.Product AS p WHERE p.StandardCost < 100	(from p in Products where p.StandardCost < 100 select p).Take(10)	Products .Where (p => (p.StandardCost < 100)) .Take (10)
SELECT * FROM [Production].[Product] AS p WHERE p.ProductID IN( SELECT pr.ProductID FROM [Production].[ProductReview] AS [pr] WHERE pr.[Rating] = 5 )	from p in Products where (from pr in ProductReviews where pr.Rating == 5 select pr.ProductID).Contains(p.ProductID) select p	Products .Where ( p => ProductReviews .Where (pr => (pr.Rating == 5)) .Select (pr => pr.ProductID) .Contains (p.ProductID) )

Rebuild all the indexes of a table & Indexed views in a Database

Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages. When ALL is specified, all indexes on the table/view are dropped and rebuilt in a single transaction. FOREIGN KEY constraints do not have to be dropped in advance.

In general, rebuilding or reorganizing small indexes often does not reduce fragmentation. The pages of small indexes are stored on mixed extents. Mixed extents are shared by up to eight objects, so fragmentation in a small index might not be reduced after reorganizing or rebuilding the index.

To REBUILD all the indexes, the following syntax is sufficient –

ALTER INDEX ALL ON TableName REBUILD WITH (FILLFACTOR = Any number between 0 to 100);

Before attempting to REBUILD, we should first find the level of fragmentation using the below tsql –

SELECT * FROM sys.dm_db_index_physical_stats(db_id(), object_id('tablename'), NULL, NULL , 'DETAILED')

GO

Microsoft Recommends

REBUILD IF > 30% framgmented

REORGANIZE IF >5 % and < 30% framgmented

I have prepared the following TSQL to rebuild all the indexes on the all the tables and all the indexed views of the database in use.

USE DatabaseName

GO

DECLARE @tsql NVARCHAR(MAX) 

DECLARE @fillfactor INT

SET @fillfactor = 90

SELECT @tsql =

  STUFF(( SELECT DISTINCT

           ';' + 'ALTER INDEX ALL ON ' + o.name + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

          FROM

           sysobjects o

          INNER JOIN sysindexes i

           ON o.id = i.id

          WHERE

           o.xtype IN ('U','V')

           AND i.name IS NOT NULL

          FOR XML PATH('')), 1,1,'')

--PRINT @tsql         

EXEC sp_executesql @tsql

Automatic Properties, Object Initializers and Collection Initializers

Introduction

In c# 3.0 and later versions many new features are available but some people are not aware of them. There are some new features explained below In C# 3.0 and later.

Auto-implemented properties:

Auto-implemented properties make property-declaration more concise when no additional logic is required in the property accessors. They also enable client code to create objects. When you declare a property as shown in the following example, the compiler creates a private, anonymous backing field that can only be accessed through the property's get and set accessors.

Let’s see the old writing method.

public class Person

{

 private string _firstName;

 private string _lastName;

 private int _age;

 public string FirstName

 {

  get

  { return _firstName; }

  set

  { _firstName = value; }

 }

 public string LastName

 {

  get

  { return _lastName; }

  set

  { _lastName = value; }

 }

 public int Age

 {

  get

  { return _age; }

  set

  { _age = value; }

 }

}

Now we can see the enhanced implementation.

public class Person

{

 public string FirstName

 {

  get;

  set;

 }

 public string LastName

 {

  get;

  set;

 }

 public int Age

 {

  get;

  set;

 }

}

Note: This way is possible only when you don’t want to write any logical implementation in the getter/setter part. If you want to write implementation then you must go through the old method.

Object & Collection Initializers:

Object/Collection initializers, let you assign values to any accessible fields or properties of an object at creation time without having to explicitly invoke a constructor.

Considering above example now we initialize of an object with the old method.

Person person1 = new Person();

person1.FirstName = "Munavvar";

person1.LastName = "Husein";

person1.Age = 25;

Now in place of this we can write it like,

Person person1 = new Person

{

 FirstName = "Munavvar",

 LastName = "Husein",

 Age = 25

};

In the case of Collections same thing would be done as under. Considering above examle to make a list with old method.

List<Person> lstPerson = new List<Person>();

Person persons = new Person();

persons.FirstName = "Munavvar";

persons.LastName = "Husein";

persons.Age = 25;

lstPerson.Add(persons);

persons = new Person();

persons.FirstName = "Rikin";

persons.LastName = "Patel";

persons.Age = 26;

lstPerson.Add(persons);

Now in place of this we can write it like,

List<Person> lstPerson = new List<Person>;

{

 new Person

 {

  FirstName = "Munavvar",

  LastName = "Husein",

  Age = 25

 },

 new Person

 {

  FirstName = "Rikin",

  LastName = "Patel",

  Age = 26

 }

};

Conclusion:

As per enhanced feature we can just reduse the burdon of typing extra statements with no loss of performance.

Yield Keyword in C#

Introduction:

The yield keyword was new feature in C# 2.0 and is used to simplify the implementation of enumeration in custom classes.

Following are two forms of the yield statement.

1. yield return ;

2. yield break;

· In a yield return statement, expression is evaluated and returned as a value to the enumerator object; expression has to be implicitly convertible to the yield type of the iterator.

· In a yield break statement, control is unconditionally returned to the caller of the iterator,

Problem:

The yield keyword simplifies the implementation of iterable collections, but it also allows us to move beyond collections and into result sets. Using the yield keyword, we can convert calculated sequences into collections.

Let me give an example. Let’s say that I am calculating the sequence of square roots for all numbers.

Assuming for the moment that we do create an infinite array, let’s look at how those numbers would be generated without using the yield keyword.

There would be a piece of code that would call the algorithm to generate the sequence of numbers. The sequence of numbers would be added to an array, which is returned to the calling code when the algorithm has completed. Yet we are calculating an infinite sequence of numbers, meaning that the algorithm will never end and the array will never be complete.

Of course, in reality, algorithms do end, and arrays do become complete. But the example illustrates that if you were to generate a collection that could be iterated, you must first generate the collection and then iterate the collection. This would mean you first allocate the space for an array and then fill the array, resulting in a not-as-efficient solution. The yield keyword is more efficient, because it allows a calculation to generate numbers on the fly.

Implementation:

yield return statement:

In the following example, the yield statement is used inside an iterator block, which is the method ComputePower(int number, int power). When the Power method is invoked, it returns an enumerable object that contains the powers of a number.

using System;

using System.Collections.Generic;

public class Program

{

static void Main()

{

// Compute two with the exponent of 30.

foreach (int value in ComputePower(2, 30))

{

Console.Write(value);

Console.Write(" ");

}

Console.WriteLine();

}

public static IEnumerable<int> ComputePower(int number, int exponent)

{

int exponentNum = 0;

int numberResult = 1;

// Continue loop until the exponent count is reached.

while (exponentNum < exponent)

{

// Multiply the result.

numberResult *= number;

exponentNum++;

// Return the result with yield.

yield return numberResult;

}

}

}

Output:

2 4 8 16 32 64 128 256 512 1024 2048 4096 8192 16384 32768 65536 131072 262144 524288

1048576 2097152 4194304 8388608 16777216 33554432 67108864 134217728 268435456 536870912 107374182

yield break statement:

If inside this loop I want to abort the iteration and return, I do this through the yield break. The yield break will do more than a normal break, as it will return from the method, and not only from the for-loop.

E.g.: If I have to consecutive for-loops in the same method, both using yield, and I do yield break in the first, the second for-loop will never be executed:

public static IEnumerable<string> GetMoreResults()

{

for (int i = 0; i < 20; i++)

{

yield return "Value " + i;

yield break;

}

//Do something else

for (int i = 0; i < 20; i++)

{

yield return "Another value " + i;

}

}

Here I will only get one value returned in my IEnumerable, as the yield break will end the method execution.

Limitation:

· Unsafe blocks are not allowed.

· Parameters to the method, operator, or accessor cannot be ref or out.

· A yield return statement cannot be located anywhere inside a try-catch block. It can be located in a try block if the try block is followed by a finally block.

· A yield break statement may be located in a try block or a catch block but not a finally block.

· A yield statement cannot appear in an anonymous method.

Ref:

http://en.csharp-online.net/CSharp_Coding_Solutions%E2%80%94What_Does_Yield_Keyword_Generate

http://iserialized.com/a-quick-start-guide-to-yield-return-and-yield-break/

http://msdn.microsoft.com/en-us/library/9k7k7cf0(v=VS.100).aspx