I have a legacy table with 100 columns and 3 key fields. The Create, Delete, Details, and Edit pages created by adding “MVC 5 Controller with views, using Entity Framework” can only handle single key tables out of the box.
The Database
The data I need is in a single table on Microsoft SQL Server 2008 R2. The table I’m using has 3 keys: [Order_], [Company_Name] and [Acct_]
In the server explorer, click “Add Connection” and follow the prompts to add the table.
Adding the Entity Data Model
Return to the Solution Explorer and right click on Models and [Add] -->[ADO.NET Entity Data Model].
(New Item --> Data --> ADO.NET Entity Data Model)
I called my model [Order]
Since the database already exists, choose [Generate from Database]
The name of the database is “temp” (not my choice and not something I can change), so leaving defaults on the data connection page means the entity connection settings in Web.Config will be “tempEntities”
As of 12/24/2013, Entity Framework 6.0 isn’t widely supported, so I chose 5.0
Choose the specific table(s) needed. In my case, I just need one: [ORDERS]
Again, leaving the default value, the Model Namespace is tempModel. Click [Finish]
The edmx diagram is created. At the top, you can see the 3 keys.
Build the Project
You need to build the project at this point before continuing or the controller build process will error out.
Adding the Controller
Right click on Controllers and add [Controller…] to add a controller scaffold.
Choose the “MVC 5 Controller with views, using Entity Framework”
I called my controller [OrderController]. Choose the Model class we just created [ORDER (WebApplication4.Models)] and the Data context class is the one we created [tempEntities (WebApplication4.Models)].
When this step completes, you’ll see the [OrderController.cs] under Controllers and an [Order] folder under Views that contains Create, Delete, Details, Edit and Index.cshtml.
Browsing for the First Time
The temp.ORDERS table has about 48,000 rows and over 100 columns (!). I didn’t need all of the columns, so I cut out all except the three key fields for this exercise.
@model IEnumerable<WebApplication4.Models.ORDER>
@{
ViewBag.Title = "Index";
}
<h2>Index</h2>
<p>
@Html.ActionLink("Create New", "Create")
</p>
<table class="table">
<tr>
<th>
@Html.DisplayNameFor(model => model.ORDER_)
</th>
<th>
@Html.DisplayNameFor(model => model.COMPANY_NAME)
</th>
<th>
@Html.DisplayNameFor(model => model.ACCT_)
</th>
<th></th>
</tr>
@foreach (var item in Model) {
<tr>
<td>
@Html.DisplayFor(modelItem => item.ORDER_)
</td>
<td>
@Html.DisplayFor(modelItem => item.COMPANY_NAME)
</td>
<td>
@Html.DisplayFor(modelItem => item.ACCT_)
</td>
<td>
@Html.ActionLink("Edit", "Edit", new { /* id=item.PrimaryKey */ }) |
@Html.ActionLink("Details", "Details", new { /* id=item.PrimaryKey */ }) |
@Html.ActionLink("Delete", "Delete", new { /* id=item.PrimaryKey */ })
</td>
</tr>
}
</table>
*Notice the commented “id=item.PrimaryKey” parameter.
Even with this reduced display, 48,000 rows still crashed my browser, so I added a counter that breaks out of the loop after the first 50 rows.
@{int counter = 0;}
@foreach (var item in Model) {
counter = counter + 1;
if (counter == 50) { break; }
…
This results in output that looks like this:
Since the primary key is commented out of the edit, details and delete links, clicking any of them at this point will cause a 400 error.
The account I’m using does not have write access to the database, so “Details” is the only link I will activate. Uncommenting [Details] and replacing “PrimaryKey” with one of the real keys [ORDER_]will result in an exception:
@Html.ActionLink("Details", "Details", new { id = @item.ORDER_}) |
“The number of primary key values passed must match number of primary key values defined on the entity.” The entity requires three key values and I am only passing in 1. A quick look at the tempController.cs shows how the [Details] action is handled:
The [id] value is passed in by querystring and is used by Find(id)to retrieve the proper record. It took a bit of searching to learn how to use Find() with multiple keys. A comma delimited list will work , for example: db.ORDERS.find(firstcolvar, “plain_text”, 3) looks for 3 keys.
#1 specified by the var firstcolvar,
#2 looks for the plain text “plain_text” and
#3 looks for the integer 3.
Note: The values must match the column data type.
The variable “id” is defined as a string by default, so I decide to pass all 3 key values in as a comma delimited string. I edited the Details Html.ActionLink code on Index.cshtml as follows:
@Html.ActionLink("Details", "Details", new {id= @item.ORDER_+','+@item.COMPANY_NAME+','+@item.ACCT_}) |
All 3 key values, separated by commas.
In the ActionResult Details inside OrderController.cs, I need to parse each of the 3 keys out. I use split to pull the values into an array of strings, then I use each of the values individually in the Find() method.
One tricky part: the third key is an integer, not a string, so I cast the value in the call to the Find method (highlighted).
string[] splitid = id.Split(',');
ORDER order = db.ORDERS.Find(splitid[0],splitid[1],Convert.ToInt32(splitid[2]));
Another tricky part: the order of the keys is important, and I have the order wrong above!
The class ORDER in the Models was created automatically from the SQL table.
The order of the keys presented to the find method must match the order of the keys in the Models/Order.edmx/Order.tt/ORDER.cs file.
Company_Name first, ACCT_ second, and ORDER_ third. The ActionLink in Index.cshtml becomes:
@Html.ActionLink("Details", "Details", new { id = @item.ORDER_ + ',' + @item.COMPANY_NAME + ',' + @item.ACCT_ }) |
And the ActionResult in OrderController becomes:
string[] splitid = id.Split(',');
ORDER order = db.ORDERS.Find(splitid[1], int.Parse(splitid[2]),splitid[0]);
At this point the details link works!
The next goal is to figure out how to do this with a complex query (in LINQ?) against 2 databases, one of which does not have a key.
