locked
Server Side Blazor SqlRaw returns a few int colums, Razor Page shows them as 0 RRS feed

  • Question

  • User379720387 posted

    Looking at the Swagger output I can see that my controller action returns the values that I see when I execute the query in SSMS.

    Then in my Blazor Razor page the results are obtained like this:

    schedequines = await httpClient.GetFromJsonAsync<SchedEquineGrid[]>($"{baseUrl}/api/Record/ByProvider/22");

    And the model SchedEquineGrid:

    public class SchedEquineGrid
        {
            public int ClientId { get; set; }
     
            public string Location { get; set; }
     
            public string CName { get; set; }
     
            public int LastServiceWeekNo { get; set; }
     
            public int AgeInWeeks { get; set; }
     
            public int NextService { get; set; }
     
            public int Sort { get; set; }
        }

    @foreach (var schedequine in schedequines)
                {
                    <tr>
                        <td>@schedequine.ClientId</td>
                        <td>@schedequine.Location</td>
                        <td>@schedequine.CName</td>
                        <td>@schedequine.LastServiceWeekNo</td>
                        <td>@schedequine.AgeInWeeks</td>
                        <td>@schedequine.NextService</td>                    
                    </tr>
                }

    ClientId (PK) is also an Int, and it shows up correctly.

    I see the same thing happening with results from my controllers that have actions based on Linq, followed by a projection.

    Thoughts?

    Saturday, February 13, 2021 11:19 PM

All replies

  • User-474980206 posted

    as you don't show the json, we can not guess why the json mapping does not work

    Sunday, February 14, 2021 5:59 PM
  • User379720387 posted

    What I see in Swagger is that the json returned does not have the exact same capitalization as was in linq or sqlraw queries.

    [

    { "clientId": 46, "location": "Whoa Farms", "cName": "Frostey", "lastServiceWeekNo": 4, "ageInWeeks": 56, "nextService": 4, "sort": 4 },

    { "clientId": 61, "location": "J-Mar Stables", "cName": "Twilight", "lastServiceWeekNo": 4, "ageInWeeks": 56, "nextService": 4, "sort": 4 },

    { "clientId": 54, "location": "J-Mar Stables", "cName": "Casper", "lastServiceWeekNo": 5, "ageInWeeks": 3, "nextService": 5, "sort": 3 },

    { "clientId": 47, "location": "Featherdown", "cName": "LeeLou", "lastServiceWeekNo": 5, "ageInWeeks": 3, "nextService": 5, "sort": 3 }

    ]

    var eqschedules = await context.SchedEquine
                .FromSqlRaw($@"
                    SELECT * FROM
                    (
                        SELECT t.ClientId, Location, CName, t.ProviderId
                        , DatePart(wk, MAX(CONVERT(datetime,convert(nvarchar(10),TDate,101)))) as LastServiceWeekNo
                        , MIN(DATEDIFF(wk,TDate,GetDate())) AS AgeInWeeks
    			        , CASE WHEN plusWeeks IS NULL THEN 0 ELSE plusWeeks END AS plusWeeks
    			        , DatePart(wk, MAX(CONVERT(datetime,convert(nvarchar(10),TDate,101)))) + plusWeeks AS NextService
                        , DATEPART(wk, GETDATE()) - DATEPART(wk, MAX(CONVERT(datetime,convert(nvarchar(10),TDate,101)))) - PlusWeeks As Sort
                        , CASE WHEN DATEPART(wk, GETDATE()) - DATEPART(wk, MAX(CONVERT(datetime,convert(nvarchar(10),TDate,101)))) - plusWeeks > 0 
    				        THEN '+' + CAST((DATEPART(wk, GETDATE()) - DATEPART(wk, MAX(CONVERT(datetime,convert(nvarchar(10),TDate,101)))) - plusWeeks) AS varchar) 
    				        ELSE CAST((DATEPART(wk, GETDATE()) - DATEPART(wk, MAX(CONVERT(datetime,convert(nvarchar(10),TDate,101)))) - PlusWeeks) AS varchar)
    				        END AS Delta
                        FROM Transactions t
                        JOIN Clients c ON t.ClientId = c.ClientId
                        JOIN Owners o ON c.ownerLocationId = o.OwnerId
                        LEFT JOIN toDoClientR tdc ON c.ClientId = tdc.ClientId 
                        WHERE t.ClientId  NOT IN (48,52)  --IS NOT NULL
                        AND t.ProviderId= 22
                        AND c.EnteredByProviderId= 22
                        AND tdc.TimeStampComplete IS NULL
                        GROUP BY t.ClientId, Location, CName, t.ProviderId, tdc.isActive, tdc.plusWeeks
                    ) As todos
                    --WHERE AgeInWeeks >= @1 AND AgeInWeeks < @2
                    --AND Location LIKE @3
    		        ORDER BY Sort DESC, CName ASC")
                .ToListAsync();
    
                return eqschedules;

    This controller does not have a project query, but I did try that thinking that would take care of things, end result was some vague exception message about adding AsEnumerable(). Of course without telling where it should. Gave up on that.

    Is it necessary to do a projection query in the case?

    Sunday, February 14, 2021 6:49 PM
  • User379720387 posted

    Anyone?

    Tuesday, February 16, 2021 2:10 PM
  • User753101303 posted

    Hi,

    And you are sure 100% this is the same query (ie you call call the same code or you copy/pasted some code) and the same database (seems you have few hardcoded values in your querty).

    Also it seems you are showing what woks rather than what doesn"t work ? I woud start by double checking my sql querty is correctly transmitting its values to the model.

    Tuesday, February 16, 2021 3:00 PM
  • User379720387 posted

    It was indeed something stupid similar to your not the same db comment.

    Mine was not using the right controller action. I had grouped several actions in a single controller and because they are closely related and share many of the same property names it just so happened to mess up all those with property type int. That send me in the wrong direction looking for solutions.

    Wednesday, February 17, 2021 3:09 PM