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:
[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:
[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:
"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
".
#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:
[ForeignKey("IdChangeManagementTemplate")]
public TemplateInformationBase BaseInformationOfTemplate { get; set; }
and used this:
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:
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?