private static void PrintPost(PostsPerUserInfo postPerUserInfo)
{
Console.WriteLine("Post from DB");
Console.WriteLine("------------");
Console.WriteLine("User: "******" " + postPerUserInfo.Lastname);
Console.WriteLine("NbrOfPosts: " + postPerUserInfo.NbrOfPosts);
}
private static void RunQueries(ISessionFactory sessionFactory)
{
using (var session = sessionFactory.OpenSession())
using (var tx = session.BeginTransaction())
{
Console.WriteLine("Press any key to retrieve a list of posts containing 'NH' in their description");
Console.ReadLine();
var postsContainingNhQuery = session.QueryOver <Post>();
postsContainingNhQuery.Left.JoinQueryOver(p => p.WrittenBy);
postsContainingNhQuery.Left.JoinQueryOver(p => p.Comments);
postsContainingNhQuery.Left.JoinQueryOver(p => p.Tags);
postsContainingNhQuery
.Where(p => p.Description.IsLike("NH", MatchMode.Anywhere))
.TransformUsing(Transformers.DistinctRootEntity);
var postsContainingNh = postsContainingNhQuery.List();
foreach (var post in postsContainingNh)
{
PrintPost(post);
}
Console.WriteLine();
Console.WriteLine("Press any key to retrieve a list of posts ordered by the date they were posted");
Console.ReadLine();
PostInfo postInfoAlias = null;
var postsOrderByPostedOn = session
.QueryOver <Post>()
.OrderBy(p => p.PostedOn).Asc()
.SelectList(s => s
.Select(p => p.Id).WithAlias(() => postInfoAlias.Id)
.Select(p => p.Description).WithAlias(() => postInfoAlias.Description)
.Select(p => p.PostedOn).WithAlias(() => postInfoAlias.PostedOn))
.TransformUsing(Transformers.AliasToBean <PostInfo>())
.List <PostInfo>();
foreach (var post in postsOrderByPostedOn)
{
PrintPost(post);
}
Console.WriteLine();
Console.WriteLine("Press any key to retrieve a list of posts that have a tag with description 'Tag1'");
Console.ReadLine();
var postWithSpecificTag = session
.QueryOver <Post>()
.Left.JoinQueryOver <Tag>(p => p.Tags)
.Where(t => t.Description == "Tag1")
.List();
foreach (var post in postWithSpecificTag)
{
PrintPost(post);
}
Console.WriteLine();
Console.WriteLine("Press any key to retrieve a list of users and the number of posts they wrote");
Console.ReadLine();
User userAlias = null;
PostsPerUserInfo postPerUserInfoAlias = null;
var postsPerUser = session
.QueryOver <Post>()
.Left.JoinAlias(p => p.WrittenBy, () => userAlias)
.SelectList(s => s
.SelectGroup(() => userAlias.Id).WithAlias(() => postPerUserInfoAlias.Id)
.SelectGroup(() => userAlias.Firstname).WithAlias(() => postPerUserInfoAlias.Firstname)
.SelectGroup(() => userAlias.Lastname).WithAlias(() => postPerUserInfoAlias.Lastname)
.SelectCount(p => p.Id).WithAlias(() => postPerUserInfoAlias.NbrOfPosts))
.TransformUsing(Transformers.AliasToBean <PostsPerUserInfo>())
.List <PostsPerUserInfo>();
foreach (var post in postsPerUser)
{
PrintPost(post);
}
tx.Rollback();
}
}
private static void PrintPost(PostsPerUserInfo postPerUserInfo)
{
Console.WriteLine("Post from DB");
Console.WriteLine("------------");
Console.WriteLine("User: "******" " + postPerUserInfo.Lastname);
Console.WriteLine("NbrOfPosts: " + postPerUserInfo.NbrOfPosts);
}