How to get data from database with lazy loading and single table inheritance

Question

I have a database that is set up with lazy loading turned on(which is needed). I also have an ASP.NET MVC application that connects to the database and 'Does Stuff'. The database is modeled from my classes in a separate project.

I currently have 3 classes that use inheritance:

public class DeliveryNotification
    {
        [Key]
        public int Id { get; set; }

        public Status Status { get; set; }
    }

.

    public class TrackedDelivery : DeliveryNotification
{
    [Required]
    public DateTime Date { get; set; }

    [Required]
    public DateTime Time { get; set; }
}

.

public class SignedForDelivery : TrackedDelivery
    {
        [Required]
        public string Image { get; set; }

    }

These are all stored in the database using single table called 'DeliveryNotification'

I have been given a DeliveryNotification Id and would like to get all of the information.

Inside one of my controllers is this method:

var query = (from s in context.SignedForDeliverys
                    where s.Id == id
                             select s
                    ).SingleOrDefault();

^ This will return null although there is a DeliveryNotification with the ID

var deliveryNotifications = context.DeliveryNotifications.SingleOrDefault(o => o.Id == id);

^ This will return only the id and the status.

So assuming I have been given an Id how can I return (preferably a SignedForDeliverys) with all of the data?

Edit: Tried to add an Include:

var signedForDeliverys = (from s in context.SignedForDeliverys.Include("TrackedDelivery ").Include("DeliveryNotification")
                select s).ToList();

Then I get this error:

An exception of type 'System.InvalidOperationException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

Additional information: A specified Include path is not valid. The EntityType 'ClassLibrary.SignedForDelivery' does not declare a navigation property with the name 'TrackedDelivery'.

Gert Arnold · Accepted Answer · 2014-05-02 11:14:41Z

3

If you want a SignedForDelivery you do...

context.DeliveryNotifications.OfType<SignedForDelivery>().Single(n => n.Id == id)
        // or .Find(id)

Or if you have DbSet properties for each subtype...

context.SignedForDeliveries.Single(n => n.Id == id) // or .Find(id)

...and you've got all data.

But there more things to say about this.

From your description it seems that fetching an object by Id without knowing the type upfront is a business case (or use case). If so, in my opinion you shouldn't use inheritance. The only thing yo can do is retrieve a DeliveryNotification, then try to cast it to all inherited types to see what it is (because the discriminator field is not part of the class model). That's pretty clumsy.

I wouldn't solve this by inheritance. I would use one type, fetch the record, and then decide which data are relevant, e.g. by mapping it to a specific view model or domain class depending on a Type property which can now be visible. This could be done by a strategy pattern, which is one of the composition patterns. (Referring to the "Composition over inheritance" debate).

Also, with TPH inheritance (one table) you can't strongly enforce that specific properties like Image are required, because they should be nullable in the database table. It's a "soft" requirement in the sense that its enforcement depends on software. Other inheritance schemes, TPT, TPC, do allow strong enforcement, but they have other downsides.

In short, inheritance in itself isn't always the best pattern, combined with an ORM it's often better avoided.

answered May 2, 2014 at 11:14

Gert Arnold

110k36 gold badges218 silver badges319 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Colin Over a year ago

+1 Very nice explanation getting deeper into the design. One comment - it is possible to strongly enforce a specific properties by adding a check constraint. Something like

ALTER TABLE dbo.DeliveryNotifications  WITH CHECK ADD CONSTRAINT CK_Inheritance CHECK (Discriminator <> 'TrackedDelivery' OR (Date IS NOT NULL AND Time IS NOT NULL))

Gert Arnold Over a year ago

@Colin That's right, actually. Never occurred to me. I don't know if I'd like it though. Too much logic buried out of sight of application development and it's a bit of a hassle with code first migrations.

Colin · Accepted Answer · 2014-05-02 08:24:59Z

Query 1:

var query = (from s in context.SignedForDeliverys
            where s.Id == id
            select s
            ).SingleOrDefault();

should return a SignedForDelivery if the id matches a SignedForDelivery. If the id matches a DeliveryNotification that is not a SignedForDelivery then it will return null.

Query 2:

var deliveryNotifications = context.DeliveryNotifications
                                   .SingleOrDefault(o => o.Id == id);

should return a DeliveryNotification if the id matches a DeliveryNotification. To get it as TrackedDelivery or a SignedForDelivery you need to cast it:

var trackedDelivery = deliveryNotifications as DeliveryNotification;
if(trackedDelivery != null)
{
   DateTime d = trackedDelivery.Date;
}

var signedForDelivery = deliveryNotifications as SignedForDelivery 
if(signedForDelivery != null)
{
   String image = signedForDelivery.Image;
}

Query 3:

var signedForDeliverys = (from s in context.SignedForDeliverys
                                           .Include("TrackedDelivery")
                                           .Include("DeliveryNotification")
                          select s).ToList();

fails because the purpose of the Include method is to load related entities. i.e entities that are related via navigation properties. SignedForDelivery does not declare a navigation property with the name TrackedDelivery

Collectives™ on Stack Overflow

How to get data from database with lazy loading and single table inheritance

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related