0

In Entity Framework 6 code first, what attributes can I add to my C# object model for a 1-1 parent/child relationship while the underlying SQL table relationship is a 1 to many?

Data is split between data common to all customers and data for a specific customer in SQL Server 2012.

It has three entities, a WarehouseBase base entity, WarehouseCustom customer specific data and a customer entity.

The C# object:

Warehouse
{
  public Guid ID {get;set;}
  public string Name {get;set;}
  public string Note {get;set;}  //customer specific data
}

Customer
{ 
  public Guid ID {get;set;}
  public string Name {get;set;}
}

A customer user would use this workflow:

  1. Customer views Warehouse XYZ in browser. It should return warehouse XYZ shared data (ID, Name) and the customer specific data (Note).
  2. Customer edits the note and clicks save button
  3. Note should be saved to database

An admin would edit and save the WarehouseBase data (Name) but not the customer specific data.

SQL tables:

WarehouseBase
  ID : Guid
  Name: Nvarchar (255)

WarehouseCustom
  ID : Guid
  WarehouseBaseID: GUID
  CustomerID : GUID
  Note : Nvarchar (255)

Customer
  ID : Guid
  Name : Nvarchar (255)

The system has multiple different concurrent customers using web browser front end connecting to a WebAPI server. The server uses EF6/C# to acccess a SQL Server.

What attributes can be added to the C# model for this?

Already seen:

  1. One to many relationship: http://www.entityframeworktutorial.net/code-first/configure-one-to-many-relationship-in-code-first.aspx
  2. How to query one to many and load specific child data: Entity Framework include filter child collection
testx
  • 9
  • 3
  • Our application will have 50+ of these types of base/custom entities; so avoiding hand-coding a 2 query load and 1 query save for each entity would help. – testx Jun 01 '17 at 14:36
  • For new customers, a child object for Note may not exist. – testx Jun 01 '17 at 14:37
  • The user will search by Warehouse Name (base entity data) first to find and load the Warehouse Name. Searching by Warehouse note first or a linking table adds more complexity than just doing 2 queries to read a Warehouse and 1 query to update the Warehouse note. – testx Jun 01 '17 at 19:07
  • Found low level EF interceptors reference. It looks to be much more complicated than doing 2 queries for read and 1 query for write on the entity. Low level EF interceptors: https://msdn.microsoft.com/en-us/library/dn469464(v=vs.113).aspx – testx Jun 01 '17 at 19:10

1 Answers1

0

the c# should be

Warehouse {
    public Guid ID {get;set;}
    public string Name {get;set;}

    public ICollection<WarehouseCustom> CustomersNotes {get; set;}
}

Customer { 
    public Guid ID {get;set;}
    public string Name {get;set;}

    public ICollection<WarehouseCustom> WarehouseNotes {get; set;}
}

WarehouseCustom {
    public Guid ID {get;set;}
    public string Note {get;set;}  //customer specific data

    public virtual Warehouse {get; set;}
    public virtual Customer {get; set;}
}

So a customer can access all his notes on a warehouses and an warehouse administrator can access all the notes on said warehouse.

to prevent a customer to navigate in the Customer notes of a warehouse you must take care to the materialization of the data.

using (AppContext ctx = new AppContext()) {
    return ctx.Customers.
        Include(x => x.WarehouseNotes.Select(y => y.Wharehouse)).
        Where(x => x.ID == oneID).
        ToList();
}

That is: DO NOT USE LAZY LOADING.

in the code sample you only load what the customer should see.

Ideally tou should use a DTO type.

BTW: you do not need an ID for Warehousecustom (execpt if you can have multiple customer note for one warehouse). You can use a complex key made of the warehouse id and the customer id.

IMHO, this last constraint will enforce what you name a 1 to 1 relation.

tschmit007
  • 7,559
  • 2
  • 35
  • 43
  • Works if the customer has a note for a warehouse but not for the case where the customer wants to find a warehouse without a note and then add a note to the warehouse. – testx Jun 01 '17 at 19:09
  • Thanks tschmit007 – testx Jun 01 '17 at 19:12