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 Query
- How to take this sql query and turn it into nhibernate query
- How to convert this sql query into linq query. I am stuck at the group by and sum part
- How can I turn SQL query that joins two columns and groups by count of one column and a column of each joined table 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))
- How to get SQL query into LINQ form in C# code
- How is this Nested Set SQL query converted into a LINQ query?
- How to convert this complex SQL Query with Subqueries into LINQ
- How to make this SQL query into DotNet Core 2.1 Linq
- How to convert sql inner join query into linq to sql query and convert into list
- How to rewrite this LINQ query with SQL syntax and <>?
- How to simplify this LINQ to Entities Query to make a less horrible SQL statement from it? (contains Distinct,GroupBy and Count)
- How do I convert this SQL inner join query into LINQ syntax?
- How to make this simple SQL query into a Linq Statement?
- How to convert this query string into LINQ to SQL query?
- How to use one Column of Sql query into another sql query inside C# Code
- How can i convert a SQL query into LINQ using group and having?
- How to turn a string into a ClassName and execute a Linq query
- What does this C# code with an "arrow" mean and how is it called?
- How to intercept and modify SQL query in Linq to SQL
- Statistical query in SQL - is this possible with NHibernate LINQ?
- How can I use LINQ to project this parent and children object model into a flat, single object?
- How to write this Linq SQL as a Dynamic Query (using strings)?
- How can I combine this code into one or two LINQ queries?
- How to turn this LINQ query to lazy loading
- How to turn array into quote and comma delimited string for sql?
- How to turn LINQ query into anonymous type
- How can I turn this 12-line method into a 1-line LINQ expression?
- How to translate this SQL query to a LINQ query in EF Core?
More Query from same tag
- Return IQueryable<T> from another class instance
- How to group inner list data based on a key in outer list in c# linq
- In linq, how do I take specific parameter out of a list and build new list of different type?
- Lambda Expression with a statement body can not been converted
- How to convert sql query into Linq to entities?
- how can i get my local DB data to fill my txt box using LINQ?
- How to call a method with no parameters which returns Expression<Func<Model, String>>?
- linq: what is the expression tree syntax for cross join
- Is it possible to use LINQ to detect duplicate adjacent characters?
- How to check that which properties/entries are modified and which are not on Editing a record in EF 6
- LINQ/Projection - How to sort TableA by non FK column id/name field per TableB?
- LINQ and EF6: The context cannot be used while the model is being created
- Problems with generic methods and predicates
- SQL to Linq group by count
- How to get one record only once from a List?
- Predicate inside a Where<> query not hit?
- How can I convert a BinaryExpression to Expression<Func<T, bool>>?
- How to break a .Where() with OR operator into separated function calls?
- List Tuple DateTime
- .NET - execute a lambda on another computer
- C#/Linq How to get all records by ParentId and then by FolderId recursively
- Difference between two overloads of Enumerable.Except?
- Implement Smart Search with Entity framework
- XML to LINQ element selection
- Will the database send over ALL the records if I don't use Take(1) before FirstOrDefault() in LINQ + Entity Framework?
- Linq expression for where condition on entity child
- c# and LINQ- group objects based on their IDs and then find common values in them
- Using Linq to find next element in subset of a list
- calling EntityTypeBuilder<T>.HasKey via reflection for compound keys
- System.Linq.Dynamic does not support OrderByDescending("someColumn")?