CODEWITHSUNDEEP
c#linq
AK109
AK109

Reputation: 9

Select 2 Column of 2 Table

I have 2 tables:

My problem is:

  1. How could I write in query syntax and method syntax in controller and return to the View(), like this in SQL query:

     select a.NAME, b.VALUE 
 from Staff a, Salary b 
 where a.ID = b.ID

  2. How could I declare in view like:

     @model IEnumerable<Project.Model.xxxx>

This is the way I do with one table in IndexController:

LinQDataContext data = new LinQDataContext();

public ActionResult Index(int id)
{
     var staffWithTheirSalary = data.Staff
                                    .Select(a => a)
                                    .Where(a => a.ID == id);
}

Index view:

@model IEnumerable<Project.Model.Staff>

@foreach(var item in Model)
{
    @item.Name
}

Upvotes: 1

Views: 87

Answers (3)

Frank Fajardo
Frank Fajardo

Reputation: 7359

You can have your view model to be IEnumerable<StaffSalaryViewModel> where StaffSalaryViewModel is: 

public class StaffSalaryViewModel
{
    public int StaffId { get; set; }
    public string Name { get; set; }
    public decimal Salary { get; set; } // Not sure of the type
}

Then to extract the list:

Using Linq method syntax:

var result = data.Staff
   .Join(data.Salary,
      staff => staff.ID,
      salary => salary.ID,
      (staff, salary) => new StaffSalaryViewModel { 
         StaffId = staff.ID, 
         Name = staff.NAME, 
         Salary = salary.VALUE 
      });

Using Linq query syntax:

var result = from staff in data.Staff 
         join salary in data.Salary on staff.ID equals salary.ID
         select new StaffSalaryViewModel { 
            StaffId = staff.ID, 
            Name = staff.NAME, 
            Salary = salary.VALUE 
         };

UPDATE

It looks like the VALUE field in your Salary table allows null values. If so just make the corresponding property in your view model nullable, like so (provided it is a decimal type):

public class StaffSalaryViewModel
{
    public int StaffId { get; set; }
    public string Name { get; set; }
    public decimal? Salary { get; set; } // Nullable/optional decimal
}

Or if you want to set StaffSalaryViewModel.Salary to 0 If VALUE is null, change your query to have:

 Salary = salary.VALUE ?? 0

Upvotes: 1

Morteza Jangjoo
Morteza Jangjoo

Reputation: 1790

use ViewModel, you can create Viewmodel Folder in project and Create class into like this.

 public class StaffSalaryViewModel
    {
     public int StaffId { get; set; }
     public int SalaryeId { get; set; }
     public string StaffName { get; set; }
     public string SlaaryName { get; set; }

    }

in action :

public ActionResult Index(int id)
{
  StaffSalaryViewModel staffWithTheirSalary = (from itemStaff in data.Staff join     itemSalary in data.Salary in itemStaff.id equal itemSalary.id where itemSalary.id=id select new StaffSalaryViewModel {Salaryname=itemSalary.Name,StaffName=itemStaff.Name,SalaryId=itemSalary.Id,itemStaff.Id}).ToList();
}

in View

@model IEnumerable<Project.ViewModel.StaffSalaryViewModel>

@foreach(var item in Model)
{
  @item.SalaryName
}

Upvotes: 1

robjam
robjam

Reputation: 989

Because tables are being joined, I prefer using the query syntax. The controller would use the LinQDataContext to get at the data, but when creating the C# Object I would recommend creating a Class that is only used for displaying the data (in the example its called MySalaryModel).

public ActionResult Index(int id)
{
    IEnumerable<Project.Model.MySalaryModel> staffWithTheirSalary = from staff in data.Staff
            join salary in data.Salary on staff.Id equals salary.Id
            select new Project.Model.MySalaryModel 
            {
                Id = staff.Id,
                Name = staff.Name,
                Salary = salary.Value,
            };

    return View(staffWithTheirSalary);
}

Upvotes: 1

Related Questions