CODEWITHSUNDEEP
nhibernate
Rummy
Rummy

Reputation: 139

Non-unique columns in NHibernate query

I have the following case:
In SqlServer database there are two tables defined like this:

CREATE TABLE [dbo].[T1](
    [c1] [nvarchar](50) NOT NULL,
    [c2] [int] NULL,
CONSTRAINT [PK_T1] PRIMARY KEY CLUSTERED ([c1] ASC)) ON [PRIMARY]

CREATE TABLE [dbo].[T2](
    [c1] [nvarchar](50) NOT NULL,
    [c2] [int] NULL,
CONSTRAINT [PK_T2] PRIMARY KEY CLUSTERED ([c1] ASC)) ON [PRIMARY]

I add one record to T1 and another one to T2, like this:

INSERT INTO [dbo].[T1] ([C1],[C2]) VALUES ('a', 1)
INSERT INTO [dbo].[T2] ([C1],[C2]) VALUES ('a', 2)

Then I execute the following C# code (where Session is a NHibernate session):

var data = Session.CreateSQLQuery("SELECT * FROM T1 JOIN T2 ON T1.C1 = T2.C1").List();

In "data" I receive one record with four columns. Values are the following: "a", 1, "a", 1.

If I execute the query manually in Sql Server Management Studio I get good results "a", 1, "a", 2. For some reason NHibernate is not able to distinguish columns which have the same name or alias.

Does anyone know how if there is a way to make this work? I cannot guarantee using unique aliases instead of *, I also need to use a raw query instead of object mapping. Currently my only workaround is to use a good old data set, without NHibernate. I will be most grateful for all suggestions and help :)

Upvotes: 0

Views: 84

Answers (1)

gliljas
gliljas

Reputation: 729

If your table was mapped to a class, and you just wanted all ("*") mapped properties, you could

var data = Session.CreateSQLQuery("SELECT {T1.*},{T2.*} FROM T1 JOIN T2 ON T1.C1 = T2.C1")
   .AddEntity("T1", typeof(TheClass))
   .AddEntity("T2", typeof(TheClass))
   .List();

If not, I think your best bet is to use "good old data set" or a datareader, but you could expose it is an extension method on ISession, to make connection management etc. transparent.

Upvotes: 1

Related Questions