This is a migrated thread and some comments may be shown as answers.
Wrong column names in generated SQL when using Join table
2 Answers 13 Views
This is a migrated thread and some comments may be shown as answers.
This question is locked. New answers and comments are not allowed.
Jacek
Top achievements
Rank 1
Jacek asked on 03 Apr 2015, 11:25 AM
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











2 Answers, 1 is accepted

Sort by
0
Jacek
Top achievements
Rank 1
answered on 03 Apr 2015, 11:33 AM

It seems that images were not included.

links to images:

Association

Mapping‚Äč

0
Thomas
Telerik team
answered on 07 Apr 2015, 03:27 PM
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
 
OpenAccess ORM is now Telerik Data Access. For more information on the new names, please, check out the Telerik Product Map.
 
Tags
LINQ (LINQ specific questions)
Asked by
Jacek
Top achievements
Rank 1
Answers by
Jacek
Top achievements
Rank 1
Thomas
Telerik team
Share this question
or