LinQ to SQL - Compiled Queries

Descrption:

This article demonstrates how to use compiled queries with LinQ. To eliminate the overhead of building query pipeline, compiled queries should be used.

e.g. This is the query to find all customers and employees living in "USA".

db.Customers
  .Select(c => new Person { PersonName = c.ContactName,CountryName= c.Country})
  .Union(db.Employees
  .Select(c => new Person { PersonName = c.FirstName, CountryName = c.Country}))
  .Where(c => c.CountryName == "USA"));

If this query is used frequently, we can avoid creation of query pipeline everytime. To compile the query, we can use CompiledQuery.Compile method. This method takes an argument list as input and a result type. It returns a delegate as a variable that we can use later. So here, we can pass the NorthwindDataContext instance and the country variable in. We will return a IQueryable(of Person) object. So the definition of our compiled query as follows:

public static Func<NorthwindDataContext,string,IQueryable<Person>>
            GetCustomernEmployees =
            CompiledQuery.Compile((NorthwindDataContext db,string country) =>
            db.Customers
                    .Select(c => new Person { PersonName = c.ContactName,CountryName= c.Country})
                    .Union(db.Employees
                    .Select(c => new Person { PersonName = c.FirstName, CountryName = c.Country}))
                    .Where(c => c.CountryName == "USA"));

LinQ to SQL - Extending DataContext with Visual Studio 2008

Description:

This article explains how to add a .dbml file to work with SQL Server 2005 database. This process automatically extends DataContext class for your database and create code for you. This code can be modified according to the need. User can easily create Entity classes by drag and drop from Server Explorer.


Steps:

1. Right click on Project >> Add >> New Item

2. Select "LinQ to SQL classes". Give name as "Northwind.dbml" (If you are connecting with Northwind database).


3. Open Server Explorer and connect to Northwind database.

4. Expand Northwind database in Server Explorer and drag tables to Northiwind.dbml file area.







5. Northwind.designer.cs file has complete code for NorthwindDataContext and all entity classes.

LinQ to SQL (union with multiple fields)

Description:

This example demonstrate how to perform union on two tables using multiple fields.

Example:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace LinQExamples
{
    class Program
    {
        static void Main(string[] args)
        {
            UnionExample();
            Console.Read();
        }

        public static void UnionExample()
        {
            using (NorthwindDataContext db = new NorthwindDataContext())
            {
                List<Person> persons = db.Customers
                    .Select(c => new Person { PersonName = c.ContactName,CountryName= c.Country})
                    .Union(db.Employees
                    .Select(c => new Person { PersonName = c.FirstName, CountryName = c.Country}))
                    .ToList<Person>();

                foreach (Person person in persons)
                {
                    Console.WriteLine(person.PersonName + "\t" + person.CountryName);
                }             
            }
        }
    }

    class Person
    {
        private string personName;
        private string countryName;

        public string PersonName
        {
            get { return personName; }
            set { personName = value; }
        }

        public string CountryName
        {
            get { return countryName; }
            set { countryName = value;}
        }
    }
}

LinQ to SQL basic sample

Description:
1. This sample shows how to connect SQL Server 2005 Northwind sample database with LinQ DataContext.
2. How to extend built-in DataContext class and have your own Strongly Typed class.
3. Querying the database and finding employees whose name starts with alphabet 'A'.

Step 1:
Download Sample database from here :
http://code.msdn.microsoft.com/northwind/Release/ProjectReleases.aspx?ReleaseId=1401

Step 2:
Sample Console Application Code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace LinQExamples
{
    class Program
    {
        public static string connectionString
            = "Data Source=localhost;Min Pool Size=50;Max Pool Size=200;Initial Catalog=Northwind;Integrated Security=True";

        static void Main(string[] args)
        {
            NorthwindDataContext db = new NorthwindDataContext(connectionString);

            var emps = db.Employees.Where(E => E.FirstName.StartsWith("A")).ToList();

            foreach (Employees empoyee in emps)
            {
                Console.WriteLine(empoyee.FirstName + "\t" + empoyee.LastName);
            }
        }
    }

    public partial class NorthwindDataContext: DataContext
    {
        partial void OnCreated();
        public const string defaultConnectionString
            = "Data Source=localhost;Min Pool Size=50;Max Pool Size=200;Initial Catalog=Northwind;Integrated Security=True";

        public System.Data.Linq.Table<Employees> Employees
        {
            get
            {
                return this.GetTable<Employees>();
            }
        }

        public NorthwindDataContext(): base(defaultConnectionString)
        {
            OnCreated();
        }

        public NorthwindDataContext(string connectionString)
            : base(connectionString)
        {
            OnCreated();
        }
    }

    [Table(Name = "dbo.Employees")]
    public class Employees
    {
        [Column]
        public string FirstName;
        [Column]
        public string LastName;
    }
}