CODEWITHSUNDEEP
c#asp.net-coreentity-framework-core
brk
brk

Reputation: 427

Getting list of records existing in two different tables using EF Core

I have a person class with the following properties

public class Persons
{
    public Guid Id {get; set;}
    public string FirstName {get; set;}
    public string LastName {get; set;}
    public List<Connection> Connection {get; set;}
}

Each person can add another person as a friend. When a friend request is accepted, a connection between two persons will created. The Connection class contains the fowlloing properties:

public class Connection
{
    public Guid UserId { get; set; }
    public Guid FriendId {get; set}
}

From the controller I get two parameters: firstPerson's id and secondPerson's id. Then I call the ConnectionLogic method. Here is the method (firstPerson is the one who accept the request and secondPerson is the one who sent the request):

public async Task ConnectionLogic(Guid firstPerson, Guid secondPerson)
{
    Connection con = new() 
    {
        User = firstPerson;
        Friend = secondPerson;
    };

    await repo.CreateConnection(con);
}

And here is the repository code

public async Task CreateConntection(Connection con)
{
    Context.Connections.Add(con);
    await Context.SaveChangeAsync();
}

Now I want to get a list of all user's friends with their first name and last name. Here is the code which I wrote.

public async Task<List<Person>> GetAllConnections(Guid id)
{
    List<Person> firends = new();

    List<Guid> friendsId = await Context.Connections
    .Where(x => x.User == id)
    .Select(x => x.Friend)
    .ToListAsync();

    foreach (var item in friendsId)
    {
        firends.Add(Context.Persons.Where(id => id.Id == item).Select(x => new Person {FirstName == x.FirstName, LastName == x.LastName}).FirstOrDefault());    
    }

    return friends;
}

But I know when the list gets larger and larger it causes a huge performance problem. What is the best way to get all the friends of a given user's id?

Upvotes: 0

Views: 1117

Answers (1)

Neil W
Neil W

Reputation: 9247

Is there a reason for not putting navigation properties on Connection class?

Add navigation properties for User and Friend

public class Connection
{
    public Guid UserId { get; set; }
    public Guid FriendId {get; set}
    public Person User { get; set; }
    public Person Friend { get; set; }
}

Update GetAllConnections

public async Task<List<Person>> GetAllConnections(Guid id)
{
    return await Context.Connections
        .Where(c => c.UserId == id)
        .Select(c => c.Friend)
        .ToListAsync();
}

Then it's just one query to the database, instead of a separate query for each 'FriendId'.

To replicate your scenario exactly (just getting FirstName and LastName)

public async Task<List<Person>> GetJustNameOfAllConnections(Guid id)
{
    return await Context.Connections
        .Where(c => c.UserId == id)
        .Select(c => new Person
        {
            FirstName = c.Friend.FirstName,
            LastName = c.Friend.LastName
        }).ToListAsync();
}

Upvotes: 1

Related Questions