Submit My Question - Recent Questions - Recent Answer

Msdn Tech >> Visual Studio Orcas >> ADO.NET Orcas

How to combine data from two tables into one EntityType?

Using the vNext August CTP and Linq May CTP LINQtoEntities Sample I would like to include the CategoryName of the Northwind Categories table in the Product EntityType, to be able to do following query:

varproducts =frompindb.Products
orderbyp.CategoryName
selectp;

foreach(Productpinproducts)
Console.WriteLine("{0}\t{1}", p.CategoryName, p.ProductName);

What does the entity model have to look like?

Thanks!

[826 byte] By [nakrian] at [2008-3-5]

«« Help: Where are the examples in the VS 2005 Express Documentation?
»» DirectX performance issues, multithreaded

# 1

You can achieve this effect by declaring entity sets for Products, Categories, and an association set for ProductCategories - then you can use navigation properties for getting the CategoryName. Here is the CSDL for that:

<?xml version="1.0" encoding="utf-8"?>

</AssociationSet>

</EntityContainer>

</EntityType>

</EntityType>

</EntityType>

</Association>

</Schema>

Your code will look this:

var products = from p in db.Products
orderby p.Category.CategoryName
select p;

foreach (Product p in products)
Console.WriteLine("{0}\t{1}", p.Category.CategoryName, p.ProductName);

AtulAdya-MSFT at 2007-10-7 >

top of Msdn Tech,Visual Studio Orcas,ADO.NET Orcas...

# 2

Thanks for your reply. What I would actually like to do is discribed in "Next-Generation Data Access: Making the Conceptual Level Real" (http://msdn.microsoft.com/data/default.aspx?pull=/library/en-us/dnvs05/html/nxtgenda.asp)

"... ideally one would want to reason about the data as a single entity without the need for joins or knowledge of the logical model. An entity like Employee can be defined and mapped across multiple tables in the store."

To stay with our Products and Categories example, I would like the information from the Categories table to be contained inside the Products entity.

The CSDL (Model) I tried: (sorry for the layout )

<?xml version="1.0" encoding="utf-8"?>

<Schema

xmlns:cg="http://schemas.microsoft.com/ado/2006/04/codegeneration"

xmlns:edm="http://schemas.microsoft.com/ado/2006/04/edm"

xmlns="http://schemas.microsoft.com/ado/2006/04/edm"

Namespace="NorthwindLib"

Alias="Self">

</AssociationSet>

</EntityContainer>

</EntityType>

</EntityType>

</EntityType>

</Association>

</Schema>

MSL (Mapping):

<?xml version="1.0" encoding="utf-8" ?>

<Mapping

xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS"

xmlns:cdm="urn:schemas-microsoft-com:windows:storage:mapping:CS"

cdm:Space="C-S">

<EntityContainerMapping

cdm:CdmEntityContainer="NorthwindLib.Northwind"

cdm:StorageEntityContainer="NorthwindLib.Target.dbo">

</TableMappingFragment>

</EntityTypeMapping>

</TableMappingFragment>

</EntityTypeMapping>

</TableMappingFragment>

</EntityTypeMapping>

</EntitySetMapping>

</TableMappingFragment>

</EntityTypeMapping>

</EntitySetMapping>

</EndProperty>

</EndProperty>

</AssociationSetMapping>

</EntityContainerMapping>

</Mapping>

Currently I am getting this error message:
ERROR:File D:ADO.NETvNextCTP/Samples/CSharp/LINQtoEntities/bin/Debug/NorthwindLib.cs.msl
Problem in Mapping Fragment(s) starting at line(s) (17): Must specify mapping for all key properties (Products.ProductID) of entity set Products

P.s.:
To run the example in your previous post I had to insert one line of code:

var products = from p in db.Products
orderby p.Category.CategoryName
select p;

foreach (Product p in products) {
p.CategoryRef.Load();
Console.WriteLine("{0}\t{1}", p.Category.CategoryName, p.ProductName);
}

Thanks again for your help!

nakrian at 2007-10-7 >

# 3

I just wanted ask if someone is still working on my question. I would really appreciate your help. Thanks.

nakrian at 2007-10-7 >

# 4

Hi nakrian –

The issue Entity Services has with your scenario, is that in Entity Services, Entities need a unique key to define their identity and trying to define an Entity encompassing tables with different keys is not supported. The best way to solve that problem in the Entity Model today is to define relationships as Atul has noted above.

In your scenario, you need to de-normalize the data you want to represent in your entity. The most straight forward dway to do that, would be to create a view in your database made up of a query over both of those tables and mapping the Entity to that view.

Another approach to the scenario would be to leverage stored procedures for mapping information in those tables to a specific Entity. We’re planning on building support for stored procedures in our stack in a coming beta.

We’ll also note your scenario for defining an Entity, in much the same way you’d define a View in an underlying database, and weigh that against other functionality we’re trying to build into V.Next.

DanDosenMSFT at 2007-10-7 >

# 5

Adding to Dan's analysis. Yes. That is the issue here. If an entity is "split" across multiple tables, we require the split be done on the primary key of the entity (i.e., the entity's key must map to the primary keys of those tables)

Also, we do require that every Table Mapping Fragment map the table and entity's key (that is the error you are seeing).

AtulAdya-MSFT at 2007-10-7 >

# 6

To further elaborate on this...

The notion of Entity Splitting that we support is currently constrained to a 1-1 relationship in the underlying relational store. We have, thus far, constrained that 1-1 relationship to be consistent with the Entity Key of the Entity that we have defined (per Atul's comment... the entity key must map to the PK of the various tables).

Supporting Entity Splitting with the Product Category example is a little problematic today in that you are trying to model aggregation where the result looks like a 1-1 thing but really the underlying store has a 1-* relationship. We currently do not support such aggregation because the semantics of the round-trip of updates can become hairy. From a modeling perspective it is also a little odd...

- if a product contains its category do you still have a category entity?
- if you still have a category entity and the product contains some subset of the info which is the record of master for edits?
- if you still have a category entity and the product contains some subset of the info do you want to enforce consistency across the two representations?

At the end of the day, whithin our stack, the best route for this would likely be to use the relationship as Atul and Dan have suggested.

regards,

Tim M

TimMallalieu-MSFT at 2007-10-7 >

# 7

Has this been, or is it in the plans to be, addressed via any other method than an sp or a view? I am attempting to address a similar situation in that if I have an employee (which is stored in an employee table) and from an OO perspective the employee object contains its contact information (i.e. telephone number, etc) that is stored in a contact table, the business layer of the application only knows of the employee object and I would like to exchange the current DAL with a vNext generated DAL. Thus, the dot notation in the application isn't a posibility, can you assist in this?

JeannieC at 2007-10-7 >

Visual Studio Orcas Hot Topic

Visual Studio Orcas New Topic

Visual Studio Orcas

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Question about 'How to combine data from two tables into one EntityType?', No.699191,Posted by nakrian

How to combine data from two tables into one EntityType?

Visual Studio Orcas Hot Topic

Visual Studio Orcas New Topic

Visual Studio Orcas

Site Classified