Linq, emulating joins, and the Include method

Question

Hello,

I'm looking into an issue that is related to... http://stackoverflow.com/questions/416847/join-and-include-in-entity-framework

Basically the following query returns the list of "Property" objects the current user has permissions ( ACLs ) to view.

IQueryable<Property> currPropList 
                = from p in ve.Property
                                .Include("phyAddress")
                                .Include("Contact")
                  from a in ve.ACLs
                  from u in ve.Account
                  from gj in ve.ObjectGroupJoin
                  where u.username == currUsername              // The username
                        && (a.Account.id == u.id                // The ACLs
                            && a.objType == (int)ObjectType.Group)
                        && (gj.ObjectGroup.id == a.objId        // The groups
                            && gj.objId == p.id)                // The properties
                  select p;

The query returns the correct list of properties and in large works fine.

But the "Include" calls in the linq query above does not load the objects. If I call "Load()" explicitly after the LINQ query then the objects load.

The related SO question suggested that there may be a conflict between the "Include" call and the where clause. How can that be the case?

But at any rate, how can I restructure this query to load the "phyAddress" and "Contract" members? Specifically, I'd only like to load the members on returned objects, not all the "phyAddress" and "Contact" objects in the database.

Thanks.

Edit

I've tracked down the issue to the use of multiple from clauses

This works...

IQueryable<Property> currPropList 
            = from p in ve.Property
                            .Include("phyAddress")
              select p;

And the "phyAddress" member is loaded.

But this doesn't work...

IQueryable<Property> currPropList 
            = from p in ve.Property
                            .Include("phyAddress")
              from a in ve.ACLs
              select p;

Basically the Include call is ignored when there are multiple from clauses. Does anyone know of a work around for this?

Edit 2

One workaround is to cast the IQueryable result as a ObjectQuery and get the include off of that. But I would like to prevent the second roundtrip to the database I am assuming this causes.

Eg. This works....

IQueryable<Property> currPropList 
        = ((from p in ve.Property
          from a in ve.ACLs
          select p) as ObjectQuery<Property>).Include("phyAddress");

Is there a way to do this with only a single query?

Edit 3

No second query because of deferred execution [ http://blogs.msdn.com/charlie/archive/2007/12/09/deferred-execution.aspx. So edit 2 would be the solution.

Answer 1

This is a known issue with Include... if you do something that changes the shape of the query (i.e. from from) then the Include is lost there are simple enough workarounds though:

1. you can wrap the include around the query, see Tip 22 - How to make include really include.

2. or you can get everything you need in the select clause and let relationship fixup do the job for you. i.e.

   var x = from p in ve.Property from a in ve.ACLs select new {p,p.phyAddress};

   var results = x.AsEnumerable().Select(p => p.p);

Now results is an enumeration of property entities, but each one has it's phyAddress loaded, as a side-effect of the initial request for the phyAddress, and EF's relationship fixup.

Hope this helps

Alex

Answer 2

Read this tip:

http://blogs.msdn.com/alexj/archive/2009/06/02/tip-22-how-to-make-include-really-include.aspx

It should solve your problems.