Can I simulate a join in ES to help performance?

Reply

lloydphillips Posted: 09-12-2007 3:39 PM

rated by 0 users

Hi All,

I'm looking for the best way to effectively simulate a join in a query without having to loop through an itemdatabound for a datalist to return entities for each row to get the data I need. I saw a posting here asking about joins and I understand that ES doesn't generate them but Mike offered to give advice on the best way to work around the issue. The posting is at http://community.entityspaces.net/forums/thread/3647.aspx.

I have an images table, listings table and listingimages table. The imageslistings is a bridge between to the two. I am trying to bind a datalist with information from the listingimages table based on the listingId. I need the the ImageId from the listingimages table and the ImageFileType from the Image table based on the join of ImageId between the two tables. I'd normally just query the ImageId and then on the ItemDataBound load up each Image entity to get the data I need but surely that has got to hit performance and isn't the best way of doing it? Can this be done and am I correct in thinking it will hit performance if I did it in the ItemDataBound?

Also another query I had was the query caching in the quick reference at: http://www.entityspaces.net/portal/Documentation/QuickReference/tabid/87/Default.aspx#customload. This databind that I am going to be doing could be used quite frequently on postbacks so caching the query to help performance, I'm assuming, would be beneficial also. The example just seems to show how to build a query into the EmployeesQuery object. Am i correct in thinking I should then just add this object to cache like so:

Cache["EmployeesQuery"] = q;

And retrieve like so:

EmployeesQuery q = Cache["EmployeesQuery"];

Sorry if the latter is a little bit of a stupid/basic question.

Lloyd

P.S. Is there any way you can get the forum to email when responses have been added to the questions?

Reply

Mike.Griffin replied on 09-12-2007 4:45 PM

rated by 0 users

Let me re-read your post, in the meantime this is going to be the topic of tomorrow nights ES meeting (that is Joins, SubQuery's and Hierarchical Features).

For instance, look at this query.

SELECT E.* FROM Employees E LEFT JOIN Orders O 
ON E.EmployeeID = O.EmployeeID 
WHERE O.OrderID IS NULL 

Here's how we would express this in EntitySpaces (proposed)

EmployeesCollection coll = new EmployeesCollection(); 

EmployeesQuery e = coll.Query();  // short hand 
OrdersQuery o = new OrdersQuery(); 

e.LeftJoin(o).On(e.EmployeeID == o.EmployeeID).Where(o.OrderID.IsNull()); 

coll.Query.Load();

Reply

Mike.Griffin replied on 09-12-2007 4:55 PM

rated by 0 users

Maybe this will work.

1. Create a view that brings back the listing and it's associated image.
2. Generate an EntitySpaces entity set off of the View

or perhaps a short cut ...

1. Create a view that brings back the listing and it's associated image.
2. Use the QuerySource overide on the Query object like this.

ListingCollection coll = new ListingCollection();
coll.Query.es.QuerySource = "MyCustomView";
coll.Query.Load();

the problem perhaps with the second approach is you'll have no strongly typed property for the image, however, if you generate an entity off of the view you will. You can always get to the data however, via GetColumn("ImageFieldName") or you could add a property to your Listing custom class like this:

public byte[] Image
{
    get
    {
        return base.GetSystemByteArray("MyImageField");
    }
}

You could even take that QuerySource trick above and add a method to your ListingCollection called LoadWithImage() and encapsulate the QuerySource trick?

Do these come class ... 

Reply

pritcham replied on 09-12-2007 10:55 PM

rated by 0 users

Mike

Now that sounds fantastic - plain and simple (in terms of use!) and very clean - exciting!

Reply

lloydphillips replied on 09-12-2007 11:12 PM

rated by 0 users

I've opted for the view with the new Entity, works a treat. Looking forward to having a join available though, there has been a few times where I need it. Many thanks for taking the time to give me some options Mike, it's appreciated.

Is anyone able to expand on that caching query I mentioned?

Lloyd

Reply

Scott.Schecter replied on 09-13-2007 6:17 AM

rated by 0 users

At the top of the thread you should see a button with what appears to be a bell to me that says "Enable Email Subscription", that one is thread specific. For forum level subscriptions

http://community.entityspaces.net/forums/ForumSubscriptions.aspx