DevForce 2009: Issue with One-to-One RelationShips

Print Page | Close Window

Issue with One-to-One RelationShips

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2009
Forum Discription: For .NET 3.5
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=1560
Printed Date: 08-Apr-2025 at 11:02pm

Topic: Issue with One-to-One RelationShips

Posted By: ngoel
Subject: Issue with One-to-One RelationShips
Date Posted: 17-Nov-2009 at 8:09pm

Hi,

I am having issue with using Include function with ideablade.

I have got an Orders Entity which is linked to OrderDetails. There is a one to many relation between the two. The OrderDetail table is linked to the Product table on a one to one relationship on Product ID.

When retrieving the Orders, I am retrieving the OrderDetails using the Include function. It brings back all the Order and OrderDetails information, but when I try to retrive any value from the OrderDetails, it fires another trip to the server trying to get the related Product which I don't want. Is there any way to stop this?

If not, is there a way where any other way to retrieve information related to Order and OrderDetails in a single server trip?

Replies:

Posted By: GregD
Date Posted: 18-Nov-2009 at 10:47am

_em.Orders.Include("OrderDetails").Include("OrderDetails.Product").Include("OrderDetails.Product.Supplier")

Posted By: ngoel
Date Posted: 18-Nov-2009 at 12:58pm

Thanks Grieg,

Is there a way to, not do an Include on Product or Product Supplier , and still save the server trips...I don't need any information from Product or Product Supplier tables...OrderDetails have enough information for me......I have included the OrderDetails table and only trying to get information from the OrderDetails table....I am not sure why it tries to get information from the Product table when I have not asked for one. Shouldn't it only try to get the Product information when I try to get details from the Product table? I am writing dynamic queries and this is just one case. There are many cases like this.

Is there any other way prevent it from not going to server to get the Product and Product Suppier information?

Posted By: GregD
Date Posted: 19-Nov-2009 at 8:39am

In case you're not clear on this: in the code statement in my previous post I was simply illustrating that you can continue nesting your Includes to any desired depth. How far you go is up to you.

Suppose you issue this query:

_em.Orders.Include("OrderDetails")

No Products will be retrieved. However, if you immediately attempt to use the retrieved OrderDetails in some context that needs their associated Product (e.g., in a grid where one of the columns is a ComboBox displaying the related Product), then DevForce will of course go retrieve the required Product.

Posted By: ngoel
Date Posted: 22-Nov-2009 at 3:57pm

Thanks Greg

This is exactly what I thought it should do. I think what is happening is when there is a one to one relationship, it tries to get the details. e.g. each OrderDetails is linked to one Product (i.e there is a One to One Relationship). My include statement is

_em.Orders.Include("OrderDetails"),

but when I am trying to get information for OrderDetails it goes to the server and gets the Product associated with it ( even though, the information I am trying to get has nothing to do with the product).

However, if the relationship is one to many, it does not retrieve the information.

e.g. if we do _em.Orders.Include("Customer") and then try to get the Customer Information, it does not go and grap all the phone Numbers of the Customer as there a a One to many relationsip between customer and Phone Number tables.

I hope I made myself clear.

Cheers
Nitin

Posted By: GregD
Date Posted: 23-Nov-2009 at 3:02pm

ngoel:

I'm not able to reproduce what you're seeing. To one of our standard sample solutions (an AddingDeleting console app), I added the InternationalOrder type from NorthwindIB to the Entity Data Model. InternationalOrder is related 1-1 to Order.

I then run this method

    private void DoIt() {
      var query = _mgr.OrderDetails
        .Where(od => od.Quantity > 10)
        .Include("Order")
        .ToList();
      DisplayCacheContents();

    }

    private void DisplayCacheContents() {
      Console.WriteLine("OrderDetails: {0}",_mgr.OrderDetails.With(QueryStrategy.CacheOnly).Count());
      Console.WriteLine("Orders: {0}", _mgr.Orders.With(QueryStrategy.CacheOnly).Count());
      Console.WriteLine("InternationalOrders: {0}", _mgr.InternationalOrders.With(QueryStrategy.CacheOnly).Count());

      PromptToContinue();
    }

The WriteLine() calls report these as the cache contents:

OrderDetails: 1547
Orders: 718
InternationalOrders: 0

Posted By: ngoel
Date Posted: 23-Nov-2009 at 7:23pm

Thanks Greg.

Here is my code:

IEntityQuery<Order> query= em.OrderSet.Where(o=>o.OrderId==1);

IEnumerable<Order> results= query.Include("OrderDetail").Execute();

System.Diagnostics.Debug.Print("Before: " + em.OrderSet.With(QueryStrategy.CacheOnly).Count());

System.Diagnostics.Debug.Print("Before: " + em.OrderDetailSet.With(QueryStrategy.CacheOnly).Count());

System.Diagnostics.Debug.Print("Before: " + em.ProductSet.With(QueryStrategy.CacheOnly).Count());

foreach (Order o in results){

if(o.OrderDetail.Count > 0){

int n = o.OrderDetail[0].items;

}
}

System.Diagnostics.Debug.Print("After: " + em.OrderSet.With(QueryStrategy.CacheOnly).Count());

System.Diagnostics.Debug.Print("After: " + em.OrderDetailSet.With(QueryStrategy.CacheOnly).Count());

System.Diagnostics.Debug.Print("After: " + em.ProductSet.With(QueryStrategy.CacheOnly).Count());

The first lot of Debug statement printed:

Before: 1

Before: 0

The second lot of Debug statement printed:

After: 1

Even though the items I am trying to get is in the OrderDetail table, it went to the server and downloaded the related Product as well. I am not sure what I am doing wrong here. Did I miss something while setting up the relations?

Thanks

Nitin

Posted By: GregD
Date Posted: 25-Nov-2009 at 10:33am

"items" is a simple int property of OrderDetail? Or is it a computed property?

If a computed property, what is the calculation?

Posted By: ngoel
Date Posted: 25-Nov-2009 at 12:38pm

Its a simple int property. There are no calcuations involved.

I have tried this with different entity and even looked at the SQL profiler. The first query(with the include) brings back all the values for the item column from the database, but still it is making trip to the database and gets the related Product information when I try to get the values for item column.

Posted By: GregD
Date Posted: 25-Nov-2009 at 3:46pm

Why is the relationship 1-to-1 between OrderDetail and Product? I would expect it to be many-to-1. Otherwise any Product, once ordered, can't be ordered again.

There must be something funny going on in how the relationship between those two is modeled.

Posted By: ngoel
Date Posted: 25-Nov-2009 at 4:22pm

Greg,

This is just an example. I created this relationship because Orders, Customer Products are easy to understand. The Actual Entities are different. I have tried it with different entities and the problem is the same.

Is it working fine at your end?

Nitin

Posted By: ngoel
Date Posted: 25-Nov-2009 at 4:24pm

Greg,

Just to add to my previous reply, the problem is same for many to 1 relationship. The problem seems to be when there is just one related entity attached and not a collection.

Thanks

Posted By: GregD
Date Posted: 28-Nov-2009 at 11:00am

Nitin:

Why don't you grab the AddingDeleting solution from the Learning Resources at

LearningResources\040_BusObjPersistence\AddingDeleting\Samples\100COR\CodeCS

add the InternationalOrder to the model like I did (open Entity Data Model in the designer, choose Update Model from Database, and under Add Tables, check the InternationalOrder table), then add and run the method I included above to see if you get the same results I did. That'll tell us something, and you may see something in that model that's different from yours that will give you a clue. I just haven't seen the behavior you're describing.

Posted By: ngoel
Date Posted: 08-Dec-2009 at 8:12pm

Greg

I figured out what was the issue. Its working fine now. It was Custom Code in one of the GetInterceptor.

Thanks

Nitin

Posted By: GregD
Date Posted: 09-Dec-2009 at 12:49pm

Great, thanks for closing the loop, Nitin, and happy you're back in business!

Print Page | Close Window