Solved

Get Extra Foreign Field in Linq Query Result

Posted on 2011-02-19
12
987 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
  • 5
  • 4
  • 3
12 Comments
 
LVL 62

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
 
LVL 62

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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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 62

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 62

Accepted Solution

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

A basic question.. “What is the Garbage Collector?” The usual answer given back: “Garbage collector is a background thread run by the CLR for freeing up the memory space used by the objects which are no longer used by the program.” I wondered …
It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

760 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now