Solved

How do I pass an aggregate Linq query to my view

Posted on 2011-02-15
5
736 Views
Last Modified: 2012-06-21
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
Comment
Question by:nikomanek
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 34900814
Hi nikomanek;

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

Fernando
0
 

Author Comment

by:nikomanek
ID: 34900881
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
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 34901187
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
 

Author Closing Comment

by:nikomanek
ID: 34902594
Perfect, thank you!
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 34902624
Not a problem, glad I was able to help.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
I was asked about the differences between classic ASP and ASP.NET, so let me put them down here, for reference: Let's make the introductions... Classic ASP was launched by Microsoft in 1998 and dynamically generate web pages upon user interact…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question