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 >
        <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)

CodePudding user response：

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.