如何从关联表向QueryOver投影中添加Translation字段？

Let's break down what went wrong with your original attempt and fix it properly.

Why Your Original Code Threw an Exception #

The error Unrecognised method call in expression value happens because NHibernate's QueryOver needs to translate your C# expressions into SQL. When you tried to run _session.QueryOver(...).SingleOrDefault() directly inside the projection, you're executing a runtime C# method that NHibernate can't convert into a SQL statement. That's a no-go for query projections.

Solution 1: Join Directly to Translation (One-to-One or Unique Constraint) #

If each Product has exactly one Translation (or you only care about a single matching translation), you can join the Translation entity directly to your query using JoinAlias or JoinQueryOver:

Catalog catalogAlias = null;
Product productAlias = null;
Translation translationAlias = null;

return _session
    .QueryOver(() => catalogAlias)
    .JoinAlias(() => catalogAlias.ProductList, () => productAlias)
    // Join Translation to Product using the foreign key relationship
    .JoinAlias(() => productAlias, () => translationAlias, JoinType.LeftOuterJoin)
    .Where(() => translationAlias.Product.Id == productAlias.Id)
    .Select(
        Projections.ProjectionList()
            .Add(Projections.Property(() => catalogAlias.Id))
            .Add(Projections.Property(() => productAlias.Name))
            .Add(Projections.Property(() => translationAlias.Name))
    )
    .Future<object[]>();

If your Product entity has a direct navigation property to Translation (e.g., public Translation Translation { get; set; }), you can simplify the join to just:

.JoinAlias(() => productAlias.Translation, () => translationAlias)

Solution 2: Use a Subquery (One-to-Many Translation Relationships) #

If a Product can have multiple Translations (like multi-language content) and you need to fetch a specific one (e.g., English translations), use a subquery in your projection to avoid duplicate rows from Cartesian product:

Catalog catalogAlias = null;
Product productAlias = null;
Translation translationAlias = null;

// Define a subquery to get the desired Translation for each Product
var translationSubQuery = QueryOver.Of(() => translationAlias)
    .Where(() => translationAlias.Product.Id == productAlias.Id)
    .And(() => translationAlias.Language == "en") // Filter for your target language
    .Select(Projections.Property(() => translationAlias.Name));

return _session
    .QueryOver(() => catalogAlias)
    .JoinAlias(() => catalogAlias.ProductList, () => productAlias)
    .Select(
        Projections.ProjectionList()
            .Add(Projections.Property(() => catalogAlias.Id))
            .Add(Projections.Property(() => productAlias.Name))
            .Add(Projections.SubQuery(translationSubQuery))
    )
    .Future<object[]>();

Key Notes #

• Avoid Cartesian Products: If you join directly to a one-to-many Translation collection without filtering, you'll get duplicate Catalog/Product rows for each matching Translation. Subqueries or post-query grouping in memory can fix this.
• Navigation Properties: Adding a navigation property from Product to Translation (or its collection) will make your queries cleaner and easier to maintain, even if you didn't have it before.

内容的提问来源于stack exchange，提问作者Raphael Schmitz