Solved

Get Extra Foreign Field in Linq Query Result

Posted on 2011-02-19
12
990 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

910 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