Can I define a filter for a property within a relationship in Entity Framework 6?
Before you start, please note that I have simplified the data structure for what I am trying to do and in the real world it is not as bad as you might think. Or maybe it is. Regardless, I cannot change the way the data is structured, so I am not looking for suggestions on how to better structure the data. I just hope that I can do what I ask in Entity Framework 6.
I have the following tables:
Person
ID FirstName LastName
1 John Smith
2 Jane Doe
Job:
ID Name
1 Developer
ExtendedData:
ID TableName RowID FieldName FieldValue
1 Person 1 MiddleInitial A
2 Person 1 Gender M
3 Person 2 MiddleInitial B
4 Person 2 Gender F
5 Job 1 Description Develop Stuff
The purpose of this ExtendedData table is to allow additional data to be stored if there is no column for the data. For example, here "MiddleInitial" is not a column in the Person table, but in the ExtendedData table, we can add a row to store this data.
In my Person class, I can add the following code to add the ExtendedData property:
public virtual ICollection<ExtendedData> ExtendedData { get; set; }
Then I can create a relationship in Entity Framework with this:
modelBuilder.Entity<Person>()
.HasMany(e => e.ExtendedData)
.WithRequired(e => e.Person)
.HasForeignKey(e => e.RowID);
I have a concern if I call ...
john = Persons.Where(a => a.ID == 1);
john.ExtendedData...
... I will return all Extended Data rows where RowID = 1, including the row for the "Job" table. Obviously I could do something like ...
john.ExtendedData.Where(a => a.TableName == "Person")...
... but it's a bit dangerous because if I (or some other developer) forgot to include an additional filter in the code?
I tried to do something like this ...
modelBuilder.Entity<Person>()
.HasMany(e => (ICollection<ExtendedData>))e.ExtendedData.Where(a => a.TableName == "Person"))
.WithRequired(e => e.Person)
.HasForeignKey(e => e.RowID);
... but got a runtime error ...
The expression 'e => Convert (e.ExtendedData.Where (a => (a.TableName == "Face")))' is not a valid property expression. Expression must represent a property: C #: 't => t.MyProperty' VB.Net: 'Function (t) t.MyProperty ".
The point I am making is that it wants me to specify a property from "e" rather than trying to do another magic.
Is there somewhere I can change the Entity Framework model so that when person.ExtendedData is called, it will only return to me. ExtendedData writes where TableName = "Person"? Or should I remember to always include this additional filter when trying to retrieve data from the ExtendedData table?
source to share
What you really need is Table Per Hierarchy. Define a base object for your table, then define an inherited object for each variation and set up a discriminator.
Public abstract class ExtendedData
{
public int Id {get;set;}
public string FieldName {get;set;}
public string FieldValue {get;set;}
}
public class ExtendedPersonData : ExtendedData
{}
public class ExtendedJobData : ExtendedData
{}
public class Person
{
....
public virtual ICollection<ExtendedPersonData> ExtendedData {get;set}
}
public class Job
{
....
public virtual ICollection<ExtendedJobData> ExtendedData {get;set;}
}
public class Context : DbContext
{
....
public DbSet<ExtendedData> ExtendedData {get;set;}
}
modelBuilder.Entity<ExtendedData>()
.Map<ExtendedPersonData>(m => m.Requires("TableName").HasValue("Person"))
.Map<ExtendedJobData>(m => m.Requires("TableName").HasValue("Job"));
with inherited classes, you can now execute Non-Polymorphic Queries on table data, i.e.
IQueryable<ExtendedPersonData> query = from e in context.ExtendedData
.OfType<ExtendedPersonData>()
select e;
This will generate an SQL query that only returns records where the discriminator column matches ( "TableName" == "Person"
in this case). Returning Person
and querying it ExtendedData
will automatically create a Non-Polymorphic Query due to the collection type defined for the object.
Note that in this case, you can mix common columns and columns that are unique to each variation. Any columns specific to a unique variation are automatically nullable in the table and populated only by the entities that provide it. In your case, however, you don't have unique columns, so the Inherited classes are just implementation stubs.
source to share