Entity framework migration C# GUID foreign keys

Question

0.00/5 (No votes)

See more:

As I am newly learning EF and wanted to migrate the existing "I write my own queries, that's way cooler" to something more stable and better to work with.

Now I came across the question on how I can actually use the benefits of the relations with EF that allow me to load a whole object and its relations in one go, instead of having to write a huge query with JOINS.

But it seems like my current scheme with guids is not compatible with the EF way of doing things?

What my question is about:
I received this error:

Quote:
System.InvalidOperationException: The relationship from 'ChangeManagementTemplate.BaseInformationOfTemplate' to 'TemplateInformationBase' with foreign key properties {'IdTemplateInformationBase' : int?} cannot target the primary key {'IdTemplateInformationBase' : Guid} because it is not compatible. Configure a principal key or a set of foreign key properties with compatible types for this relationship.

I have:

C#

[Table("ChangeManagementTemplate")]
    public class ChangeManagementTemplate
    {
        #region "Properties"

        [Key]
        [Column("IdChangeManagementTemplate", 
                 TypeName = nameof(SqlDbType.UniqueIdentifier))]
        public Guid IdChangeManagementTemplate { get; set; }

        [Column("AllowedChangeManagementUsage", 
                 TypeName = nameof(SqlDbType.Int))]
        public ChangeManagementState AllowedChangeManagementUsage 
        { get; set; }

        #endregion

        #region "Sub Data"

        [ForeignKey("IdTemplateInformationBase")]
        public TemplateInformationBase BaseInformationOfTemplate 
        { get; set; }

        #endregion
    }

The Base Model is:

C#

[Table("TemplateInformationBase")]
    public class TemplateInformationBase
    {
        #region "Properties"

        [Key]
        [Column("IdTemplateInformationBase", 
                 TypeName = nameof(SqlDbType.UniqueIdentifier))]
        public Guid IdTemplateInformationBase { get; set; }

        [Column("TemplateName", TypeName = nameof(SqlDbType.NVarChar))]
        public string TemplateName { get; set; }

        [Column("IdRoleForResponsible", 
                 TypeName = nameof(SqlDbType.UniqueIdentifier))]
        public Guid IdRoleForResponsible { get; set; }

        [Column("StateOfTemplate", TypeName = nameof(SqlDbType.Int))]
        public TemplateState StateOfTemplate { get; set; }

        [Column("TypeOfProjectTemplateIsFor", 
                 TypeName = nameof(SqlDbType.Int))]
        public ProjectType TypeOfProjectTemplateIsFor { get; set; }

        [Column("DescriptionOfTemplate", 
                 TypeName = nameof(SqlDbType.NVarChar))]
        public string DescriptionOfTemplate { get; set; }

        [Column("IdCurrentVersionOfTemplate", 
                 TypeName = nameof(SqlDbType.UniqueIdentifier))]
        public Guid IdCurrentVersionOfTemplate { get; set; }

        [Column("TypeOfTemplate", TypeName = nameof(SqlDbType.Int))]
        public TemplateType TypeOfTemplate { get; set; }

        [Column("CreationDateOfTemplate", 
                 TypeName = nameof(SqlDbType.Date))]
        public DateTime CreationDateOfTemplate { get; set; }

        [Column("LastChangeOnTemplate", TypeName = nameof(SqlDbType.Date))]
        public DateTime LastChangeOnTemplate { get; set; }

        #endregion

        #region "SubData"

        [ForeignKey("idTemplateOfTemplateToLocation")]
        public List<TemplateToLocation> CompaniesAllowedToUseTemplate 
        { get; set; } = new List<TemplateToLocation>();

        [JsonIgnore]
        [NotMapped]
        public List<TemplateHistory> 
        HistoryOfTemplate { get; set; } = new List<TemplateHistory>();

        #endregion
    }

So now my question is, how do I solve this problem? I saw you can define some sort of PrincipalKeys, and I really haven't gotten through understanding that, but it seems to require me adding the "ChangeManagementTemplate" class and id to the "TemplateInformationBase", which in fact would be kinda against the idea of TemplateInformationBase being the default data for any template, that gets consumed by any other TemplateType defined to specific types of Templates.

This specific case is a one-to-one relation, but the base has some additional one-to-many relations I would also like to add as soon as this first step is working.

I have the EFCore 7.0.10 and the corresponding NuGetPackage for SqlServer installed.
Please note that I want to stay DB first and not use features like "migrate" from Data Models.

If anything else is unclear, please let me know.

Many thanks in advance!

Edit with DB Tables:

C#

"ChangeManagementTemplate":
"IdChangeManagementTemplate" (PK, uniqueidentigier, not null)
"AllowedChangeManagementUsage" (int, not null)

"TemplateInformationBase":
"IdTemplateInformationBase" (PK, uniqueidentifier, not null)
"TemplateName" (nvarchar(max), not null)
"IdRoleForResponsible" (uniqueidentifier, not null)
...
"CreationDateOfTemplate" (date, not null)
"LastChangeOnTemplate" (date, not null)

So all properties of the classes that are columns in the database tables should match.

What I have tried:

I tried understanding the HasPrincipalKey thing, but the examples of Microsoft led me to the thought that I may go against my idea of having one BaseTemplate class that contains default data and having specific template classes that simply use the base as a property to be complete.

Edit:
I tried using the "IdChangeManagementTemplate" as ForeignKey since it should match the "IdTemplateInformationBase".

C#

#region "Properties"

        [Key]
        [Column("IdChangeManagementTemplate", 
              TypeName = nameof(SqlDbType.UniqueIdentifier))]
        public Guid IdChangeManagementTemplate { get; set; }

        [Column("AllowedChangeManagementUsage", 
                 TypeName = nameof(SqlDbType.Int))]
        public ChangeManagementState AllowedChangeManagementUsage 
               { get; set; }

        #endregion

        #region "Sub Data"

        [ForeignKey("IdChangeManagementTemplate")]
        public TemplateInformationBase BaseInformationOfTemplate 
               { get; set; }

        #endregion

Error was gone but the "BaseInformationOfTemplate" Property was null...

Then I tried giving the MS explanation and removed the "Column" Identifier:

C#

[ForeignKey("IdChangeManagementTemplate")]
        public TemplateInformationBase BaseInformationOfTemplate { get; set; }

and used this:

C#

modelBuilder.Entity<ChangeManagementTemplate>()
                .HasOne(emt => emt.BaseInformationOfTemplate)
                .WithOne()
                .HasForeignKey<TemplateInformationBase>(tib => tib.IdTemplateInformationBase);

Same result, no error but also no "sub" data :(

The query on the Database though shows my test data:

SQL

SELECT * FROM ChangeManagementTemplate
  JOIN TemplateInformationBase ON IdChangeManagementTemplate = IdTemplateInformationBase

Shows one row with all the data:

Quote:
TemplateName IdRoleForResponsible StateOfTemplate TypeOfProjectTemplateIsFor DescriptionOfTemplate IdCurrentVersionOfTemplate TypeOfTemplate CreationDateOfTemplate LastChangeOnTemplate

IdChangeManagementTemplate : B0050EE9-74E7-4EE0-9560-C359F43E28C5
AllowedChangeManagementUsage: 0
IdTemplateInformationBase : B0050EE9-74E7-4EE0-9560-C359F43E28C5

ECR_Process_Change_Equipment 3BEC51ED-E84E-4626-9262-A8C95CA68C04 2 2 Dieser Änderungstyp ist jegliche Änderungen an der Linie/ am Equipment vorgesehen. 0BE72C70-6783-4A01-8D69-7F3F337C7AE9 1 2020-10-20 2023-08-08

Either my idea of how things can be done with EF are completely wrong or I made a mistake somewhere, but I really have no clue where.

Edit2:
A futher interesting behaviour is that if I load the "TemplateInformationBase" List first and then the "ChangeManagementTemplate" List, the Property of BaseInformationOfTemplate is filled with the data of the table. If I don't load the TemplateInformationBase, it stays null.
I thought that would not be a requirement when using Entity Framework?

Posted 21-Aug-23 0:17am

HobbyProggy

Updated 14-Sep-23 6:19am

Deeksha Shenoy

v5

Add a Solution

3 solutions

Solution 1

Read the error message carefully:

Quote:
he relationship from 'ChangeManagementTemplate.BaseInformationOfTemplate' to 'TemplateInformationBase' with foreign key properties {'IdTemplateInformationBase' : int?} cannot target the primary key {'IdTemplateInformationBase' : Guid} because it is not compatible.

You are trying to link BaseInformationOfTemplate with IdTemplateInformationBase but they aren;t the same type:

[ForeignKey("IdTemplateInformationBase")]
public TemplateInformationBase BaseInformationOfTemplate { get; set; }

BaseInformationOfTemplate looks to be an enum but IdTemplateInformationBase is a Guid:

[Column("IdTemplateInformationBase", TypeName = nameof(SqlDbType.UniqueIdentifier))]
public Guid IdTemplateInformationBase { get; set; }

I think you need to check exactly what you want to establish a link between before you go any further!

Posted 21-Aug-23 0:56am

OriginalGriff

Comments

HobbyProggy 21-Aug-23 7:17am

Hi OG,

it's looks for EF like an enum or for you or both?
TemplateInformationBase is a class, like shown above that contains Data that is similar across all types of templates I have (5 currently). The specific class for this template, in this case ChangeManagementTemplate contains a property that should contain the object of TemplateInformationBase read from the database.

I should also note that both primary keys are the same. So by rule of design the IdChangeManagementTemplate equals IdTemplateInformationBase. Since both together form the complete set of Data.

Edit: sorry I am currently not able to highligh things in red...

OriginalGriff 21-Aug-23 8:21am

Check the DB you created has the same types.

HobbyProggy 22-Aug-23 7:32am

Yep, i am stuck. I really don't get it. For my eyes everything should match as expected, nonetheless it seems like it doesn't...

HobbyProggy 21-Aug-23 9:57am

Everywhere where I have "IDs" for objects I use Guid inside the DB table, I am sure that's also the case here and I added one entry for each so I could get started with testing.

But I'll double check that tomorrow and post the update to the question. Thanks so far :)

Solution 4

So it worked with using

C#

modelBuilder.Entity<ChangeManagementTemplate>()
                .HasOne(emt => emt.BaseInformationOfTemplate)
                .WithOne()
                .HasForeignKey<TemplateInformationBase>(tib => tib.IdTemplateInformationBase);

and then using the "Include" on loading, like Richard pointed out, lazy loading was preventing me from see the result immediately after loading from the DB.

C#

_dbContext.ChangeManagementTemplatesOfSystem.Include(cmt => cmt.BaseInformationOfTemplate).ToList();

For the sake of completeness i also tried out again using only the "ForeignKey" Annotation.

C#

[Table("ChangeManagementTemplate")]
  public class ChangeManagementTemplate
  {
    [Key]
    [Column("IdChangeManagementTemplate", TypeName = "UniqueIdentifier")]
    public Guid IdChangeManagementTemplate { get; set; }

    [Column("AllowedChangeManagementUsage", TypeName = "Int")]
    public ChangeManagementState AllowedChangeManagementUsage { get; set; }

    [ForeignKey("IdChangeManagementTemplate")]
    public TemplateInformationBase BaseInformationOfTemplate { get; set; }
  }

This works as well and loads the corresponding data from the tables, i personally will go with this, since it feels a little more natural than working with the modelBuilder.
Though it's good to know both ways.

Another thing that will come into the final solution is the

C#

[ForeignKey("IdChangeManagementTemplate")]
    public virtual TemplateInformationBase BaseInformationOfTemplate { get; set; }

since i read that this should be made for all "Navigation Properties", also because of the lazy loading.

Posted 22-Aug-23 3:00am

HobbyProggy

Comments

Dave Kreskowiak 22-Aug-23 10:50am

The more you work with EF, the easier it'll get.

You'll also find out that, right now, you're typing twice the code you really need. You do NOT need all of those Column attributes and telling EF what the type of the column is when it already figures that stuff out on its own. The only time you need to provide column names is when the column name in the database is different from the one in the model.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2023-08-22T02:09:00

Quote:
A futher interesting behaviour is, that if i load the "TemplateInformationBase" List first and then the "ChangeManagementTemplate" List, the Property of BaseInformationOfTemplate is filled with the data of the table. If i don't load the TemplateInformationBase it stays null.

EF Core doesn't automatically load the related data for you. You either need to eagerly load it:

C#

List<ChangeManagementTemplate> results = db.ChangeManagementTemplates.Include(t => t.BaseInformationOfTemplate).ToList();

explicitly load it:

C#

List<ChangeManagementTemplate> results = db.ChangeManagementTemplates.ToList();
foreach (ChangeManagementTemplate template in results)
{
    db.Entry(template).Reference(t => t.BaseInformationOfTemplate).Load();
}

or add lazy-loading support:

C#

internal static class LazyLoadingExtensions
{
    public static TRelated Load<TRelated>(
        this Action<object, string> loader,
        object entity,
        ref TRelated navigationField,
        [CallerMemberName] string navigationName = null)
        where TRelated : class
    {
        if (navigationField is null && loader is not null)
        {
            loader(entity, navigationName);
        }
        
        return navigationField;
    }
}

public class ChangeManagementTemplate
{
    private readonly Action<object, string> _lazyLoader;
    private TemplateInformationBase _baseInformationOfTemplate;
    
    public ChangeManagementTemplate()
    {
    }
    
    private ChangeManagementTemplate(Action<object, string> lazyLoader)
    {
        _lazyLoader = lazyLoader;
    }
    
    public TemplateInformationBase BaseInformationOfTemplate 
    { 
        get { return _lazyLoader.Load(this, ref _baseInformationOfTemplate); }
        set { _baseInformationOfTemplate = value; }
    }
}

NB: I would recommend eager loading in this case; anything else will introduce a "SELECT N+1" problem. Since it's a 1:1 relationship, there's no danger of eager loading introducing a "Cartesian explosion" issue.

Loading Related Data - EF Core | Microsoft Learn[^]

If you're using EF Core 5.x or later, you can configure your navigation property to always be included:

C#

modelBuilder.Entity<ChangeManagementTemplate>()
    .HasOne(emt => emt.BaseInformationOfTemplate)
    .WithOne()
    .HasForeignKey<TemplateInformationBase>(tib => tib.IdTemplateInformationBase);

modelBuilder.Entity<ChangeManagementTemplate>()
    .Navigation(emt => emt.BaseInformationOfTemplate)
    .AutoInclude();

Model configuration for auto-including navigations | Eager Loading of Related Data - EF Core | Microsoft Learn[^]