I'm trying to figure out whether its possible to represent a less common model in entity framework. The database I'm using has Persons, PersonFile, Files, Images, and Executables. A Person can have many PersonFiles, each PersonFile has one File, and a File is either an Image or Executable (all of these have their own table, but Images/Executable share their primary key with File, instead of having an explicit foreign key relationship)
My Persons looks like this:
public class Person {
// Properties
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
[JsonProperty("id")]
public Guid Id { get; set; }
public virtual ICollection<PersonFile> PersonFile { get; set; }
}
public class Person_Config : IEntityTypeConfiguration<Person> {
public void Configure(EntityTypeBuilder<Person> entity) {
entity.ToTable("person");
// Properties
entity.Property(e => e.Id)
.HasColumnName("id");
}
}
My PersonUploads looks like this:
public class PersonFile {
// Ids
public Guid FileId { get; set; }
public Guid PersonId { get; set; }
// Relations
public virtual File File { get; set; }
public virtual Person Person { get; set; }
}
public class PersonFile_Config : IEntityTypeConfiguration<PersonFile> {
public void Configure(EntityTypeBuilder<PersonFile> entity) {
entity.ToTable("_Person_File");
entity.HasKey(e => new { e.FileId, e.PersonId });
entity.Property(e => e.FileId)
.HasColumnName("File_id");
entity.Property(e => e.PersonId)
.HasColumnName("Person_id");
entity.HasOne(d => d.File)
.WithMany(p => p.PersonFile)
.HasForeignKey(d => d.FileId)
.OnDelete(DeleteBehavior.ClientSetNull);
entity.HasOne(d => d.Person)
.WithMany(p => p.PersonFile)
.HasForeignKey(d => d.PersonId)
.OnDelete(DeleteBehavior.ClientSetNull);
}
}
My File model looks like this:
public class File {
// Properties
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public Guid Id { get; set; }
public string Type { get; set; }
// Relations
public virtual Image Image { get; set; }
public virtual Executable Executable { get; set; }
public virtual ICollection<PersonFile> PersonFile { get; set; }
}
public class File_Config : IEntityTypeConfiguration<File> {
public void Configure(EntityTypeBuilder<File> entity) {
entity.ToTable("File");
// Properties
entity.Property(e => e.Id)
.HasColumnName("id")
.IsRequired();
entity.Property(e => e.Type)
.HasColumnName("type")
.HasMaxLength(255)
.IsRequired();
}
}
And for the sake of brevity, my Image and Executables look almost identical, its just the relationship of each of them to file that matters. Here is what Image looks like:
public class Image {
// Properties
public Guid Id { get; set; }
// Relations
public virtual File File { get; set; }
}
public class Image_Config : IEntityTypeConfiguration<Image> {
public void Configure(EntityTypeBuilder<Image> entity) {
entity.ToTable("Image");
entity.Property(e => e.Id)
.HasColumnName("id")
.IsRequired();
entity.HasOne(u => u.File)
.WithOne(r => r.Image)
.HasForeignKey<File>(u => u.Id)
.OnDelete(DeleteBehavior.ClientSetNull);
}
}
While querying with linq, I can retrieve a File by Person no problem by doing something like this:
ICollection<File> files_image = await (from file in db.File
join image in db.Image
on file.Id equals image.Id
join personfile in db.PersonUpload
on file.Id equals personfile.UploadId
where personfile.PersonId == id
select file)
.ToListAsync();
However, as soon as I try to query specifically for an image using the join, and select that image as the resulting type like so:
ICollection<Image> files_image = await (from file in db.File
join image in db.Image
on file.Id equals image.Id
join personfile in db.PersonFile
on file.Id equals personfile.FileId
where personfile.PersonId == id
select image).ToListAsync();
I get the following error:
MySql.Data.MySqlClient.MySqlException (0x80004005): Unknown column 'r.PersonId' in 'field list'
Does anyone know either where I'm going wrong or how to properly model this kind of relationship? (I think that's the issue, but maybe I'm completely missing something else) Thanks for your help!
User contributions licensed under CC BY-SA 3.0