I have a Client class and a Meeting class, I am trying to retrieve all the next meetings for each Client. So that I would end up with one meeting per client.
I'm ending up with a query like this:
var qry = from client in session.Query<Client>()
select new
{
c = client,
e = client.Meetings.OrderBy(x => x.Date).First()
};
The sql generated is:
select
client0_.ClientId as col_0_0_,
(select
meetings1_.EventId
from
Event meetings1_
where
client0_.ClientId=meetings1_.ClientId
order by
meetings1_.Date asc) as col_1_0_,
client0开发者_开发知识库_.ClientId as ClientId13_,
client0_.ContactName as ContactN2_13_,
client0_.ClientStatus as ClientSt3_13_,
client0_.HomePhoneNumber as HomePhon4_13_,
client0_.FaxNumber as FaxNumber13_,
client0_.WorkPhoneNumber as WorkPhon6_13_,
from
Client client0_
I am expecting the sub query to have a top 1 but it does not, is this 'n problem with Linq to NHibernate or am I doing something wrong?
I dont know if this will work, but its worth a shot:
Session.Linq<Meeting>().OrderByDescending(x => x.DateOfMeeting).Distinct();
I'm trying to do the VERY same thing.
I tried using a Formula in my mapping, and this works when I can limit the rows returned without a top 1 or where rownum = 1 (like, if the state of a column in the row can be used: WHERE HasMeetingOccured = null, but obviously for this situation that won't work.)
For me - Oracle throws up about the syntax - for whatever reason it doesn't like the rownum restriction in the subquery - it breaks syntax somehow.
I haven't tried using the linq provider for it, nor do I know how to do this in the critiera API .. however, does Single() or Take(1) work?
精彩评论