Display Data from Different Database Tables ASP.NET MVC

Question

EDIT - I have made progress from the original post and have now made the relevant changes to the question.

In the controller I now have a variable called lstSteps that brings in all the details of the steps that are associated with the relevant Execution.

The problem now is that I am unable to show this list of Steps in the Execution view. I understand that I need to write a foreach loop but cant figure out how to write it.

Controller

public ActionResult Execution(int id = 0)
    {
        Execution execution = db.Executions.Find(id);

var lstSteps = db.Steps.Where(z => z.Execution.Id == id).ToList();  

        if (execution == null)
        {
            return HttpNotFound();
        }

        ViewBag.ExecutionSeconds = (execution.End - execution.Start).ToString(@"dd\.hh\:mm\:ss\.fff");

        return View(execution);

    }

Execution Model

namespace DataIntelligence.Models
{
    [Table("Execution")]
    public class Execution
    {
        public int Id { get; set; }
        public Int16 PackageId { get; set; }
        public DateTime Start { get; set; }
        public DateTime End { get; set; }
        public Boolean Successful { get; set; }
    }
}

Step Model

namespace DataIntelligence.Models
{
    [Table("Step")]
    public class Step
    {
        public int Id { get; set; }
        public int ExecutionId { get; set; }
        public string Name { get; set; }
        public DateTime Date { get; set; }

        public virtual Execution Execution { get; set; }
    }
}

Execution View

@model DataIntelligence.Models.Execution
@{
    ViewBag.Title = "Execution Details";
}
<script language="javascript" type="text/javascript" src="jquery-1.8.3.js">    </script>
<script>
function goBack() {
    window.history.back();
}
</script>
<div class="jumbotron">
    <h2>Execution Details</h2>
</div>

<fieldset>
    <legend>Execution Id - @Html.DisplayFor(model => model.Id)</legend>


    <div class="row">
        <div class="col-sm-3">
            <div class="display-label">
                @Html.DisplayNameFor(model => model.PackageId)
            </div>
            <div class="display-field">
                <a href="@Url.Action("Package", new { id=Model.PackageId})">       @Html.DisplayFor(model => model.PackageId) </a>
            </div>
        </div>

        <div class="col-sm-3">
            <div class="display-label">
                @Html.DisplayNameFor(model => model.Start)
            </div>
            <div class="display-field">
                @Html.DisplayFor(model => model.Start)
            </div>
        </div>
        <div class="col-sm-3">
            <div class="display-label">
                @Html.DisplayNameFor(model => model.End)
            </div>
            <div class="display-field">
                @Html.DisplayFor(model => model.End)
            </div>
        </div>
        <div class="col-sm-3">
            <div class="display-label">
                Execution Time
            </div>
            <div class="display-field">
                @ViewBag.ExecutionSeconds  
            </div>
        </div>
    </div>
    <div class="row">
        <div class="col-sm-3">
            <div class="display-label">
                @Html.DisplayNameFor(model => model.Successful)
            </div>
            <div class="display-field">
                @Html.DisplayFor(model => model.Successful)
            </div>
        </div>
     </div>
    <br />

</fieldset>

<table>
    <tr>
        <th>
            Step ID
        </th>
        <th>
            Name
        </th>
        <th>
            Date
        </th>
    </tr>

    @foreach (var ? in ?) {
        <tr>
            <td>
                @Html.DisplayFor(modelItem => item.Id)
            </td>
            <td style="width: 600px;">
                @Html.DisplayFor(modelItem => item.Name)
            </td>
            <td>
                @Html.DisplayFor(modelItem => item.Date)
            </td>
        </tr>
    }

</table>
<button class="btn btn-link" onclick="goBack()" style="padding-right: 0px; padding-top: 0px; padding-bottom: 0px; padding-left: 0px;">Back</button>

I believe the parts I am going wrong on is where I have placed the ?

Help would be much appreciated.

Thanks in advance.

I can see you have ExecutionId as foreign key from Execution table. Add a navigation property to Step class "public virtual Execution Execution {get; set; }" then you can do linq query to get steps for execution. — Zaki
– Zaki, Commented Aug 10, 2015 at 9:51
I have added this property to the Step Class, have you any idea how the linq query may look? @Zaki — D.M
– D.M, Commented Aug 10, 2015 at 10:27
@Zaki please could take a look at my comment on your answer, thanks — D.M
– D.M, Commented Aug 10, 2015 at 11:04
Your Step model has a list of Execution. Should your Execution have a list of Steps? Once you have a list of 'Step' you can use @foreach(var step in Model.Steps) { // code } — Jason Cidras
– Jason Cidras, Commented Aug 10, 2015 at 14:34

D.M · Accepted Answer · 2015-08-10 15:11:53Z

1

So add navigation property to Step class as you have a foreign key to Execution class :

public virtual Execution Execution {get; set; }

And your Linq query would look like this:

var lstSteps = db.Steps.Where(z => z.Execution.Id == id).ToList();

This will get you all Steps where the execution id is equal to the id you are passing. You can Iterate though lstSteps and print it in your view. If you know it will have only one result then use .FirstOrDefault() instead of ToList()

EDIT :

Ok so you want to show a list of Executions with their corresponding Step.

Add another navigation property in Execution :

public virtual ICollection<Step> Steps {get; set; }

change your linq to (this should get steps too):

var Executions = db.Executions.Find(id);

In your view you can do a foreach :

@foreach(var step in Model.Steps) 
{ 
    <label>Step id</label>step .Id; 
    <label>step Name </label>step.Name 
     ....
 }

Make sure you your model is : @model DataIntelligence.Models.Execution

edited Aug 10, 2015 at 15:11

D.M

4271 gold badge5 silver badges24 bronze badges

answered Aug 10, 2015 at 10:34

Zaki

5,6087 gold badges58 silver badges91 bronze badges

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

9 Comments

D.M Over a year ago

Thanks I I have added my linq query (in the controller im guessing) The controller takes in a count of steps the Execution has but now I am struggling to display the list of steps within the view. Sorry for the constant questions but I am a newbie to this. If you could help with this it would be much appreciated

Zaki Over a year ago

ok so if you put a break point on the lstSteps do you see a list of steps and executions? if so go to your view and add a foreach loop and go though the Steps and display them

D.M Over a year ago

I've tried a number of different ways of writing the foreach loop and I can't seem to get it working, above I have added the code in my view if you could take a look at that and see where I am going wrong.

Zaki Over a year ago

add using statement to System.Linq, so you have a execution with list of steps ?

Zaki Over a year ago

see my edit, change the navigation property to collection, then in foreach loop just loop over that nav property and output details

|

Pawan Nogariya · Accepted Answer · 2015-08-10 10:02:29Z

0

Just define a new class like this and pass it to your view as your model

namespace DataIntelligence.Models
{

    public class ExecutionStep
    {
       public Execution Execution { get;set; }
       public List<Step> Steps { get;set; }  

       public static ExecutionStep Load(int executionStepId)
       {
           // load your execution and step data here and return it
           // within ExecutionStep class object
           ExecutionStep executionStep = new ExecutionStep();
           executionStep.Execution = db.Executions.Find(id); 
           executionStep.Steps = db..... // load steps from db
           return executionStep;
        }
    }
}

You can then load ExecutionStep call and can pass it to your view like this

public ActionResult Execution(int id = 0)
{
    // now this executionStep class object will serve as a modal 
    // for your view
    ExecutionStep executionStep = ExecutionStep.Load(id);      

    if (execution == null)
    {
        return HttpNotFound();
    }

    ViewBag.ExecutionSeconds = (execution.End - execution.Start).ToString(@"dd\.hh\:mm\:ss\.fff");

    return View(executionStep);
}

You can access them in your view like this

@Modal.Execution.Start
@foreach(var step in Modal.Steps)
{
}

edited Aug 10, 2015 at 10:02

answered Aug 10, 2015 at 9:50

Pawan Nogariya

9,04614 gold badges61 silver badges137 bronze badges

1 Comment

D.M Over a year ago

couldn't get this the work unfortunately. It's throwing out numerous errors such as... The name 'db' does not exist in the current context

Collectives™ on Stack Overflow

Display Data from Different Database Tables ASP.NET MVC

Controller

Execution Model

Step Model

Execution View

2 Answers 2

9 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Controller

Execution Model

Step Model

Execution View

2 Answers 2

9 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related