I have the following objects:
public abstract class Event {
public virtual Guid Id { get; set; }
public virtual string Description { get; set; }
}
public class TeamEvent : Event {
public virtual Guid Id { get; set; }
public virtual Team Team { get; set; }
}
public class PersonEvent : Event {
public virtual Guid Id { get; set; }
public virtual Person Person { get; set; }
}
public class Team {
public virtual Guid Id { get; set; }
public virtual string Name { get; set; }
}
public class Person {
public virtual Guid Id { get; set; }
public virtual string Forename { get; set; }
public virtual string Surname { get; set; }
}
And Mappings:
<discriminator column="Discriminator"
not-null="true"
type="System.String"/>
<subclass
name="TeamEvent"
discriminator-value="TeamEvent">
<many-to-one name="Team" column="TeamId"/>
</subclass>
<subclass
name="PersonalEvent"
discriminator-value="PersonalEvent">
<many-to-one name="Person" column="PersonId"/>
</subclass>
</class>
<class name="Person" table="Persons">
<id name="Id">
<generator class="assigned"/>
</id>
<property name="Forename"/>
<property name="Surname"/>
</class>
<class name="Team" table="Teams">
<id name="Id">
<generator class="assigned"/>
</id>
<property name="TeamName"/>
</class>
What I'd like to do is a query that would get all events regardless of base type in one query to populate a DTO like:
public class EventDTO {
public virtual Guid EventId { get; set; }
public virtual string EventDescription { get; set; }
public virtual Guid PersonId { get; set; }
public virtual string PersonForename { get; set; }
public virtual string PersonSurname { get; set; }
public virtual Guid TeamId { get; set; }
public virtual string TeamName { get; set; }
}
Where if it's a person event, the team fields are null and vice versa.
In SQL I'd just do something like:
Select * from Events
left join Team on Events.TeamId = Team.Id
left join Person on Events.Person = Person.Id
I can't do this in QueryOver as the Events c# object doesn't have properties for Team/Person. I can do it like so:
var events = session.QueryOver<Event>
.Where ( /* insert my restrictions */ )
.Future<Event>();
var teamEvents = session.QueryOver<TeamEvent>
.Where ( /* insert my restrictions */ )
.Future<TeamEvent>();
var personEvents = session.QueryOver<PersonEvent>
.Where ( /* insert my restrictions */ )
.Future<PersonEvent>();
/* union the results in memory */
The amount of data coming back from these queries should be such that it's feasible to just union the results in memory. My question is really is there a more elegant way of doing this? without doing three separate queries.
I should point out this is just a quick example I've typed up to highlight my question so there may be some typos, etc in the above code samples. In the real world problem there is other parts of the system depending on the mappings, so changing the schema/mappings/objects is not an option.
And whilst I'd prefer to use query over, I don't mind using criteria or hql if it's possible to do it in a simpler way there. Also this query will only return a max of 20 rows, of entities that are frequently accessed and thus stored in second level cache, so even an N + 1 isn't a major issue if it makes the code simple.
I should also point out the reason the three queries one isn't so simple is because there needs to be 10 restrictions that need to be duplicated per query, and there are a couple of other subclasses of event.
Thanks for any help.
When I hit problems like this I stop fighting NH and create a DB view. that I map as mutable=false. This way I don't get in a mess with paging or with my where clauses and merging multiple sets on client.
Not sure if there is a nicer way (especially as you have related tables that you want to flatten) than what you have come up with. As you have said that you don't have much data then merging on the client might be best.