LINQtoEntities-根據連接、條件和排序獲取結果

我有一些代碼回圈遍歷 3 個單獨串列的記錄并執行邏輯來創建一個新物件。它有效，但需要很長時間。我希望我可以將其全部簡化為 LINQ to Entities 查詢。

本質上，我有一個人員串列和一個電話號碼串列。電話號碼有一個型別（'cell'、'home'、'office'等）和等級（1-x）。如果某人有兩部手機，則排名較低的應優先考慮。

結果集應該只有一個手機（排名最低）和一部家庭電話（排名最低）。如果沒有手機或家庭電話，這些應該為空。

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);

uj5u.com熱心網友回復：

嘗試使用 linq。我給了你 2 個使用 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;
    }
}

標籤：实体框架 林克 实体框架核心 链接到实体

上一篇：如何通過python3中的執行緒通過多個帳戶獲取資料

下一篇：在C#類中使用虛擬屬性時如何解決“SqlException：列名無效”？