Wrong column names in generated SQL when using Join table

[Jacek]

Hi,

I have a three tables:
aspnet_Users
int_users
int_langs

aspnet_Users has an UserId GUID primary key

other tables:

CREATE TABLE [dbo].[int_users](
[user_id] [uniqueidentifier] NOT NULL,
[lang_id] [nvarchar](5) NOT NULL,
 CONSTRAINT [PK_int_users] PRIMARY KEY CLUSTERED
(
[user_id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)
GO
ALTER TABLE [dbo].[int_users]  WITH NOCHECK ADD  CONSTRAINT [FK_int_users_aspnet_Users] FOREIGN KEY([user_id])
REFERENCES [dbo].[aspnet_Users] ([UserId])
GO

ALTER TABLE [dbo].[int_users] CHECK CONSTRAINT [FK_int_users_aspnet_Users]
GO

ALTER TABLE [dbo].[int_users]  WITH NOCHECK ADD  CONSTRAINT [FK_int_users_int_langs] FOREIGN KEY([lang_id])
REFERENCES [dbo].[int_langs] ([lang_id])
GO
ALTER TABLE [dbo].[int_users] CHECK CONSTRAINT [FK_int_users_int_langs]
GO
CREATE TABLE [dbo].[int_langs](
[lang_id] [nvarchar](5) NOT NULL,
[lang_name] [nvarchar](50) NULL,
 CONSTRAINT [PK_langs] PRIMARY KEY CLUSTERED
(
[lang_id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF)
)

Updating model from database (SQL Azure) creates two class (aspnet_Users and int_langs) and one join table (int_users).

Trying to run the following

var q = from a in Context.aspnet_Users
        where a.UserId == Guid.Empty
        select a.int_langs;

results in error (first line)

Error executing query: Telerik.OpenAccess.RT.sql.SQLException: Invalid column name 'UserId'.

Interesting part is this:

SQL:
SELECT a.[UserId] AS COL1, b.[UserId] AS COL2, c.[lang_id] AS COL3, c.[lang_name] AS COL4 FROM [aspnet_Users] a JOIN [int_users] AS b ON (a.[UserId] = b.[UserId]) LEFT JOIN [int_langs] AS c ON (b.[lang_id] = c.[lang_id]) WHERE a.[UserId] = ?                                       ORDER BY COL2  Telerik.OpenAccess.RT.sql.SQLException: Invalid column name 'UserId'.
Invalid column name 'UserId'.
Statement(s) could not be prepared. ---> System.Data.SqlClient.SqlException: Invalid column name 'UserId'.
Invalid column name 'UserId'.
Statement(s) could not be prepared.

In other words column name "user_id" in table "int_users" was changed to "UserId" somehow (int_users primary key column is called user_id, not UserId)

Below association settings:



As for mapping:


So, something is not right..

(DA version 2015.1.225)

Jacek

[Jacek]

It seems that images were not included.

links to images:

Association

Mapping​

[Thomas]

Hi Jacek,

I think somewhere there is a misconfiguration as I tried this here with your script against MSSQL 2014, but had no luck with finding the issue. What I've got is, however, that the foreign and primary key tables in the designer are reversed compared to your picture. Can you try to update your model again from the database?

Regards,
Thomas
Telerik