Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I pass an aggregate Linq query to my view

Posted on 2011-02-15
5
Medium Priority
?
742 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 64

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 64

Accepted Solution

by:
Fernando Soto earned 2000 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 64

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

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

670 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