Skip to content
Advertisement

How to make lookup field work in this case without defining the foreign key in the database?

The code is as follows (database first):

CREATE TABLE [dbo].[Users] 
(
    [Id] [uniqueidentifier] default newid() NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [Description] [varchar](50) NOT NULL,
    [UserTypeId] [int] NULL,
);

CREATE TABLE [dbo].[UserTypes] 
(
    [Id] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL,
    [Description] [varchar](50) NOT NULL
);

INSERT INTO [dbo].[UserTypes] ([Id], [Name], [Description]) 
VALUES (1, 'Name1', 'Descr1');
INSERT INTO [dbo].[UserTypes] ([Id], [Name], [Description]) 
VALUES (2, 'Name2', 'Descr2');

INSERT INTO [dbo].[Users] ([Id], [Name], [Description], [UserTypeId]) 
VALUES (newid(), 'User1', 'Descr1', 1);
INSERT INTO [dbo].[Users] ([Id], [Name], [Description], [UserTypeId]) 
VALUES (newid(), 'User2', 'Descr2', 2);

C# code:

[Table("Users")]
public class User
{
    [Key]
    public Guid Id { get; set; }

    public string Name { get; set; }

    [ForeignKey(nameof(UserType))]
    public int UserTypeId { get; set; }

    public UserType UserType { get; set; }
}

[Table("UserTypes")]
public class UserType
{
    [Key]
    public int Id { get; set; }

    public string Name { get; set; }
    public string Description { get; set; }
}

UserTypeId is mapped, UserType is not. Any ideas how to make it work?

Advertisement

Answer

If we consider this as one to many relationship. Then Mapping using dataanotation will be like this.

public partial class User
{
    [Key]
    public System.Guid Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    public int UserTypeId { get; set; }
    public UserType UserType { get; set; }
}

public partial class UserType
{
    [Key]
    public int Id { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }
    [ForeignKey("UserTypeId")]
    public System.Collections.Generic.ICollection<User> Users { get; set; }
}

And to query

var data = await _dbcontext.Users.Include(x => x.UserType).ToListAsync();
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement