c# - Can I define a filter on a property within a relationship in Entity Framework 6? -
before started, note i've simplified data structure i'm trying , in real world isn't awful of implementation might think. or maybe is. regardless, can't change way data structured i'm not looking suggestions on how better structure data. i'm hoping see if there way can i'm asking in entity framework 6.
consider have 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 2 person 1 gender m 3 person 2 middleinitial b 4 person 2 gender f 5 job 1 description develop stuff
the purpose of extendeddata table allow additional data stored when there isn't column data. example, here "middleinitial" not column in person table, in extendeddata table can add row store data.
in "person" class can add following code add extendeddata property:
public virtual icollection<extendeddata> extendeddata { get; set; }
then can create relationship in entity framework this:
modelbuilder.entity<person>() .hasmany(e => e.extendeddata) .withrequired(e => e.person) .hasforeignkey(e => e.rowid);
the concern have, if call...
john = persons.where(a => a.id == 1); john.extendeddata...
... i'll extended data rows rowid = 1, including row "job" table. obviously, like...
john.extendeddata.where(a => a.tablename == "person")...
... little dangerous because if (or other developer) forget specify filter in code?
i tried doing this...
modelbuilder.entity<person>() .hasmany(e => (icollection<extendeddata>))e.extendeddata.where(a => a.tablename == "person")) .withrequired(e => e.person) .hasforeignkey(e => e.rowid);
... received error @ run time stating...
the expression 'e => convert(e.extendeddata.where(a => (a.tablename == "person")))' not valid property expression. expression should represent property: c#: 't => t.myproperty' vb.net: 'function(t) t.myproperty'.
the sense make of wants me specify property "e" , not try further wizardry.
is there anywhere can modify entity framework model such when call person.extendeddata return me extendeddata records tablename = "person"? or must remember include filter when trying pull data extendeddata table?
what want here table per hierarchy. define base entity table, define inherited entity each variation, , customize 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, can non-polymorphic queries against table data, i.e.
iqueryable<extendedpersondata> query = e in context.extendeddata .oftype<extendedpersondata>() select e;
this generate sql query returns records discriminator column matches ("tablename" == "person"
in case). returning person
, querying it's extendeddata
automatically create non-polymorphic query, due collection type defined on entity.
note in scenario, can mix shared columns , columns unique each variation. columns specific unique variation automatically become nullable in table, , filled entities provide it. in case, however, have no unique columns, inherited classes implementation stubs.
Comments
Post a Comment