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.

Entity Framework - Part II

In previous post we created a simple winform project and then generated an entity framework data model (EDM) using a small Orders database. Today, we will use the same project and query the data using entity framework from the underlying database and bind it to an element. If you haven't reviewed previous post, I strongly suggest you do so now.

In this example, we will use some of the built in methods such as List to get a list of customers and bind it to the dropdown list.

We will also use Linq to entities to get a list of addresses that are associated with the selected customer and load the address in read only text fields.

Example - 

using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Objects;
using System.Data.Objects.DataClasses;

namespace EntiryFramework
{
    public partial class Main : Form
    {
        private OrdersEntities orders;
        public Main()
        {
            InitializeComponent();
        }

        private void Close_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void Main_Load(object sender, EventArgs e)
        {

            //Initialize the ObjectContext
            orders = new OrdersEntities();

            //Load all customers 

            List<Customer> customerList = orders.Customers.ToList();
            try
            {
                //bind to the database
                this.customerList.DisplayMember = "LastName";
                this.customerList.ValueMember = "CustomerID";
                this.customerList.DataSource = customerList;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }



        }

        private void customerList_SelectedIndexChanged(object sender, EventArgs e)
        {
            //lets get the addresses that are associated with the selected customer
            int customerID = (int)this.customerList.SelectedValue;
           
            IQueryable<CustomerAddress> customerAddress = orders.Customers
                .Where(d => d.CustomerID == customerID)
                .SelectMany(d => d.CustomerAddresses);
            foreach (CustomerAddress address in customerAddress)
            {
                txtAddress1.Text = address.Address1;
                txtAddress2.Text = address.Address2;
                txtCity.Text = address.City;
                txtState.Text = address.State;
                txtZip.Text = address.ZipCode;
            }
           
        }
    }
}

When you run this application, you will see something like this and as you change the customer name in the drop down list, the address will change.

In this example I used the List() method to load all the customers from the database and then used Linq to entities to filter the list of addresses for the selected customer. I could have used Linq to entities to load all customers, doing something like this...

var customerQuery = from d in orders.Customers
                                  orderby d.LastName
                                  select d;
            try
            {
                // Bind the ComboBox control to the query, 
                // which is executed during data binding.
                // To prevent the query from being executed multiple times during binding, 
                // it is recommended to bind controls to the result of the Execute method. 
                this.customerList.DisplayMember = "LastName";
                this.customerList.ValueMember = "CustomerID";
                this.customerList.DataSource = ((ObjectQuery)customerQuery)
                                               .Execute(MergeOption.AppendOnly);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }

In the next post we will update a customer using entity framework.

Thank you.