C#•2y ago

✅ API result

Hello, I have two tables, group and person. These are created in Sqlite using EntityFramework and therefore the i have two classes like this:

public class Group
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

public class Group
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

public class Person
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public ICollection<Group> Group{ get; set; }
    }

public class Person
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public ICollection<Group> Group{ get; set; }
    }

From what i understand the line: "public ICollection<Group> Group{ get; set; }" creates a relation between the two tables(the id) when created by EF. So to my question - when i try to retrieve Person via API(postman) i only get Id and Name. The goal is to get The person object but with Group nested in the JSON response. Is there a step im missing? 🙂
Thanks in advance!

37 Replies

Pobiega•2y ago

did you include the Group property with your query? ie

var peopleWithGroups = await _dbContext.People.Include(x => x.Group).ToListAsync();
return peopleWithGroups;

var peopleWithGroups = await _dbContext.People.Include(x => x.Group).ToListAsync();
return peopleWithGroups;

Accord•2y ago

Was this issue resolved? If so, run /close - otherwise I will mark this as stale and this post will be archived until there is new activity.

big OOFOP•2y ago

@Pobiega Thank you, it worked when i changed from ICollection<Group> to just Group but when im using ICollection i get no data at all. I also tried to add a relation in the Group class:

 cs
 public class Group
    {
        public long Id { get; set; }
        public string? GroupName { get; set; }
        public ICollection<Person> Person { get; set; }
    }
}

 cs
 public class Group
    {
        public long Id { get; set; }
        public string? GroupName { get; set; }
        public ICollection<Person> Person { get; set; }
    }
}

Sadly i get a object cycle error, do i understand it incorrectly or do u just need to alter my LinQ select: var a = await _context.Person.Include(x => x.Group).ToListAsync(); Thanks in advance 🙂

Pobiega•2y ago

Right, the two-way nav props wont serialize to JSON nicely, as that will indeed cause a reference loop (group refering to person refering to group ...) you'll need to use a DTO that doesn't have that loop and map to that before serializing

Callum•2y ago

Is this meant to be a group or groups? Also the Json serializer has serializer options

Pobiega•2y ago

I think OOF wants a many-to-many relationship, as a person can belong to many groups, and a group is many people And yes, the serializer can be configured to avoid reference loops, but its in general not a good idea to return raw entities anyways, DTO mapping is best practice.

Callum•2y ago

I've heard the opposite I take it OOF is inferring the many to many relationship through the icollection on both sides Albeit I do wonder if it should be People instead of Person or Persons haha

Pobiega•2y ago

As in, that it's good practice to return raw entities from your API?

Callum•2y ago

I'd really prefer to avoid these kinda discussions as they get heated whenever I bring them up so I'd prefer to just keep my mouth shut 🙂 I do use DTOs, but for me they're in the minority if I feel the consumer doesn't need to care about the domain model and should just achieve a given task I also use an OData API at my work and our project size is quite small and it's a small team

Pobiega•2y ago

Hm, been a long time since I used OData but the general idea for why its a bad idea to expose raw entities is that you... 1: tend to overshare stuff that shouldnt be shared (private IDs etc) 2: run a risk of accidentally introducing breaking api changes unintentionally 3: the whole create/modify/get might have different shapes situation Thats what made me think its M-t-M, and EF handles this just fine (in 7.x atleast, unsure about previous versions)

Callum•2y ago

I understand those points. But you can tell it to ignore/not serialize private properties... Breaking changes regarding API can also be said for DTOs as though you may not try to do it I feel your DTO ends up being indirectly linked to your domain models which therefore encourages a culture of "unchanging" and thus if you wanted to completely scrap your domain model, you'd end up having to jump through some hoops for your DTO processing or changing the DTO by which point you end up back at square one... Which is I have some domain level change I need to effect private ids given how for me I expose invoices to customers I suppose are a bit different as there's always going to be a "Due Date" on an invoice for instance. and they're always going to have companies so perhaps given a different scenario I'd think differently

Pobiega•2y ago

eh, I dislike putting app logic in my serialization to me thats a distinct step with one job and one job only - turn an object into json and vice-versa

Callum•2y ago

well OData you go model.Entity<User>().Ignore(u => u.Password) for instance and now anytime OData encounters that property it won't serialize neither show metadata for their property Again though I use OData so that people can get custom cuts of their own data without me building many endpoints if I did it using DTOs I'd have to tell OData the mapping between the two and do some complicated business logic mapping code such that the IQueryable for the DTO could be translated to an IQueryable for the domain model But I accept serialization business logic isn't pretty and hence I try to avoid "sensitive" information on endpoints that I expose. Ids to me are fine as consumers can delete stuff as well or retrieve stuff by id If they hard code ids and it breaks then that's on them for their shoddy integration

Pobiega•2y ago

Thats fine, but OData is not the norm

Callum•2y ago

Anyways sorry to detract from the conversation I feel I've rambled off on one when we just have a simple many to many endpoint We haven't even asked the OP if they want to use a DTO or not?

Pobiega•2y ago

99% of web APIs do not use OData, so i feel its a bit disingenuous to say " this bad practice is actually a good practice (if you use OData)"

Callum•2y ago

I just don't see it as a black or white answer... apologies...

Pobiega•2y ago

¯\_(ツ)_/¯

Callum•2y ago

I've worked with people whom have venemously sworn by either or and I've seen both play out successfully or badly tbh. But I suppose the same could be said for any "good practice" if you abuse it bad enough

Pobiega•2y ago

Sure it comes with a tradeoff for sure

Callum•2y ago

so I'm reluctant to say one or the other tbh Anyways if we do want to use a DTO... I suggest we return a PeopleDTO that has the properties of I dunno group names so you could do something like...

public class PeopleDTO
{
    public string Name { get; set; }
    public IEnumerable<string> GroupNames { get; set; }
}

var peopleWithGroups = await _dbContext.People.Select(p = > new PeopleDTO
{
    Name = p.Name,
    GroupNames = p.Group.Select(l => l.GroupName).ToArray()
});

return peopleWithGroups;

public class PeopleDTO
{
    public string Name { get; set; }
    public IEnumerable<string> GroupNames { get; set; }
}

var peopleWithGroups = await _dbContext.People.Select(p = > new PeopleDTO
{
    Name = p.Name,
    GroupNames = p.Group.Select(l => l.GroupName).ToArray()
});

return peopleWithGroups;

which I think should serialize fine? This has been written in notepad just now so if there's any mistakes..

Pobiega•2y ago

yep, should serialize fine

big OOFOP•2y ago

Okey now i see the reasoning behind the DTO, thanks @Pobiega! One additional question regarding EF: This generates a field "GroupId" in the persons table

 cs 
public class Person
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public Group Group{ get; set; }
}

 cs 
public class Person
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public Group Group{ get; set; }
}

While this dosent

 cs 
public class Person
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public ICollection<Group> Group{ get; set; }
}

 cs 
public class Person
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public ICollection<Group> Group{ get; set; }
}

I want to use the latter i think (because a group can hold multiple persons) but how will i ever be able to query Person to include group if the Person object dosent hold a reference to the group object/class/table? 🙂 Or do a DTO solve this problem and im maybe a bit slow? 🙂

Pobiega•2y ago

okay lets rewind a bit If Person has a Group property, thats a many to one relationship many people can belong to a group but max one group per person to represent that relationship in a database, you would add a GroupId property on the person thats why it does that but if we say that a person can belong to many groups, you need a many to many relationship, and they are represented as its own table in the database

Pobiega•2y ago

big OOFOP•2y ago

Ah okey, and in that case EF creates for example a PersonGroup table, a join table

Pobiega•2y ago

you can see here the database structure this creates yes exactly and if I run this query:

var emily = await _db.People
    .Include(x => x.Groups)
    .FirstOrDefaultAsync(x => x.Name == "Emily");

var emily = await _db.People
    .Include(x => x.Groups)
    .FirstOrDefaultAsync(x => x.Name == "Emily");

it will return Emily (a person in my database) and all her groups if I leave out the .Include(..) bit, Emily has no groups the SQL would look like this:

[20:32:43 INF] Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "p"."Id", "p"."Name"
FROM "People" AS "p"
WHERE "p"."Name" = 'Emily'
LIMIT 1

[20:32:43 INF] Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "p"."Id", "p"."Name"
FROM "People" AS "p"
WHERE "p"."Name" = 'Emily'
LIMIT 1

no joins on the group but WITH the include, its...

[20:33:22 INF] Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "t"."Id", "t"."Name", "t0"."GroupsId", "t0"."PeopleId", "t0"."Id", "t0"."Name"
FROM (
    SELECT "p"."Id", "p"."Name"
    FROM "People" AS "p"
    WHERE "p"."Name" = 'Emily'
    LIMIT 1
) AS "t"
LEFT JOIN (
    SELECT "g"."GroupsId", "g"."PeopleId", "g0"."Id", "g0"."Name"
    FROM "GroupPerson" AS "g"
    INNER JOIN "Groups" AS "g0" ON "g"."GroupsId" = "g0"."Id"
) AS "t0" ON "t"."Id" = "t0"."PeopleId"
ORDER BY "t"."Id", "t0"."GroupsId", "t0"."PeopleId"

[20:33:22 INF] Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "t"."Id", "t"."Name", "t0"."GroupsId", "t0"."PeopleId", "t0"."Id", "t0"."Name"
FROM (
    SELECT "p"."Id", "p"."Name"
    FROM "People" AS "p"
    WHERE "p"."Name" = 'Emily'
    LIMIT 1
) AS "t"
LEFT JOIN (
    SELECT "g"."GroupsId", "g"."PeopleId", "g0"."Id", "g0"."Name"
    FROM "GroupPerson" AS "g"
    INNER JOIN "Groups" AS "g0" ON "g"."GroupsId" = "g0"."Id"
) AS "t0" ON "t"."Id" = "t0"."PeopleId"
ORDER BY "t"."Id", "t0"."GroupsId", "t0"."PeopleId"

big OOFOP•2y ago

Perfect i think i understand the concept now! So EF knows "under the hood" that when we are querying the ICollection entity in the object it will look to the join table? 🙂

Pobiega•2y ago

only if we use .Include

big OOFOP•2y ago

Ah yes!

Callum•2y ago

I think you can also use .Select?

Pobiega•2y ago

if you want to only get parts of the group, sure and transform the entire result

Callum•2y ago

Well DTOs are best practice so that'd make logical sense to me.

Pobiega•2y ago

Sure, but sometimes you dont know what fields to select at the time you're making the query for example, its a more generic GetPeopleWithGroups style query

big OOFOP•2y ago

I that all my questions got answered! I want you to know you made my day - cant thank you enough @Pobiega! Is there a way to save this help threads ion discord? 🙂

Pobiega•2y ago

Its done automatically: https://www.answeroverflow.com/c/143867839282020352

Answer Overflow

C# Community Page

C# community page on Answer Overflow. See what others in the community are asking questions about

Pobiega•2y ago

or rather, it should? those seem to be from december. hm threads are archived and can be searched up, or you can copy the permalink https://discord.com/channels/143867839282020352/1075837613669367948 but I would appreciate if you would /close the thread when you are happy with the results.

Gaming

Programming

✅ API result