Full Text Search with Query expression on a joined entity

I have the following data model.

I would like to get the list of Hymn entities which title or which stanzas contain certain Full-Text keywords. What is the best way of doing this? I tried the following,

            query = new Query(typeof(Hymn));
            query.Join = Join.Inner<Hymn, HymnStanza>("Id", "HymnId");
            query.SearchQuery = keyword;
            query.QueryExpression = Entity.Attribute("HymnalId") == HymnalId;
            var results = unitOfWork.Search(query, typeof(Hymn), typeof(HymnStanza));

However, I get the following error "Query Error: Could not find field [HymnalId] on model [HymnStanza]", which means that lightspeed is trying to find the "HymnalId" column in the HymnStanza table instead of the joined Hymns table.

Also, I only need the distinct list of Hymn entities, what is the best way of achieving this from the search results?

Thank you!

If you are specifying a QueryExpression when searching over multiple entities then the expression needs to be satisfiable by all of the entity types involved. So effectively this is the same as doing the same search for Hymn and then again for HymnStanza.

You have different criteria for each query though, so you will either need to execute this as 2 queries, e.g.

query.SearchQuery = keyword;
query.QueryExpression = Entity.Attribute("HymnalId") == HymnalId;
var hymnResults = unitOfWork.Search(query, typeof(Hymn));

var stanzaResults = unitOfWork.Search(keyword, typeof(HymnStanza));

And then combine the results as required. Alternatively you could avoid specifying the query expression and then subsequently filter out any Hymn results, e.g.

var results = unitOfWork.Search(keyword, typeof(Hymn), typeof(HymnStanza));
var filteredHymns = results.Where(r => r.Entity is Hymn).Select(r => r.Entity).Where(h => h.HymnalId == HymnalId).Distinct().ToList();

Jeremy