Szymon Kobalczyk

Hi,

I've started playing with EDM on a sample database I have. After generating the mappings with edmgen I've started refactoring them to better fit the conceptual model.

In this database I have a table for Supplier that contains address information, but since the same kind of information is also stored for Customer and Order tables I would like to introduce a new Address entity and reference it in the these other entities. Orders even have billing and shipping addresses both stored in the same table.

Here is the initial definition for the Supplier entity:

Code Snippet
<EntityType Name="SupplierInfo" Key="SupplierId">
<Property Name="SupplierId" Type="Int32" Nullable="false" />
<Property Name="Name" Type="String" MaxLength="80" Unicode="false" />
<Property Name="Status" Type="String" Nullable="false" MaxLength="2" Unicode="false" />
<Property Name="Address1" Type="String" MaxLength="80" Unicode="false" />
<Property Name="Address2" Type="String" MaxLength="80" Unicode="false" />
<Property Name="City" Type="String" MaxLength="80" Unicode="false" />
<Property Name="State" Type="String" MaxLength="80" Unicode="false" />
<Property Name="Zip" Type="String" MaxLength="5" Unicode="false" />
<Property Name="Phone" Type="String" MaxLength="40" Unicode="false" />
</EntityType>

And here is how I would like to have it:

Code Snippet

<EntityType Name="SupplierInfo" Key="SupplierId">
<Property Name="SupplierId" Type="Int32" Nullable="false" />
<Property Name="Name" Type="String" MaxLength="80" Unicode="false" />
<Property Name="Status" Type="String" Nullable="false" MaxLength="2" Unicode="false" />
<NavigationProperty Name="Address" Relationship="PetShop.Model.FK__Supplier__Address" FromRole="Supplier" ToRole="Address" />
</EntityType>

<EntityType Name="AddressInfo">
<Property Name="Address1" Type="String" MaxLength="80" Unicode="false" />
<Property Name="Address2" Type="String" MaxLength="80" Unicode="false" />
<Property Name="City" Type="String" MaxLength="80" Unicode="false" />
<Property Name="State" Type="String" MaxLength="80" Unicode="false" />
<Property Name="Zip" Type="String" MaxLength="5" Unicode="false" />
<Property Name="Phone" Type="String" MaxLength="40" Unicode="false" />
</EntityType>

However I don't know how I could map these two entities to the single table. The first problem is that Address doesn't have any key here so I can't even define the entity set for it.

I hope you can help me with that and I promise to blog about my experiences with EF as soon as I get it to work :-)



Re: ADO.NET (Pre-release) EDM: Mapping two entities from single table

Srikanth Mandadi - MSFT

The scenario you describe above would require support for Complex Types in the stack. We are planning to support it in a later CTP. ComplexTypes don't have a Key. So you would declare Address as a ComplexType and thus you can share the same definition accross multiple Entity Types( Supplier, Customer and Order in this case). I can add in the details on how exactly you would map these when we start supporting the scenario.

We don't allow multiple EntityTypes to be mapped to the same table unless they belong to the same EntitySet( typical example is TPH inheritance). The reason we don't allow this is because instances of EntityTypes are unique( based on their Key value) with in an EntitySet, so we can update these instances to their corresposding rows based on their Key value. But if the same table is mapped to multiple EntitySets, we could end up with a situation where mutliple instances( in different sets) are mapped to the same row and thus could end up with the update in one EntitySet overwriting changes made in the other Set which we don't want to end up with.

Thanks

Srikanth






Re: ADO.NET (Pre-release) EDM: Mapping two entities from single table

Daniel Simmons - MSFT

It looks like Srikanth, posted a more concise version of essentially this same answer before I realized it... In any case, here's the response I also wrote to this topic:

You have hit the nail on the head when you have pointed out the issue that your addresses don't have keys. Having identity (in the form of a key) is fundamental to being an entity and enabling all of the scenarios like navigation properties and other things. Without a key there would be no way for the system to have a query that will return the address related to a particular supplier or something like that--the fact that the address happens to be co-located with the supplier is not a replacement for identity--especially if you then wanted to relate the same address to some other entity type besides supplier (as would be appropriate if address was an entity type on its own).

Further, the entity framework has a specific designed-in limitation with regard to mapping which is that only one entity can live in any particular row in a table. So even if you could pick a particular column (or couple of columns) which is part of the address to designate as the key, you would not be able to declare that one part of the row is for one entity and another part of the row is for a different entity. All kinds of interesting problems arise if you do this--like what happens if you delete one of those entities but leave the other one in place

So, you are left with two options: One possibility would be to refactor your database, split the address columns out into a separate table, declare a column as the key of the address or add a surrogate key, etc. If you go down this route, then you can create the model you are describing above, and you can also relate the same address entities to other entity types if you like, etc. The problem with this approach, of course, is that you have to change your database and one of the goals of the mapping solution is to allow you to create the model you want without having to change the database. On the other hand, it may well be that you don't really want to allow other entities to relate to the same addresses--addresses are really more value types than reference types in the sense that for many applications I don't really want to query for multiple things that happen at the same address (if I did that would be more of a "location" or "residence" entity or something like that)--I just want to have a common form for an address and maybe some shared operations or business logic, etc. This brings us to the second option...

The other approach would be to wait for beta 2 of the entity framework when we will be introducing the concept of "complex type" as another modelling construct along with entities. A complex type is a value type that does not have identity of its own but it does have structure. You can define a complex type for address info, and that type can appear as a property of one or more entities. When we generate code for the model you will have a separate class for the complex type and it will appear as a direct member of the entity class, so you will get a structure much like you define above, but you won't actually have a navigation property from the supplier to the address you would instead just have a property of the supplier whose type is an address. With complex types you can actually map a complex type's properties to different columns in the same row with the entity that contains the type--in fact the identity of the complex type is slaved to the identity of the entity which contains it so it must be part of the same row (or rows in multiple tables if you are using entity splitting or table-per-type inheritance or something like that where parts of a single entity appear in multiple tables). The nice thing about this kind of construct is that you can fill out the address class with methods and validation and such and share that logic across all instances of an address regardless of which entity type they appear in--it's just that each of those instances is merely a part of the containing entity rather than an entity of its own.

It's my suspicion that the 2nd approach is what you want, but you'll just have to wait until beta 2 before that's available.

- Danny






Re: ADO.NET (Pre-release) EDM: Mapping two entities from single table

Szymon Kobalczyk

Thank you both for very informative answers. I've already tried the approach with mapping both entities to the same row and got this exception.

Code Snippet

ERROR (3021): Problem in Mapping Fragment(s) starting at line(s) {79}: Each of the following columns in table Supplier is mapped to multiple EDM properties:

Supplier.SuppId is mapped to <FK__Supplier__Address.Address.AddressOwnerId, FK__Supplier__Address.Supplier.SupplierId>

But from your description I now understand why this configuration wouldn't work.

You are right, that what I really wont here are ComplexTypes for the Addresses as it makes better fit to the existing model. I'll try to continue working with the current configuration but I was hoping to cleanly replace the existing DAL layer in this sample application with generated entities.

In general, I really like the work you are doing and would definitively want to learn more. After watching couple of videos showing the EDM designer I already started thinking about a series of screencasts and a presentation for our local INETA group. I guess I have to wait for the next Beta whenever it would be available. Are there any other options to evaluate these tools any sooner





Re: ADO.NET (Pre-release) EDM: Mapping two entities from single table

Daniel Simmons - MSFT

I'm glad to hear that you are seeing some value in the project. Please let us know if other questions come up.

Unfortunately, there's really no way to evaluate either complex types or the designer until later CTPs/betas. You can see a lot of the stack with beta 1, there will be another CTP coming out before too long, and then we have additional releases planned to give as much visibility into the product and gather as much feedback as we can before the product ships.

- Danny






Re: ADO.NET (Pre-release) EDM: Mapping two entities from single table

MarkMMullin

I'm starting to dive into the EF, and I'm concerned with this issue

I've got a table of companies - it contains address info for their headquarters (street,city,zipcode) (also statecode, but that's a denormalization :-() It's keyed by a company id

zipcode joins to the zipinfo table, which provides latlong for zipcode center, plus countycode and statecode - countycode and statecode form a composite key into FIPSSTATE which provides names of state and counties, more geocoding data, etc

What I ___really___ want to do is to define two entities out of the company table, the basic company data entity and the company location entity. If I only create one entity I've got a whole mess of relations to other tables, and it fundamentally makes the company too heavyweight - I don't want to restructure the company table, for both performance and legacy reasons. These two entities reflect two completely separate flows in the business logic, i.e. there are flows that deal with business attributes of companies and there are those that deal with locations, and the ones that deal with both still distinguish between locative and descriptive information.

Other than the key itself, there is zero overlap between the basic data and location entities - therefore the multiple instances problem doesn't exist (I think) because the intersection of the data across the two entities is only the key.

TPH inheritence doesn't seem to be what I want, unless I define a root class as having nothing but an id, and then a set of derived siblings that carry the relevent bits (base AbstractCompany, derived CompanyBasicData and CompanyLocation) This seems a bit much but I am off to try it after this post.

My own 2c, with admittedly only introductory exposure at this point, is that it should be legal to map several entities to a common table as long as those entities share no common attributes beyond key - I think that rules out the risk that Srikanth mentioned above





Re: ADO.NET (Pre-release) EDM: Mapping two entities from single table

Daniel Simmons - MSFT

On the surface, this seems like a reasonable request, and it's something we might be able to add at some point. There are more complexities than meet the surface, though. For the first release your best option (as stated earlier in the thread) is to map this as an entity with an embedded complex type, but of course you can't create relationships from a complex type to some other entity.

The one other option I can imagine is to use a defining query in the CSDL to create separate entitysets out of the different parts of the rows--this is analagous to just using a view within the database to make it look like two separate tables. For updates, though, you would have to use stored procs because defining queries/views aren't directly updateable. So this ends up looking a lot more complicated.

- Danny






Re: ADO.NET (Pre-release) EDM: Mapping two entities from single table

MarkMMullin

Everythings always more complicated than it originally appears - if it looks easy, it's tough, if it looks tough, it's damn near impossible.

Anyway...

1) The complex type won't work - I really like them, don't get me wrong, but they're not first class objects within the context of the framework because they can't participate in relationships - they're a great way to encapsulate dumb data but they're basically nested class definitions, not sibling classes that can be composed together

2) I admit to imposing a draconian restriction on this effort, in terms of an overwhelming reluctance to modify the database, but I think this is realistic in terms of EF adoption scenarios - I know my clients reaction to 'Here's a brand new framework and oh by the way we need to refactor the database to use it' would be 'interesting' From the EF perspective this is going to be an ongoing pain, because EF really does need/exploit decent relational models, and for years most of our tools have let us get away with murder, but that's the way it is.....

3) Attempting to create two sibling subclasses to company, one carrying locative info, one carrying everything else, and the base carrying only the key didn't work - the ef generator noticed I had a bunch of non-null column restrictions and fussed about what it was supposed to do - but that does lead to a question - would this be an easy change in EF if you could mark a base class as abstract/non-materializable, and allow simultaneos instantiation of the children (lets leave the problem at one level of derivation for the moment) given there is no intersection between attributes within the children - that is to say, key attributes in the base, and unique attributes in each child (could even say that the union of all attributes of all children and base must equal set of attributes/columns in the store) - given those restrictions you wouldn't need a filter (which is what is annoying EF in my current attempt, I think) because there is no overlap

4) I have to work this out on paper, but I think this is a serious issue WRT the object model that will be used by developers - TPH inheritance et al only seem to address the inheritence side of OOD, i.e. the static relations between types - it doesn't help at all with composition, which is exactly what I need to accomplish here. I've got an existing system that (using older technology) uses one table to manifest/persist two distinct kinds of objects that when composed represent the entire tuple, but in practice are never composed - the table exists because the decision at the time was that this was the most efficient way to represent commonly consumed information. I can certainly do this again in the bizobjs behind EF, but I really really want to do it in the conceptual model - if I can only model specialization/generalization through inheritence, I've only addressed half the problem

For what it's worth, here's the EF definition of what I'm playing with (entities only)

<EntityType Name="COMPANY">

<Key>

<PropertyRef Name="ID" />

</Key>

<Property Name="ID" Type="Int16" Nullable="false" />

<Property Name="Name" Type="String" Nullable="false" MaxLength="128" Unicode="false" />

<Property Name="Address" Type="String" MaxLength="256" Unicode="false" />

<Property Name="City" Type="String" Nullable="false" MaxLength="50" Unicode="false" />

<Property Name="FXPOSTALCODE" Type="String" MaxLength="10" FixedLength="true" />

<Property Name="FXSTATE" Type="String" MaxLength="2" Unicode="false" FixedLength="true" />

<Property Name="Phone" Type="String" MaxLength="20" Unicode="false" FixedLength="true" />

<Property Name="FAX" Type="String" MaxLength="20" Unicode="false" FixedLength="true" />

<Property Name="EMail" Type="String" MaxLength="128" Unicode="false" />

<Property Name="WebSite" Type="String" MaxLength="128" Unicode="false" />

<Property Name="FiscalYearEnd" Type="DateTime" PreserveSeconds="false" />

<Property Name="EMPLOYEES" Type="Int32" Nullable="false" />

<Property Name="Description" Type="String" Nullable="false" MaxLength="2147483647" Unicode="false" />

<Property Name="AuditInfo" Type="Self.RecordAuditInfo" Nullable="false" />

<NavigationProperty Name="INDUSTRYMAP" Relationship="V3DataStoreModel.FK_COMPANIES_IndustryMap" FromRole="COMPANY" ToRole="INDUSTRYMAP" />

<NavigationProperty Name="REGION" Relationship="V3DataStoreModel.FK_COMPANIES_REGIONS" FromRole="COMPANY" ToRole="REGION" />

<NavigationProperty Name="SECTORMAP" Relationship="V3DataStoreModel.FK_COMPANIES_SectorMap" FromRole="COMPANY" ToRole="SECTORMAP" />

<NavigationProperty Name="TICKER" Relationship="V3DataStoreModel.FK_COMPANIES_TICKERS" FromRole="COMPANY" ToRole="TICKER" />

<NavigationProperty Name="FIPSSTATE" Relationship="V3DataStoreModel.FK_COMPANY_FIPSSTATE" FromRole="COMPANY" ToRole="FIPSSTATE" />

<NavigationProperty Name="ZIPINFO" Relationship="V3DataStoreModel.FK_COMPANY_ZIPINFO" FromRole="COMPANY" ToRole="ZIPINFO" />

<NavigationProperty Name="EXECUTIVE" Relationship="V3DataStoreModel.FK_EXECUTIVE_COMPANY" FromRole="COMPANY" ToRole="EXECUTIVE" />

<NavigationProperty Name="HOLDING" Relationship="V3DataStoreModel.FK_HOLDING_COMPANY" FromRole="COMPANY" ToRole="HOLDING" />

<NavigationProperty Name="PENDINGQUARTERLYBALANCE" Relationship="V3DataStoreModel.FK_PendingQuarterlyBalance_COMPANIES" FromRole="COMPANY" ToRole="PENDINGQUARTERLYBALANCE" />

<NavigationProperty Name="PENDINGQUARTERLYCASHFLOW" Relationship="V3DataStoreModel.FK_PendingQuarterlyCashFlow_COMPANIES" FromRole="COMPANY" ToRole="PENDINGQUARTERLYCASHFLOW" />

<NavigationProperty Name="PENDINGQUARTERLYINCOME" Relationship="V3DataStoreModel.FK_PendingQuarterlyIncome_COMPANIES" FromRole="COMPANY" ToRole="PENDINGQUARTERLYINCOME" />

<NavigationProperty Name="QUARTERLYBALANCE" Relationship="V3DataStoreModel.FK_QuarterlyBalance_COMPANIES" FromRole="COMPANY" ToRole="QUARTERLYBALANCE" />

<NavigationProperty Name="QUARTERLYBALANCEDUPLICATE" Relationship="V3DataStoreModel.FK_QuarterlyBalanceDuplicates_COMPANIES" FromRole="COMPANY" ToRole="QUARTERLYBALANCEDUPLICATE" />

<NavigationProperty Name="QUARTERLYCASHFLOW" Relationship="V3DataStoreModel.FK_QuarterlyCashFlow_COMPANIES" FromRole="COMPANY" ToRole="QUARTERLYCASHFLOW" />

<NavigationProperty Name="QUARTERLYCASHFLOWDUPLICATE" Relationship="V3DataStoreModel.FK_QuarterlyCashFlowDuplicates_COMPANIES" FromRole="COMPANY" ToRole="QUARTERLYCASHFLOWDUPLICATE" />

<NavigationProperty Name="QUARTERLYEQUITY" Relationship="V3DataStoreModel.FK_QUARTERLYEQUITY_COMPANY" FromRole="COMPANY" ToRole="QUARTERLYEQUITY" />

<NavigationProperty Name="QUARTERLYINCOME" Relationship="V3DataStoreModel.FK_QuarterlyIncome_COMPANIES" FromRole="COMPANY" ToRole="QUARTERLYINCOME" />

<NavigationProperty Name="QUARTERLYINCOMEDUPLICATE" Relationship="V3DataStoreModel.FK_QuarterlyIncomeDuplicates_COMPANIES" FromRole="COMPANY" ToRole="QUARTERLYINCOMEDUPLICATE" />

<NavigationProperty Name="SUBSIDIARY" Relationship="V3DataStoreModel.FK_SUBSIDIARIES_COMPANIES" FromRole="COMPANY" ToRole="SUBSIDIARY" />

<NavigationProperty Name="TICKER1" Relationship="V3DataStoreModel.FK_TICKERS_COMPANIES" FromRole="COMPANY" ToRole="TICKER" />

<NavigationProperty Name="SICCODE" Relationship="V3DataStoreModel.COMPANYSIC" FromRole="COMPANY" ToRole="SICCODE" />

</EntityType>

<ComplexType Name="RecordAuditInfo">

<Property Name="DEFINEDON" Type="DateTime" Nullable="false" PreserveSeconds="false" />

<Property Name="CHECKEDON" Type="DateTime" Nullable="false" PreserveSeconds="false" />

<Property Name="MODIFIEDON" Type="DateTime" Nullable="false" PreserveSeconds="false" />

</ComplexType>





Re: ADO.NET (Pre-release) EDM: Mapping two entities from single table

Srikanth Mandadi - MSFT

The short answer as Danny already mentioned is that we don't support the scenario today. I don't think there is any way to map this today( atleast that I know of). I understand that it is frustrating when you hit this kind of issue.

Regarding your point about the two Entities sharing only the Key, even when the two EntitySets share only the key column in their mapping, there are still some problems that we would have to compensate for like What happens when an instance gets deleted in one Set - Do we delete the instance in the other set too Similar probalems exist for inserts.

Ofcourse these are all solvable problems. But given the priority of other scenarios, we did not have time to tackle these for this relese. We will explore the possibility of enabling scenario for our next release.

Thanks

Srikanth






Re: ADO.NET (Pre-release) EDM: Mapping two entities from single table

MarkMMullin

I see your point - the issues themselves are relatively simple, however implementing them meaningfully and realistically in a framework is tough - that said, as far as I can tell from initial analysis, conceptual models using composition/aggregation beyond what is provided by ComplexType aren't possible yet. Please correct me if I'm wrong, because I think this has major implications for incorporating EF with legacy databases - I agree that with a new database its more an issue of stopping doing dumb things in the first place

Thanks

MMM





Re: ADO.NET (Pre-release) EDM: Mapping two entities from single table

Daniel Simmons - MSFT

You are correct that there aren't a lot of options for multiple separate entities composed/aggregated in a single database row. Sometimes people think of composition as talking more about the semantics of how entities in the conceptual model relate to one another (liftetime of one entity is tied to the lifetime of the other, etc.) where there is some support in the EF, but I think you are referring to composition within the storage schema which we just won't be able to address deeply in the first release.

- Danny






Re: ADO.NET (Pre-release) EDM: Mapping two entities from single table

MarkMMullin

Sounds good - I was just concerned with getting my point across - I came up with a mechanism this weekend to allow me to keep moving forward (i hope)

For the time being, I'm leaning towards advocating a three tier approach - base tier is the EF conceptual model, untouched by mere mortals (aside from endless fiddling with the CSDL that is) - second tier is strictly concerned with handling change between EF layer and business entity layer (tier 3) - if you change/delete something in tier 3 this is what can either stop you (using eventing with full pre-event/event and cancel support), cause your tier 3 objects to disappear (disposable), etc - in essence, I'm trying to keep the mess contained in this layer - the third tier lets me realize business entities (not biz objs) where I can support composition (another weird one came up, an object that is built from multiple rows in a table, where it's not a master detail, but is either annual summary of quarterly data (not a nice summary, needs weird logic) or a normalized financial statement built from several partial statements)) - business objects all aggregate third tier business entities.

Reason I bring this up - my central assumption is that, within reason, as EF continues to mature, I would end up deleting more and more functionality from tiers 2 and 3 - in a perfect world, those two tiers would disappear and my business objects would be directly binding to EF conceptual entities.





Re: ADO.NET (Pre-release) EDM: Mapping two entities from single table

Daniel Simmons - MSFT

As the EF evolves, I do expect that we will expand the mapping capabilities in addition to building out the API surface and overall set of services that are EDM aware, etc. That said, there are a great many kinds of mappings that we can imagine which will be very difficult to support in a general, declarative manner--particularly if we want to support updates as well as queries. Something we are giving thought to now as we look to the future in the EF is not only directly expanding mapping capabilities but also working to create an interface/extensibility point for mapping so that some of the most complex scenarios can be enabled by writing some dedicated custom code which would plug into the EF at a lower layer than the rest of your app. So it might be that some parts of the code you write in tiers 2 & 3 would live on but be much better encapsulated within the EF as opposed to sitting in your own separate layer on top.

- Danny