Home > Articles > Programming > Windows Programming

  • Print
  • + Share This
From the author of

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.

  • + Share This
  • 🔖 Save To Your Account