1

I have 2 entities in my database that have a one-to-many relationship.

The principal table is called Items, the related table is called Jobs.

An Item can have 0 ... many Jobs. A job is only related to a single item.

The EF Core entities look like this (simplified):

public class Item
{
    public Guid Id { get; set; }
    public virtual ICollection<Job> Jobs { get; set; }
}

public class Job
{
    public Guid Id { get; set; }
    public Guid ItemId { get; set; }
    public virtual Item Item { get; set; }
}

For the Items table, I have created a view (called ItemView) with some additional data I need.

ItemView is a readonly abstraction based on Item and some other information in the database. It's only used to simplify queries from the application.

The relationship to the Jobs table however stays the same.

Is it possible in EF Core 3.1 to define the same relationship between Item and Jobs also for the ItemView to make use of includes in the queries and have EF generate nested objects?

I would like to be able to have a query like

var result = await context.ItemView.Include(i => i.Jobs)

and get back the same nested objects (but containing my additional properties in ItemView) I would get with

var result = await context.Item.Include(i => i.Jobs)
Markus S.
  • 2,602
  • 13
  • 44
  • I think this may help you: https://stackoverflow.com/questions/7461265/how-to-use-views-in-code-first-entity-framework – G.Y Dec 05 '21 at 12:23
  • It seems like you might be able to do it by using ItemView as a table-per-hierarchy driver for a parent/child pair of inherited entities, but then you have some more fudging to do to make the data updatable. Is that a concern? And what stops you just defining a client side equivalent query to what the view does, so that you can stick with the job/items you have? – Caius Jard Dec 05 '21 at 13:49
  • Thank you for your feedback! @G.Y This link only covers general mapping of views as part of an EF Model which is not my concern. I would like to extend my already mapped and working view with a Navigation Property to a list of existing entities based on a FK existing on the Job-Entity. I will update this FK in the sample Entity definition above to make it more clear. – Markus S. Dec 06 '21 at 09:37
  • Thank you for your ideas @CaiusJard ! I'm not sure though if i completely follow. Are you suggesting to derive the ItemView class in my EF Model from the Item Entity? The query behind ItemView is quite complex by using Joins based on JSON_VALUE access of a JSON-Column contained in the Item Entity which is not very easy to express as a Linq query. That was the reason for introducing a SQL View which abstracts this madness from the code. – Markus S. Dec 06 '21 at 09:48
  • I got the impression that you had Item, which is like some base class, and you had ItemView which is like a child class i.e. an Item, but with more properties. If all the needed columns of an item are present in the ItemView VIEW then you could conceptually have `ItemView:Item` inheritance on the C# side and I believe you can tell EF that it gets both these objects from the ItemView view as if it were a table with a table-per-hierarchy inheritance tree.. That's if EF even needs to know that it's an inheritance and materialize ents differently, because being a readonly view means you can't.. – Caius Jard Dec 06 '21 at 10:16
  • ..update it without some fudgery. At the mo I think there are too many unresolved questions (in my mind) to know what to answer; do you need to update these objects? Is an ItemView an Item (base) and do you use both in a polymorphic fashion in the code? Do you need to navigate from an ItemView to something else? What data is being stored and pulled apart in the view? Is there merit in redesiging things so that JSON is pulled apart and stored in pulled-apart form so that ItemView could take over Item completely (and just be Item) with editable props? What does having a VIEW in the db make easy? – Caius Jard Dec 06 '21 at 10:19
  • ItemView is not a child of Item, it's just an abstraction over data contained in the Item Table along with some joins and it expresses data contained in the JSON Column as Columns in the Resultset. It's only used to simplify queries. Writing will only be done to the physical entity (Item-Table). – Markus S. Dec 06 '21 at 10:43

0 Answers0