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
    }
}