CODEWITHSUNDEEP
c#sqlentity-framework.net-core
user626528
user626528

Reputation: 14418

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?

Upvotes: 0

Views: 58

Answers (1)

cdev
cdev

Reputation: 5371

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();

Upvotes: 1

Related Questions