John Smith
John Smith

Reputation: 103

NHibernate. Join unrelated entities

Is there any way to execute following query with native NHibernate linq provider?

var result =
    (from e1 in Session.Query<Entity1>()
     join e2 in Session.Query<Entity2>() on e1.SomeField equals e2.SomeField into je
     from je2 in je.DefaultIfEmpty()
     select new {e1.Id, e1.SomeField, je2.SomeUrelatedField}).ToList()

Currently I'm using Fluent NHibernate 1.2 with NHibernate 3.1 and I get NotImplementedException

I don't want to introduce any relation between e1 and e2. By design they have no a relation and query like above could be used in rare purposes.

This blog post tells that it was unsupported. What about now?

Upvotes: 2

Views: 2286

Answers (3)

Randy Burden
Randy Burden

Reputation: 2661

I'm not quite sure when this was introduced but this is now supported by NHibernate. I am using version 3.3.1 and I have a query very similar to yours working well. The test below works for me:

[TestFixture]
public class NHibernateJoinUnrelatedEntitiesTest
{
    private ISessionFactory sessionFactory;

    [Test]
    public void I_Can_Join_Unrelated_Entities()
    {
        // Arrange
        ISession session = sessionFactory.OpenSession();

        // Act
        var results = (
                          from c in session.Query<Customer>()
                          from wal in session.Query<WebsiteActivityLog>()
                          where c.Id == wal.CustomerId
                                && c.Id == 54856
                          select new { CustomerId = c.Id, Name = c.FirstName, Address = wal.IpAddress }
                      ).ToList();

        // Assert
        Assert.NotNull( results );
    }

    public class Customer
    {
        public virtual int Id { get; set; }
        public virtual string FirstName { get; set; }
    }

    public class WebsiteActivityLog
    {
        public virtual int Id { get; set; }
        public virtual int CustomerId { get; set; }
        public virtual string IpAddress { get; set; }
    }

    public class CustomerMap : ClassMap<Customer>
    {
        public CustomerMap()
        {
            Id( x => x.Id );
            Map( x => x.FirstName );
        }
    }

    public class WebsiteActivityLogMap : ClassMap<WebsiteActivityLog>
    {
        public WebsiteActivityLogMap()
        {
            Id( x => x.Id );
            Map( x => x.CustomerId );
            Map( x => x.IpAddress );
        }
    }
}

Upvotes: 2

Vadim
Vadim

Reputation: 17965

Not using LINQ, but you can do theta joins when using HQL.

Select e1.Id, e1.SomeField, e2.SomeUnrelatedField 
from Entity1 e1, Entity2 e2  where e1.SomeField = e2.SomeField

However, you won't be able to do outer joins with this. So if that's a requirement, you'll have to go with raw SQL.

Upvotes: 1

escargot agile
escargot agile

Reputation: 22399

It's still not supported.

You should either introduce a relation between the objects or use SQL for the query.

Upvotes: 0

Related Questions