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.

Answer 2

If AssignedUser is properly mapped (i.e. assosiation is generated by Linq2SQL designer or you have mark property with AssosiationAttribute (or some other from http://msdn.microsoft.com/en-us/library/system.data.linq.mapping(v=VS.90).aspx namespace, I'm not sure right now), Linq2Sql will translate linq query to SQL command, and will not iterate throught AssingedUser for each Customer.

Also you may use 'reversed' query like

from c in Customer
join u in Users on c.CustomerId equals u.AssignedToCustomerId // or other condition links user to customer
where <you condition here>
select c

Answer 3

LINQ is an abstraction, it wraps a bunch of functionality into a nice little package with a big heart on top.

With any abstraction, you're going to get overhead mainly because things are just not as efficient as you or I might want them. MS did a great job in making LINQ quite efficient.

Logic should be where it is needed.Pure is nice, but if you are delivering a service or product you have to have the following in mind (these are in no particular order):

1. Maintenance. Will you be easily be able to get some work done after it's released without pulling the entire thing apart.
2. Scalability.
3. Performance
4. Usability.

Number 3 is one of the biggest aspects when working with the web. Would you do trigonometry on a SQL Server? No. Would you filter results based on input parameters? Yes.

SQL Server is built to handle massive queries, filtering, sorting, and data mining. It thrives on that, so let it do it.

It's not a logic creep, it's putting functionality where it belongs.

Answer 4

I'd personally go with the stored proc. That's the right tool for job. Not using it might be a nice design choice, but design paradigms are there to guide you, not to constrain you, in my opinion. Plus, the boss only cares about performance :-)

Answer 5

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.

There should be no need to enumerate a local Customer collection.

The primary purpose of LinqToSql, is to allow you to declare the logic in the service layer, and execute that logic in the data layer.

int userId = CurrentUser.UserId;

IEnumerable<Customer> customerQuery =
  from c in dataContext.Customers
  where c.assignedUsers.Any(au => au.UserId = userId)
  select c;

List<Customer> result = customerQuery.ToList();

Answer 6

I think your model is best described as a many-to-many relationship between the Customer class and the User class. Each User references a list of related Customers, and each Customer references a list of related Users. From a database perspective, this is expressed using a join table (according to Microsoft's LINQ to SQL terminology, they call this a "junction table").

Many-to-many relationships is the one feature LINQ to SQL doesn't support out of the box, you probably will notice this if you tried generating a DBML.

Several blogs have published workarounds, including one from MSDN (without any concrete examples, unfortunately). There's one blog (2-part post) which closely adheres to the MSDN suggestion:

http://blogs.msdn.com/b/mitsu/archive/2007/06/21/how-to-implement-a-many-to-many-relationship-using-linq-to-sql.aspx

http://blogs.msdn.com/b/mitsu/archive/2008/03/19/how-to-implement-a-many-to-many-relationship-using-linq-to-sql-part-ii-add-remove-support.aspx