如何用Linq和NHibernate实现子查询并优化数据库查询量？

Absolutely! You can pull this off with LINQ to NHibernate—no need to prematurely yank all data into memory with ToList() first. The key is to let NHibernate translate your LINQ logic directly into efficient SQL, so the database does the heavy lifting and only sends back the data you actually need. Let’s walk through how to do this, using common scenarios you might be dealing with.

Common Scenario Example: Filtering Contracts with Matching Plans #

Say your goal is to get all Contracts that have at least one active Plan with a cost over 100, and you only need specific properties from both entities.

The Inefficient Way (Your Current Approach) #

This pulls all contracts into memory first, then filters there—wasting bandwidth and CPU on data you don’t need:

// Not ideal: Loads ALL contracts into memory first
var allContracts = session.Query<Contract>().ToList();
var filtered = allContracts
    .Where(c => c.Plans.Any(p => p.IsActive && p.Cost > 100))
    .ToList();

The Efficient LINQ to NHibernate Approach #

Skip the early ToList() and let NHibernate generate a SQL subquery that filters at the database level. You can also project only the properties you need to minimize data transfer:

// Ideal: NHibernate translates this to a SQL subquery, database does the filtering
var filteredContracts = session.Query<Contract>()
    // Filter contracts where any related plan meets your criteria
    .Where(c => c.Plans.Any(p => p.IsActive && p.Cost > 100))
    // Project only the properties you need (no full entity loading)
    .Select(c => new 
    {
        ContractId = c.Id,
        ContractName = c.Name,
        // Include only matching plans, not all of them
        ActiveHighCostPlans = c.Plans
            .Where(p => p.IsActive && p.Cost > 100)
            .Select(p => new { PlanId = p.Id, PlanName = p.Name, p.Cost })
    })
    .ToList();

NHibernate will convert this into SQL that uses an EXISTS subquery for filtering, and only selects the columns you specified—no unnecessary data pulled from the database.

More Complex Scenario: Get the Latest Plan per Contract #

If you need to fetch the most recently created Plan for each Contract, you can use LINQ’s ordering and FirstOrDefault() directly in the query:

var latestPlansPerContract = session.Query<Contract>()
    .Select(c => new 
    {
        c.Id,
        c.Name,
        LatestPlan = c.Plans
            .OrderByDescending(p => p.CreatedDate)
            .FirstOrDefault()
    })
    .ToList();

NHibernate will translate this into SQL that uses a window function (like ROW_NUMBER()) or a correlated subquery to get the latest plan per contract—all executed at the database level.

Key Tips to Avoid Pitfalls #

1. Avoid N+1 Queries
   If you need to load related entities, use FetchMany (for collections) or Fetch (for single associations) to join in the initial query. Just note: FetchMany will load all related entities, so if you need to filter the child collection, projection (like the Select example above) is better than Fetch.

   // Use FetchMany only if you need all related plans, not filtered ones
   var contractsWithAllPlans = session.Query<Contract>()
       .Where(c => c.Plans.Any(p => p.IsActive))
       .FetchMany(c => c.Plans)
       .ToList();
   

2. Validate Generated SQL
   Enable NHibernate’s SQL logging (via log4net, Serilog, etc.) to inspect the SQL that gets generated. This ensures your LINQ logic is being translated correctly and that no unnecessary data is being pulled. For example, the first scenario’s SQL will look something like:

   SELECT c.Id, c.Name
   FROM Contract c
   WHERE EXISTS (
       SELECT 1 FROM Plan p
       WHERE p.ContractId = c.Id AND p.IsActive = 1 AND p.Cost > 100
   )
   

3. Stick to NHibernate-Supported LINQ Methods
   Not all LINQ methods are supported by NHibernate’s LINQ provider. If you hit a parsing error, check the NHibernate documentation for supported methods. For edge cases, you can fall back to QueryOver or native SQL, but LINQ should cover most common subquery scenarios.

By keeping your LINQ queries "unbroken" (no early ToList()), you let NHibernate leverage the database’s query optimizer, resulting in faster, more efficient data retrieval—way better than that 1-second in-memory filter you’re dealing with now.

内容的提问来源于stack exchange，提问作者Keith Nicholas