CODEWITHSUNDEEP
c#entity-frameworkasp.net-core
xcelm
xcelm

Reputation: 571

Developing Entity Framework compatible data model

I'm trying to develop a data model for c# .net Core application using Entity Framework Code first.

What I basicaly have is a data model bellow.

enter image description here

There are tables Dog and Person. They each contain different information. For example I can have different type of dogs. Person can have something specific and that would be not applicable for dogs.

There is 1:1 relation between Person and Item. The same relation is between Dog and Item.

In addition to that table ItemReference contains child-parent relations between Items. For example there is child - parent relation between dog and person (dog owner - dog or persons may be siblings)

I need to make alphabetical list of dogs and persons (mix them up).In addition I need to be able to get to Details page for each of the following.

Using this feels really stupid as there might be more tables in the future. For Example Cat or Car and I would need to rework these If statements each time.

@foreach (var x in Model.Item.OrderBy(y=>y.ItemName))
{
    if (x.ItenType == "Dog")
    {
        <li><a asp-page="DogDetails" asp-route-id="@x.Item.Dog.Id">@x.ItemName</a></li>
    }
    if (x.ItenType == "Person")
    {
        <li><a asp-page="PersonDetails" asp-route-id="@x.Item.Person.Id">@x.ItemName</a></li>
    }
}

Anyone knows how to make such a data model so it is more intelligent design?

Any help greatly appreciated

Tables Dog, Person and Item will look like this:

Dog

Id  DogName     DogTypeId   DogStatusId ItemId
1   "Alex"      1           1           1
2   "Rex"       2           1           2

Person

Id  PersonName  PersonStatusId      ItemId
1   "Joe"       1                   3     
2   "Jane"      2                   4

Item 

Id  ObjectName   ItemType
1   "Alex"       "Dog"
2   "Rex"        "Dog"
3   "Joe"        "Person"
4   "Jane"       "Person"

Upvotes: 0

Views: 81

Answers (1)

Chris Pratt
Chris Pratt

Reputation: 239440

Your object graph isn't entirely clear. It looks like Dog and Person are just related to Item, but based on your description and brief code sample, it would appear that they're actually types of items. If that's the case, you should be employing an inheritance strategy, not composition.

public class Item

public class Dog : Item

public class Person : Item

By default, EF will handle inheritance with a TPH (table per hierarchy) strategy, which doesn't seem appropriate here. Instead, I think you're going to want TPT (table per type) which can be achieved by using the Table attribute on your derived classess:

[Table("Dogs")]
public class Dog : Item

[Table("Persons")]
public class Person :

What this will do is create three tables, Dogs, Persons, and Items. Any data common to all items, regardless of whether it's a dog or person, will obviously go on your Item class and will end up in the Items table. Foreign keys will be added on both the Dogs and Persons table pointing to the Items table. When you retrieve a Dog, for example, EF will automatically join the corresponding record in Items to create a complete Dog.

This then gives you some special abilities with your queries. You can generically get all items, regardless of type, with _context.Items of course, but you can also potentially have separate DbSets for Dog and Person, allowing you to query those independently: e.g. _context.Dogs. Even without an explicit DbSet, you can utilize the generic Set<TEntity> method: _context.Set<Dog>(). Additionally, you can use OfType<TEntity> to filter the Items set: _context.Items.OfType<Dog>().

Lastly, even if you just retrieve Items, EF still tracks what ultimate type each Item is, which means you can do things like pattern matching:

if (item is Dog dog)
{
    // `dog` is now a variable in this scope of your `item` downcast to `Dog`
}

Or in switch statements:

switch (item) 
{
    case Dog dog:
        // do something with `dog`
        break;
    case Person person:
        // do something `person`
        break;
    default:
        // do something with generic items (`item`)
        break;
}

EDIT

I realized that I only alluded to the following, rather than stating explicitly. Since Dog and Person are totally different tables in TPT, you can define totally separate relationships there. For example, Dog could have a relationship with DogStatus, without affecting anything that's going on with Person. You can technically still do this even with TPH, but you'd end up with a bunch of nullable foreign keys on the Items table and that would just be a mess and also breaks normalization.

Upvotes: 2

Related Questions