Solved

Get Extra Foreign Field in Linq Query Result

Posted on 2011-02-19
12
1,018 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 63

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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 63

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 63

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 63

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

621 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