LINQ deferred loading using C# yield-return

Yield keyword exists in C# language since version 2 along with Visual Studio 2005, however it is rarely used by developers. Recently I read Demystifying the C# Yield-Return Mechanism article about yield-return keyword usage. This article gave three most common usage scenarios, but author (James McCaffrey) didn't mention LINQ. I think LINQ is where yield-return becomes most useful since it provides mechanism for deferred loading. Here is a quick example.

Lets say we have bunch of unit tests to run. It takes some time to run a single test, lets say 1 sec. For simplicity lets assume that even tests pass and odd fail:

public class Test
{
    public int Id { get; set; }        
    public bool Assert()
    {
        System.Threading.Thread.Sleep(1000);
        Console.WriteLine(string.Format("Test {0} was processed.", Id));            
        return Id % 2 == 0;
    }        
}

 

Lets write couple LINQ like extension methods for Test collections. Process1 doesn't use yield return. Process2 uses yield return and PrintPass output "Test # is passed" message.

public static class Extensions
{        
    public static IEnumerable<Test> Process1(this IEnumerable<Test> tests)
    {
        var result = new List<Test>();
        foreach (var test in tests)
        {
            if (test.Assert())
            {
                result.Add(test);
            }
        }
        return result;
    }
 
    public static IEnumerable<Test> Process2(this IEnumerable<Test> tests)
    {
        foreach (var test in tests)
        {
            if (test.Assert())
            {
                yield return test;
            }
        }
    }
 
    public static void PrintPass(this IEnumerable<Test> tests)
    {
        foreach (var t in tests)
        {
            Console.WriteLine(string.Format("Test {0} is passed.", t.Id));                
        }
    } 
}

 

Lets fill up test collection and compare output for Process1 and Process2 method:

class Program
{
    static void Main()
    {
        var tests = new List<Test>(new []
                        {
                            new Test {Id = 1},                                                         
                            new Test {Id = 2},
                            new Test {Id = 3},
                            new Test {Id = 4},
                            new Test {Id = 5},
                        });
 
        Console.WriteLine("**** No yield return *******");
        tests.Process1().PrintPass();            
        Console.WriteLine("**** Using yield return *******");
        tests.Process2().PrintPass();  
        Console.ReadKey();
    }
}

 

Output:

**** No yield return *******
Test 1 was processed.
Test 2 was processed.
Test 3 was processed.
Test 4 was processed.
Test 5 was processed.
Test 2 is passed.
Test 4 is passed.

**** Using yield return *******
Test 1 was processed.
Test 2 was processed.
Test 2 is passed.
Test 3 was processed.
Test 4 was processed.
Test 4 is passed.
Test 5 was processed.

As you can see, in first case whole collection was built before PrintPass method output anything. In second case you can see PrintPass prints "Test 2 is passed." right after Assert method for this test have done its processing.

Posted on Saturday, March 31, 2012 by | Add Comment

How to use PIVOT in T-SQL queries?

Web has a lot of PIVOT examples, however all of them are based on fact tables. Regular relational database rarely has facts tables. Lets see how you can use PIVOT statement with regular database. First of all lets create sample Grocery database:

Grocery Database Diagram

Here is a script that can create this database:

CREATE DATABASE Grocery
GO
 
USE Grocery
 
CREATE TABLE Products
(
  ProductId INT IDENTITY(1,1) PRIMARY KEY,
  ProductName VARCHAR(50),
  Price MONEY
)
 
CREATE TABLE Stores
(
    StoreId INT IDENTITY(1,1) PRIMARY KEY,
    StoreName VARCHAR(50)
)
 
 
CREATE TABLE Orders
(
    OrderId INT IDENTITY(1,1) PRIMARY KEY,
    StoreId INT,
    OrderDate DATE,
    CONSTRAINT FK_Orders_Store FOREIGN KEY (StoreId) REFERENCES Stores(StoreId)
)
 
CREATE TABLE OrderProducts
(
    OrderId INT,
    ProductId INT,
    Qty INT,
    CONSTRAINT PK_OrderId_ProductId PRIMARY KEY (OrderId,ProductId),
    CONSTRAINT FK_OrderProducts_Products FOREIGN KEY (ProductId) REFERENCES Products(ProductId),
    CONSTRAINT FK_OrderProducts_Orders FOREIGN KEY (OrderId) REFERENCES Orders(OrderId)
)

 

Lets populate those tables:

 
INSERT INTO Products
(ProductName, Price)
VALUES  
('Milk', 2.99),
('Bread',1.99),
('Tomato',0.99),
('Grape',1.99)
 
INSERT INTO Stores
(StoreName )
VALUES  
('Jewel'),
('Dominicks'),
('Walmart')
 
INSERT INTO Orders
(StoreId, OrderDate )
VALUES  
(1,'1/11/2011'),
(1,'1/14/2011'),
(2,'2/05/2011'),
(3,'3/17/2011'),
(3,'3/29/2011'),
(3,'4/02/2011')
 
 
INSERT INTO OrderProducts
( OrderId, ProductId, Qty )
VALUES 
(1,2,2),
(1,1,1),
(2,3,1),
(3,4,3),
(3,1,2),
(3,3,1),
(4,3,2),
(4,4,2),
(5,1,1),
(5,3,1),
(6,1,1),
(6,2,3),
(6,4,1)

 

Products:

ProductId ProductName Price
1 Milk 2.99
2 Bread 1.99
3 Tomato 0.99
4 Grape 1.99

 

Stores:

StoreId StoreName
1 Jewel
2 Dominicks
3 Walmart

 

Orders:

OrderId StoreId OrderDate
1 1 2011-01-11
2 1 2011-01-14
3 2 2011-02-05
4 3 2011-03-17
5 3 2011-03-29
6 3 2011-04-02

 

OrderProducts:

OrderId ProductId Qty
1 1 1
1 2 2
2 3 1
3 1 2
3 3 1
3 4 3
4 3 2
4 4 2
5 1 1
5 3 1
6 1 1
6 2 3
6 4 1

 

Now our sample relation database is ready for some pivoting. Lets say we want to know what products are sold by what store. Lets pull needed data using regular query:

SELECT 
    s.StoreName,p.ProductName, op.Qty * p.Price AS Totals 
FROM 
    Products p INNER JOIN 
    OrderProducts op ON p.ProductId = op.ProductId INNER JOIN 
    Orders o ON op.OrderId = o.OrderId INNER JOIN 
    Stores s ON o.StoreId = s.StoreId

 

Results:

StoreName ProductName Totals
Jewel Milk 2.99
Jewel Bread 3.98
Jewel Tomato 0.99
Dominicks Milk 5.98
Dominicks Tomato 0.99
Dominicks Grape 5.97
Walmart Tomato 1.98
Walmart Grape 3.98
Walmart Milk 2.99
Walmart Tomato 0.99
Walmart Milk 2.99
Walmart Bread 5.97
Walmart Grape 1.99

 

Those result rows look like a fact table. Lets apply PIVOT to them:

SELECT * FROM
(
    SELECT 
        s.StoreName,p.ProductName, op.Qty * p.Price AS Totals 
    FROM 
        Products p INNER JOIN 
        OrderProducts op ON p.ProductId = op.ProductId INNER JOIN 
        Orders o ON op.OrderId = o.OrderId INNER JOIN 
        Stores s ON o.StoreId = s.StoreId
) AS Source        
PIVOT
(
    SUM(Totals)
    FOR ProductName IN ([Bread],[Milk],[Tomato],[Grape])    
) AS p

 

PIVOT results:

StoreName Bread Milk Tomato Grape
Dominicks NULL 5.98 0.99 5.97
Jewel 3.98 2.99 0.99 NULL
Walmart 5.97 5.98 2.97 5.97

 

Lets do another one: products sales by months.

SELECT 
    p.ProductName, DATENAME(m,o.OrderDate) AS [Month], op.Qty * p.Price AS Totals 
FROM 
    Products p INNER JOIN 
    OrderProducts op ON p.ProductId = op.ProductId INNER JOIN 
    Orders o ON op.OrderId = o.OrderId INNER JOIN 
    Stores s ON o.StoreId = s.StoreId

 

Results:

ProductName Month Totals
Milk January 2.99
Bread January 3.98
Tomato January 0.99
Milk February 5.98
Tomato February 0.99
Grape February 5.97
Tomato March 1.98
Grape March 3.98
Milk March 2.99
Tomato March 0.99
Milk April 2.99
Bread April 5.97
Grape April 1.99

 

PIVOT version:

SELECT * FROM
(
    SELECT 
        p.ProductName, DATENAME(m,o.OrderDate) AS [Month], op.Qty * p.Price AS Totals 
    FROM 
        Products p INNER JOIN 
        OrderProducts op ON p.ProductId = op.ProductId INNER JOIN 
        Orders o ON op.OrderId = o.OrderId INNER JOIN 
        Stores s ON o.StoreId = s.StoreId
) AS Source        
PIVOT
(
    SUM(Totals)
    FOR [Month] IN ([January],[February],[March],[April])    
) AS p

 

PIVOT results:

ProductName January February March April
Bread 3.98 NULL NULL 5.97
Grape NULL 5.97 3.98 1.99
Milk 2.99 5.98 2.99 2.99
Tomato 0.99 0.99 2.97 NULL

 

And last one lets do stores sales by months:

SELECT 
    s.StoreName,DATENAME(m,o.OrderDate) AS [Month], op.Qty * p.Price AS Totals 
FROM 
    Products p INNER JOIN 
    OrderProducts op ON p.ProductId = op.ProductId INNER JOIN 
    Orders o ON op.OrderId = o.OrderId INNER JOIN 
    Stores s ON o.StoreId = s.StoreId

 

Results:

StoreName Month Totals
Jewel January 2.99
Jewel January 3.98
Jewel January 0.99
Dominicks February 5.98
Dominicks February 0.99
Dominicks February 5.97
Walmart March 1.98
Walmart March 3.98
Walmart March 2.99
Walmart March 0.99
Walmart April 2.99
Walmart April 5.97
Walmart April 1.99

 

PIVOT version:

SELECT * FROM
(
    SELECT 
        s.StoreName,DATENAME(m,o.OrderDate) AS [Month], op.Qty * p.Price AS Totals 
    FROM 
        Products p INNER JOIN 
        OrderProducts op ON p.ProductId = op.ProductId INNER JOIN 
        Orders o ON op.OrderId = o.OrderId INNER JOIN 
        Stores s ON o.StoreId = s.StoreId
) AS Source        
PIVOT
(
    SUM(Totals)
    FOR [Month] IN ([January],[February],[March],[April])    
) AS p

 

PIVOT results:

StoreName January February March April
Dominicks NULL 12.94 NULL NULL
Jewel 7.96 NULL NULL NULL
Walmart NULL NULL 9.94 10.95
Posted on Tuesday, March 27, 2012 by | Add Comment

How to list sql server table dependencies using T-SQL?

You can always use SQL Server Management Studio, just right click on a table in object explorer and select View Dependencies.

View Dependencies

However this way doesn't give you ability to copy dependencies to clipboard or any other way to export dependencies list. Luckily you can find the same data using SysObjects and SysDepends tables. Here is a quick T-SQL snippet that you might want to use for this purpose.

SELECT DISTINCT dobj.name,dobj.type
FROM SysObjects obj INNER JOIN 
SysDepends d ON obj.id = d.depid INNER JOIN 
SysObjects dobj ON d.id = dobj.id
WHERE obj.name = 'WorkOrder' ORDER BY dobj.type,dobj.name
Posted on Sunday, February 19, 2012 by | Add Comment

Categories

Recent Tweets

Valid HTML5