Linq Left join

Scenario: Search all Stakeholder regardless if they have a current effective address. Thanks to this post.


var qry = from data in dbx.Stakeholder_UVs
           join addressData in dbx.Stakeholder_Address_UVs
               on data.Stakeholder_ID equals addressData.Stakeholder_ID into tempAddresses
               from addressData in tempAddresses
                .Where(x => x.Effective_Date < DateTime.Today)
                .Where(x => x.Expired_Date > DateTime.Today)
                .Where(x => x.Address_Role  == "Mailing")
                .DefaultIfEmpty() ;

Or filter in table instead of temp data.


var qry = from data in dbx.Stakeholder_UVs
           join addressData in dbx.Stakeholder_Address_UVs
                .Where(x => x.Effective_Date < DateTime.Today)
                .Where(x => x.Expired_Date > DateTime.Today)
                .Where(x => x.Address_Role  == "Mailing")
               on data.Stakeholder_ID equals addressData.Stakeholder_ID into tempAddresses
               from addressData in tempAddresses.DefaultIfEmpty() ;

But, what if more than one addresses appear? Here I added a filter to get the Max addressId back, which should be the recently added:

 
            var qry =  from data in dbx.Stakeholder_UVs
                       join addressData in dbx.Stakeholder_Address_UVs
                            .Where(x => x.Effective_Date < DateTime.Today)
                            .Where(x => x.Expired_Date > DateTime.Today)
                            .Where(x => x.Address_ID == 
                                (dbx.Stakeholder_Address_UVs)
                                .Where(a=>a.Stakeholder_ID == x.Stakeholder_ID)
                                .Max(a=> a.Address_ID))
                           on data.Stakeholder_ID equals addressData.Stakeholder_ID into tempAddresses
                           from ad in tempAddresses 
 
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s