Solved

How do I pass an aggregate Linq query to my view

Posted on 2011-02-15
5
731 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Suggested Solutions

Title # Comments Views Activity
Help with IIS intermittent hangs on Windows 2012 5 65
Send parameters via datatable ajax call 4 48
C# DateTime Help 6 47
Data Analysis 7 59
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

751 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