Linq to NHibernate generating 3,000+ SQL statements in one request!

Question

Hi,

I've been developing a webapp using Linq to NHibernate for the past few months, but haven't profiled the SQL it generates until now. Using NH Profiler, it now seems that the following chunk of code hits the DB more than 3,000 times when the Linq expression is executed.

        var activeCaseList = from c in UserRepository.GetCasesByProjectManagerID(consultantId)
                             where c.CompletionDate == null
                             select new { c.PropertyID, c.Reference, c.Property.Address, DaysOld = DateTime.Now.Subtract(c.CreationDate).Days, JobValue = String.Format("£{0:0,0}", c.JobValue), c.CurrentStatus };

Where the Repository method looks like:

    public IEnumerable<Case> GetCasesByProjectManagerID(int projectManagerId)
    {
        return from c in Session.Linq<Case>()
               where c.ProjectManagerID == projectManagerId
               select c;
    }

It appears to run the initial Repository query first, then iterates through all of the results checking to see if the CompletionDate is null, but issuing a query to get c.Property.Address first.

So if the initial query returns 2,000 records, even if only five of them have no CompletionDate, it still fires off an SQL query to bring back the address details for the 2,000 records.

The way I had imagined this would work, is that it would evaluate all of the WHERE and SELECT clauses and simply amalgamate them, so the inital query would be like:

SELECT ... WHERE ProjectManager = @p1 AND CompleteDate IS NOT NULL

Which would yield 5 records, and then it could fire the further 5 queries to obtain the addresses. Am I expecting too much here, or am I simply doing something wrong?

Anthony

Answer 1

Change the declaration of GetCasesByProjectManagerID:

public IQueryable<Case> GetCasesByProjectManagerID(int projectManagerId)

You can't compose queries with IEnumerable<T> - they're just sequences. IQueryable<T> is specifically designed for composition like this.

Answer 2

Since I can't add a comment yet. Jon Skeet is right you'll want to use IQueryable, this is allows the Linq provider to Lazily construct the SQL. IEnumerable is the eager version.