public void SQLTest()
{
var dbConnection = new DatabaseConnection();
ISessionFactory sessionFactory = dbConnection.GetSessionFactory();
IList<DaeHeurigen> returnList;
string name = "";
int id = 2;
int skip = 0;
int limit = 20;
using (var session = sessionFactory.OpenSession())
{
DaeHeurigen myHeurigen = null;
try
{
var query = session.CreateCriteria<DaeHeurigen>("h")
.CreateCriteria("h.Ratings", "r", JoinType.LeftOuterJoin)
.SetProjection(Projections.ProjectionList()
.Add(Projections.Property("h.Name").WithAlias(() => myHeurigen.Name))
.Add(Projections.Property("h.Postcode").WithAlias(() => myHeurigen.Postcode))
.Add(Projections.Property("h.City").WithAlias(() => myHeurigen.City))
.Add(Projections.Property("h.Street").WithAlias(() => myHeurigen.Street))
.Add(Projections.Property("h.Telephone").WithAlias(() => myHeurigen.Telephone))
.Add(Projections.Property("h.Mail").WithAlias(() => myHeurigen.Mail))
.Add(Projections.Property("h.HomepageUrl").WithAlias(() => myHeurigen.HomepageUrl))
.Add(Projections.Property("h.Description").WithAlias(() => myHeurigen.Description))
.Add(Projections.Property("h.Latitude").WithAlias(() => myHeurigen.Latitude))
.Add(Projections.Property("h.Longitude").WithAlias(() => myHeurigen.Longitude))
.Add(Projections.Property("h.WarmFood").WithAlias(() => myHeurigen.WarmFood))
.Add(Projections.Property("h.Logo").WithAlias(() => myHeurigen.Logo))
.Add(Projections.Avg("r.RatingStars").WithAlias(() => myHeurigen.AverageRating))
.Add(Projections.GroupProperty("h.Id").WithAlias(() => myHeurigen.Id)))
.SetResultTransformer(Transformers.AliasToBean<DaeHeurigen>());
if (!string.IsNullOrEmpty(name))
{
query.Add(Restrictions.Like("h.Name", name, MatchMode.Anywhere));
}
if (id != 0)
{
query.Add(Restrictions.Eq("h.Id", id));
}
query.SetFirstResult(skip);
query.SetMaxResults(limit);
returnList = query.List<DaeHeurigen>();
}
catch (Exception)
{
throw;
}
}
Assert.IsNotNull(returnList);
}
public void SQLRatingTest()
{
var dbConnection = new DatabaseConnection();
ISessionFactory sessionFactory = dbConnection.GetSessionFactory();
IList<DaeRating> returnList;
using (var session = sessionFactory.OpenSession())
{
var rquery = session.CreateCriteria<DaeRating>()
.Add(Restrictions.Eq("FkHeurigenId", 1));
returnList = rquery.List<DaeRating>();
}
Assert.IsTrue(returnList.Any());
}
public void MySQLJoinTest()
{
var dbConnection = new DatabaseConnection();
ISessionFactory sessionFactory = dbConnection.GetSessionFactory();
IList<DaeHeurigen> returnList;
using (var session = sessionFactory.OpenSession())
{
DaeHeurigen myHeurigen = null;
var query = session.CreateCriteria<DaeHeurigen>("h")
.CreateCriteria("h.Ratings", "r", JoinType.InnerJoin)
.SetProjection(Projections.ProjectionList()
.Add(Projections.GroupProperty("h.Id").WithAlias(() => myHeurigen.Id))
.Add(Projections.Property("h.Name").WithAlias(() => myHeurigen.Name))
.Add(Projections.Property("h.Postcode").WithAlias(() => myHeurigen.Postcode))
.Add(Projections.Property("h.City").WithAlias(() => myHeurigen.City))
.Add(Projections.Property("h.Street").WithAlias(() => myHeurigen.Street))
.Add(Projections.Property("h.Telephone").WithAlias(() => myHeurigen.Telephone))
.Add(Projections.Property("h.Mail").WithAlias(() => myHeurigen.Mail))
.Add(Projections.Property("h.HomepageUrl").WithAlias(() => myHeurigen.HomepageUrl))
.Add(Projections.Property("h.Description").WithAlias(() => myHeurigen.Description))
.Add(Projections.Property("h.Latitude").WithAlias(() => myHeurigen.Latitude))
.Add(Projections.Property("h.Longitude").WithAlias(() => myHeurigen.Longitude))
.Add(Projections.Property("h.WarmFood").WithAlias(() => myHeurigen.WarmFood))
.Add(Projections.Avg("r.RatingStars").WithAlias(() => myHeurigen.AverageRating))
.Add(Projections.Property("h.Logo").WithAlias(() => myHeurigen.Logo)))
.SetResultTransformer(Transformers.AliasToBean<DaeHeurigen>());
returnList = query.List<DaeHeurigen>();
}
Assert.IsNotNull(returnList);
}