Click here to Skip to main content
Click here to Skip to main content
Go to top

LINQ to SQL Samples

, 17 Mar 2008
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.

Comments and Discussions

 
Questionvery nice Pinmemberpayaljain269-Jun-14 23:37 
GeneralMy vote of 5 Pinmemberusrikanthvarma22-May-13 11:31 
GeneralMy vote of 5 Pinmembermanoj kumar choubey18-Mar-13 23:06 
GeneralMy vote of 5 PinmemberMember 44334143-Oct-12 21:59 
GeneralMy vote of 4 PinmemberAmolPandurangBorkar20-Sep-11 20:12 
GeneralMy vote of 4 Pinmembercoder4116-Jul-11 2:48 
Generalthanks helpfull for beginers PinmemberRavi Sadalagi27-Dec-10 1:38 
GeneralWhat's an useful article! Pinmembernvntung6-Jul-10 4:37 
GeneralNice article PinmemberFoyzul Karim15-Mar-10 21:08 
GeneralWell Done (and a note on joins) Pinmemberjohan_vw1-Apr-09 4:26 
GeneralThank for a useful article Pinmemberdefwebserver1-Nov-08 9:03 
Generalan trivial article PinmemberQuachNguyen6-Apr-08 9:07 
GeneralRe: an trivial article PinmemberWuJunyin6-Apr-08 21:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    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 | Mobile
Web03 | 2.8.140916.1 | Last Updated 18 Mar 2008
Article Copyright 2008 by CooperWu
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid