Click here to Skip to main content
15,936,903 members
Please Sign up or sign in to vote.
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
Updated 14-Sep-23 6:19am
v5

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!
 
Share this answer
 
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 :)
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[^]
 
Share this answer
 
Comments
HobbyProggy 22-Aug-23 8:48am    
Yep, that's what i just found out then, the lazy loading "killed" me. Thanks for that addition on the AutoInclude! Will try that one out.

One question though, regarding lazy loading, so if i go further with migration i will come across objects that have relations 1:n, since i want to load everything on my rest endpoint and then transfer it via json to the web client or desktop client i need to be sure that all data is loaded when creating the json. Is that working out or would i also need to loop through each "possible" item to trigger the loading?
Richard Deeming 22-Aug-23 8:52am    
Eager loading is still the simplest option:
db.YourEntity.Include(x => x.SomeCollection).ThenInclude(y => y.SomeOtherEntity).ThenInclude(z => z.AThirdEntity)

However, that can potentially lead to Cartesian explosion[^].

If you're using EF Core 5.x or later, you can use split queries[^] to avoid that whilst still eagerly loading the related data.
HobbyProggy 22-Aug-23 9:17am    
Okay thanks! It seems like I may probably need to go with both options in the future, though, as from reading the links you posted it's always a good idea to start of eager loading and then narrowing down the performance issue.

I'll keep that in mind!
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.
 
Share this answer
 
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.

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900