Linq To SQL - Part I

In previous few posts we discussed entity framework and how you can use it to model your database tables/views/stored procedures and use them in your application. We also touched on Linq to Entities (more on this later).

Today we will review Linq to SQL. Linq to SQL is another way to work with your database instead of using entity framework. Linq to SQL is good for simple applications as it only supports 1 to 1 mapping of tables, views, procedures or UDFs.

Let's look at an example.

1. I created a console app and added Linq to SQL Classes dbml object.

2. Add the tables  / views you want to generate classes for.

3. Now we will select the data from these tables using Linq to SQL.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace LinqToSQL
{
    class Program
    {
        static void Main(string[] args)
        {
            // DataContext takes a connection string 
            linqToSQLDataContext db = new linqToSQLDataContext();
            
            var customerQuery = from d in db.Customers
                                orderby d.LastName
                                select d;
            foreach (var cust in customerQuery)
            {
                Console.WriteLine("id = {0}, FirstName = {1},LastName = {2}", 
                cust.CustomerID, cust.FirstName, cust.LastName);
            }

            Console.WriteLine(Environment.NewLine);

            //getting data from multiple tables
            var CustomersAndAddresses = from c in db.Customers
                                        from o in db.CustomerAddresses
                                    select new { c.FirstName,c.LastName, 
                                    o.Address1,o.Address2,o.City,o.State };

            foreach (var cust in CustomersAndAddresses)
            {
                Console.WriteLine("Name={0}, Address = {1},City = {2}", 
                cust.FirstName + ' ' + cust.LastName, cust.Address1 + ' ' + 
                cust.Address2, cust.City);
            }
            Console.Read();

        }
    }
}

As you can see in code above, we created two Linq to SQL queries - 1st one is getting data with just one table, the other one with both tables.

You can also add a WHERE clause similar to T-SQL. For example, I can modify above query

var CustomersAndAddresses = from c in db.Customers
                                        from o in db.CustomerAddresses
                                        WHERE c.CustomerID == o.CustomerID
                                       WHERE c.CustomerID == 1
                                        select new { c.FirstName,c.LastName, o.Address1,o.Address2,o.City,o.State };

In next post we will review how you can insert, update or delete a record using Linq to SQL.

Thank you.