Filtering children collection in LINQ

[Kakone]

Hello,

I try to find a way to filter children collection in LINQ. I know I
can enable a filter on the session, but the filter string is native
SQL, I don't want to use this.
With Criteria, I think I can use a result transformer
(Transformers.AliasToEntityMap) to achieve this, but is there a way to
do this with the new LINQ provider ?

For example, I've got two classes :

public class ThirdParty : Entity
{
public virtual string FirstName { get; set; }
public virtual string LastName { get; set; }

public virtual ThirdParty CurrentParent
{
get
{
var thirdPartyAssignment =
Assignments.FirstOrDefault(tpa => tpa.EndDate == null);
return thirdPartyAssignment == null ? null :
thirdPartyAssignment.ThirdParty;
}
}

public virtual IEnumerable<ThirdPartyAssignment> Assignments
{ get; set; }
}

public class ThirdPartyAssignment : Entity
{
public virtual ThirdParty ThirdParty { get; set; }
public virtual ThirdParty Parent { get; set; }
public virtual DateTime? EndDate { get; set; }
}

Sometimes, I want all the ThirdParties with all the Assignments (in
this case, session.Query<ThirdParty>() works well). Other times, I
want the ThirdParties with only the Assignments where EndDate is null.
I didn't succeed to do this with the LINQ provider.


Cordially,
Kakone.

[Mohamed Meligy]

Did you try:

var query = from thirdParty in Session.Query<ThirdParty>() where Assignments.Any(assignment => assignment .EndDate == null ) select thirdParty;

Depends on whether NHibernate LINQ provider supports Any() like this. Not sure about it.

Check also the QueryOver API. Feels a lot like LINQ (when called using methods not LINQ syntax). They have WhereRestrictionOn() method, you may use it to add IN restriction on the Assignments (passing it different QueryOver<Assignment>() query, again, not sure if that will work also).

One other way is to do JOIN and only select ThirdParty, then put the query in brackets and add DISTINCT like (from ... select ...).Distinct()

 

Mohamed Meligy
Readify | Senior Developer

M:+61 451 835006 | W: www.readify.net

    

--
You received this message because you are subscribed to the Google Groups "nhusers" group.
To post to this group, send email to nhu...@googlegroups.com.
To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/nhusers?hl=en.

[Kakone]

Thanks for this answer but it's not what I wanted to obtain.

If I do this :

var query = from thirdParty in Session.Query<ThirdParty>() where
Assignments.Any(assignment => assignment .EndDate == null )
select thirdParty;

The Assignments collection will be filled entirely. In some cases, I
would like that the Assignments collection contain only the assignment
where EndDate == null. It's not necessary to get all the records of
the child collection because I want only the last assignment. I don't
know how I can do this in LINQ.

On 8 déc, 00:52, Mohamed Meligy <eng.mel...@gmail.com> wrote:
> Did you try:
>
> var query = from thirdParty in Session.Query<ThirdParty>() where
> Assignments.Any(assignment => assignment .EndDate == null )
> select thirdParty;
>
> Depends on whether NHibernate LINQ provider supports Any() like this. Not
> sure about it.
>
> Check also the QueryOver API. Feels a lot like LINQ (when called using
> methods not LINQ syntax). They have WhereRestrictionOn() method, you may use
> it to add IN restriction on the Assignments (passing it different
> QueryOver<Assignment>() query, again, not sure if that will work also).
>
> One other way is to do JOIN and only select ThirdParty, then put the query
> in brackets and add DISTINCT like (from ... select ...).Distinct()
>

> *Mohamed Meligy
> *Readify | Senior Developer

>
> M:+61 451 835006 | W:www.readify.net
> [image: Description: Description: Description: Description: rss_16]  [image:
> Description: Description: Description: Description:

> cid:image003....@01CAF81D.6A076510]
> <http://www.linkedin.com/in/meligy>  [image:
> Description: Description: Description: Description:
> cid:image005....@01CAF81D.6A076510] <http://twitter.com/meligy>
>  <http://www.greatplacetowork.com.au/best/best-companies-australia.php><http://www.readify.net/AboutUs/NewsItem.aspx?id=10>

> > nhusers+u...@googlegroups.com<nhusers%2Bunsu...@googlegroups.com­>
> > .

> > For more options, visit this group at

> >http://groups.google.com/group/nhusers?hl=en.- Masquer le texte des messages précédents -
>
> - Afficher le texte des messages précédents -

[Kakone]

I tried this :

from tp in Session.Query<ThirdParty>()
select new ThirdParty
{
FirstName = tp.FirstName,
LastName = tp.LastName,
Assignments = (from tpa in Session.Query<ThirdPartyAssignment>()
where (tpa.ThirdParty == tp.ThirdParty && tpa.EndDate == null)
select tpa)
}

but it doesn't work...

In fact, I think it would be nice if we could do this (but it doesn't
work either) :
(from tp in Session.Query<ThirdParty>()).FetchMany(tp =>
tp.Assignments).Where(tpa => tpa.EndDate == null)

> > >http://groups.google.com/group/nhusers?hl=en.-Masquer le texte des messages précédents -

[Mohamed Meligy]

Did you try the QueryOver WhereRestrictionOn() method?

It's not LINQ, but it's very very similar and has all kinds of functionality it has has and some more also.

try something like: var query = session.QueryOver<ThirdParty>().WhereRestrictionOn(t => t.Assignment).In(....)

 

Mohamed Meligy
Readify | Senior Developer

M:+61 451 835006 | W: www.readify.net

    

To unsubscribe from this group, send email to nhusers+u...@googlegroups.com.

[birchoff]

You can do this with the QueryOver/Criteria/HQL methods for querying.
Just construct a query rooted at ThirdParty with a left outer join to
the child collection and apply your where clause and your off to the
races. Right now it does not seem like this is possible with the LINQ
API because it doesn't support left outer joins, and NHibernate only
seems to load the data returned in the select statement of the
generated SQL query when a left outer join is used to pull the data
in.