Querying LINQ to SQL: Basics - Column aliasing and expression based columns using LINQ
(Page 2 of 6 )
Let us start with the following SELECT Query:
SELECT RegionID, RegionName = RegionDescription FROM Region
The following is the code that uses LINQ to represent the above (in VB):
Protected Sub btnColumnAlias_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnColumnAlias.Click
Dim db As New NorthwindDataContext
Dim s As New IO.StringWriter
db.Log = s
Dim q = From p In db.Regions _
Select p.RegionID, RegionName = p.RegionDescription
Me.GridView1.DataSource = q
Me.GridView1.DataBind()
Me.lblGenSQL.Text = s.ToString
End Sub
Translating into C#, we have:
protected void btnColumnAlias_Click(object sender, EventArgs e)
{
NorthwindDataContext db = new NorthwindDataContext();
StringWriter s = new StringWriter();
db.Log = s;
var q = from p in db.Regions
select new { p.RegionID, RegionName = p.RegionDescription };
this.GridView1.DataSource = q;
this.GridView1.DataBind();
this.lblGenSQL.Text = s.ToString();
}
We can also represent columns with .NET expressions as follows (in VB):
Dim q = From p In db.Orders _
Select p.OrderID, p.OrderDate, NoOfDays = Date.Today.Subtract(p.OrderDate).Days
We can also use the “LET” keyword for better representation as follows:
Dim q = From p In db.Orders _
Let u = Date.Today.Subtract(p.OrderDate).Days _
Select p.OrderID, p.OrderDate, NoOfDays = u
And we can use any number of “LET”s as follows:
Dim q = From p In db.Orders _
Let d = Date.Today.Subtract(p.OrderDate).Days _
Let y = Year(p.OrderDate) _
Let m = Month(p.OrderDate) _
Select p.OrderID, p.OrderDate, d, m, y
In C#,
var q = from p in db.Orders
let u = System.DateTime.Today.Subtract(p.OrderDate.Value).Days
select new { p.OrderID, p.OrderDate, NoOfDays = u };
Next: Retrieving DISTINCT values using LINQ >>
More .NET Articles
More By Jagadish Chaterjee
