Wrong column names in generated SQL when using Join table

3 posts, 0 answers
  1. Jacek
    Jacek avatar
    31 posts
    Member since:
    Sep 2011

    Posted 03 Apr 2015 Link to this post

    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. Jacek
    Jacek avatar
    31 posts
    Member since:
    Sep 2011

    Posted 03 Apr 2015 in reply to Jacek Link to this post

    It seems that images were not included.

    links to images:

    Association

    Mapping

  3. DevCraft banner
  4. Thomas
    Admin
    Thomas avatar
    590 posts

    Posted 07 Apr 2015 Link to this post

    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.
     
Back to Top