Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
BRH 12
#DOTNET 10
#LINQ 6
LINQ 5
#ASP.NET 4
#DATAACCESS 4
ASP.NET 3
DOTNET 3
.NET 2
WCF 2

Archive · View All
July 2010 5
August 2010 4
June 2011 3
October 2010 1

LINQ to SQL Query Operators Part II

Aug 1 2010 6:40AM by IBhadelia   

What we learned previously

In previous article we understand what are projection operators and how we can use them to selecting and ordering them. In this article we are going to see how we can group data, making use of join operator.

In this article we are going to see following Grouping Operators:

  • GroupBy
  • Join
  • GroupJoin

NOTE: The code samples in this article were created in C# language using Northwind Database which can be downloaded from here. 

GroupBy operator

GroupBy operator is only operator which provides you group result based on criteria. There are four overloads of this function.

public static IEnumerable<IGrouping<K, T>> GroupBy<T, K>(
    this IEnumerable<T> source, Func<T, K> keySelector);
public static IEnumerable<IGrouping<K, T>> GroupBy<T, K>(
    this IEnumerable<T> source, Func<T, K> keySelector, 
    IEqualityComparer<K> comparer);
public static IEnumerable<IGrouping<K, E>> GroupBy<T, K, E>(
    this IEnumerable<T> source, Func<T, K> keySelector, 
    Func<T, E> elementSelector);
public static IEnumerable<IGrouping<K, E>> GroupBy<T, K, E>(
    this IEnumerable<T> source, Func<T, K> keySelector,
    Func<T, E> elementSelector, 
    IEqualityComparer<K> comparer);

All of these overloads return IEnumerable<IGrouping<K, T>>, where the IGrouping<K, T> generic interface is a specialized implementation of IEnumerable<T>. This implementation can return a specific Key of type K for each item within the enumeration:

public interface IGrouping<K, T> : IEnumerable<T>
{
    K Key { get; }
}

Let’s see how we can group by to find how many products fall in individual category.

using (NorthwindModelDataContext context = new NorthwindModelDataContext())
{
    var query = (from product in context.Products
                    group product by product.CategoryID).ToList();

    foreach (var product in query)
    {
        Console.WriteLine(product.Key);
        foreach (var item in product)
        {
            Console.WriteLine("-->" + item.ProductName);
        }
    }
}

The above first query will group product by category name and gives products along with category name. The output will look like following.

image 

You can write same query in different way, lets see how we can use Lamda expression to minimize code of our query.

using (NorthwindModelDataContext context = new NorthwindModelDataContext())
{
    var query = context.Products.GroupBy(p => p.CategoryID).ToList();

    foreach (IGrouping<string, Product> productGroup in query)
    {
        Console.WriteLine(productGroup.Key);
        foreach (var item in productGroup)
        {
            Console.WriteLine("-->" + item.ProductName);
        }
    }
}

The output of above query is same as previous one. You can see how easy to write query using LINQ. The example we seen was having single key in group, let see another example having multiple key.

using (NorthwindModelDataContext context = new NorthwindModelDataContext())
{
    var query = (from product in context.Products
                    group product by new
                    {
                        product.CategoryID,
                        product.SupplierID
                    }).ToList();

    foreach (var productGroup in query)
    {
        Console.WriteLine(productGroup.Key);
        foreach (var product in productGroup)
        {
            Console.WriteLine("-->" + product.ProductName);
        }
    }
}

For doing group on multiple column you have to create anonymous class with keys you are willing ton include in group, in our example we have created such anonymous class and provided CategoryID and SupplierID both as part of group. This will first group on CategoryID and then SupplierID if you want to reverse the order then you have to change order in anonymous class too.

image

We can add our group into quick watch to understand the result, we can see that Key is having two key with value 1 for each, and we find two products fall into that criteria.

image 

The following is the output of multi group key query

image

Join Operator

In Relational world its always required to need join of one or more table in our SQL query, same way in our LINQ query we can define relationship between tables using join method, following is the signature of join method.

public static IEnumerable<V> Join<T, U, K, V>(
    this IEnumerable<T> outer,
    IEnumerable<U> inner,
    Func<T, K> outerKeySelector,
    Func<U, K> innerKeySelector,
    Func<T, U, V> resultSelector);

public static IEnumerable<V> Join<T, U, K, V>(
    this IEnumerable<T> outer,
    IEnumerable<U> inner,
    Func<T, K> outerKeySelector,
    Func<U, K> innerKeySelector,
    Func<T, U, V> resultSelector,
    IEqualityComparer<K> comparer);

Join method is having four types in its arguments. T represents outer source, U represents inner source. Here T and U both are sequences. Then we have two predicts having same key of type K. And then we have one more predicts to define our result sequence. Second overload provides custom comparer. We can understand this by one simple example having product details along with category name.

using (NorthwindModelDataContext context = new NorthwindModelDataContext())
{
    var query = context.Products
        .Join(context.Categories,
        p => p.CategoryID,
        c => c.CategoryID,
        (p, c) => new { p.ProductID, p.ProductName, c.CategoryName });

    foreach (var product in query)
    {
        Console.WriteLine("PID:{0}, PName:{1}, PCategory:{2}", product.ProductID, product.ProductName, product.CategoryName);
    }
}

Following is the result of query.

image

In this example, products represents outer sequence and category represents inner sequence, p and c are used in Lambda expression are of the type Product and Category respectively. At the time of execution, operator get elements of inner sequence and create hash table with specified key innerKeySelector, and then it enumerates outer sequence, for mapping it used Key value extracted with outerKeySelector, this is the reason why join operator result sequence keeps order of outer sequence first and then inner sequence for each outer.

Following is LINQ syntax of join operator.

var query = from product in context.Products
            join prodCat in context.Categories
            on product.CategoryID equals prodCat.CategoryID
            select new { product.ProductID, product.ProductName, prodCat.CategoryName };

foreach (var product in query)
{
    Console.WriteLine("PID:{0}, PName:{1}, PCategory:{2}", product.ProductID, product.ProductName, product.CategoryName);
}

In this syntax order of relation does matter, it mush have outer sequence first and then inner, however it will not compile if you have changed sequence!

GroupJoin

We saw inner join using join operator; if we need LEFT OUTER or RIGHT OUTER join then we can use GroupJoin  operator.

Let see how we can use GroupJoin to select all products which are in order along with products which are not. So we need all products on which order is placed and which are not there in any order.

var query = context.Products
    .GroupJoin(
    context.Orders.SelectMany(o => o.Order_Details),
    p => p.ProductID,
    o => o.ProductID,
    (p, orders) => new { p.ProductID, p.ProductName, Orders = orders });

foreach (var po in query)
{
    Console.WriteLine("PID:{0}, PName:{1}, OrderCount:{2}", po.ProductID, po.ProductName, po.Orders.Count());
}

Following is the output of this query

image

You can see there are no order placed for PID 77 and 70 as their OrderCount is 0

We can write same query in LINQ query expression as follow, here we can define GroupJoin operator as join into lets see how…

var query = from product in context.Products
            join orderDet in context.Order_Details
            on product.ProductID equals orderDet.ProductID
            into orders
            select new { product.ProductID, product.ProductName, Orders = orders };

foreach (var po in query)
{
    Console.WriteLine("PID:{0}, PName:{1}, OrderCount:{2}", po.ProductID, po.ProductName, po.Orders.Count());
}

The output will be same as using Lambda expression.

Summary

In this part we learned some more LINQ operators. GroupBy operator is used to group result based on given criteria, we seen group by single field and also with multiple fields. Join Operator use to join two or more tables just like inner join in SQL, GroupJoin use to make outer joins. In both case we can also use Where and Let operators which we learned in previous article. The article will explain we will going to see more operators like Set operators,Aggregate Operators and Generation Operators.

Tags: #DOTNET, LINQ, #DATAACCESS, #LINQ, BRH, DOTNET,


IBhadelia
164 · 1% · 299
0
Liked
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

Your Comment


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]