Why the database view show data correct but when I called it in controller show different result?-CodePudding

I changed the project design and created database view :

ALTER VIEW [dbo].[samplesList] 
AS 
    SELECT DISTINCT
        results.machine_id, 
        sample_id, 
        program_id, 
        Machines.Machine_id AS 'ID', 
        custid,
        sys_users.user_full_name AS 'HospitalName',
        Programs.name AS 'ProgramName', 
        machines.Machine_name AS 'MachineName', 
        samples.name AS 'SampleName'
    FROM 
        results 
    INNER JOIN
        programs ON RESULTS.program_id = Programs.id
    INNER JOIN
        Machines ON RESULTS.machine_id = Machines.Machine_id
    INNER JOIN
        sys_users ON RESULTS.custid = sys_users.user_id 
    INNER JOIN
        samples ON RESULTS.sample_id = samples.id

This is the result in the database :

See the screenshot - it shows the correct data sample no 1, sample no 2, sample no 3 and their machines are correct.

But in the controller when I link the view with the controller its not show same result from the database this is the controller code :

public ActionResult Indexs()
{
    int UserId = Convert.ToInt32(Session["UserID"]);

    var samples = _context.samplesLists
                          .Where(x=> x.custid == UserId).ToList();

    return View(samples);
}

This is the model :

namespace warehouse.Models
{
    using System;
    using System.Collections.Generic;
    
    public partial class samplesList
    {
        public Nullable<int> machine_id { get; set; }
        public Nullable<int> sample_id { get; set; }
        public Nullable<int> program_id { get; set; }
        public int ID { get; set; }
        public Nullable<int> custid { get; set; }
        public string HospitalName { get; set; }
        public string ProgramName { get; set; }
        public string MachineName { get; set; }
        public string SampleName { get; set; }
    }
}

And finally the surprise this is the output for same view in the site :

All data appears as "sample no 1":

This is the view markup:

@model IEnumerable<warehouse.Models.samplesList>

@{
    ViewBag.Title = "Indexs";
    Layout = "~/Views/Shared/_LayoutDashboard.cshtml";
}

<img style="margin-left:250px;" src="~/images/weblogo.png" />
<p style="margin-left:40px;">

    @*<h3 style="margin-left:100px; font-family:Andalus;text-underline-position:below">
        @Html.Label("Hospital Name :")
        @Html.DisplayFor(model => Model.FirstOrDefault().)

    </h3>*@


    <table >

      
        <tr style="background-color:hotpink">

            <th>
                @Html.DisplayNameFor(model => model.ProgramName)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.SampleName)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.MachineName)
            </th>
            @*<th></th>
            <th></th>
            <th></th>*@
          
        </tr>

        @foreach (var item in Model)
        {
            <tr>

                <td>
                    @Html.DisplayFor(modelItem => item.ProgramName)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.SampleName)
                </td>

                <td>
                    @Html.DisplayFor(modelItem => item.MachineName)
                </td>

Please I need your help what happening why all solutions not working and what I need to change ?

CodePudding user response：

EF has its share of troubles with views - because views typically don't have a defined primary key, which is crucial for EF to detect which data has already been loaded (and which hasn't).

What happens here is: since there's no primary key for your view, EF will just use all non-nullable columns from the view as a "substitute" PK.

And when EF reads the data, it will go:

read the row
check the primary key (or the "substitute" PK in this case)
if it has already read a row with that PK - it just duplicates that row that it already has - it will disregard any non-PK columns actually read from the view!

So in your case, once it's read a first row with the "substitute" PK, any further rows with those same values will just get the already read values - no matter what's stored in the database!

SOLUTION: EF needs a proper primary key to uniquely identify each individual row of data.

One solution is to add every single PK of all the underlying tables, and make sure those are marked as "non-nullable" in your model class. Another solution might be to add a custom ROW_NUMBER() OVER (....) column as an "artificial" dummy PK which gives each row a unique value - so that no two rows being read from the view are considered identical.

CodePudding user response：

I solved it in different way as marc_s said there is different activity in database views and primary keys not deal like the tables primary key

What I did :

I created new TABLE (MASTER_RESULTS) and inserted the data I need without duplicate and created the controller and view based on this master table .