• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 70
  • Last Modified:

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
0
LionCalledAlbert
Asked:
LionCalledAlbert
2 Solutions
 
Miguel OzSoftware 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 SotoCommented:
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

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now