Design and Architecture

var anonCustWithOrderCt = from c in db.Customers
                          where c.LastName = "Smith"
                          select new
                          {
                              c.CustomerID,
                              c.LastName,
                              c.FirstName,
                              OrdersInLastSixMonths = c.Orders.Count(o => o.OrderDate > DateTime.Now.AddMonths(-6))
                          }

SELECT [t0].[CustomerID], [t0].LastName, [t0].FirstName, (
    SELECT COUNT(*)
    FROM [Orders] AS [t1]
    WHERE ([t1].[OrderDate] > @p0) AND ([t1].[CustomerID] = [t0].[CustomerID])
    ) AS [OrdersInLastSixMonths]
FROM [Customers] AS [t0]
WHERE [t0].LastName = 'Smith'
-- @p0: Input DateTime (Size = 0; Prec = 0; Scale = 0)

var query = from o in MyObjects select o;

// Apply dynamic criteria
if (minID > Int32.MinValue)
{
    query = query.Where(o => o.ID >= minID);
}

if (maxID > Int32.MaxValue)
{
    query = query.Where(o => o.ID <= maxID);
}

if (name != null && IsEqual)
{
    query = query.Where(o => o.Name == name);
}
else if (name != null && ContainedWithin)
{
    query = query.Where(o => o.Name.Contains(name));
}

// Apply dynamic order by
switch (orderField)
{
    case "Name": query = query.OrderBy(o => o.Name); break;
    case "ID": query = query.OrderBy(o => o.ID); break;
    default: query = query.OrderBy(o => o.OrderIndex); break;
}

// Our query is now set up, lets iterate it to retrieve and process data
foreach (var result in query)
{
    Console.WriteLine("ID: " + result.ID + ", Name: " + result.Name);
}

// Service layer
class PersonService
{
    Person Load(int id)
    {
        // Validate ID is greater than 0
        PersonManager mgr = new PersonManager();
        Person person = mgr.GetByID(id);

        return person;
    }

    void Save(Person person)
    {
        PersonManager mgr = new PersonManager();
        
        if (person.ID <= 0)
        {
            mgr.Insert(person);
        }
        else
        {
            mgr.Update(person);
        }
    }
}

// Business Layer
class Person
{
    int ID { get; set; }
    string Name { get; set; }
}

// Data Access Layer
class PersonManager // This is really a 'Repository' (http://blogs.hibernatingrhinos.com/nhibernate/archive/2008/10/08/the-repository-pattern.aspx)
{
    Person GetByID(int id)
    {
        // Load a person object from the database
    }

    void Update(Person person)
    {
        // Update a person in the database
    }

    Person Insert(Person person)
    {
        // Insert a person into the database, returning the person with an updated Key (ID in this case)
    }

    void Delete(Person person)
    {
        // Delete a person from the database
    }
}

// API - This is shared between all layers
interface IRepository<t>
{
    T GetByID(int id);
    T Insert(T item);
    void Update(T item);
    void Delete(T item);
}

class DataAccessException: ApplicationException
{
    // ...
}

class BusinessException: ApplicationException
{
    // ...
}

class PresentationException: ApplicationException
{
    // ...
}

// Service layer - Primary exception handling here
class PersonService
{
    public PersonService(IRepository<person> repository)
    {
        _repository = repository;  // Inject the repository, so it can be mocked when you unit test PersonService
    }

    private IRepositoru<person> _repository;

    Person Load(int id)
    {
        // Always throw ArgumentExceptions for parameter validations, rather than BusinessExceptions
        if (id <= 0) throw new ArgumentException("The Person ID must be greater than zero."); 

        try
        {
            Person person = _repository.GetByID(id);
            return person;
        }
        catch (DataAccessException ex)
        {
            throw new BusinessException("An error occurred while accessing data.", ex);
        }
        catch (ArgumentNullException ex)
        {
            throw ex;
        }
        catch (ArgumentException ex)
        {
            throw ex;
        }
        catch (Exception ex)
        {
            throw new BusinessException("An error occurred while processing your request.", ex);
        }
    }

    void Save(Person person)
    {
        if (person == null) throw new ArgumentNullException("person");
        
        try
        {
            if (person.ID <= 0)
            {
                _repository.Insert(person);
            }
            else
            {
                _repository.Update(person);
            }
        }
        catch (DataAccessException ex)
        {
            throw new BusinessException("An error occurred while updating data.", ex);
        }
        catch (ArgumentNullException ex)
        {
            throw ex;
        }
        catch (ArgumentException ex)
        {
            throw ex;
        }
        catch (Exception ex)
        {
            throw new BusinessException("An error occurred while processing your request.", ex);
        }
    }
}

// Business Layer - Just data and basic behavior here
class Person
{
    int ID { get; set; }
    string Name { get; set; }
}

// Data Access Layer - Handle all data management here
class PersonRepository: IRepository<person> // Implements IRepository<t> from API
{
    Person GetByID(int id)
    {
        if (id <= 0) throw new ArgumentException("The ID must be greater than zero.");

        try
        {
            // Load a person object from the database
        }
        catch (Exception ex)
        {
            throw new DataAccessException(
                String.Format("An error occurred retrieving the requested Person: {0}", id), ex
            );
        }
    }

    void Update(Person person)
    {
        if (person == null) throw new ArgumentNullException(person);

        try
        {
            // Update a person in the database
        }
        catch (Exception ex)
        {
            throw new DataAccessException(
                String.Format("An error occurred updating an existing Person: {0}", person.ID), ex
        }
    }

    Person Insert(Person person)
    {
        if (person == null) throw new ArgumentNullException(person);

        try
        {
            // Insert a person into the database, returning the person with an updated Key (ID in this case)
        }
        catch (Exception ex)
        {
            throw new DataAccessException(
                "An error occurred inserting a new Person.", ex 
            );
        }
    }

    void Delete(Person person)
    {
        if (person == null) throw new ArgumentNullException(person);

        try
        {
            // Delete a person from the database
        }
        catch (Exception ex)
        {
            throw new DataAccessException(
                String.Format("An error occurred deleting an existing Person: {0}", person.ID), ex
            );
        }
    }
}
</t></person></person></person></t>

public class Person
{
    public DateTime Birthdate { get{ ... something secret ... } }
    public int Age { get { ... something secret ... } }
}

try
{
    ... exception thrown here ...
}
catch(Exception e)
{
    if(null != e.InnerException)
    {
        if(e.InnerException is SqlException)
        {
            ... do something SqlException specific here ...
        }
    }
}