1000+ Linq queries or logic in the database...which is worse?

Question

I had asked this question in a much more long-winded way a few days ago, and the fact that I got no answers isn't surprising considering the length, so I figured I'd get more to the point.

I have to make a decision about what to display a user based on their assignment to a particular customer. The domain object looks like this vastly simplified example:

public class Customer
{
    public string Name { get; set; }
    public IEnumerable<Users> AssignedUsers { get; set; }
}

In the real world, I'll also be evaluating whether they have permissions (using bitwise comparisons on security flags) to view this particular customer even if they aren't directly assigned to it.

I'm trying to stick to domain-driven design (DDD) principles here. Also, I'm using LINQ to SQL for my data access. In my service layer, I supply the user requesting the list of customers, which right now is about 1000 items and growing by about 2% a month.

If I am strict about keeping logic in my service layer, I will need to use Linq to do a .Where that evaluates whether the AssignedUsers list contains the user requesting the list. This will cause a cascade of queries for each Customer as the system enumerates through. I haven't done any testing, but this seems inefficient.

If I fudge on the no-logic-in-the-data, then I could simply have a GetCustomersByUser() method that will do an EXISTS type of SQL query and evaluate security at the same time. This will surely be way faster, but now I'm talking about logic creeping into the database, which might create problems later.

I'm sure this is a common question people come up on when rolling out Linq...any suggestions on which way is better? Is the performance hit of Linq's multiple queries better than logic in my database?

Answer 1

Which is worse? Depends who you ask.

Possibly if you ask the DDD ultra-purist they'll say logic in the database is worse.

If you ask pretty much anyone else, IMHO, especially your end users, pragmatic developers and the people who pay for the hardware and the software development, they'll probably say a large performance hit is worse.

DDD has much to commend it, as do lots of other design approaches, but they all fall down if you dogmatically follow them to the point of coming up with a "pure" design at the expense of real world considerations, such as performance.

If your really are having to perform this sort of query on data, then the database is almost certainly far better at performing the task.

Alternatively, have you "missed a trick" here. Is your design, however DDD, actually not right?

Overall - use your tools appropriately. By all means strive to keep logic cleanly seperated in your service layer, but not when that logic is doing large amounts of work that a database is designed for.