EF LINQ Query for selecting multiple table in single row and count

Question

I have two tables with data for veterinary medicine.

Customers have many patients(pats), relation is "one to many". I want to show customers with their petsname and count single line

Table: Customer

public class Customer
    {
        [Key]
        public int ID { get; set; }
        public bool IsActive { get; set; }
        public string TC { get; set; }
        public string Name { get; set; }
        public string Surname { get; set; }
        public string Email { get; set; }
        public string Address { get; set; }
        public string Phone { get; set; }
        public string Note { get; set; }
        public int AccountID { get; set; }

    }

Table: Patient (Pet)

public class Patient
    {
        [Key]
        public int PatientID { get; set; }
        public bool IsActive { get; set; }
        public int TypePatientID { get; set; }
        public string TypeRace { get; set; }
        public string CIPCode { get; set; }
        public string Color { get; set; }
        public string PatientName { get; set; }
        public int Status { get; set; }
        public int GenderID { get; set; }
        public DateTime BirthDate { get; set; }
        public DateTime? DeathDate { get; set; }
        public int CustomerID { get; set; }
        public int AccountID { get; set; }

    }


public class CustomerPageModel
    {
        [Key]
        public int ID { get; set; }
        public bool IsActive { get; set; }
        public string TC { get; set; }
        public string Name { get; set; }
        public string Surname { get; set; }
        public string Email { get; set; }
        public string Address { get; set; }
        public string Phone { get; set; }
        public string Note { get; set; }
        public int AccountID { get; set; }
        public string Pats { get; set; }
        public int PatCount { get; set; }

    }

I tried the following code:

        var result = from p in context.Customers
                      join f in context.Patients on p.ID equals f.CustomerID
                      where p.AccountID == AccountID
                      group f by new { f.CustomerID, p.IsActive, p.TC, p.Name, p.Surname, p.Email, p.Address, p.Phone, p.Note, p.AccountID, f.PatientName,p.ID } into g
                      select new CustomerPageModel
                      {
                          ID=g.Key.ID,
                          IsActive = g.Key.IsActive,
                          TC = g.Key.TC,
                          Name = g.Key.Name,
                          Surname = g.Key.Surname,
                          Email = g.Key.Email,
                          Address = g.Key.Address,
                          Phone = g.Key.Phone,
                          Note = g.Key.Note,
                          AccountID = g.Key.AccountID,
                          Pats = string.Join(",", g.Select(x => x.PatientName))
                      };

Expected Result is:

[  
    {  
        "id":13,
        "isActive":true,
        "tc":"1234",
        "name":"John ",
        "surname":"Snow",
        "email":"",
        "address":"",
        "phone":"",
        "note":null,
        "accountID":3,
        "pats":"Oscar,Puffy",
        "patCount":2
    },
    {  
        "id":14,
        "isActive":true,
        "tc":"2345",
        "name":"Mark",
        "surname":"Zurk",
        "email":"",
        "address":"",
        "phone":"",
        "note":null,
        "accountID":3,
        "pats":"Mars",
        "patCount":1
    }
]

Please check link: https://dotnetfiddle.net/rsv45D

Can anyone help me to write this ef query?

Answer 1

Why Group by all the fields, as you just want to group by user that should be CustomerID (or whatever its key field is):

var result = from p in customers

                         join f in patients on p.ID equals f.CustomerID
                         where p.AccountID == AccountID

                         group new { f, p } by f.CustomerID into g

                         select new CustomerPageModel
                         {
                             ID = g.Key,
                             IsActive = g.First().p.IsActive,
                             TC = g.First().p.TC,
                             Name = g.First().p.Name,
                             Surname = g.First().p.Surname,
                             Email = g.First().p.Email,
                             Address = g.First().p.Address,
                             Phone = g.First().p.Phone,
                             Note = g.First().p.Note,
                             AccountID = g.First().f.AccountID,
                             Pats = string.Join(",", g.Select(x => x.f.PatientName)),
                             PatCount = g.Count()
                         };

Demo Link