Fluent Nhibernate inner join

Question

I have 3 tables (Master, Imagen, Linea) were:

Master
public virtual int Id { get; private set; }
public virtual Imagen imagen { get; set; }

Imagen
public virtual int Id { get; private set; }
public virtual Linea linea { get; set; }

Linea
public virtual int Id { get; private set; }
public virtual String Nombre { get; set; }

I need a query like this:

SELECT * FROM dbo.Master 
INNER JOIN dbo.Imagen ON dbo.Master.imagen_id = dbo.Imagen.Id 
INNER JOIN dbo.Linea ON dbo.Imagen.linea_id = dbo.Linea.Id 
WHERE dbo.Linea_Id = 5

But I dont know how to tell Fluent Nhibernate to create this query using the automapper. So far I've tried this:

ICriteria c = session.CreateCriteria(typeof(Master))
  .CreateAlias("dbo.Imagen", "img", JoinType.InnerJoin)
  .Add(Restrictions.Eq("img.linea_id", id_linea));

return c.List<Master>();

But I get this error: could not resolve property: dbo of: ImageManager.Model.Entity.Master

Any ideas on how to do a Inner Join? Thanks in advance

Nathan Fisher · Accepted Answer · 2011-10-28T13:07:33.690

10

For a start I would get rid of the dbo from dbo.Imagen. Using the ICriteria interface you need to think in-terms of objects, not database tables, even though there may be a one to one mapping of object to table and properties to columns.

EDIT:
another option would be to use the QueryOver Lambda syntax.

   var list = session.QueryOver<Master>()
                        .JoinQueryOver(master => master.imagen)
                        .Where(imagen => imagen.linea.Id == 5)
                        .List();

edited Oct 28 '11 at 13:07

answered Oct 27 '11 at 13:49

Nathan Fisher

7,961
3
47
68

Thanks, Alredy did that. Im really new to Fluent Nhibernate. – lloiacono Oct 27 '11 at 14:35
I tried this but I get this error: "could not resolve property: imagen.linea.Id of: ImageManager.Model.Entity.Master" I also changed to .Where(Master => Master.imagen.linea.Id == 5) but i get the same error – lloiacono Oct 28 '11 at 09:37
Sorry about that... still learning the QueryOver syntax myself. Will fix. – Nathan Fisher Oct 28 '11 at 12:37
it works!!!, thanks a lot. Anyway I end up using ICriteria because I needed to get the images and lines related to that master, thats why I added createalias for imagen and hdd, if I didnt do that i get a LazyLoadException when I try to access to image or hdd properties on my master object. – lloiacono Oct 28 '11 at 14:41
here is the code: ICriteria c = session.CreateCriteria(typeof(MasterHdd)) .CreateAlias("imagen", "img", JoinType.InnerJoin) .CreateAlias("hdd", "h", JoinType.InnerJoin) .CreateAlias("img.linea", "lin", JoinType.InnerJoin) .Add(Restrictions.Eq("lin.Id", id_linea)); return c.List(); – lloiacono Oct 28 '11 at 14:42

fluent · Answer 2 · 2011-10-28T08:16:13.067

3

I can't work out from your previous comments if you have already fixed it but I would try

ICriteria c = session.CreateCriteria(typeof(Master))

.CreateAlias("imagen", "img", JoinType.InnerJoin)
.CreateAlias("img.linea", "lin", JoinType.InnerJoin)
.Add(Restrictions.Eq("lin.Id", 5));

return c.List<Master>();

EDIT : Casing changed as stated below.

edited Oct 28 '11 at 08:16

answered Oct 27 '11 at 15:18

fluent

2,393
4
22
32

Thanks a lot, I'm currently working on this. The problem is that is not working. This is the error I get: "could not resolve property: Imagen of: ImageManager.Model.Entity.Master" Im using Automapping to Map everythin under model folder: .Mappings(m => m.AutoMappings.Add(model))) in the model folder I have a sub folder called Entity and there i have the entities: – lloiacono Oct 27 '11 at 15:56
public class Master{ public virtual int Id { get; private set; } public virtual Imagen imagen { get; set; } public virtual Hdd hdd { get; set; } public virtual String hash_master { get; set; } } – lloiacono Oct 27 '11 at 15:57
public class Linea{ public virtual int Id { get; private set; } public virtual int ObjVersion { get; set; } public virtual String Nombre { get; set; } public virtual String Tabla { get; set; } public virtual String NombreCorto { get; set; } } public class Imagen{ public virtual int Id { get; private set; } public virtual String PartNumber { get; set; } public virtual String Version { get; set; } public virtual String So { get; set; } public virtual String Revision { get; set; } public virtual Linea linea { get; set; } public virtual String Nombre { get; set; } } – lloiacono Oct 27 '11 at 15:57
I think the only problem with this answer will be the case of the object references. you have Imagen where @lloiacono has his object reference is all lowercase - imagen. Linea reference will be the same. That may be the reason why it's not working. – Nathan Fisher Oct 28 '11 at 06:09
Good spot. Casing changed in my answer. – fluent Oct 28 '11 at 07:47
Thanks @NathanFisher that was indeed the problem. I dont get the error anymore, could you recommend me a good manual for fluent, I read the wiki but I cannot find anythin about Icriteria. – lloiacono Oct 28 '11 at 09:47
Have a look at the summer of nhibernate series by stephen bohlen, its a few years old now but the basics are there for ICriteria and nhibernate in general. also have a look at http://funnelweblog.com it's an open-source blog platform based on fluent-nhibernate – Nathan Fisher Oct 28 '11 at 12:36

Fluent Nhibernate inner join

2 Answers2