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

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
                                 .Include("CompanyProductDesign.ProductDesignContent")
                                 .Where
                                    (
                                        cr =>
                                            cr.CRIFileCreated == true
                                            &&
                                            cr.CreationEmailSent == false
                                    )
                                .ToList();
                }

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

Thanks
Phil
LionCalledAlbertAsked:
Who is Participating?
 
Miguel OzConnect With a Mentor Software EngineerCommented:
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
cr.CRIFileCreated,
cr.CreationEmailSent
};

Open in new window


There are more examples using join in this link
0
 
Fernando SotoConnect With a Mentor RetiredCommented:
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
                             }
                   ).ToList();
}                   

Open in new window

0
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.

All Courses

From novice to tech pro — start learning today.