• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 747
  • Last Modified:

How do I pass an aggregate Linq query to my view

Hi,
I am trying to create a view from two tables in my database. I am using EF4 and ASP.NET MVC 3. Now I query these 2 tables and need to return a view model which contains all columns from the Report table and the business name column from the Customers table. In my view I just want to add one model name. How do I go about it? The attached code of course only returns the report data. So how do I get the one column from the Customers table in the same model (or meta model?)?
public IEnumerable<Report> GetMyReports(int resellerid)
        {
            ReportsAndName repn = new ReportsAndName();
            var reports = from c in entities.Customers
                          from r in entities.Reports
                          where c.CustID == r.CustomerID &&
                          c.Reseller_id == resellerid
                          select r;// String.Format("{0} - {1}", r, c.Business_name);
            
            return reports;

Open in new window

0
nikomanek
Asked:
nikomanek
  • 3
  • 2
1 Solution
 
Fernando SotoRetiredCommented:
Hi nikomanek;

Do you want the results of the query, reports, to be a collection of type ReportsAndName?

Fernando
0
 
nikomanekAuthor Commented:
Hi, sorry, the ReportsAndName is a leftover from one of my attempts to create a repository which contains Reports and Customers. Did not really work.

I have entities which are from a EF4 model generated from the Customer and Report table and I would like to have all reports and the business name from Customers in my result.
0
 
Fernando SotoRetiredCommented:
Hi nikomanek;

You need to create a class with all the fields you want back from the query and then change the select clause of the query as shown in the code snippet and that should do it.

public IEnumerable<ReportAndCustomerName> GetMyReports(int resellerid)
{
    var reports = from c in entities.Customers
                  from r in entities.Reports
                  where c.CustID == r.CustomerID &&
                  c.Reseller_id == resellerid
                  select new ReportAndCustomerName
                  {
                      CustomerID = r.CustomerID,
                      //... All other fields from Reprts and Customers
                      BusinessName = c.BusinessName
                  };
    
    return reports;
}

// This class is used to get all the needed info from the query
public class ReportAndCustomerName
{
    // List all the fields from Reports table
    public int CustomerID { get; set; }
    // All the other fields from Reports here
    
    // The BusinessName From the cuistomer table
    public string BusinessName { get; set; }   
}

Open in new window


Fernando
0
 
nikomanekAuthor Commented:
Perfect, thank you!
0
 
Fernando SotoRetiredCommented:
Not a problem, glad I was able to help.
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now