ASP.NET MVC 5 / C# Foreach loop - repeats same data

Question

This seems pretty 'beginner' but I am struggling.

I have a SQL Server view that joins two tables (Employee and Cars). I have a model for that view.

When I open SQL Server Management Studio and query the SQL Server view with Select * records that match employee A. I get two records back, because the employee is listed once in Employee table, and twice in the Cars table. In SQL Server Management Studio, I verify the two Cars for said person, are unique. Exactly as it should be.

But I must be doing something wrong in my code, within the ASP.NET MVC 5 app because when I load the Index view, that should display the two unique records - it, instead shows me the exact same record (same car) twice.

In my controller:

public ActionResult Index()
{
    string MyName = 
    System.Security.Principal.WindowsIdentity.GetCurrent().Name;
    String result = MyName.Substring(MyName.Length - 6);
    ViewBag.WhoAmI = result;

    UserPrincipal userPrincipal = UserPrincipal.Current;
    String name = userPrincipal.DisplayName;
    ViewBag.MyRealID = name;

    try
    {
        Web_Parkinglot_Header_FullViewRepository oItem = new 
        Web_Parkinglot_Header_FullViewRepository();
        Var item = oItem.GetbyLogon((string)result);
       
        if (!String.IsNullOrEmpty(result))
        {
        }                              

        return View(item.ToList());
    }

    catch (Exception ex)
    {
        throw ex;
    }
}

Repository:

public List<Web_Parkinglot_Header_FullView> GetbyLogon(string result)
{
    return this.Context.Web_Parkinglot_Header_FullViews.Where(a => a.logon_id == result).ToList();
}

View:

foreach (var item in Model)
{
    <table class="table">
        <tr>
            <td>
                <img src="~/Content/images/bmw.png" width="75" height="50" />
            </td>
            <td>
                <b>Vehicle Make:</b> @Html.DisplayFor(modelItem => item.veh_make)
            </td>
            <td>
                <b>Model:</b> @Html.DisplayFor(modelItem => item.veh_model)
            </td>

            <td>
                <b>Color:</b> @Html.DisplayFor(modelItem => item.veh_color)
            </td>
            <td>
                <b>Plate:</b> @Html.DisplayFor(modelItem => item.veh_plate)
            </td>
        </tr>
    </table>
}

What I don't understand is: why, when I query the SQL Server view directly, I get the correct 2 vehicles for logon_id = A. But when I run the same query via the repository, I get 2 records, but it's the same record twice, in the foreach loop.

The SQL Server view is an inner join. I can also post more info (Model, etc if needed)

Yes. Item contains the 2 duplicate records. But I don't understand why. It's a simple Sql View. I am just asking for all records that match logon_id. When I run directly in SQL, for logon_id = A, I get two unique records with 2 different cars (Correct). But what am I doing wrong in the code, that pulls back 2 duplicate records (incorrect). — ExecChef
– ExecChef, Commented Mar 10, 2022 at 16:22
What is your SQL query that is returning the correct data? I do not see a join in your repository query. It is a simply selecting from your table based on a where condition. — Rahul Sharma
– Rahul Sharma, Commented Mar 10, 2022 at 16:23
This is the SQL query that is returning correct data: SELECT * From Web_Parkinglot_Header_FullView Where logon_id = 'A' — ExecChef
– ExecChef, Commented Mar 10, 2022 at 16:25
@ExecChef Then your repository query looks correct. Can you show us your Controller action method where you are returning the result to your `View? — Rahul Sharma
– Rahul Sharma, Commented Mar 10, 2022 at 16:26

marc_s · Accepted Answer · 2022-03-10 16:39:53Z

4

There is a subtle problem with views when used from Entity Framework.

If you have a table, do use it with EF, you need to have a primary key to uniquely identify each row. Typically, that's a single column, e.g. an ID or something like that.

With a view, you don't have the concept of a "primary key" - the view just contains some columns from some tables.

So when EF maps a view, it cannot find a primary key - and therefore, it will use all non-nullable columns from the view as "substitute" primary key.

I don't know what these are in your case - you should be able to tell from the .edmx model.

So the problem really is that you can't have explicit primary keys on a view.

The easiest solution is to just simply include the primary key from both tables involved in your view definition (even if you don't want to show them ever).

That way, these will be in the view, and they will be non-nullable, and thus you should be fine and not getting any duplicates.

answered Mar 10, 2022 at 16:39

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Andrew Corrigan Over a year ago

Either that, or use a stored procedure.

Collectives™ on Stack Overflow

ASP.NET MVC 5 / C# Foreach loop - repeats same data

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related