- Introduction
- LINQ to SQL in a Nutshell
- Mapping Entity Classes to Tables
- Defining a Custom DataContext
- Querying the CreditCard Table with LINQ
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Querying the CreditCard Table with LINQ
All you need is a DataContext and a mapped class, and you're ready to query a table with LINQ to SQL. The Main function in Listing 3 shows how easy this is.
Listing 3 Querying the AdventureWorks CreditCard table with LINQ to SQL.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;
namespace LinqToSQLDemo
{
class Program
{
static void Main(string[] args)
{
AdventureWorks db = new AdventureWorks();
var cardNumbers = from card in db.CreditCards
let month = card.ExpMonth
let year = card.ExpYear
select new
{
CardNumber = card.CardNumber,
ExpirationDate =
string.Format("{0,2:00}/{1}", month, year)
};
Array.ForEach(cardNumbers.Take(5).ToArray(), cn => Console.WriteLine(cn));
Console.ReadLine();
var one = db.CreditCards.First();
Console.WriteLine("{0}: {1}", one.CardNumber, one.ExpYear);
Console.ReadLine();
one.ExpYear = 1999;
db.SubmitChanges();
}
}
}
The code in Listing 3 creates an instance of the AdventureWorks DataContext, queries the CreditCards table, displays the results, modifies the ExpYear value of the first record, and saves the changes.
Understanding the LINQ Query
The LINQ query starts with a from clause. The from clause's first argument is called the range. The range is like the control variable in a foreach loop. The range variable in the LINQ query is card. The in predicate indicates the source of the data. The let clause permits you to specify temporary range variables. In Listing 3, the month and year values are stored in range variables defined in let statements. The select clause indicates the data returned.
In Listing 3, select new is used to create what's called a projection. A projection is a new anonymous type. In the example, instances of an anonymous type containing just the card number and a string value containing the month and year are returned.
The Array.ForEach method takes an array and iterates over the members in the array. ForEach is shorthand for a for loop. The second argument is a Lambda expression that's a very shorthand version of an anonymous delegate. In the listing, cn is the input representing one of the anonymous objects returned by the LINQ query, and the Console.WriteLine(cn) method sends that object's state to the console.
Updating the Table
The last five lines of Listing 3 get the first CreditCard object from the CreditCards property of the DataContext, displaying the card number and expiration year. The ExpYear columns is changed, and the results are written to the database.
For the update to work, you must specify the primary key (refer to Listing 1) so that LINQ to SQL can uniquely identify which object to change.