CODEWITHSUNDEEP
.netsqlnhibernatefluent-nhibernate
Ksice
Ksice

Reputation: 3327

NHibernate select from more then one table

How to write this select using NHibernate objects?

Select a.Id, a.Name, b.Data 
From Table1 a, Table2 b
Where a.Id between :1 and :2
And :3 = b.Period(+)
And 1 = b.Type(+)
And a.Id = b.Table1Id(+)
Order by 1

I.e. it gives all Table1 rows with Table2.Data for each row, even if there is no Table2 data for it (then 0 or null will be given)

Classes and mappings for Table1 and Table2 already created, and works. Reference for b.Table1Id added also.

So the problem is in (+) operator mainly.

Mappings: Table1 is very simple and doesn't referenced to any tables. Table2:

Table("Table2");
CompositeId()
 .KeyProperty(x => x.Type)
 .KeyProperty(x => x.Table1Id)
 .KeyProperty(x => x.Period);
Map(x => x.Type);
Map(x => x.Table1Id);
Map(x => x.Period);
Map(x => x.Data);
ReferencesAny(x => x.TableG)
 .IdentityType<string>()
 .EntityTypeColumn("Type")
 .EntityIdentifierColumn("Table1Id")
 .AddMetaValue<Table1>("1")
 .AddMetaValue<Table33>("33");

Table1 and Table33 ingerites from one parent class TableG. Sql query updated now accordingly.

Upvotes: 0

Views: 115

Answers (1)

mridula
mridula

Reputation: 3283

You can do something like this to get the records. And use projections to select the fields you want. There is a similar example here to get you started.

var queryResult = Session.QueryOver<Table2>()
                    .Left.JoinAlias(table2 => x.Period, () => period)
                    .Where(table2 => table2.Id >= value1)
                    .Where(table2 => table2.Id <= value2)
                    .Where(table2 => table2.Type == 1)
                    .OrderBy(table2 => table2.Type).List<Table2>();

Upvotes: 1

Related Questions