private void GetTransporters()
{
var session = DataConnector.NHibernateConnector.OpenSession();
NHibernate.ICriteria criteria = session.CreateCriteria(typeof(Models.Logistics.Planning));
criteria.SetProjection(
Projections.Distinct(Projections.ProjectionList()
.Add(Projections.Alias(Projections.Property("Transporter"), "Transporter"))
));
criteria.SetResultTransformer(
new NHibernate.Transform.AliasToBeanResultTransformer(typeof(Models.Logistics.Planning)));
var res = criteria.List <Models.Logistics.Planning>();
cmbTransporter.DataSource = res;
cmbTransporter.DisplayMember = "Transporter";
}
/*
*
* Added two versions of NHibernateHqlQueryStrongTypeRepository.GetSongsByArtist() - GetSongsByArtist2() and GetSongsByArtist3()
*
* the idea is:
* 1. the original version does 3 trips to DB running 3 queries (at very minimum) due to the dot (.) notation / lazy loading of the graphs
* 2. we can select all the Song data in one trip to DB running one query.
*
* Simple output:
*
* Loading from the original GetSongsByArtist.
* NHibernate: select track0_.TrackId as TrackId2_, track0_.AlbumId as AlbumId2_, track0_.Bytes as Bytes2_, track0_.Composer as Composer2_, track0_.GenreId as GenreId2_, track0_.MediaTypeId as MediaTyp6_2_, track0_.Milliseconds as Millisec7_2_, track0_.Name as Name2_, track0_.UnitPrice as UnitPrice2_ from Track track0_ inner join Album album1_ on track0_.AlbumId=album1_.AlbumId inner join Artist artist2_ on album1_.ArtistId=artist2_.ArtistId where artist2_.Name='Aerosmith'
* NHibernate: SELECT album0_.AlbumId as AlbumId1_0_, album0_.Title as Title1_0_, album0_.ArtistId as ArtistId1_0_ FROM Album album0_ WHERE album0_.AlbumId=@p0;@p0 = 5 [Type: Int32 (0)]
* NHibernate: SELECT artist0_.ArtistId as ArtistId0_0_, artist0_.Name as Name0_0_ FROM Artist artist0_ WHERE artist0_.ArtistId=@p0;@p0 = 3 [Type: Int32 (0)]
* Aerosmith - Big Ones - Walk On Water
* Aerosmith - Big Ones - Love In An Elevator
* Aerosmith - Big Ones - Rag Doll
* Aerosmith - Big Ones - What It Takes
* Aerosmith - Big Ones - Dude (Looks Like A Lady)
* Aerosmith - Big Ones - Janie's Got A Gun
* Aerosmith - Big Ones - Cryin'
* Aerosmith - Big Ones - Amazing
* Aerosmith - Big Ones - Blind Man
* Aerosmith - Big Ones - Deuces Are Wild
* Aerosmith - Big Ones - The Other Side
* Aerosmith - Big Ones - Crazy
* Aerosmith - Big Ones - Eat The Rich
* Aerosmith - Big Ones - Angel
* Aerosmith - Big Ones - Livin' On The Edge
* Loading from the new GetSongsByArtist2.
* NHibernate: SELECT album1_.Title as y0_, this_.Name as y1_, artist2_.Name as y2_ FROM Track this_ inner join Album album1_ on this_.AlbumId=album1_.AlbumId inner join Artist artist2_ on album1_.ArtistId=artist2_.ArtistId WHERE artist2_.Name = @p0;@p0 = 'Aerosmith' [Type: String (4000)]
* Aerosmith - Big Ones - Walk On Water
* Aerosmith - Big Ones - Love In An Elevator
* Aerosmith - Big Ones - Rag Doll
* Aerosmith - Big Ones - What It Takes
* Aerosmith - Big Ones - Dude (Looks Like A Lady)
* Aerosmith - Big Ones - Janie's Got A Gun
* Aerosmith - Big Ones - Cryin'
* Aerosmith - Big Ones - Amazing
* Aerosmith - Big Ones - Blind Man
* Aerosmith - Big Ones - Deuces Are Wild
* Aerosmith - Big Ones - The Other Side
* Aerosmith - Big Ones - Crazy
* Aerosmith - Big Ones - Eat The Rich
* Aerosmith - Big Ones - Angel
* Aerosmith - Big Ones - Livin' On The Edge
* Loading from the new GetSongsByArtist3.
* NHibernate: select album1_.Title as col_0_0_, track0_.Name as col_1_0_, artist2_.Name as col_2_0_ from Track track0_ inner join Album album1_ on track0_.AlbumId=album1_.AlbumId inner join Artist artist2_ on album1_.ArtistId=artist2_.ArtistId where artist2_.Name='Aerosmith'
* Aerosmith - Big Ones - Walk On Water
* Aerosmith - Big Ones - Love In An Elevator
* Aerosmith - Big Ones - Rag Doll
* Aerosmith - Big Ones - What It Takes
* Aerosmith - Big Ones - Dude (Looks Like A Lady)
* Aerosmith - Big Ones - Janie's Got A Gun
* Aerosmith - Big Ones - Cryin'
* Aerosmith - Big Ones - Amazing
* Aerosmith - Big Ones - Blind Man
* Aerosmith - Big Ones - Deuces Are Wild
* Aerosmith - Big Ones - The Other Side
* Aerosmith - Big Ones - Crazy
* Aerosmith - Big Ones - Eat The Rich
* Aerosmith - Big Ones - Angel
* Aerosmith - Big Ones - Livin' On The Edge
* Press <Enter> to finish.
*
*/
public IEnumerable <Song> GetSongsByArtist2(string name)
{
using (var session = NHibernateHelper.OpenSession())
{
NHibernate.ICriteria qry = session.CreateCriteria <Track>("track")
.CreateAlias("Album", "album")
.CreateAlias("album.Artist", "artist")
.Add(NHibernate.Criterion.Expression.Eq("artist.Name", name))
.SetProjection(NHibernate.Criterion.Projections.ProjectionList()
.Add(NHibernate.Criterion.Projections.Property("album.Title"), "AlbumName")
.Add(NHibernate.Criterion.Projections.Property("track.Name"), "SongName")
.Add(NHibernate.Criterion.Projections.Property("artist.Name"), "ArtistName")
);
qry.SetResultTransformer(NHibernate.Transform.Transformers.AliasToBean <Song>());
return(qry.List <Song>());
}
}