CODEWITHSUNDEEP
c#nhibernatefluent-nhibernate
Alex
Alex

Reputation: 38509

Mapping a collection to a subselect with Fluent nHibernate

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.

Upvotes: 1

Views: 2938

Answers (2)

Alex
Alex

Reputation: 38509

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....

Upvotes: 0

Miroslav Popovic
Miroslav Popovic

Reputation: 12128

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();

Upvotes: 1

Related Questions