DevForce 2010: Need help with Join in Lambda expression.

Need help with Join in Lambda expression.

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2010
Forum Discription: For .NET 4.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=2424
Printed Date: 29-Jul-2026 at 1:07pm

Topic: Need help with Join in Lambda expression.

Posted By: BillG
Subject: Need help with Join in Lambda expression.
Date 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

Replies:

Posted By: sbelini
Date 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 };

Posted By: BillG
Date 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.

Posted By: sbelini
Date 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;

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.