How to do a join with Lync and Entity Framework v4.0

Posted on 2015-02-08
Medium Priority
Last Modified: 2016-06-23
I have a sql server database from which I have created an edmx file in my csharp project. The database has all the requisite foreign keys setup and I can see these as NavigationProperties in the XML view of the edmx. All tables have come through as entities correctly.

In SQL I can run a query as follows:

SELECT * FROM Card.CardRequest cr
  JOIN product.CompanyProductDesign cpd ON cpd.CompanyProductDesignID = cr.CompanyProductDesignID
  JOIN product.ProductDesignCOntent pdc ON cpd.CompanyProductDesignID = pdc.CompanyProductDesignID

which will return only the rows that have been matched from all tables (in my case 6 rows). However I am struggling to get the same result using EF and Lync.

I have got the following code in my c# program:

                using (PrepayEntities ent = new PrepayEntities())
                    cardRequests = ent.CardRequest
                                        cr =>
                                            cr.CRIFileCreated == true
                                            cr.CreationEmailSent == false

which I know includes an extra Where condition but in any case returns all the rows (in my case 6100 rows). I have tried every possibility from examples that I have found the Internet but just can't seem to crack it - I either get 6100 rows or none.

Please can someone point me in the right direction for what I am sure is a simple solution

Question by:LionCalledAlbert
LVL 36

Accepted Solution

Miguel Oz earned 1000 total points
ID: 40597561
Your LINQ join query should look like:
var result = from cr in ent.CardRequest
join cpd in ent.CompanyProductDesign on cr.CompanyProductDesignID equals cpd.CompanyProductDesignID
join pdc in ent.ProductDesignCOntent on cpd.CompanyProductDesignID equals pdc.CompanyProductDesignID
select new
//add more fields as needed

Open in new window

There are more examples using join in this link
LVL 64

Assisted Solution

by:Fernando Soto
Fernando Soto earned 1000 total points
ID: 40597566
Hi Phil;

Try this code snippet to see if it gives you what you are looking for.

using (PrepayEntities ent = new PrepayEntities())
    cardRequests = ent.CardRequest.
                   Join( CompanyProductDesign, 
                         cr => cr.CompanyProductDesignID,
                         cpd => cpd.CompanyProductDesignID,
                         (cr, cpd) => new
                              cr = cr,
                              cpd = cpd
                   Join( ProductDesignCOntent,
                         temp => temp.cpd.CompanyProductDesignID,
                         pdc => pdc.CompanyProductDesignID,
                         (temp, pdc) => new
                               cr = temp.cr,
                               cpd = temp.cpd,
                               pdc = pdc

Open in new window


Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Free Data Recovery software is an advanced solution from Kernel Tools to recover data and files such as documents, emails, database, media and pictures, etc. It supports recovery from physical & logical drive after a hard disk crash, accidental/inte…

627 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