New Posts New Posts RSS Feed: Need help with Join in Lambda expression.
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Need help with Join in Lambda expression.
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
BillG View Drop Down
DevForce MVP
DevForce MVP
Avatar

Joined: 05-Dec-2007
Location: Monroe, MI
Posts: 233 		Post Options Post Options   Quote BillG Quote  Post ReplyReply Direct Link To This Post Topic: Need help with Join in Lambda expression.
    Posted: 06-Jan-2011 at 10:45pm
Here is my linq statement so far.
 

var records = Mgr.Members

.Where(m => m.Status == "A" && m.DuesRate > 0 && m.DuesDeduction == "Y")

.Select(m => m)

.ExecuteAsync();

 
Now I need to add a .Join() statement. I need to join Member  on SocSecNo to JobHistory on SocSecNo
and (JobHistory to JobSite on both EmployerNo and JobSiteNo)
 
Thanks
Bill
 
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786 		Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post Posted: 07-Jan-2011 at 1:45pm

Hi Bill,

 
What's the result you are trying to retrieve from the query? (Members?, JobHistories?, JobSites?, a projection with more than one entity type?)
 
Below is a simple example returning Members
 
.var records = Mgr.Members
.Where(m => m.Status == "A" && m.DuesRate > 0 && m.DuesDeduction == "Y")
//.Select(m => m)
 
.Join(Mgr.JobHistories
  , m => m.SocSecNo, jH => SocSecNo
  ,(m, jH) => new {members = m})
.Select(m = m.members)
 
.ExecuteAsync();
 
You mentioned that you need to do a Join Member and JobHistory (the example above describes it) and a Join on JobHistory and JobSite.
Do you want all this joins in one query? Or are they separate queries?
Also, note that for your Join between JobHistory and JobSite,where you have 2 conditions, (EmployerNo and JobSiteNo) using query comprehension syntax might actually be simpler:
 
var query = from jH in mgr.JobHistories
                   from jS in mgr.JobSites
                   where jH.EmployerNo == jS.EmployerNo
                   && jH.JobSiteNo == jS.JobSiteNo
                   select new { JobHistory = jH, JobSite = jS };
 
 
Back to Top
BillG View Drop Down
DevForce MVP
DevForce MVP
Avatar

Joined: 05-Dec-2007
Location: Monroe, MI
Posts: 233 		Post Options Post Options   Quote BillG Quote  Post ReplyReply Direct Link To This Post Posted: 07-Jan-2011 at 4:53pm

var records = Mgr.Members

.Where(m => m.Status == "A" && m.DuesRate > 0 && m.DuesDeduction == "Y")

.Join(Mgr.JobHistories, m => m.SocSecNo, jh => jh.SocSecNo, (m, jh) => new { members = m })

.Select(m => m)

.ExecuteAsync();

so how can I add the Jobhistory to the where clause. I need to search for jh.TSFinish is null in addition to the above where criteria.
Back to Top
sbelini View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 13-Aug-2010
Location: Oakland
Posts: 786 		Post Options Post Options   Quote sbelini Quote  Post ReplyReply Direct Link To This Post Posted: 12-Jan-2011 at 12:06pm
Hi Bill,
 
try:
 
var records = from m in Mgr.Members
                      from jH in Mgr.JobHistories
                      where m.SocSecNo == jH.SocSecNo
                      && jH.TSFinish == null
                      select m;
 
or
 
var records = mgr.Members
        .SelectMany(jH => mgr.Jobhistories, (m, jH) => new { m = m, jH = jH })
        .Where((w) => (w.m.SocSecNo == w.jH.SocSecNo && w.jH.TSFinish== null))
        .Select(m => m.m);
 
note that in the latter you will want to have only Distinct results.
 
Silvio.
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down