Extending ApplicationUser with a foreign key

ThomasS · June 27, 2018, 6:53am

Hello Radzen team,
i extend the Application User with a new property. All fine.
But now i must extend it with a new property as a foreign key.
My applicationuser must have an assignment to a “firma” record. I didnt find out how to do that with ef migrations.
Can u help me?

My app is in development so i dont need a migration. can i simply add the new property and foreign key in sql management studio and Radzen is aware of the new properties when i go to the security tab and click “create …” and “save”?

Kind Regards
Thomas

korchev · June 27, 2018, 11:45am

Hi,

The ApplicationUser entity is treated in a “special” way because it has a known purpose to Radzen. Also the backing DB tables are created via EF migrations which is why we recommend using them to add additional properties (that map to table columns). Adding a foreign key property is done via the ForeignKey attribute:

    [ForeignKey("CategoryID")]
    public Category Category { get; set; }

After doing this the migration should work fine.

Modifying the DB manually would probably work too. You still need to add the property decorated with the ForeignKey yourself as Radzen will not infer it (it doesn’t infer the user management related tables).

ThomasS · June 27, 2018, 1:07pm

Hi Atanas,
Thanks!

For the ApplicationPrincipalFactory.Properties.cs client side…
is this correct?

namespace Waste.Authentication
 {
     public partial class ApplicationPrincipalFactory
     {
         partial void OnCreatePrincipal(ClaimsPrincipal principal, ApplicationUser user)
         {
             var identity = principal.Identity as ClaimsIdentity;
             if (!string.IsNullOrEmpty(user.Zeichen))
             {
                 identity.AddClaim(new Claim("zeichen", user.Zeichen));
             }
             if (!string.IsNullOrEmpty(user.Firma.FirmaId.ToString()))
             {
                 identity.AddClaim(new Claim("firmaid", user.Firma.FirmaId.ToString()));
             }
         }
     }
 }

ThomasS · June 27, 2018, 1:12pm

The dotnet ef migration call throws an error:
C:\Users\Thomas\Documents\RadZen\waste\server>dotnet ef migrations add Firma -c ApplicationIdentityDbContext
Build failed.

ThomasS · June 27, 2018, 1:21pm

If have corrected it but it doesnt work either:

ApplicationUser.Properties.cs:

namespace Waste.Models
{
public partial class ApplicationUser
{
public String Zeichen { get; set; }

    [ForeignKey ("FirmaId")]
    public Firma Firma { get; set; }
    public int FirmaId { get; set; }
}

}

ApplicationPrincipalFactory.Properties.cs:

 namespace Waste.Authentication
 {
     public partial class ApplicationPrincipalFactory
     {
         partial void OnCreatePrincipal(ClaimsPrincipal principal, ApplicationUser user)
         {
             var identity = principal.Identity as ClaimsIdentity;

             if (!string.IsNullOrEmpty(user.Zeichen))
             {
                 identity.AddClaim(new Claim("zeichen", user.Zeichen));
             }
             if (!string.IsNullOrEmpty(user.Firma.FirmaId.ToString()))
             {
                 identity.AddClaim(new Claim("firmaid", user.Firma.FirmaId.ToString()));
             }
         }
     }
 }

C:\Users\Thomas\Documents\RadZen\waste\server>dotnet ef migrations add FirmaId -c ApplicationIdentityDbContext
Build failed.

C:\Users\Thomas\Documents\RadZen\waste\server>dotnet ef migrations add Firma -c ApplicationIdentityDbContext
Build failed.

korchev · June 27, 2018, 2:54pm

How does the Firma class look like? Does it work if you manually add the foreign key constraint in the database?

ThomasS · June 28, 2018, 10:56pm

I have added the FK to AspNetUsers table manually. Here is the server\models\wastedata\firma class:
using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Waste.Models.Wastedata
{
[Table(“Firma”, Schema = “dbo”)]
public class Firma
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int FirmaId
{
get;
set;
}
public string Name
{
get;
set;
}
public string Name2
{
get;
set;
}
public string Strasse
{
get;
set;
}
public string Plz
{
get;
set;
}
public string Ort
{
get;
set;
}
public string Telefon
{
get;
set;
}
public string Fax
{
get;
set;
}
public string EMail
{
get;
set;
}
public string Internet
{
get;
set;
}
public string Steuernummer
{
get;
set;
}
public string Geschaeftsfuehrer
{
get;
set;
}
public string EUSteuernummer
{
get;
set;
}
public string Bankname
{
get;
set;
}
public string IBAN
{
get;
set;
}
public string BIC
{
get;
set;
}
public int MandantId
{
get;
set;
}

[ForeignKey("MandantId")]
public Mandant Mandant { get; set; }
public int? LeistungsscheinNrvon
{
  get;
  set;
}
public int? WiegebelegNrvon
{
  get;
  set;
}
public int? GutschriftsNrvon
{
  get;
  set;
}
public int? RechnungNrvon
{
  get;
  set;
}
public int? PartnerNrvon
{
  get;
  set;
}

}
}

No Collection for AspNetUsers. Clear because Radzen didnt “know” the AspNet* tables.

I think i missunderstood what you want me to do…

Thomas

ThomasS · June 28, 2018, 11:03pm

When i add

namespace Waste.Models
{
public partial class ApplicationUser
{
public String Zeichen { get; set; }

    [ForeignKey ("FirmaId")]
    public Firma Firma { get; set; }
    public int FirmaId { get; set; }
}

}

This error occurs when pressing run in the designer:
dotnet: Unhandled Exception:
dotnet: System.InvalidOperationException: Entity type ‘RechLeistPartnerZuordnung’ has composite primary key defined with data annotations. To set composite primary key, use fluent API.
at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.KeyAttributeConvention.Apply(InternalModelBuilder modelBuilder)
at Microsoft.EntityFrameworkCore.Metadata.Conventions.Internal.ConventionDispatcher.ImmediateConventionScope.OnModelBuilt(InternalModelBuilder modelBuilder)
at Microsoft.EntityFrameworkCore.Infrastructure.ModelSource.CreateModel(DbContext context, IConventionSetBuilder conventionSetBuilder, IModelValidator validator)
at System.Lazy1.ViaFactory(LazyThreadSafetyMode mode) at System.Lazy1.ExecutionAndPublication(LazyHelper executionAndPublication, Boolean useDefaultConstructor)
at System.Lazy1.CreateValue() at Microsoft.EntityFrameworkCore.Internal.DbContextServices.CreateModel() at Microsoft.EntityFrameworkCore.Internal.DbContextServices.get_Model() at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProviderEngineScope scope) at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitConstructor(ConstructorCallSite constructorCallSite, ServiceProviderEngineScope scope) at Microsoft.Extensions.DependencyInjection.ServiceLookup.CallSiteRuntimeResolver.VisitScoped(ScopedCallSite scopedCallSite, ServiceProviderEngineScope scope) at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService(IServiceProvider provider, Type serviceType) at Microsoft.Extensions.DependencyInjection.ServiceProviderServiceExtensions.GetRequiredService[T](IServiceProvider provider) at Microsoft.EntityFrameworkCore.DbContext.get_DbContextDependencies() at Microsoft.EntityFrameworkCore.DbContext.get_InternalServiceProvider() at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.GetRelationalService[TService](IInfrastructure1 databaseFacade)
at Microsoft.EntityFrameworkCore.RelationalDatabaseFacadeExtensions.Migrate(DatabaseFacade databaseFacade)
at Waste.Startup.Configure(IApplicationBuilder app, IHostingEnvironment env, ILoggerFactory loggerFactory, ApplicationIdentityDbContext identityDbContext) in C:\Users\Thomas\Documents\RadZen\waste\server\Startup.cs:line 223
— End of stack trace from previous location where exception was thrown —
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at Microsoft.AspNetCore.Hosting.ConventionBasedStartup.Configure(IApplicationBuilder app)
at Microsoft.AspNetCore.Hosting.Internal.AutoRequestServicesStartupFilter.<>c__DisplayClass0_0.b__0(IApplicationBuilder builder)
at Microsoft.AspNetCore.Hosting.Internal.WebHost.BuildApplication()
at Microsoft.AspNetCore.Hosting.WebHostBuilder.Build()
at Waste.Program.BuildWebHost(String[] args) in C:\Users\Thomas\Documents\RadZen\waste\server\Program.cs:line 16
at Waste.Program.Main(String[] args) in C:\Users\Thomas\Documents\RadZen\waste\server\Program.cs:line 10

Table RechLeistPartnerZuordnung has nothing to do with the AspNetUsers or Firma Tables.

Thomas

ThomasS · June 28, 2018, 11:03pm

After adding the FK table AspNetUsers Create Script looks like this now:
USE [recentdata]
GO

/****** Object: Table [dbo].[AspNetUsers] Script Date: 29.06.2018 01:03:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[AspNetUsers](
[Id] nvarchar NOT NULL,
[AccessFailedCount] [int] NOT NULL,
[ConcurrencyStamp] nvarchar NULL,
[Email] nvarchar NULL,
[EmailConfirmed] [bit] NOT NULL,
[LockoutEnabled] [bit] NOT NULL,
[LockoutEnd] datetimeoffset NULL,
[NormalizedEmail] nvarchar NULL,
[NormalizedUserName] nvarchar NULL,
[PasswordHash] nvarchar NULL,
[PhoneNumber] nvarchar NULL,
[PhoneNumberConfirmed] [bit] NOT NULL,
[SecurityStamp] nvarchar NULL,
[TwoFactorEnabled] [bit] NOT NULL,
[UserName] nvarchar NULL,
[Zeichen] nvarchar NULL,
[FirmaId] [int] NULL,
CONSTRAINT [PK_AspNetUsers] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[AspNetUsers] WITH CHECK ADD CONSTRAINT [FK_AspNetUsers_Firma] FOREIGN KEY([FirmaId])
REFERENCES [dbo].[Firma] ([FirmaId])
GO

ALTER TABLE [dbo].[AspNetUsers] CHECK CONSTRAINT [FK_AspNetUsers_Firma]
GO

korchev · June 29, 2018, 7:08am

Hi Thomas,

Indeed the composite key error is strange. Does it happen if you remove the changes to the ApplicationUser class?

Anyway we investigated the issue and it seems impossible to add a foreign key property (e.g. Firma) to ApplicationUser because of this Entity Framework issue. At the moment the AspNet* tables are served by a different Entity Framework context. The reason for this is that those tables are created at runtime via migration. If we used one context for all tables the migration would have dropped the existing DB tables which we obviously don’t want.

We will have to research this matter further and will update this thread with our findings (and probably a demo).

ThomasS · June 29, 2018, 7:24am

The composite key error only occurs when i add

namespace Waste.Models
{
public partial class ApplicationUser
{
public String Zeichen { get; set; }

[ForeignKey ("FirmaId")]
public Firma Firma { get; set; }
public int FirmaId { get; set; }

}

the ForeignKey part to the ApplicationUser class.
When i rmove the ForeignKey the error vanishes

Ok. Its no problem to not have the ForeignKey for now. I can implement it later when there is a workaround.

Thank you Atanas!

Thomas

korchev · June 29, 2018, 3:47pm

Hi @ThomasS,

We’ve made some progress however a new version of Radzen should be released in order for you to try. We will release early next week and update this thread.

Best regards,
Atanas

korchev · July 2, 2018, 12:47pm

Hi @ThomasS,

We just released the new Radzen version which should allow foreign key properties of the ApplicationUser.

I will describe the process required to do that. Will use the Northwind Category table as the foreign key for simplicity. You should use your Firma/FirmaId class.

Follow these steps:

Install Radzen 2.0.5 (you should get it from autoupdate).
Press the run button to generate the updated server-side code. We have made some classes partial to allow more extensibility points. Those are the model classes (e.g. Category, Product etc) and the ApplicationIdentityDbContext (used for the security features of a Radzen application).
Create a new file which will extend the ApplicationUser class (you probably already have one). Add two properties - CategoryID and Category (FirmaId, Firma in your case). You should not set the [ForeignKey] attribute:

    public partial class ApplicationUser
    {
        public int CategoryID { get; set;}

        public Category Category { get; set;}
    }

Create a new file to extend the Category class (Firma in your case) e.g. Category.properties.cs. Add a new property of type ICollection<ApplicationUser> and call it ApplicationUsers. This is needed to tell Entity Framework that there is a foreign key relationship.

  public partial class Category
  {
    public ICollection<ApplicationUser> ApplicationUsers {get; set;}
  }

Create a new file called ApplicationIdentityDbContext.Properties.cs. We will use it to describe the relationship between the ApplicationUser and Category (Firma) classes:

   public partial class ApplicationIdentityDbContext
   {
        partial void OnModelBuilding(ModelBuilder builder)
        {
            builder.Entity<ApplicationUser>()
              .HasOne(i => i.Category)
              .WithMany(i => i.ApplicationUsers)
              .HasForeignKey(i => i.CategoryID)
              .HasPrincipalKey(i => i.CategoryID); 
        }
   }

Finally add the Category as a claim in ApplicationPrincipleFactory:

    public partial class ApplicationPrincipalFactory
    {
        partial void OnCreatePrincipal(ClaimsPrincipal principal, ApplicationUser user)
        {
            var identity = principal.Identity as ClaimsIdentity;

            // Populate the Category property from the DB
            this.identityContext.Entry(user).Reference(u => u.Category).Load();

            if (user.Category != null)
            {
                // Add a property to be available client side as ${security.user.profile.Category}
                identity.AddClaim(new Claim("Category", user.Category.CategoryName));
            }
        }
   }

To support editing that property from say the Edit ApplicationUser page:

Open the Edit ApplicationUser page in Radzen.
In Page Load invoke the getCategories (getFirmas) method and create a new property with the result.

Screen Shot 2018-07-02 at 15.40.12.png1788×1072 86.9 KB
Add a new field to the form. Set CategoryID (FirmaId) as Property - you have to type it in the combobox and click the 'Add CategoryID ...' item (it is a custom property). Set Type to lookup, Data to getCategoryResult, TextProperty to CategoryName and ValueProperty to CategoryID.
This creates a new form field which will list all Categories (Firma-s) as items.

Screen Shot 2018-07-02 at 15.40.25.png700×1206 46.9 KB

One more thing. Since the custom property is stored as a claim it will be updated at the client-side after the user logs out and logs in again.

Best regards,
Atanas

ThomasS · August 14, 2018, 8:39pm

Hello Radzen team,
i followed your instructions:

korchev:

Install Radzen 2.0.5 (you should get it from autoupdate).

Press the run button to generate the updated server-side code. We have made some classes partial to allow more extensibility points. Those are the model classes (e.g. Category, Product etc) and the ApplicationIdentityDbContext (used for the security features of a Radzen application).

Create a new file which will extend the ApplicationUser class (you probably already have one). Add two properties - CategoryID and Category (FirmaId, Firma in your case). You should not set the [ForeignKey] attribute:
    public partial class ApplicationUser
    {
        public int CategoryID { get; set;}

        public Category Category { get; set;}
    }
public partial class ApplicationUser
{
    public int FirmaID { get; set;}

    public Waste.Models.Wastedata.Firma Firma { get; set;}
}
Create a new file to extend the Category class (Firma in your case) e.g. Category.properties.cs. Add a new property of type ICollection<ApplicationUser> and call it ApplicationUsers. This is needed to tell Entity Framework that there is a foreign key relationship.
  public partial class Category
  {
    public ICollection<ApplicationUser> ApplicationUsers {get; set;}
  }
public partial class Firma
{
    public ICollection<ApplicationUser> ApplicationUsers { get; set; }
}
Create a new file called ApplicationIdentityDbContext.Properties.cs. We will use it to describe the relationship between the ApplicationUser and Category (Firma) classes:
   public partial class ApplicationIdentityDbContext
   {
        partial void OnModelBuilding(ModelBuilder builder)
        {
            builder.Entity<ApplicationUser>()
              .HasOne(i => i.Category)
              .WithMany(i => i.ApplicationUsers)
              .HasForeignKey(i => i.CategoryID)
              .HasPrincipalKey(i => i.CategoryID); 
        }
   }
public partial class ApplicationIdentityDbContext
{
    partial void OnModelBuilding(ModelBuilder builder)
    {
        builder.Entity<ApplicationUser>()
          .HasOne(i => i.Firma)
          .WithMany(i => i.ApplicationUsers)
          .HasForeignKey(i => i.FirmaId)
          .HasPrincipalKey(i => i.FirmaId);
    }
}
Finally add the Category as a claim in ApplicationPrincipleFactory:
    public partial class ApplicationPrincipalFactory
    {
        partial void OnCreatePrincipal(ClaimsPrincipal principal, ApplicationUser user)
        {
            var identity = principal.Identity as ClaimsIdentity;

            // Populate the Category property from the DB
            this.identityContext.Entry(user).Reference(u => u.Category).Load();

            if (user.Category != null)
            {
                // Add a property to be available client side as ${security.user.profile.Category}
                identity.AddClaim(new Claim("Category", user.Category.CategoryName));
            }
        }
   }
public partial class ApplicationPrincipalFactory
{
    partial void OnCreatePrincipal(ClaimsPrincipal principal, ApplicationUser user)
    {
        var identity = principal.Identity as ClaimsIdentity;

        // Populate the Firma property from the DB
        this.identityContext.Entry(user).Reference(u => u.Firma).Load();

        if (user.Firma != null)
        {
            // Add a property to be available client side as ${security.user.profile.Category}
            identity.AddClaim(new Claim("Firma", user.Firma.Name));
        }
    }
}
To support editing that property from say the Edit ApplicationUser page:

Open the Edit ApplicationUser page in Radzen.

In Page Load invoke the getCategories (getFirmas) method and create a new property with the result.

Screen Shot 2018-07-02 at 15.40.12.png1788x1072 86.9 KB

image887×467 19.7 KB

Add a new field to the form. Set CategoryID (FirmaId) as Property - you have to type it in the combobox and click the ‘Add CategoryID …’ item (it is a custom property). Set Type to lookup, Data to getCategoryResult, TextProperty to CategoryName and ValueProperty to CategoryID.
This creates a new form field which will list all Categories (Firma-s) as items.

Screen Shot 2018-07-02 at 15.40.25.png700x1206 46.9 KB

image349×635 15.2 KB

One more thing. Since the custom property is stored as a claim it will be updated at the client-side after the user logs out and logs in again.

Best regards,
Atanas

And this happens: Video

I dont know wht the problem is... Please help

Kind Regards
Thomas

ThomasS · August 14, 2018, 8:40pm

I’ve done this on the add page…

korchev · August 14, 2018, 9:13pm

Hi Thomas,

HTTP Error 500 means that there is a server-side error. You can check Radzen’s Output window to see what the exception was and act accordingly.

Best regards,
Atanas

ThomasS · August 15, 2018, 10:33pm

Hi Atanas,
it was an error because i had an old partial class whit another field firmaid. so it was double. i corrected it.
But now i get an ForeignKey violation error.

  INSERT INTO [AspNetUsers] ([Id], [AccessFailedCount], [ConcurrencyStamp], [Email], [EmailConfirmed], [FirmaId], [LockoutEnabled], [LockoutEnd], [NormalizedEmail], [NormalizedUserName], [PasswordHash], [PhoneNumber], [PhoneNumberConfirmed], [SecurityStamp], [TwoFactorEnabled], [UserName], [Zeichen])
  VALUES (p0, p1, p2, p3, p4, p5, p6, p7, p8, p9, p10, p11, p12, p13, p14, p15, p16);

System.Data.SqlClient.SqlException (0x80131904): Die INSERT-Anweisung steht in Konflikt mit der FOREIGN KEY-Einschränkung "FK_AspNetUsers_Firma". Der Konflikt trat in der recentdata-Datenbank, Tabelle "dbo.Firma", column 'FirmaId' auf.
Die Anweisung wurde beendet.

I cant find out whats going wrong....

Kind Regards
Thomas

korchev · August 16, 2018, 6:35am

Hi Thomas,

Not sure what the exception message says in German but it could be that the value submitted for FirmaId is either missing or not the right type. You can check with a debugger what the actual value is. Also check in your browser’s developer tools what is being sent in the Request section.

ThomasS · September 6, 2018, 5:41am

Hi Atanas,
i cant find out what the problem is. The value FirmaId is send to the Controler but the user object firmaId and Firma is not set.
Here is the Video

Please help
Regards
Thomas

korchev · September 6, 2018, 6:14am

Hi @ThomasS,

In the video I saw that the property being submitted as JSON is called FirmaID whereas the C# property is called FirmaId. The casing is important and must be the same.