LINQ joining values from different classes

Question

I am new to LINQ and sorry if my question have been asked

I have 2 classes

public class Person
{
    int ID {get;set;}
    string FirstName {get;set;}
    string LastName {get;set;}
}

and

public class House
{
    int ID {get;set;}
    string Address {get;set;}
    string ZipCode {get;set;}
    int PersonId {get;set;}
}

I am saving the list of houses in a IEnumerable List

IEnumerable<House> ListHouses = GetAllHouses();

GetAllHouses return the list of houses from the database

I want to use Lamda select in LINQ in order to do the following

var st = ListHouses .Select(h => new
{
    id = h.ID,
    Address= h.Address,
    Zip= h.ZipCode ,
    PersonFirstName = GetPersonByID(h.PersonId ).FirstName, 
    PersonLastname = GetPersonByID(h.PersonId ).lastname

});

Where GetPersonByID returns an object of Type Person that has the given ID. and then I take his first name and last name.

My question is this:

Instead of Getting the Person 2 times for the variables (personFirstName and PersonLastName) Is there a way I can get it one time and then used it. Something like

PersonForId = GetPersonByID(h.PersonId)
PersonFirstName =  PersonLastName.FirstName,
PersonLastname = PersonLastName.lastname

I'm looking for something similar to Join in SQL where you join a value from another table.

Thanks you very much for any help

Have a look here http://www.dotnetperls.com/join C# Join Example: LINQ — NoWar, Apr 10 '12 at 12:23

score 5 · Accepted Answer · edited May 23 '17 at 11:46

You're extremely close! Using your code (and making all properties on House and Person public), here is a method using the LINQ Join method:

var st = GetAllHouses().Join(GetAllPersons(),
    outerKey => outerKey.PersonId,
    innerKey => innerKey.ID,
    (house, person) => new
    {
        house.ID,
        house.Address,
        house.ZipCode,
        PersonFirstName = person.FirstName,
        PersonLastname = person.LastName
    });

Note: I would recommend the GetAllPersons() and the GetAllHouses() methods return IQueryable rather than IEnumerable. Doing so will build the expression (including the join), which means LINQ-to-SQL (or Entities) will build a proper SQL statement with the JOIN included, instead of enumerating the collections and then joining.

Additional information on such can be found here: Returning IEnumerable<T> vs. IQueryable<T>

score 3 · Answer 2 · answered Apr 10 '12 at 12:24

using System;
using System.Linq;

class Customer
{
    public int ID { get; set; }
    public string Name { get; set; }
}

class Order
{
    public int ID { get; set; }
    public string Product { get; set; }
}

class Program
{
    static void Main()
    {
    // Example customers.
    var customers = new Customer[]
    {
        new Customer{ID = 5, Name = "Sam"},
        new Customer{ID = 6, Name = "Dave"},
        new Customer{ID = 7, Name = "Julia"},
        new Customer{ID = 8, Name = "Sue"}
    };

    // Example orders.
    var orders = new Order[]
    {
        new Order{ID = 5, Product = "Book"},
        new Order{ID = 6, Product = "Game"},
        new Order{ID = 7, Product = "Computer"},
        new Order{ID = 8, Product = "Shirt"}
    };

    // Join on the ID properties.
    var query = from c in customers
            join o in orders on c.ID equals o.ID
            select new { c.Name, o.Product };

    // Display joined groups.
    foreach (var group in query)
    {
        Console.WriteLine("{0} bought {1}", group.Name, group.Product);
    }
    }
}

Output

Sam bought Book Dave bought Game Julia bought Computer Sue bought Shirt

LINQ joining values from different classes

2 Answers2