locked
Adding Joins to existing ObjectQuery RRS feed

  • Question

  • Can an existing object query be extended with additional joins before executing?

     

    Let's say I create an initial objectQuery with a block of eSQL.  Let's say I'm selecting employees of certain types, in certain roles, and I've joined several entitysets and have several predicates in the Where clause. 

     

    Now, I want to create a new objectQuery using the first one, where I add some additional joins, let's say to the address and phone number tables, so that I can project the employee name, address, and phone number. 

     

    Can I do that in multiple steps like that?  I know I could do it purely through string manipulation before I create the first object query, but can I do it after I've already created one?  Or is the From clause pretty much untouchable once it is in the first object query?  I don't see any objectQuery methods that would seem to let you do this.

     

    Regards,

    Bob Reader

     

     

     

    Friday, April 4, 2008 2:56 PM

Answers

  • Sorry I didn’t understand the original intent of the question.

     

    Before you proceed with using JOIN, please take a look at my Entity SQL Tip #1 (http://blogs.msdn.com/esql/archive/2007/11/01/EntitySQL_5F00_Tip_5F00_1.aspx). The point is: if there is a logical relationship that makes sense in your business domain, you should model a navigation property for it.

     

     

    I still want to close the loop on Join. As you’ve seen there is no Join() builder method on ObjectQuery. Notice that LINQ’s IQueryable doesn’t have a Join() method either.

     

    I see two possible ways to work that around:

    1.    Build a joint query using LINQ to Entities. (LINQ does that through a Cartesian product plus WHERE.):

    Code Snippet

    from q1 in objectQuery1

    from q2 in objectQuery2

    where q1.q1_key_prop = q2.q2_key_prop

     

     

     

    2.    Build a new ObjectQuery using the Entity SQL command text behind each of the joining queries:

    Code Snippet

    q3 = ctx.CreateQuery(@“SELECT q1, q2 FROM ({0}) AS q1 JOIN ({1}) AS q2 ON q1.q1_key_prop = q2.key_prop”, objectQuery1.CommandText, objectQuery2.CommandText)

     

     

    I recommend the first option. I haven’t tried the second one - my suggestion is based on the Entity SQL language rules.  

     

     

    Zlatko Michailov

    Program Manager, Entity Services

    Microsoft Corp.

    http://blogs.msdn.com/esql

     

    Friday, April 4, 2008 8:50 PM

All replies

  • Yes. Each builder method returns a new instance of ObjectQuery<T> so that you can invoke another builder method. You can assign those interim results to variables and branch off to different queries.

     

     

    Zlatko Michailov

    Program Manager, Entity Services

    Microsoft Corp.

    http://blogs.msdn.com/esql

     

    Friday, April 4, 2008 4:36 PM
  • Ok.  But how do I add another join?  I don't see a builder method for doing that.  I may be not understanding something fundamental here.  It seems like I need something like :

    OldQuery.From("Left Join Address As add On it.ID = add.ID  Left Join AddressType On .....")

    How do I continue stringing another series of joins onto and existing object query?

     

    Regards,

    Bob

     

    Friday, April 4, 2008 4:53 PM
  • Sorry I didn’t understand the original intent of the question.

     

    Before you proceed with using JOIN, please take a look at my Entity SQL Tip #1 (http://blogs.msdn.com/esql/archive/2007/11/01/EntitySQL_5F00_Tip_5F00_1.aspx). The point is: if there is a logical relationship that makes sense in your business domain, you should model a navigation property for it.

     

     

    I still want to close the loop on Join. As you’ve seen there is no Join() builder method on ObjectQuery. Notice that LINQ’s IQueryable doesn’t have a Join() method either.

     

    I see two possible ways to work that around:

    1.    Build a joint query using LINQ to Entities. (LINQ does that through a Cartesian product plus WHERE.):

    Code Snippet

    from q1 in objectQuery1

    from q2 in objectQuery2

    where q1.q1_key_prop = q2.q2_key_prop

     

     

     

    2.    Build a new ObjectQuery using the Entity SQL command text behind each of the joining queries:

    Code Snippet

    q3 = ctx.CreateQuery(@“SELECT q1, q2 FROM ({0}) AS q1 JOIN ({1}) AS q2 ON q1.q1_key_prop = q2.key_prop”, objectQuery1.CommandText, objectQuery2.CommandText)

     

     

    I recommend the first option. I haven’t tried the second one - my suggestion is based on the Entity SQL language rules.  

     

     

    Zlatko Michailov

    Program Manager, Entity Services

    Microsoft Corp.

    http://blogs.msdn.com/esql

     

    Friday, April 4, 2008 8:50 PM
  • Thanks for the suggestions.  Actually in Visual Basic there is a Join keyword that can be used in Linq statements.  But I'll try using sub-queries more often - I guess they would be particularly useful in place of outer joins - you just get nothing back in your select subquery. 

     

    Is it true that sometimes a Linq statement will not return an ObjectQuery object, even though you started with one, returning IQueriable instead?  I seem to remember running into this.  When does it do this?  And is it possible to cast it back to an ObjectQuery when this happens, or has it been turned into some other query object?

     

    Thanks for your great help.

    Bob

     

     

    Friday, April 4, 2008 11:30 PM
  • You can always cast a LINQ to Entities query to the non-generic ObjectQuery type. (It’s not public yet, but it’s coming with our next Beta.) You can get the native SQL and other “untyped” services through that type. To be able to cast to the generic ObjectQuery<T>, your LINQ to Entities query must be returning IQueryable<T> and T must be an entity type. If, on the other hand, your LINQ to Entities expression is projecting an anonymous type, you can’t cast that to the generic ObjectQuery<T>.

     

     

    Zlatko Michailov

    Program Manager, Entity Services

    Microsoft Corp.

    http://blogs.msdn.com/esql

     

    Saturday, April 5, 2008 12:59 AM