Extract one specific entry from a table?

Question

I am currently looking for a way I can pass a foreing key to a table entry that is listed in one table, and should be extracted in another table.

for example purposes I created this ?

public class Parent 
{
    public string Name {get; set;}
    public virtual ICollection<child> Children
    Public virtual ICollection<School> Schools {get; set;}
}

public class Child 
{
    public string Name {get; set;}
    Public School Schoola{get; set;} // Which should be a school Name that the Parent Should know?
}


public class School
{
    //ParentID
    //ChildID
    public string SchoolName {get; set;}
}

How do i give my Child instance a SchoolName that the Parent contains within the SchoolNames?

Children and SchoolNames are seperate tables - but child only need to know a specific entry..

There is something wrong with your database design. You will need to fix the relationships between parents, schools and children before you start writing data access code on top of it. Currently there is no relation defined between a child and a school, you're trying to "guess" which school should match to a child. — Captain Kenpachi, Mar 11 '20 at 14:59
@CaptainKenpachi: I don't think they're trying to guess a possible school, but rather _restrict_ the particular school names that can be set (limited to those of the child's parent, no others). — Flater, Mar 11 '20 at 15:02
@CaptainKenpachi Yes and added a UML aswell still not sure why this is not possible with EF since Parent both have an one to many relation between a child and School.. So from that one should be able to deduce a foreing key to a specific School for a child? — kafka, Mar 12 '20 at 07:53

Flater · Accepted Answer · 2020-03-11T15:00:57.690

Caveat

Your code does not work, since EF does not serialize collections of primitive types. EF Core does have value conversions but it is unclear what you're exactly looking for. I'm going to assume you meant to store these as actual School entities, since your question asks how to "extract one entry from a table".

For the sake of answering your question, I assume that your child should have a reference to the school entity, not a string property that's technically unrelated to the school entity itself, which would make it a question not related to Entity Framework and thus the question tags would be wrong.

I'll address both my assumption and your literal question, just to be sure.

If you need a relationship between a child and a school

From a purely database standpoint, there is no way to specify that an entity's (Child) foreign key should refer to an entity (School) which in and of itself has a foreign key to another entity (Parent). It simply doesn't exist in SQL and therefore EF cannot generate this behavior for you.

What you can do, is implement business validation on your code and refuse to store any child with a school that doesn't belong to its parent. Keep in mind, this requires you to load the parent and their schools every time you want to save a child to the database (because otherwise you can't check if the selected school is allowed for this child), so it will become a somewhat expensive operation.

However, that doesn't prevent the possibility for someone to introduce data into the database (circumventing your business logic, e.g. by a DBA) where this rule is violated but the FK constraint itself is upheld.
How you handle these bad data states is up to you. Do you remove those entries when you stumble upon them? Do you proactively scan the database once in a while? Do you allow it to exist but restrict your application's users to only choosing schools from the parent's scope? These are all business decisions that we cannot make for you.

If a child needs a school name without a relation to the school itself

At first sight, this seems to me to be a bad solution. What happens when the school's name changes? Wouldn't you expect the child's schoolname to also change? Because that's not going to happen in your current setup.

In either case, if you are looking to set a string property, that's trivial, you simply set the property. Presumably, your question is how to restrict the user's options to the child's parent's schools.

This restrictive list can be fetched from the database using the child's identifier:

var childID = 123;

var schoolsFromParent = db
                         .Children
                         .Where(c => c.Id == childId)
                         .Select(c => c.Parent.Schools)
                         .FirstOrDefault();

Note that this code works regardless of whether you have a School entity or a list of strings - though the type of schoolsFromParent will be different.

And then restrict your end user to only being able to pick from the presented options. Note that to prevent bad data, you should doublecheck the chosen name after the user has selected it.

your assumption is right i fixed the code - but would the school class not both have an id to actuial parent and child? Hence always able to extract one name? — kafka, Mar 11 '20 at 15:29
not sure why this is not possible with EF since Parent have an one to many relation to both between a child and School.. So from that one should be able to deduce a foreing key to a specific School for a child? — kafka, Mar 12 '20 at 07:53
@kafka: _"the school class not both have an id to actuial parent and child?"_ If the school had a FK to the child, that would mean that only one child can attend a school. Seems counterintuitive to have a one-to-one relationship between children and schools, one-to-many makes much more sense (one child attends one school, one school is attended by multiple children). It's similarly weird that a school can only be related to one parent. — Flater, Mar 12 '20 at 11:24
@kafka: The issue is in _the database_, not EF. EF simply doesn't give you features that it can't actually use in its datastore. The validation logic for a foreign key is rigorously defined as "must be an existing PK from [this other table]". There is no way to extend that validation logic to also include "and the entry with that PK must in and of itself also have a FK which is an existing PK in the other table and exactly equal to the FK that the original entry has towards that third table". That's simply not what a foreign key is. — Flater, Mar 12 '20 at 11:27
@kafka Maybe a better explanation: If each unique child has exactly one unique school (and vice versa), and the child must have this school (and vice versa), then there is no point to separating the child and school entities as the entries to both tables will be forceably synchronized. You can just merge the two entities into one entity, and save yourself a lot of pointless juggling logic in the process. However, based on an educated guess and things you've implicitly mentioned, that is not what you want and your proposed diagram seems to be the Y of an XY problem. — Flater, Mar 12 '20 at 11:42

Extract one specific entry from a table?

1 Answers1