Monday, 28 January 2013

Groupby LINQ to Entities Example


Groupby LINQ to Entities Example


How to add ADO.NET Entity Model to Existing Application , pls read this article.
http://www.wnewsone.com/tutorials/ado.net/ShowArticle.aspx?articleid=20262


This example explains how to implement Group by on ADO.NET entities.
 
static void GroupByEntity()
        {
        //DBContext Derived object should be created

            AdventureWorks2012Entities context = new AdventureWorks2012Entities();

            //Here On Vendor Entity Grouping based on Credit Rating
            //Filtering Vendors on Credit rating.

            var query = from vendor in context.Vendors
                        group vendor by vendor.CreditRating into vendor_rating_group
                        where vendor_rating_group.Key > 1
                        select vendor_rating_group;

            //Key Value pair 
            //Key contains Vendor Credit rating
            //Value contains Vendor Details.

            foreach(IGrouping<byte,Vendor> vendorGrp in query)
            {
                Console.WriteLine("GroupID Rating={0}",vendorGrp.Key);

                foreach (Vendor vendor in vendorGrp)
                {
                    Console.WriteLine("\t"+vendor.CreditRating);
                    Console.WriteLine("\t" + vendor.Name);
                    Console.WriteLine("\t" + vendor.AccountNumber);
                }
            }
        }

OUTPUT  (This is based on AdventureWorks  Vendor table) You can check in your System.

GroupID Rating=2
    2
    Allenson Cycles
    ALLENSON0001
    2
    Trikes, Inc.
    TRIKES0001
    2
    Chicago Rent-All
    CHICAGO0002
    2
    Custom Frames, Inc.
    CUSTOMF0001
    2
    Signature Cycles
    SIGNATUR0001
    2
    Reliance Fitness, Inc.
    RELIANCE0001
    2
    Wide World Importers
    WIDEWOR0001
    2
    Carlson Specialties
    CARLSON0001
    2
    Business Equipment Center
    BUSINESS0001
GroupID Rating=3
    3
    Vista Road Bikes
    VISTARO0001
    3
    Consumer Cycles
    CONSUMER0001
    3
    Inner City Bikes
    INNERCI0001
    3
    Trey Research
    TREYRE0001
    3
    Continental Pro Cycles
    CONTINEN0001
    3
    Federal Sport
    FEDERAL0001
    3
    Northern Bike Travel
    NORTHERN0001
GroupID Rating=4
    4
    Recreation Place
    RECREATI0001
    4
    Proseware, Inc.
    PROSE0001
GroupID Rating=5
    5
    Merit Bikes
    MERITBI0001
    5
    Victory Bikes
    VICTORY0001