Archive

Posts Tagged ‘LINQ’

SQL to LINQ Cheat Sheet

September 27, 2009 2 comments

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)
   )

 

Also, here is an excellent LINQ query comprehension diagram http://www.albahari.com/nutshell/linqsyntax.emf

Tags: , , ,

Difference between LINQ to SQL and the Entity Framework

June 30, 2008 9 comments

I was browsing through the usual newsgroups I visit and came up with this post about LINQ to SQL vs EDM and thought I should share it here.

LINQ to SQL and the Entity Framework have a lot in common, but each have features targeting different scenarios.

LINQ to SQL has features targeting "Rapid Development" against a Microsoft SQL Server database. Think of LINQ to SQL as allowing you to have a strongly-typed view of your existing database schema. LINQ to SQL supports a direct, 1:1 mapping of your existing database schema to classes; a single table can be mapped to a single inheritance hierarchy (i.e., a table can contain persons, customers, and employees) and foreign keys can be exposed as strongly-typed relationships.  You can build LINQ queries over tables/views/table valued functions and return results as strongly typed objects, and call stored procedures that return strongly typed results through strongly typed methods.  A key design principle of LINQ to SQL is that it "just work" for the common cases; so, for example, if you access a collection of orders through the Orders property of a customer, and that customer’s orders have not previously been retrieved, LINQ to SQL will automatically get them for you.  LINQ to SQL relies on convention, for example default insert, update, and delete logic through generated DML can be overwritten by exposing appropriately named methods (for example, "InsertCustomer", "UpdateCustomer", "DeleteCustomer").  These methods may invoke stored procedures or perform other logic in order to process changes.

The Entity Framework has features targeting "Enterprise Scenarios".  In an enterprise, the database is typically controlled by a DBA, the schema is generally optimized for storage considerations (performance, consistency, partitioning) rather than exposing a good application model, and may change over time as usage data and usage patterns evolve.  With this in mind, the Entity Framework is designed around exposing an application-oriented data model that is loosely coupled, and may differ significantly, from your existing database schema.  For example, you can map a single class (or "entity") to multiple tables/views, or map multiple classes to the same table/view. You can map an inheritance hierarchy to a single table/view (as in LINQ to SQL) or to multiple tables/views (for example, persons, customers, and employees could each be separate tables, where customers and employees contain only the additional columns not present in persons, or repeat the columns from the persons table).  You can group properties into complex (or “composite”) types (for example, a Customer type may have an “Address” property that is an Address type with Street, City, Region, Country and Postal code properties). The Entity Framework lets you optionally represent many:many relationships directly, without representing the join table as an entity in your data model, and has a new feature called "Defining Query" that lets you expose any native query against the store as a "table" that can be mapped just as any other table (except that updates must be performed through stored procedures).  This flexible mapping, including the option to use stored procedures to process changes, is specified declaratively in order to account for the schema of the database evolving over time without having to recompile the application.

The Entity Framework includes LINQ to Entities which exposes many of the same features as LINQ to SQL over your conceptual application data model; you can build queries in LINQ (or in “Entity SQL”, a canonical version of SQL extended to support concepts like strong typing, polymorphism, relationship navigation and complex types), return results as strongly typed CLR objects, execute stored procedures or table valued functions through strongly-typed methods, and process changes by calling a single save method.

However, the Entity Framework is more than LINQ to Entities; it includes a "storage layer" that lets you use the same conceptual application model through low-level ADO.NET Data Provider interfaces using Entity SQL, and efficiently stream results as possibly hierarchical/polymorphic DataReaders, saving the overhead of materializing objects for read-only scenarios where there is no additional business logic. 

The Entity Framework works with Microsoft SQL Server and 3rd party databases through extended ADO.NET Data Providers, providing a common query language against different relational databases through either LINQ to Entities or Entity SQL.

So while there is a lot of overlap, LINQ to SQL is targeted more toward rapidly developing applications against your existing Microsoft SQL Server schema, while the Entity Framework provides object- and storage-layer access to Microsoft SQL Server and 3rd party databases through a loosely coupled, flexible mapping to existing relational schema.

Technorati Tags: ,

GOTCHA – LINQtoSQL Paging Problem

April 20, 2008 Leave a comment

A quick search for how to display paged records in ASP.NET will yield a ton of links and how to’s ranging from as simple as automatic paging with the grid controls to custom paging and server side paging in SQL Server, but what about paging in LINQ?

Consider a simple LINQ query to find all employees with marital status single using the AdventureWorks db

var query = from e in Employees
where e.MaritalStatus == "S"
select e;

To get to the first page is simple as we just need to add a TOP(n) clause to the generated SQL. In LINQ we do this with the Take(n) function. Assuming we have a page size of 10 our first page would be

query.Take(10);

To get subsequent pages we need to somehow tell SQL to skip a certain number of records and then send us the TOP 10 records. i.e.

Skip Rows = Page Size * (Required Page – 1)

Again in LINQ this is extremely simple

query.Skip(10).Take(10); //page 2

query.Skip(20).Take(10); //page 3

Now the problem we have just created exists in the generated SQL when we apply Skip() / Take(); the resulting SQL queries have an unstable sort order.

For the first page the generated query is

SELECT TOP (10) [t0].[EmployeeID], [t0].[NationalIDNumber], [t0].[ContactID], [t0].[LoginID], [t0].[ManagerID], [t0].[Title], [t0].[BirthDate], [t0].[MaritalStatus], [t0].[Gender], [t0].[HireDate], [t0].[SalariedFlag], [t0].[VacationHours], [t0].[SickLeaveHours], [t0].[CurrentFlag], [t0].[rowguid] AS [Rowguid], [t0].[ModifiedDate]
FROM [HumanResources].[Employee] AS [t0]
WHERE [t0].[MaritalStatus] = @p0
ORDER BY [t0].[ManagerID]
— @p0: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [S]

And for subsequent pages where Skip(n) is specified it generates:

SELECT [t1].[EmployeeID], [t1].[NationalIDNumber], [t1].[ContactID], [t1].[LoginID], [t1].[ManagerID], [t1].[Title], [t1].[BirthDate], [t1].[MaritalStatus], [t1].[Gender], [t1].[HireDate], [t1].[SalariedFlag], [t1].[VacationHours], [t1].[SickLeaveHours], [t1].[CurrentFlag], [t1].[rowguid] AS [Rowguid], [t1].[ModifiedDate]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[ManagerID]) AS [ROW_NUMBER], [t0].[EmployeeID], [t0].[NationalIDNumber], [t0].[ContactID], [t0].[LoginID], [t0].[ManagerID], [t0].[Title], [t0].[BirthDate], [t0].[MaritalStatus], [t0].[Gender], [t0].[HireDate], [t0].[SalariedFlag], [t0].[VacationHours], [t0].[SickLeaveHours], [t0].[CurrentFlag], [t0].[rowguid], [t0].[ModifiedDate]
    FROM [HumanResources].[Employee] AS [t0]
    WHERE [t0].[MaritalStatus] = @p0
    ) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p1 + 1 AND @p1 + @p2
ORDER BY [t1].[ROW_NUMBER]
— @p0: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [S]
— @p1: Input Int (Size = 0; Prec = 0; Scale = 0) [10]
— @p2: Input Int (Size = 0; Prec = 0; Scale = 0) [10]

Why different queries?

By definition ‘SELECT’ returns an unordered set, as SQL is set based, and sets have no order. If you want an order in the returned set, you have to define the ordering, with ORDER BY. The ROW_NUMBER() is required to determine which rows are in which page to return.

The problem is that different queries for page 1 vs page n returns the rows in a different sort order. Which means that some of the records displayed on the first page are displayed again on subsequent pages while other records may be missing entirely i.e. don’t show up at all on any page. Adding a Skip(0) to our first page query doesn’t solve things either because LINQ optimizes out the ROW_NUMMBER() from the generated SQL query for Skip(0).

query.Skip(0).Take(10); // doesn’t solve the problem

SELECT TOP (10) [t0].[EmployeeID], [t0].[NationalIDNumber], [t0].[ContactID], [t0].[LoginID], [t0].[ManagerID], [t0].[Title], [t0].[BirthDate], [t0].[MaritalStatus], [t0].[Gender], [t0].[HireDate], [t0].[SalariedFlag], [t0].[VacationHours], [t0].[SickLeaveHours], [t0].[CurrentFlag], [t0].[rowguid] AS [Rowguid], [t0].[ModifiedDate]
FROM [HumanResources].[Employee] AS [t0]
WHERE [t0].[MaritalStatus] = @p0
ORDER BY [t0].[ManagerID]
— @p0: Input NVarChar (Size = 1; Prec = 0; Scale = 0) [S]

From the looks of it the problem seems to be that, for page 1, skip is 0, and the query is processed as if the skip wasn’t applied at all, all the row numbering, and order stabilizing stuff is optimized out. Skip(0) should generate the same row numbering SQL that skip(n) does so that paging systems can rely on the first page of a paged view operates on the same query, with the same row order that will be used for the other pages. Skip(n) imposes a consistent row order. Skip(0) should use the same row order.

But looks can be deceiving. No, you think that the ordering you see in the ROW_NUMBER() column is a valid order, but that’s applied to a set which happens to be not properly ordered! The order in which the rows are in that set with ROW_NUMBER() is determined based on the ordering in the query! The ROW_NUMBER() are added to the final, ordered set. So if in that set there are some rows with undeterminable ordering it can be that the order between queries flips.

So how to solve this? By adding an ORDER BY clause to our original query which forces a sort on the query and stabilizes the row order

var query = from e in Employees
where e.MaritalStatus == "S"
orderby e.NaionalIDNumber
select e;

Without a stated sort order, any standard SQL query is of indeterminate order, and thus in theory unstable. In reality, most databases will (as an implementation detail) honor a clustered index as a default sort (assuming it can pick one from the numerous tables involved in joins), but even this isn’t typically guaranteed. In reality, one should always specify an order condition if you are paging. Not specifying an ORDER BY clause in a query with TOP (n) specified is not recommended (and detected by the SQL Best Practices Analyzer tool) because the set of rows that will be returned will be plan dependent.

Specifying a mere order condition isn’t sufficient. If I were to sort by last name and there were multiple people with same last name I don’t want the rows with the same last name trading places on the first page and between subsequent pages. The order by should be 100% deterministic i.e. be as specific down to the unique column as possible.

 

Technorati Tags: ,,,

Tags: ,