Hello,

I've the following many to many relation: File 1 --- * File_Insurer * --- 1 Insurer. I'm trying to query this relation using the Criteria API to get Files that meet ALL specified Insurers (Get all Files where Insurer.Id == 2 AND Insurer.Id == 3).

I tried many solutions:

DetachedCriteria dc = DetachedCriteria.For<File>();
dc.SetResultTransformer(new DistinctRootEntityResultTransformer());
dc.CreateCriteria("Insurers").Add(Expression.Eq("Id", long.Parse("2")));
dc.CreateCriteria("Insurers").Add(Expression.Eq("Id", long.Parse("3")));
List<File> searchResults = File.FindAll(dc).ToList<File>();

That gives me a error "duplicate association path: Insurers".

Next option:

DetachedCriteria dc = DetachedCriteria.For<File>();
dc.SetResultTransformer(new DistinctRootEntityResultTransformer());
dc.CreateCriteria("Insurers").Add(Expression.And(Expression.Eq("Id", long.Parse("3")), Expression.Eq("Id", long.Parse("2"))));
List<File> searchResults = File.FindAll(dc).ToList<File>();

The result list is empty (but shouldn't).

Next option with alias:

DetachedCriteria dc = DetachedCriteria.For<File>();
dc.SetResultTransformer(new DistinctRootEntityResultTransformer());
dc.CreateAlias("Insurers", "i").Add(Expression.Eq("i.Id", long.Parse("2"))).Add(Expression.Eq("i.Id", long.Parse("3")));
List<File> searchResults = File.FindAll(dc).ToList<File>();

The result list is empty again - strange.

Next try:

DetachedCriteria dc = DetachedCriteria.For<File>();
dc.SetResultTransformer(new DistinctRootEntityResultTransformer());
List<long> insurerIds = new List<long>();
insurerIds.Add(2);
insurerIds.Add(3);
dc.CreateCriteria("Insurers").Add(Expression.In("Id", insurerIds));
List<File> searchResults = File.FindAll(dc).ToList<File>();

This works somehow, but the result set contains a all possible options (OR) - it's not an exact match.

I'm very confused on this topic. Any help would be highly appreciated.

Regards,
Jakub

Try this:




I'm not sure if the Property.Eq(...) works correctly, but the rest shoulr work. You problem is that you want all files which are linked to one or more insureres at the same time. The queries you tried, always tested if the insurer id of one link is 2 and 3 which is impossible.
In the above query you have to dynamically set numOfSpecifiedInsureres. In your example 2.