LINQ to Entities - Get results based on join, conditions and sorting-CodePudding

I have some code that loops through records of 3 separate lists and performs logic to create a new object. It works but takes a very long time. I'm hoping I can simplify it all into a LINQ to Entities query.

Essentially, I have a list of People, and a List of Phone Numbers. The Phone numbers have a type ('cell', 'home', 'office', etc.) and a rank (1-x). If someone has two cell phones the the lower the rank should take priority.

The result set should have only one cell (the lowest ranking) and one home phone (the lowest ranking). These should be null if there is no cell or home phone.

Person person1 = new("Person", "One");
Person person2 = new("Person", "Two");
Person person3 = new("Person", "Three");
Person person4 = new("Person", "Four");
Person person5 = new("Person", "Five");

Phone ph1 = new("1112222222", "home", 2, person1);
Phone ph2 = new("1111111111", "cell", 1, person1);
Phone ph3 = new("2223331111", "cell", 1, person2);
Phone ph4 = new("3334441111", "cell", 1, person3);
Phone ph5 = new("3335552222", "cell", 2, person3);
Phone ph6 = new("4446662222", "home", 2, person4);
Phone ph7 = new("5557772222", "cell", 3, person5);
Phone ph8 = new("5557773333", "home", 2, person5);

// Create two lists.
List<Person> people = new() { person1, person2, person3, person4, person5 };
List<Phone> phones = new() { ph1, ph2, ph3, ph4, ph5, ph6, ph7, ph8 };

***//CAN A LINQ to ENTITIES QUERY PRODUCE THE DESIRED RESULTS
var query =
    from person in people
    join ph in phones on person equals ph.Owner into persPh
    from personPhones in persPh.DefaultIfEmpty()
    select new PersonPhone
    {
        FirstName = person.FirstName,
        LastName = person.LastName,
        CellPhoneNumber = personPhones?.Number ?? string.Empty, //<==== Needs to be lowest ranking number of type = "cell"
        HomePhoneNumber = personPhones?.Number ?? string.Empty  //<==== Needs to be lowest ranking number of type = "home"
    };***

foreach (PersonPhone v in query)
{
    Console.WriteLine($"{v.FirstName   " "   v.LastName   ":",-15} {v.CellPhoneNumber,-15} {v.HomePhoneNumber,-15} ");
}

// This code Should produce the following output:
//
// Person One:    1111111111    1112222222
// Person Two:    2223331111
// Person Three:  3334441111    
// Person Four:                 4446662222    
// Person Five:   5557772222    5557773333


public record class Person(string FirstName, string LastName);
public record class Phone(string Number, string Type, int Rank, Person Owner);
public record class PersonPhone(string FirstName, string LastName, string CellPhoneNumber, string HomePhoneNumber);

CodePudding user response：

try to use linq. i gave you 2 options for education using Linqpad

void Main()
{
    Person person1 = new("Person", "One");
    Person person2 = new("Person", "Two");
    Person person3 = new("Person", "Three");
    Person person4 = new("Person", "Four");
    Person person5 = new("Person", "Five");

    Phone ph1 = new("1112222222", "home", 2, person1);
    Phone ph2 = new("1111111111", "cell", 1, person1);
    Phone ph3 = new("2223331111", "cell", 1, person2);
    Phone ph4 = new("3334441111", "cell", 1, person3);
    Phone ph5 = new("3335552222", "cell", 2, person3);
    Phone ph6 = new("4446662222", "home", 2, person4);
    Phone ph7 = new("5557772222", "cell", 3, person5);
    Phone ph8 = new("5557773333", "home", 2, person5);

    // Create two lists.
    List<Person> people = new() { person1, person2, person3, person4, person5 };
    List<Phone> phones = new() { ph1, ph2, ph3, ph4, ph5, ph6, ph7, ph8 };
    
    var personPhoneList = new List<PersonPhone>();
    //option 1
    people.ForEach(person =>
    {
        var pp = new PersonPhone()
        {
            FirstName = person.FirstName,
            LastName = person.LastName,
            CellPhoneNumber = phones.FirstOrDefault(cp => cp.type == "cell" && cp.Owner == person)?.Number,
            HomePhoneNumber = phones.FirstOrDefault(cp => cp.type == "home" && cp.Owner == person)?.Number
        };
        personPhoneList.Add(pp);
    });
    personPhoneList.Select(v => new
    {
        v.FirstName,
        v.LastName,
        v.CellPhoneNumber,
        v.HomePhoneNumber
    }).Dump("Option 1");

    //option 2
    people.Select(person => new PersonPhone()
    {
        FirstName = person.FirstName,
        LastName = person.LastName,
        CellPhoneNumber = phones.OrderBy(o=> o.Rank).FirstOrDefault(cp => cp.type == "cell" && cp.Owner == person)?.Number,
        HomePhoneNumber = phones.OrderBy(o=> o.Rank).FirstOrDefault(cp => cp.type == "home" && cp.Owner == person)?.Number
    })
    .ToList().Dump("option 2");

    
    
}

public class PersonPhone
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string CellPhoneNumber { get; set; }
    public string HomePhoneNumber { get; set; }

}



public class Person
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public Person(string _first, string _last)
    {
        FirstName = _first;
        LastName = _last;
    }
}
public class Phone
{
    public string Number { get; set; }
    public string type { get; set; }
    public int Rank { get; set; }
    public Person Owner { get; set; }

    public Phone(string _number, string _type, int _order, Person _person)
    {
        Number = _number;
        type = _type;
        Rank = _order;
        Owner = _person;
    }
}