Mapping object with list of objects 1 to n relationship

[kaeedo]

Hello. I've been trying out SqlHydra and like it so far. But one things that I always look for in an ORM is the ability to select a list (or single) entity that has a 1 to N relationship, and have the ORM hydrate the list as well. Basically a Company has a list of Employees.

With a simple select * from company c join employee e on e.company_id = c.id I would want to map it to something like { Company.Id = query.Id; Name = query.Name; Employees = ??? } Is this possible or do I need to manually construct the employee list out of the resulting tuple when using a selectTask HydraReader ctx { my query }

[JordanMarr]

While I agree that can be a nice feature to have, I purposefully avoided adding any "ORM" style features to SqlHydra. I think of it as more of a strongly typed query library rather than an ORM. I see the generated types as strongly typed database access.

A common complaint of ORMs like EF is that they can become more complicated to reason about than writing the SQL itself. With SqlHydra, you would manually query the company/companies, then manually map them to your domain or DTO if you really need to embed them together.

[JordanMarr]

That's not to say that you couldn't return the generated types directly to your application if that what you would prefer. It's just not my preferred use case.

However, I was considering adding a toml config option that would allow you to make the generated record properties mutable. For some CRUD use cases, I think it could be useful to bind the generated types directly to be mutated by controls in the application.

[kaeedo]

Thanks for the response. At least now I know i'm not missing something :sweat_smile: Perhaps it makes sense to add an example that showcases this usage?

[JordanMarr]

I generally would do something like this:

Data

let listUsersForCompany companyId =
    selectTask' (Create openContext) {
        for u in dbo.Users do
        orderBy u.Username
        mapList (toEntity u) // Map users to DTO or domain entity
    }    

let getCompany companyId = 
// ... 

API/Application

let getCompanyFormData companyId = 
    task { 
        let! company = getCompany companyId
        let! users = listUsersForCompany companyId
        return
            {
                Company = company
                UsersInCompany = users
            } : GetCompanyFormDataResponse
    } 

Of course, you could factor it out in different ways, depending on how you want to structure your application.