score:3
Accepted answer
Without mappings I assume that you have the following relationships: Courses
-> Tasks
(1:n) and Courses
-> CoursePermissions
(1:n)
I also assumed that you do not want the complete objects but only certain properties, so I used projections.
QueryOver version:
// the aliases are required here, so that we can reference the entities properly
Courses cAlias = null;
Tasks tAlias = null;
CoursePermissions cpAlias = null;
var result = session.QueryOver<Courses>(() => cAlias)
.JoinAlias(() => cAlias.Tasks, () => tAlias)
.JoinAlias(() => cAlias.CoursePermissions, () => cpAlias)
.Where(() => cAlias.CourseId == 1)
.Select(Projections.Property(() => cAlias.CourseId),
Projections.Property(() => cpAlias.BackgroundColor),
Projections.Property(() => tAlias.DueDate),
Projections.Property(() => tAlias.TaskName),
Projections.Property(() => tAlias.TaskId))
.List<object[]>();
Edit start
If you need to do a WHERE IN clause, you can do this:
List<int> courseIdList = new List<int>() { 1, 2 };
var result = session.QueryOver<Courses>(() => cAlias)
.JoinAlias(() => cAlias.Tasks, () => tAlias)
.JoinAlias(() => cAlias.CoursePermissions, () => cpAlias)
.Where(Restrictions.In(Projections.Property(() => cAlias.CourseId), courseIdList))
.Select(...)
.List<object[]>();
Edit end
Edit 2 start
If you want to transform it into a DTO:
// AliasToBeanResultTransformer is in namespace NHibernate.Transform
// we have to use .As("...") for the transformer to find the correct property-names
var result = ...
.Select(Projections.Property(() => cAlias.CourseId).As("CourseId"),
Projections.Property(() => cpAlias.BackgroundColor).As("BackgroundColor"),
Projections.Property(() => tAlias.DueDate).As("DueDate"),
Projections.Property(() => tAlias.TaskName).As("TaskName"),
Projections.Property(() => tAlias.TaskId).As("TaskId"))
.TransformUsing(new AliasToBeanResultTransformer(typeof(TaskAppointments)))
.List<TaskAppointments>();
Edit 2 end
HQL version:
string hql = "select c.CourseId, cp.BackgroundColor, t.DueDate, t.TaskName, t.TaskId"
+ " from Courses as c inner join c.Tasks as t inner join c.CoursePermissions as cp"
+ " where c.CourseId = 1";
var result2 = session.CreateQuery(hql)
.List<object[]>();
Be aware that this will result in a cartesian product, so for each Course you will get Tasks.Count + CoursePermissions.Count rows.
Source: stackoverflow.com
Related Articles
- How to take this sql query and turn it into nhibernate query
- Turn this code into LINQ
- How to turn a SQL Query Into Linq? "People who viewed this product also viewed this product."
- How do I translate this GROUP BY / MIN SQL query into LINQ?
- How can I convert this SQL Query into LINQ (OVER (PARTITION BY Date))
- Statistical query in SQL - is this possible with NHibernate LINQ?
- How can I combine this code into one or two LINQ queries?
- How to turn this LINQ query to lazy loading
- How to turn LINQ query into anonymous type
- How can I turn this 12-line method into a 1-line LINQ expression?
- How to get SQL query into LINQ form in C# code
- How do I get this Linq query with custom join into a DataSet?
- How is this Nested Set SQL query converted into a LINQ query?
- What would be a reasonably fast way to code this sql query in c#?
- NHibernate LINQ query performance, which code fragment is better?
- sub linq query is making this take a very long time, how can I make this faster?
- LINQ : Nesting this into a single query
- How to break complex query into different methods to achieve less code complexity
- How should I change this source into LINQ?
- How to convert this complex SQL Query with Subqueries into LINQ
- Is this the code the compiler generates from the following query expression?
- Can I make this two LINQ queries into one query only?
- Why does this NHibernate Linq query attempt to execute an insert statement?
- How to take driver id column using this linq query
- How to write this Linq to NHibernate query
- How to turn this Func into an expression?
- How can I code numerous MIN functions into one LINQ to DataSet query
- Reduce the line of code for this LINQ query
- Can I can convert this C# code into some Linq code?
- How to make this SQL query into DotNet Core 2.1 Linq
- Passing grouped linq object to view
- Remove items that match memory objects with RemoveRange
- Extra subquery generated on left join
- Transposing the list based on property names in C#
- Function evaluation disabled, Error in VS 2015 on Query LinQ Execution?
- LINQ - many to many
- EF Core query where sum of relationship is less than property
- How do i use navigation properties correctly EF?
- LINQ where clause with an if statement
- Performance penalty for accessing properties in base class (C#)
- Get Rank from list of object depending upon some condition
- Join three tables with Entity Framework
- How can i Insert into one table and Update another table using Linq
- LINQ to SQL - Why can't you use a WHERE after an ORDER BY?
- How can I conditionally apply a Linq operator?
- Using LINQ how do I create a List of one particular field of an entity from a collection entities
- Merging sequences by type With LINQ
- Nhibernate - Table-Valued Functions
- How can I store Linq to Sql objects in session using Windows Azure Cache Service (Preview)
- LINQ query works agains SQL Server but not Entity Framework/LINQ to SQL