Click here to Skip to main content
12,354,349 members (72,555 online)
Click here to Skip to main content
Add your own
alternative version

Stats

72.7K views
1.7K downloads
30 bookmarked
Posted

LINQ to SQL Samples

, 17 Mar 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
List basic SQL sentences to LINQ queries

Introduction

LINQ is one of the most important features in .NET Framework 3.5 (Visual Studio 2008). It's the new way to mapping database tables to classes, and as we know, we call this O/R Mapping. An article on how to write LINQ code quickly is always welcome for beginners, and I think that reading samples is the best way to learn a new technique.

These are samples created while I was learning and using LINQ, and I want to share them now. Hope they will be helpful. I will use Northwind database as a sample, which you can download from the link at the top of this article.

I recommend that you read 101 LINQ Samples if you would like to learn more.

Samples

// Basic
    // Select * From Products 
    var query1 = from p in db.Products
                 select p;
    // Select ProductID, ProductName, UnitPrice From Products
    var query2 = from p in db.Products
                 select new { 
                     p.ProductID,
                     p.ProductName,
                     p.UnitPrice
                 };

Note: query2 will create a new class which contains three properties that map the ProductId, ProductName, and UnitPrice.

// Where
    // Select * From Products Where ProductID = 1
    var query3 = from p in db.Products
                 where p.ProductID == 1
                 select p;

    // Select * From Products Where SupplierId =5 and UnitPrice > 20
    var query4 = from p in db.Products
                 where p.SupplierID == 5 && p.UnitPrice > 20
                 select p;


    // Select * From Products Where SupplierId =5 Or SupplierId=6 
    var query5 = from p in db.Products
                 where p.SupplierID == 5 || p.SupplierID == 6
                 select p;

Note: The condition in the where block is a logical express, a boolean value is returned just like in if().

// Order By 
    // Select * From Products Order By ProductId
    var query6 = from p in db.Products
                 orderby p.ProductID
                 select p;

    // Select * From Products Order By ProductId Desc
    var query7 = from p in db.Products
                 orderby p.ProductID descending
                 select p;

    // Select * From Products Order By CategoryId, UnitPrice Desc
    var query8 = from p in db.Products
                 orderby p.CategoryID, p.UnitPrice descending
                 select p;

Note: The default order is ascending, the order by p.ProductID is same as order by p.ProductID ascending, just like in T-SQL.

// Top 
    // Select Top 10 * From Products
    var query9 = (from p in db.Products
                 select p).Take(10);

    // Select Top 1 * From Products
    var query10 = (from p in db.Products
                   select p).Take(1);
    // or
    var query11 = (from p in db.Products
                   select p).First();

Note: If it just returns one record, I recommend using First instead of Take(1).

// Top with Order By
    // Select Top 10 * From Products Order By ProductId
    var query12 = (from p in db.Products
                   orderby p.ProductID
                   select p).Take(10);

// Distinct
    // Select Distinct CategoryId From Products
    var query13 = (from p in db.Products
                   select p.CategoryID).Distinct();

// Group By
    // Select CategoryId, Count(CategoryID) As NewField 
    // From Products Group By CategoryId
    var query14 = from p in db.Products
                  group p by p.CategoryID into g
                  select new { 
                      CategoryId = g.Key, 
                      NewField = g.Count() 
                  };

    // Select CategoryId, Avg(UnitPrice) As NewField From Products Group By CategoryId
    var query15 = from p in db.Products
                  group p by p.CategoryID into g
                  select new { 
                      CategoryId = g.Key, 
                      NewField = g.Average(k => k.UnitPrice) 
                  };

    // Select CategoryId, Sum(UnitPrice) As NewField From Products Group By CategoryId
    var query16 = from p in db.Products
                  group p by p.CategoryID into g
                  select new { 
                      CategoryId = g.Key,
                      NewField = g.Sum(k => k.UnitPrice )
                  };

// Union
    // Select * From Products Where CategoryId =1 union Select * 
    // From Products Where CategoryId = 2
    var query17 = (from p in db.Products
                   where p.CategoryID == 1
                   select p).Union(
                       from m in db.Products
                       where m.CategoryID == 2
                       select m
                   );

// Two tables
    // Select A.ProductId, A.ProductName, B.CategoryId, B.CategoryName 
    // From Products A, Categories B 
    //    Where A.CategoryID = B.CategoryID and A.SupplierId =1 
    var query18 = from p in db.Products
                  from m in db.Categories
                  where p.CategoryID == m.CategoryID && p.SupplierID == 1
                  select new {
                      p.ProductID,
                      p.ProductName,
                      m.CategoryID,
                      m.CategoryName
                  };

History

  • Ver 1.0 - 2008-03-18: Article created

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

CooperWu
Software Developer (Senior) Thomson Reuters
China China
I started to programming in 2002, started when I was grade 2 in university, I participated some part-time projects at that time, I have much experiences on Windows, includes C#, ASP.NET, Visual Basic, Visual C++, AJAX, Power Shell Script, JavaScript, XML..etc, I am learning design and architect.

You may also be interested in...

Comments and Discussions

 
Questionvery nice Pin
payaljain269-Jun-14 23:37
memberpayaljain269-Jun-14 23:37 
GeneralMy vote of 5 Pin
usrikanthvarma22-May-13 11:31
memberusrikanthvarma22-May-13 11:31 
GeneralMy vote of 5 Pin
manoj kumar choubey18-Mar-13 23:06
membermanoj kumar choubey18-Mar-13 23:06 
GeneralMy vote of 5 Pin
Member 44334143-Oct-12 21:59
memberMember 44334143-Oct-12 21:59 
GeneralMy vote of 4 Pin
AmolPandurangBorkar20-Sep-11 20:12
memberAmolPandurangBorkar20-Sep-11 20:12 
GeneralMy vote of 4 Pin
coder4116-Jul-11 2:48
membercoder4116-Jul-11 2:48 
Generalthanks helpfull for beginers Pin
Ravi Sadalagi27-Dec-10 1:38
memberRavi Sadalagi27-Dec-10 1:38 
GeneralWhat's an useful article! Pin
nvntung6-Jul-10 4:37
membernvntung6-Jul-10 4:37 
GeneralNice article Pin
Foyzul Karim15-Mar-10 21:08
memberFoyzul Karim15-Mar-10 21:08 
GeneralWell Done (and a note on joins) Pin
johan_vw1-Apr-09 4:26
memberjohan_vw1-Apr-09 4:26 
GeneralThank for a useful article Pin
defwebserver1-Nov-08 9:03
memberdefwebserver1-Nov-08 9:03 
Generalan trivial article Pin
QuachNguyen6-Apr-08 9:07
memberQuachNguyen6-Apr-08 9:07 
GeneralRe: an trivial article Pin
WuJunyin6-Apr-08 21:52
memberWuJunyin6-Apr-08 21:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.160621.1 | Last Updated 18 Mar 2008
Article Copyright 2008 by CooperWu
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid