Stored procecure keeps adding HasKey and a unique identifier.

[PETERHUP]

Every time I save my Database.tt file, It generates this code for my stored procedure in VisitPivotReturnModelMap.cs

public void Configure(EntityTypeBuilder<VisitPivotReturnModel> builder)
{
    builder.ToTable("VisitPivotReturnModel", "dbo");
    // I do not have a key and I do not have an Id field but used to
    builder.HasKey(x => new { x.ClientId, x.EmployeeId });
    builder.Property(x => x.Id).HasColumnName(@"Id").HasColumnType("uniqueidentifier").IsRequired(false);

No idea how to get it to stop generating the HasKey and Id column name,

Peter

Stored Procedure:

CREATE PROCEDURE dbo.VisitPivot @StartDate DATETIME = NULL
AS
BEGIN
    SET @StartDate
        = ISNULL(
                    CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 0, @StartDate), 0)),
                    CONVERT(DATE, DATEADD(WEEK, DATEDIFF(WEEK, 0, GETDATE()), 0))
                );

    SELECT *
    FROM
    (
        SELECT LTRIM(STR(v.ClientId)) + ':' + LTRIM(STR(v.employeeId)) + ':' + STR(RowNumber, 5) ClientEmployee,
               v.VisitId,
               v.ClientId,
               c.Clientno ClientNo,
               ISNULL(
               (
                   SELECT x.id FROM Client x WHERE c.MasterClientId > 0 AND x.Id = c.MasterClientId
               ),
               0
                     ) MclientId,
               e.EmplNo,
               TRIM(c.lastName) + CASE WHEN LEN(TRIM(c.FirstName)) > 0 THEN ', '
                                      ELSE ''
                                  END + TRIM(c.FirstName) Company,
               LTRIM(RTRIM(e.lastname)) + ', ' + LTRIM(RTRIM(e.firstname)) + ' (' + LTRIM(STR(v.EmployeeId)) + ')' EmployeeName,
               v.EmployeeId,
               v.RowNumber,
               'Day' + RIGHT('00' + CAST(DATEDIFF(dd, @StartDate, v.FromDatetime) + 1 AS VARCHAR(2)), 2) DayNumber
        FROM
        (
            SELECT tv.ClientId,
                   tv.EmployeeId,
                   tv.FromDatetime,
                   tv.Id VisitId,
                   ROW_NUMBER() OVER (PARTITION BY tv.clientId, tv.EmployeeId, tv.fromdatetime ORDER BY tv.EmployeeId,
                                                                                                        tv.EmployeeId
                                     ) RowNumber
            --, row_number() OVER(PARTITION BY tv.Clientno, tv.EmplNo, tv.fromdatetime ORDER BY tv.Clientno, tv.EmplNo) AS RowNumber
            FROM visit tv
            WHERE tv.FromDatetime >= @StartDate
                  AND tv.Todatetime <= DATEADD(dd, 14, @StartDate)
        ) v
            LEFT JOIN client c
                ON v.ClientId = c.Id
            LEFT JOIN employee e
                ON v.EmployeeId = e.Id
    ) t
    PIVOT
    (
        MIN(VisitId)
        FOR DayNumber IN (Day01, Day02, Day03, Day04, Day05, Day06, Day07,
                          Day08, Day09, Day10, Day11, Day12, Day13, Day14)
    ) pivot_table;
END;

[sjh37]

Hi @PETERHUP Can you tell me what version of the generator you are using. It will be at the top of the <database>.tt and EF.Reverse.POCO.v3.ttinclude files.

[PETERHUP]

Sure, was using 3.5 and just upgraded to 3.6 to see if it would fix the problem, it did not.

[sjh37]

You are correct, the PIVOT is indeed casuing it to look like a table. Investigating.