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

x
?
Solved

Get Extra Foreign Field in Linq Query Result

Posted on 2011-02-19
12
Medium Priority
?
1,023 Views
Last Modified: 2013-11-11
What's the best way with linq to do this typical scenario using Entity Framework and Linq:
Two tables, Invoices ,Customers, you want to list Invoices with CompanyName which is in Customers (fk CustomerID in Invoices).
Say I have an extension Partial Invoice class with the extra CompanyName field (say I don’t want the whole hog of creating an InvoiceDetailFoUIOnly class)
var query = from inv in _context.Invoices where(x=>x.Date > yesterday) join cust in _context.Customers on inv.CustomerID equals cust.CustomerID
                                  select new { Do I really have to create anon type and cast back to Invoice??? };
I can of course create anonymous type and repopulate a List<Invoice> one by one, but its not very elegant?
0
Comment
Question by:Silas2
[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
  • 5
  • 4
  • 3
12 Comments
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 34934770
Hi Silas2;

Not exactly sure what the question is. Can you give some more details and draw out table and what you you want as a result set. For example You say I have two tables

Invoices                        Customers
CompanyName             CompanyName (FK)

and give the important fields in both and any PK / FK relationships. and what the shape of the result set should be. It would greatly help.

Fernando
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 34935671
I think it should be something like:

var query = from inv in _context.Invoices
               where inv.Date > yesterday
            from cust in _context.Customers
               where cust.CustomerID = inv.CustomerID 
                  && cust.CompanyName = theCompanyName
            select inv;

Open in new window

0
 

Author Comment

by:Silas2
ID: 34937081
Sorry I haven’t made myself clear. Say database tables (just sample):
Customers -> Fields:CustomerID, CompanyName
Invoices -> Fields:InvoiceID, CustomerID, Date,Amount
I run Entity Framework tool (i.e. by creating new ADO Data Entity  Model)
I want to display list of Invoices with company names, (which is not field in Invoice table), so I create Partial Invoice class to append extra field to EF’s Invoice class, call it “DisplayOnlyCompanyField”. In the old days  I would  run the query ‘Select Invoices.*, Customs.CompanyName as DisplayOnlyCompanyField from Invoice inner join Customers’ , iterate the result set, and populate my Invoice class with its extra field from that.
Of course I could do that by returning an anonymous type, and populating new Invoice objects, but I thought there must be a nicer way of doing it…is there?
0
PowerShell Core for Advanced Linux Administrators

Understand advanced principals around Powershell Core with a focus on the Linux Administrator.  This course covers how to administer numerous environments across multiple platforms including Linux, Azure, AWS, and Google Cloud from a single shell instance.

 
LVL 64

Expert Comment

by:Fernando Soto
ID: 34937479
Hi Silas2;

Because of the relationship between Customers and Invoices, 1 Customer to Many Invoices,  this becomes simply querying the Invoices and to get the CompanyName just follow the Customer navigation property as shown in the code snippet. Also seeming to you created a class DisplayOnlyCompanyField you can have the query automatically fill it also shown in code snippet.

TestDBEntities db = new TestDBEntities();

List<DisplayOnlyCompanyField> invoiceData = (from inv in db.Invoices
                                             select new DisplayOnlyCompanyField
                                             {
                                                 InvoiceID = inv.InvoiceID,
                                                 CustomerID = inv.CustomerID,
                                                 CompanyName = inv.Customer.CompanyName,
                                                 Date = inv.Date,
                                                 Amount = inv.Amount
                                             }).ToList();

foreach (DisplayOnlyCompanyField docf in invoiceData)
{
    Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}", docf.InvoiceID, docf.CustomerID, docf.CompanyName, docf.Date, docf.Amount);
}


public class DisplayOnlyCompanyField
{
    public int InvoiceID { get; set; }
    public int CustomerID { get; set; }
    public string CompanyName { get; set; }
    public DateTime Date { get; set; }
    public decimal Amount { get; set; }
}

Open in new window


Fernando
0
 

Author Comment

by:Silas2
ID: 34937689
Yes, but I wanted to return List<Invoice>, (the invoice  class being a combination of my Partial and the EF class), is that possible?
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 34937728
I suggest a couple of changes on FernandoSoto's query to address your Invoice.* requirement:

var query = from inv in db.Invoices
       where inv.Date > yesterday
       select new DisplayOnlyCompanyField
               {
                   Invoice = inv,
                   CompanyName = inv.Customer.CompanyName,
               };

public class DisplayOnlyCompanyField
{
    public Invoice Invoice { get; set; }
    public string CompanyName { get; set; }
}

Open in new window


If the relationship between Invoice and Customer is not configured on EF, the query would be something like this:

var query = from inv in db.Invoices
       where inv.Date > yesterday
       from cust in db.Customers
       where cust.CustomerID = inv.CustomerID
       select new DisplayOnlyCompanyField
               {
                   Invoice = inv,
                   CompanyName = cust.CompanyName,
               };

public class DisplayOnlyCompanyField
{
    public Invoice Invoice { get; set; }
    public string CompanyName { get; set; }
}

Open in new window


BTW, there is no need to ToList() the query, for most practical purposes you can use the query directly which has a better performance (since it does not pre-load all the results into memory).
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 34937744
Invoice does not have a place to hold the name, so you need a 3rd class. Am I missing something?
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 34937900
Hi Silas2;

How does this do?

TestDBEntities db = new TestDBEntities();

// Creates a List of Invoices which are instandiated from the inherited class DisplayOnlyCompanyField
List<Invoice> invoiceData = (from inv in db.Invoices
                                             select new DisplayOnlyCompanyField
                                             {
                                                 InvoiceID = inv.InvoiceID,
                                                 CustomerID = inv.CustomerID,
                                                 CompanyName = inv.Customer.CompanyName,
                                                 Date = inv.Date,
                                                 Amount = inv.Amount
                                             }).ToList<Invoice>();

// Iterate as a List of DisplayOnlyCompanyField
foreach (DisplayOnlyCompanyField docf in invoiceData)
{
    Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}", docf.InvoiceID, docf.CustomerID, docf.CompanyName, docf.Date, docf.Amount);
}

// Iterate as a List of Invoice
foreach (Invoice inv in invoiceData)
{
    Console.WriteLine("{0}\t{1}\t{2}\t{3}", inv.InvoiceID, inv.CustomerID, inv.Date, inv.Amount);
}


class DisplayOnlyCompanyField : Invoice
{
    public string CompanyName { get; set; }
}

Open in new window


Fernando
0
 

Author Comment

by:Silas2
ID: 34938077
Thanks for all your suggestions, but if I can't return a List<Invoice> + the extra (i.e. in Partial class only) field unless I do the laborious  InvoiceID = inv.InvoiceID,
                                                 CustomerID = inv.CustomerID,
                                                 CompanyName = inv.Customer.CompanyName,
                                                 Date = inv.Date,
                                                 Amount = inv.Amount
I would be better (from a maintainability+typing point of view) just to get an List<anonymous type> and drop it into a generic procedure using reflection to populate a List<Invoice> wouldn't I?
What irks me about doing that is just that it doesn't seem right to get a lovely List<Invoice> and for want of the population of a single extra field, turn that into anonymous types, then turn them back into Invoices doesn't it?
0
 

Author Comment

by:Silas2
ID: 34938123
Just a quick point to wdosanjos, when I said create partial class, I meant Paritial Invoice class, with an extra field called :DisplayOnlyCompanyField
0
 
LVL 64

Accepted Solution

by:
Fernando Soto earned 1000 total points
ID: 34938443
Sorry but what you are trying to do in the query to the model will not work. Invoice table has no field called CompanyName. If you want to return an object with a Invoice and a field of company name you will need to return a anonymous type or create an object of all fields of invoice and the company name and return that. But you can't add a partial class of Invoice because the model does not know about it until compile time and the model does not have a mapping to the database for that field.
0
 

Author Comment

by:Silas2
ID: 34938968
Ok, message understood.
0

Featured Post

Docker-Compose to Simplify Multi-Container Builds

Our veteran DevOps Author takes you through how to build a multi-container environment, managed with a single utility in order to simplify your deployments.

Question has a verified solution.

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

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

688 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