Querying LINQ to SQL: Basics
(Page 1 of 6 )
This is an introductory article that focuses on basic querying using “LINQ to SQL.” It covers basic LINQ operators with both SQL and LINQ examples. If you are new to programming LINQ, please consider reading my previous articles, called “Beginning ‘LINQ to SQL’ using Visual Studio 2008” and “Introducing ‘LINQ to SQL’ designer using Visual Studio 2008.”
For the purpose of this article, I created a LINQ model using “LINQ to SQL designer” as follows:
Fig 01
All the queries in this article will focus on the above model. The image is linked to here due to size and clarity considerations.
The entire source code for this article is available in the form of a free downloadable zip file (CS and VB). The solution was developed using Microsoft Visual Studio 2008 Team Edition on Microsoft Windows Server 2003 Standard Edition with Microsoft SQL Server 2005 Developer Edition. I didn’t really test it in any other environment. I request that you post in the discussion area if you have any problems regarding execution.
Beginning to fetch data using LINQ (i.e. fetching rows from a table)
To begin fetching data, you must start with a query. As we already have a model created using “LINQ to SQL Designer,” we will start fetching data from that model using LINQ.
In general, to retrieve all rows from the Regions table, we would write a SQL SELECT as follows:
SELECT * FROM Region
The LINQ statement (in VB) for the above SELECT can be written as follows:
Dim q = From p In db.Regions _
Select p
To make it simpler, we can even omit the “Select” part as follows (only in VB):
Dim q = From p In db.Regions
The complete code for the above examples would be as follows:
Protected Sub btnSelectFromRegions_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSelectFromRegions.Click
Dim db As New NorthwindDataContext
Dim s As New IO.StringWriter
db.Log = s
Dim q = From p In db.Regions
'Dim q = From p In db.Regions _
' Select p
Me.GridView1.DataSource = q
Me.GridView1.DataBind()
Me.lblGenSQL.Text = s.ToString
End Sub
This is the code in C#:
protected void btnSelectFromRegions_Click(object sender, EventArgs e)
{
NorthwindDataContext db = new NorthwindDataContext();
StringWriter s = new StringWriter();
db.Log = s;
var q = from p in db.Regions
select p;
this.GridView1.DataSource = q;
this.GridView1.DataBind();
this.lblGenSQL.Text = s.ToString();
}
Let us go further with the following SELECT query:
SELECT RegionID, RegionDescription FROM Region
In the above case, we are specific for only certain columns (but not all columns). The LINQ query (in VB) for the above example would be as follows:
Dim q = From p In db.Regions _
Select p.RegionID, p.RegionDescription
The same in C# would be as follows:
var q = from p in db.Regions
select new { p.RegionID, p.RegionDescription };
Next: Column aliasing and expression based columns using LINQ >>
More .NET Articles
More By Jagadish Chaterjee
