Mapping a collection to a subselect with Fluent nHibernate

Question

I've got a class that looks like:

public class Competitor
{
    public virtual int CompetitorId { get; set; }
    public virtual string FirstName { get; set; }
    public virtual string LastName { get; set; }

    public virtual IEnumerable<string> SportsCompeted { get; set; }
}

SportsCompeted is a list of SportIDs (strings) resolved like so:

SELECT DISTINCT SportID FROM results WHERE competitorId = xxx

How would I go about mapping something like that?
Looking at HasMany I can specify a Where clause, but I don't think that's quite what I'm looking for in this case?

I'm using Fluent Mappings, omitted for brevity.

score 1 · Answer 1 · edited May 23 '17 at 12:29

1

You should be able to this with .Element(). Something like:

HasMany(x => x.SportsCompeted)
    .KeyColumn("CompetitorId")
    .Element("SportID") // You can define element type as second parameter
    .Table("results");

More info:
Mapping collection of strings with NHibernate
Fluent NHIbernate automapping of List<string>?

Edit:

Let's say that you have your Result and Sport entities instead:

public class Sport
{
    public virtual int SportId { get; set; }
    // Other properties
}

public class Result : Entity
{
    public virtual ResultId { get; set; }
    public virtual Competitor Competitor { get; set; }
    public virtual Sport Sport { get; set; }
    // Other properties
}

public class Competitor
{
    public virtual int CompetitorId { get; set; }
    public virtual IList<Result> Results { get; set; }
    // Other properties
}

Your HasMany would now look like this:

// For this, you would need to have Result and Sport classes mapped
// This property isn't necessary for your Sports Competed query
HasMany(x => x.Results)
    .KeyColumn("CompetitorId")
    .Table("results");

Then you could use ie. Linq to get the results you want:

var sports = session.Query<Result>()
    .Where(x => x.Competitor.CompetitorId = competitorId)
    .Select(x => x.Sport) // Or .Select(x => x.Sport.SportId)
    .Distinct();

edited May 23 '17 at 12:29

Community

1
1

answered Jul 02 '12 at 16:37

Miroslav Popovic

12,100
2
35
47

but that doesn't take into account the DISTINCT part does it? or am i reading this wrong...? My results table has many SportID for the same CompetitorID – Alex Jul 02 '12 at 16:38
Sorry, I failed to see `DISTINCT` clause there. You're right about `Where` not being helpful in your case. Instead of doing this within mapping, I would rather have a separate query (HQL, Criteria, QueryOver or LINQ) in a data layer that will get a list of sports competed ids by `CompetitorId`. – Miroslav Popovic Jul 02 '12 at 16:48
Sounds good... have you got any example of how to do this with Fluent? – Alex Jul 02 '12 at 20:11
I've updated my answer. Hopefully, it will give you a starting point to think about. – Miroslav Popovic Jul 02 '12 at 20:34
Is there anyway to lazy-load this? – Alex Jul 03 '12 at 14:18
The last expression is just a query you would be calling when necessary. The `Competitor.Results` property, although not used in this query, should be lazy loaded by default. – Miroslav Popovic Jul 03 '12 at 14:23
hmm- i can't go changing the model though- this is being extracted from SQL, and then stored in a nosql store for querying... so trying to do my HasMany as a lazy-loaded query i think? – Alex Jul 03 '12 at 14:26

score 0 · Accepted Answer · answered Jul 03 '12 at 15:37

What I ended up doing was creating a View in SQL to basically do:

SELECT DISTINCT SportID, CompetitorID FROM results

Then, in my mapping:

HasMany(x => x.CompetitorDisciplines)
    .Not.LazyLoad()
    .Inverse()
    .AsBag()
    .KeyColumn("competitorId")
    .Element("DisciplineCode")
    .Table("vCompetitorDisciplines");

That seemed to produce the desired result.
Slow, but it's only a one time (or once per day) operation....

Mapping a collection to a subselect with Fluent nHibernate

2 Answers2