Linq to nHibernate - exclude elements without child elements

Question

var locations = (from location in session.Query<Location>()                                     
             where 
                (location.MB_ID == 0 || location.MB_ID == null) &&
                (location.hide != "Y" || location.hide == null) &&
                (location.locationNameRaw != "" && location.locationNameRaw != null) &&
                ((location.isIPCapableText != "" && location.isIPCapableText != null) || (
                    (location.ISDNNumber1 != null && location.ISDNNumber1 != "") ||
                    (location.ISDNNumber2 != null && location.ISDNNumber2 != "") ||
                    (location.ISDNNumber3 != null && location.ISDNNumber3 != "") ||
                    (location.ISDNNumber4 != null && location.ISDNNumber4 != "") ||
                    (location.ISDNNumber5 != null && location.ISDNNumber5 != "") ||
                    (location.ISDNNumber6 != null && location.ISDNNumber6 != "")                                        
                ))
                && (location.privateRoom == "N" || location.privateRoom == "" || location.privateRoom != null)
                && (
                        from lll in session.Query<LocationLonLat>()                                             
                        where
                            location.locationID == lll.locationId
                        select lll.locationId
                    ).Any()
                && (location.LastUpdatedTime > lastUpdateTime)
                && location.LocationTimes.Count() > 0
               /*&& (
                        from lt in session.Query<LocationTimes>()
                        where
                            location.locationID == lt.LID
                        select lt.LID
                    ).Any()*/
                select location
                  )
                  .ToList();

There is a relationship between Location (1) and LocationTimes (many), and I only want to return a dataset of locations that have at least one LocationTime record.

I tried a couple of things...

When I add the line:

&& location.LocationTimes.Count() > 0

or if I add the line:

  && (                      
     from lt in session.Query<LocationTimes>()
     where
    location.locationID == lt.LID
        select lt.LID
     ).Any()

The underlying connection was closed: A connection that was expected to be kept alive was closed by the server.

I suspect that this may because of the size of the dataset or something...

Is there a better way of doing this? Like with a 'left outer join' or something?

why not use a join location with locationTime ? as since you are searching for existence of locations with locationtimes — kalki, Jul 25 '12 at 09:53

kalki · Accepted Answer · 2012-07-25T11:03:20.787

1

I think a simple join should do it.

from locationTime in Query<LocationTime>()
join location in Query<Location>() on locationTime.Location.LocationId equals location.LocationId
join locationLat in Query<LocationLat>() on location.LocationLat.LocationLatId equals locationLat.LocationLatId
where ...
select location;

edited Jul 25 '12 at 11:03

answered Jul 25 '12 at 10:01

kalki

516
2
10

Thanks for your reply. I tried that and I still get the same error. Any ideas? – Positonic Jul 25 '12 at 10:51
can't see why such a simple query which is basically all about joins should fail, is there are underlying exception that is triggering the connection loss ? – kalki Jul 25 '12 at 11:04
It seems that the problem is not actually caused by the query that is returning fine. It seems that it's actually just pulling a bigger databset or something, as the error I'm receiving now seems to be caused by the WCF web service instead. Sorry for the confusion. – Positonic Jul 25 '12 at 11:14

Linq to nHibernate - exclude elements without child elements

1 Answers1