Null data returned using a 1:1 model

Question

I have two SQL tables, User and UserType joined with UserType as a foreign key, with their respective models in ASP. To my understanding, this should be a 1:1 relationship (correct me if I'm wrong). One unique user, set as a type of user (being admin, super admin, user etc).

When I try and retrieve a list of users, it returns a null on the property UserType.

I used Google to get this far, but I'm struggling to get this particular issue fixed.

At one point I got an error stating: "Unable to determine the principal end of an association". To get around that, I included a Required annotation (didn't work) and a ForeignKey annotation (didn't work either) in both models, both simultaneously and separately.

This is what I have so far.

[Table("Users", Schema = "dbo")]
public class Users
{
    [Key, ForeignKey("UserType")]
    public Guid Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string ContactNumber { get; set; }
    public UserType UserType { get; set; }
    public string IsActive { get; set; }
}

[Table("UserType", Schema = "dbo")]
public class UserType
{
    [Key]
    public Guid Id { get; set; }
    public string Type { get; set; }
    public string Description { get; set; }
    public string IsActive { get; set; }
    public Users Users { get; set; }
}

I'm using the below LINQ method to retrieve the data:

public PagedTables<Users> GetAllUsers(Pagination pagination)
{
    using (var db = new DbContext())
    {
        var user = new PagedTables<Users>()
        {
            Data = db.Users.OrderBy(U => U.Id).Skip(pagination.Page).Take(pagination.Limit).ToList(),
            Count = db.Users.Count()
        };

        return user;
    }
}

A break point on the users var shows that the property UserType returns null. I would expect the assigned user type to be joined onto the user.

Any help would be appreciated. Thanks

"have two SQL tables, User and UserType joined with UserType as a foreign key" - a foreign key is usually one to many — onemorecupofcoffee, Jun 03 '19 at 06:18
No reason why a 1:M can’t be 1:1 though, and if the M end has a primary key on the M column it’ll be 1:1, which is nothing to do with the foreign key, so I’m not really seeing the usefulness of the comment. It might be more helpful to observe that two tables that are related 1:1 are generally a candidate for being one table — Caius Jard, Jun 03 '19 at 06:27
Don’t know what ORM this is(EF? EFCore?) but tell us what efforts you’ve made to resolve so far by googling the error (eg see https://stackoverflow.com/questions/6531671/what-does-principal-end-of-an-association-means-in-11-relationship-in-entity-fr) and also do you need to explicitly load the related data? Eg https://ef.readthedocs.io/en/staging/querying/related-data.html — Caius Jard, Jun 03 '19 at 06:29
Not entirely certain. From the documentation you provided, Lazy loading isn't supported in EF, which is what I'm using. So, I believe I'm using eager loading. — ITGuy, Jun 03 '19 at 06:34
@CaiusJard - OP stated - "this should be a 1:1 relationship (correct me if I'm wrong)" - just correcting that statement — onemorecupofcoffee, Jun 03 '19 at 06:38
No issue with the comment. I'm inclined to agree on some level, but I need the userType table to have its own master page, so that types can be added, removed, modified etc. So its not 1:M, but it needs its own table hence the fk. — ITGuy, Jun 03 '19 at 06:44
Still not sure what you're trying to map, to be honest. To me a "usertype" is a limited classification, not an infinite variety. In a workplace there may a manager type, teamlead type and worker type, and all 100 employees fit into one of these. The usertype table doesn't have a userid and 100 records (it has 3 records), the user table has a usertypeid column with 3 distinct values — Caius Jard, Jun 03 '19 at 07:19
I'm not following. Yes I only specified 3 types of users but I'm not sure how that indicates whether its limited or not. However, UserType shouldn't have a userId, its not necessary. The relationship between User and UserType is specified in User via the UserTypeId foreign key. This will indicate what 'type' of user the user is. Whether there are 3 types or 100. Regardless, the relationship is what it is. I would just like to find out why EF isn't able to get data from it when I constructed the table and model to use a 1:1 relationship. Thanks — ITGuy, Jun 03 '19 at 07:43

score 0 · Answer 1 · answered Jun 04 '19 at 12:43

My EF background is database-first but if you are eager loading (i.e. not lazy loading) then are you missing an Include to tell LINQ to go and get the UserType? Something like;

Data = db.Users.OrderBy(U => U.Id).Skip(pagination.Page).Take(pagination.Limit).Include(U => U.UserType).ToList(),

Null data returned using a 1:1 model

1 Answers1