I just figured out that HQL maps onto classes and not tables. This is
a fundamental concept that was eluding me. :)
Let me elaborate on my schema. This is abbreviated for clarity.
Table1
-RowId (PK)
Table2
-RowId (PK)
-Table1Id (FK -> Table1)
Table3
-RowId (PK)
-Table2Id (FK -> Table2)
What I want to do is load all of Table3 that has Table1Id = x using
one query.
In SQL, I would simply execute this tSQL statement:
select *
from Table3
inner join Table2 on Table3.Table2Id = Table2.RowId
where Table2.Table1Id = x
In Linq2SQL or LLBLGen I would use this:
var q = from table3 in metaData.Table3
join table2 in metaData.Table2 on table3.Table2Id equals
table2.RowId
where table2.Table1Id == x
select table3;
I was expecting that nHibernate would let me query table3 using a
value out of table2 without needing a foreign key to table1.
As a workaround, I could add a field in table3 that points to table1.
However, this is redundant and breaks the rules of normalization (Join
dependency).
Does this make sense? How can I use Linq to query this? Do I need to
use HQL with a DTO?
Thank you,
Rick
On Apr 1, 2:59 pm, Paco Wensveen <[email protected]> wrote:
> I didn't know that. Isn't it HSql what you mean, that returns object
> arrays instead of entities? When it's implemented in hql, it will
> probably implemented in linq in the future (Not sure again).
>
>
>
> On Thu, Apr 1, 2010 at 11:37 PM, Diego Mijelshon <[email protected]>
> wrote:
> > That's not correct. You can easily query unrelated entities in HQL, just
> > like in SQL.
> > Of course it's a lot easier when they ARE related, and there aren't many
> > valid reasons NOT to create the relationships...
>
> > Diego
>
> > On Thu, Apr 1, 2010 at 18:32, Paco Wensveen <[email protected]> wrote:
>
> >> As far as I know (but I can be wrong) joins between unrelated entities
> >> are not implemented in hql, or any other part of NHibernate. So the
> >> options are:
> >> - Create a relation between the entities
> >> - Use a sql query (with NHibernate)
> >> - Use a different orm that supports what you want.
>
> >> This is probably not the answer you would like to get, so I wonder why
> >> you don't use a reference from SSObject to OtherObject in the code?
>
> >> Paco
>
> >> On Thu, Apr 1, 2010 at 11:21 PM, rboarman <[email protected]> wrote:
> >> > It's not in the mapping. I am trying to create an arbitrary query.
> >> > This works fine in Linq2SQL and LLBLGen.
>
> >> > In summary, does this mean we can only create inner joins when there
> >> > is a FK relationship in place?
>
> >> > If so, am I better off using an HQL query?
>
> >> > Rick
>
> >> > On Apr 1, 2:15 pm, Paco Wensveen <[email protected]> wrote:
> >> >> I mean this a reference in the classes and mappings:
>
> >> >> public class SSObject
> >> >> {
> >> >> public OtherObject Row {get;}
>
> >> >> }
>
> >> >> var q = from sso in session.Linq<SSObject>()
> >> >> where ot.TargetId == someId
> >> >> select sso;
>
> >> >> I guess the join method in NHibernate is not implemented because
> >> >> relations between tables are handled by the mapping already, and there
> >> >> is a join is never needed when you use it in the mapping.
>
> >> >> On Thu, Apr 1, 2010 at 10:45 PM, rboarman <[email protected]> wrote:
> >> >> > What? This is the sql I am trying to replicate:
>
> >> >> > select *
> >> >> > from SSObject
> >> >> > inner join OtherObject on SSObject.OtherObjectId = OtherObject.RowId
> >> >> > where OtherObject.TargetId= 98
>
> >> >> > Thanks,
>
> >> >> > Rick
>
> >> >> > On Apr 1, 11:44 am, Paco Wensveen <[email protected]> wrote:
> >> >> >> By creating a reference in from one to another in the SSObject or
> >> >> >> OtherObject?
>
> >> >> >> On Thu, Apr 1, 2010 at 7:23 PM, rboarman <[email protected]> wrote:
> >> >> >> > Hello,
>
> >> >> >> > It’s unclear from this site and from Google searches whether
> >> >> >> > nHibernate’s implementation of Linq supports joins or not.
>
> >> >> >> > In my case, I want to do a VERY simple join.
>
> >> >> >> > var q = from sso in session.Linq<SSObject>()
> >> >> >> > join ot in session.Linq<OtherObject>() on sso.OtherObjectId
> >> >> >> > equals ot.RowId
> >> >> >> > where ot.TargetId == someId
> >> >> >> > select sso;
>
> >> >> >> > This gives me an error of “Cannot resolve method Join.”
>
> >> >> >> > I have tried a couple different version of the dll. Right now I am
> >> >> >> > using 1.1.0.1001.
>
> >> >> >> > Any suggestions on how I can get this to work?
>
> >> >> >> > Rick
>
> >> >> >> > --
> >> >> >> > You received this message because you are subscribed to the Google
> >> >> >> > Groups "nhusers" group.
> >> >> >> > To post to this group, send email to [email protected].
> >> >> >> > To unsubscribe from this group, send email to
> >> >> >> > [email protected].
> >> >> >> > For more options, visit this group
> >> >> >> > athttp://groups.google.com/group/nhusers?hl=en.-Hidequoted text -
>
> >> >> >> - Show quoted text -
>
> >> >> > --
> >> >> > You received this message because you are subscribed to the Google
> >> >> > Groups "nhusers" group.
> >> >> > To post to this group, send email to [email protected].
> >> >> > To unsubscribe from this group, send email to
> >> >> > [email protected].
> >> >> > For more options, visit this group
> >> >> > athttp://groups.google.com/group/nhusers?hl=en.-Hide quoted text -
>
> >> >> - Show quoted text -
>
> >> > --
> >> > You received this message because you are subscribed to the Google
> >> > Groups "nhusers" group.
> >> > To post to this group, send email to [email protected].
> >> > To unsubscribe from this group, send email to
> >> > [email protected].
> >> > For more options, visit this group at
> >> >http://groups.google.com/group/nhusers?hl=en.
>
> >> --
> >> You received this message because you are subscribed to the Google Groups
> >> "nhusers" group.
> >> To post to this group, send email to [email protected].
> >> To unsubscribe from this group, send email to
> >> [email protected].
> >> For more options, visit this group at
> >>http://groups.google.com/group/nhusers?hl=en.
>
> > --
> > You received this message because you are subscribed to the Google Groups
> > "nhusers" group.
> > To post to this group, send email to [email protected].
> > To unsubscribe from this group, send email to
> > [email protected].
> > For more options, visit this group at
> >http://groups.google.com/group/nhusers?hl=en.- Hide quoted text -
>
> - Show quoted text -
--
You received this message because you are subscribed to the Google Groups
"nhusers" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/nhusers?hl=en.
