C# linq lambda join and select syntax

Question

I'm looking for the counterpart of Msql query:

SELECT per.*,add.addressDescription FROM Persons per 
       JOIN Address add ON per.AddressId = add.AddressId

I have this query:

    var query = persons.JOIN(address,per = person.addressId,add = addressId
    (per,add) => 
    new Persons{
                addressDescription = add.addressDescription,
                PersonId = per.PersonId,
                PersonFirstName = per.PersonFirstName
                PersonLastName = per.PersonLastName})

Is there a way to populate Persons.addressDescription without assigning individually the other properties of Persons? Imagine if Persons have 10 more properties.

I would like to refrain from using loops like:

foreach(Person person in PersonList)
{
  foreach(Address address in AddressList)
  {
     if(person.addressId == address.addressId){
        person.addressDescription = address.addressDescription
     }
   }
}

Possible duplicate of What is the syntax for an inner join in LINQ to SQL? — shA.t
– shA.t, Commented Jul 11, 2016 at 7:29
@shA.t hi and thanks for your reply, the problem is their question only asks how to return the Persons without populating Persons.AddressDescription. — user3770093
– user3770093, Commented Jul 11, 2016 at 7:37

Gilad Green · Accepted Answer · 2016-07-11 07:35:14Z

4

var query = persons.join(address,
    per = person.addressId,
    add = addressId
    (per,add) => 
    {
        per.addressDescription = add.addressDescription;
        return per;
    });

edited Jul 11, 2016 at 7:35

answered Jul 11, 2016 at 7:01

Gilad Green

37.3k7 gold badges67 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user3770093 Over a year ago

wow really, thats the first time I saw a return in the select statement. I'll give it a go now.

user3770093 Over a year ago

I can't use return function inside the query =/

Gilad Green Over a year ago

@user3770093 Show what you did because for me it works

user3770093 Over a year ago

Hi mate thanks your solution worked! I had simple typo errors. Thank you so much!

Gehan Fernando · Accepted Answer · 2016-07-11 06:57:19Z

4

var id = 1;
var query = database.Posts    // your starting point - table in the "from" statement
   .Join(database.Post_Metas, // the source table of the inner join
      post => post.ID,        // Select the primary key (the first part of the "on" clause in an sql "join" statement)
      meta => meta.Post_ID,   // Select the foreign key (the second part of the "on" clause)
      (post, meta) => new { Post = post, Meta = meta }) // selection
   .Where(postAndMeta => postAndMeta.Post.ID == id);    // where statement

answered Jul 11, 2016 at 6:57

Gehan Fernando

1

1 Comment

user3770093 Over a year ago

thanks for your reply but I think I will still need to loop it following your example.

Collectives™ on Stack Overflow

C# linq lambda join and select syntax

2 Answers 2

4 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related