This project is read-only.

Joins

Mar 14, 2010 at 2:37 AM

Hi,

I imported the Northwind database from SQL Server 2008 which has foreign keys defined.  When trying a linq statment I can't see fields from foreign tables.  It says in the documentaion that you don't have to do joins.  How do I make fields from foreign tables visible.  I wanted to see the Unit Price field from the Orderdetails table.  Or am I missing the point ? .... I did notice a OrderdetailsList property, am I supposed to use it.

OrdersCollection xxx = new OrdersCollection();

var ccc = from x in xxx select { x.wanted order details fields here }

Thanks,

Rob

Mar 14, 2010 at 3:51 AM

You select objects of a particular type, but this can be based on relationships. For example you can select Orders based on information in the related OrderDetails object. The following will select all Order objects that have at least one OrderDetail object with a UnitPrice > 10. You do not need define a join because this is already done in your model.

OrdersCollection ordersCollection = OrdersCollection.RunSelect(x=>x.OrderDetailEntity.UnitPrice > 10);

If you want to select OrderDetail objects based on on some parent Order property then you the same syntax.

OrderDetailCollection orderDetailCollection = OrderDetailCollection.RunSelect(x=>x.OrderEntity.SomeProperty > 37);

All of this code is executed rendered as SQL and executed on the server. Only the necessary rows are brought to the client. You can run LINQ on the object in memory after they are returned of course but this is manipulating in-memory objects not hitting the database.

OrdersCollection ordersCollection = OrdersCollection.RunSelect(x=>x.OrderDetailEntity.UnitPrice > 10);

var q = (from x in ordersCollection where x.Property == 5 select x);

Mar 14, 2010 at 5:38 PM

First off, thanks for your quick response I appreciate you are very busy and I am greatful.

My problem is that I want an ObservableCollection<T> where <T> is persistable and gets its results from many tables.  So, if I have a UI grid of orders (orders table), I would like to see the 'Unit RRP Price', that might come from the products table, in the grid/observablecollection  too, and all persistable.  It would be nice if custom views were persistable ....

It seems then I have a headache on my hands.  Do I need to create a custom persistable model by hand.  If so how would I go about this.

Mar 14, 2010 at 11:20 PM

Hi,

Don't worry about this, I think I'm chasing my tail a little.  I will work around by creating custom properties on my objects so that they store the foreign data ready for me to display and persist.

Thanks,

Rob