2

I have two tables as below in my db and there are columns same named in this tables.

[User].[Info]

TABLE_SCHEMA    TABLE_NAME  COLUMN_NAME
User    Info    Id
User    Info    UniqueId
User    Info    Name
User    Info    Email
User    Info    CompanyInfoId
User    Info    UserTypeId
User    Info    LanguageId
User    Info    InsertedBy
User    Info    InsertedOn
User    Info    UpdatedBy
User    Info    UpdatedOn
User    Info    DeletedBy
User    Info    DeletedOn
User    Info    IsDeleted

[User].[Type]

TABLE_SCHEMA    TABLE_NAME  COLUMN_NAME
User    Type    Id
User    Type    UniqueId
User    Type    Name
User    Type    CompanyInfoId
User    Type    LanguageId
User    Type    InsertedBy
User    Type    InsertedOn
User    Type    UpdatedBy
User    Type    UpdatedOn
User    Type    DeletedBy
User    Type    DeletedOn
User    Type    IsDeleted

I want to do paging in sql for my query. There is no problem one table. I can do that as below:

DECLARE @Page       AS INT = 1
DECLARE @PageSize   AS INT = 10
SELECT TOP (@PageSize) Paged.* FROM (SELECT ROW_NUMBER() OVER (ORDER BY Id) AS Row, * FROM [User].[Info]) AS Paged WHERE Row > (@Page - 1) * @PageSize

But I am using DAPPER in my project and I want to get [User].[Info] and [User].[Type] from db with paging as above.

This is my standart inner join query:

SELECT TOP 10 * FROM [User].[Info] ui INNER JOIN [User].[Type] ut ON ui.UserTypeId = ut.Id

And I tried to this query for paging:

DECLARE @Page       AS INT = 1
DECLARE @PageSize   AS INT = 10
SELECT TOP (@PageSize) Paged FROM ( 
    SELECT ROW_NUMBER() OVER (ORDER BY ui.Id) AS RowNumber, ui.*, ut.* FROM [User].[Info] ui INNER JOIN [User].[Type] ut ON ui.UserTypeId = ut.Id
) AS Paged WHERE RowNumber > (@Page - 1) * @PageSize

As a result I am getting an error as:

Msg 8156, Level 16, State 1, Line 102
The column 'Id' was specified multiple times for 'Paged'.
Msg 207, Level 16, State 1, Line 100
Invalid column name 'Paged'.

Because of the tables have same named columns. How to solve this problem?

Why do I want to do this?

In dapper call result, I want to do reach UserType from UserInfo entity as in EntityFramework and How to map to my entities of query result without problem in same columns names.

This is my dapper call for now :)

    public IEnumerable<UserInfo> All()
    {
        using (IDbConnection db = CreateConnection())
        {
            var sql = "MY SQL QUERY WITH PAGING";
            var command = db.Query<UserInfo, UserType, UserInfo>(sql, (userInfo, userType) =>
            {
                userInfo.UserType = userType;
                return userInfo;
            });

            return command;
        }
    }

I looked a lot of samples but I could not solve. Please help!

enter image description here

mstfcck
  • 721
  • 3
  • 11
  • 28
  • Shouldn't class `UserType` has property `CompanyInfoId`? – Roman Marusyk Nov 25 '16 at 08:29
  • Please look at your class `UserType` there is no `CompanyInfoId` property. That is what I'am asking. You have `Name` and all from parent members but in the parent `CompanyInfoId` also is absent. But according to your schema the `[User].[Type]` should have `CompanyInfoId` field – Roman Marusyk Nov 25 '16 at 09:52
  • @MegaTron yeah you right! But why you're asking that? I dont understand. I dont need CompanyInfoId for now in UserType. – mstfcck Nov 25 '16 at 10:42
  • If you look at the rows of data returned back from your Paged query you're returning both the UserInfo and UserType entries, both of which have an ID column. You haven't set the Dapper spliton property so when it's parsing the column headings it's finding two called ID and throwing the exception. Add a spliton property like in http://stackoverflow.com/questions/7472088/correct-use-of-multimapping-in-dapper so dapper can map the classes properly. – G Davison Nov 25 '16 at 11:37

1 Answers1

1

You can also try change your query to this one:

DECLARE @Page       AS INT = 1
DECLARE @PageSize   AS INT = 10

SELECT 
    ui.*, 
    ut.* 
FROM [User].[Info] ui 
   INNER JOIN [User].[Type] ut ON ui.UserTypeId = ut.Id
ORDER BY ui.Id
OFFSET (@Page - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY

I'm not sure that it will be OK for DAPPER but it solves the SQL Server error

Roman Marusyk
  • 23,328
  • 24
  • 73
  • 116