How to filter data using Include and Where?

Question

 

Please look at the following code:

var miportal = new AdventureWorksEntities();

 

 

// one to many relationship

 

var result = miportal.AddressType

.Include(

"CustomerAddress")

.Where(at => at.CustomerAddress.Any(ca => ca.CustomerID == 2));

I want to return only one CustomerAddress object, but 701 are returned!

On further investigation, I found out that the record which belongs to CustomerID == 2 has the AddressTypeID of 3 and there are 701 CustomerAddress rows in the table with the AddressTypeID of 3.

My question is that I need to return only 1 CustomerAddress object, how can I write this query to do so?

(just to mention, I don't want the following code

var result = miportal.CustomerAddress

.Include(

"AddressType")

.Where(ca => ca.CustomerID == 2);

 

)

Your help is much appreciated.

Answer 1

Is it your goal to just return a CustomerAddress entity?  Or do you want to return AddressType entities with a filtered set of related CustomerAddress entities?

My impression is that you want to return AddressType entities which have a CustomerAddress whose CustomerID == 2 but even though that AddressType may have multiple CustomerAddresses, you only want the one CustomerAddress that matches your condition.  Is that right?

As you have discovered the code you wrote filters the AddressType entities but then includes every CustomerAddress for each AddressType that it returns.  Include always works that way.  It returns all related entities for a particular navigation property without looking at the predicate for the outer query at all.  If you want to return just the subset, then you should not use include and instead either:

1) Do multiple queries rather than one--one query which returns the AddressType and another which returns the CustomerAddress.  This is the simplest code to write, but may not be the most efficient.  To do this, change your code above to this:

var result = miportal.AddressType.Where(at => at.CustomerAddress.Any(ca => ca.CustomerID == 2));
var ca = miportal.CustomerAddress.Where(ca => ca.CustomerID == 2);
foreach (var at in result)
{
    at.CustomerAddress.Attach(ca);
    // do whatever other processing.
}

2) Rewrite your query so that you return an anonymous type with both bits of information so that you can do this in one query and then assemble the related entities:

var result = from at in miportal.AddressType
                  where at.CustomerAddress.Any(ca => ca.CustomerID == 2)
                  select new { AT = at, CA = at.CustomerAddress.Where(ca => ca.CustomerID == 2) };
foreach (var anonType in result)
{
    var at = anonType.AT;
    at.CustomerAddress.Attach(anonType.CA);
    // do whatever other processing;
}

- Danny

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 2

Daniel, many thanks for your answers. That's exactly what I meant.

After spending several hours of my weekend investigating this I found the solution 1 you just mentioned but as you said it's not efficient since it has to make a seperate call to database for every attach.

Do you think this feature will be added to Entity Framework 4.0 to be able to filter the Include results in one go? (There needs to be an efficient way to do this)

I found another way to start the object query with the "many" side of the relationship first:

var result = miportal.CustomerAddress
            .Include("AddressType")
            .Where (ca => ca.CustomerID == 2);

It returns the expected data but I needed the other direction in my scenario so I have to convert the result to the format I want using a Convert method like

ObjectQuery<AddressType> Convert(ObjectQuery<CustomerAddress> customerAddressObjectQuery)
{ ... }

Maybe this way is more efficient since it makes less database calls but at the same time it creates some costly objects when converting.

Not sure which way is the most efficient way.

Answer 3

A key thing to realize is that the Attach method doesn't actually make a database call--it only attaches whatever you pass it in memory.  So if you take the first method I showed above and add .ToList(); to the end of the var ca = ... line, then that database call will be made only once.  So your total number of database calls is 2 regardless of the number of AddressType objects your query returns.  The second method I showed actually will do it all in one query.  The Attach is just fixing up the database relationships.  The anonymous type object does have to be created, but this actually isn't as expensive as you might think.  The second method is actually very similar to what Include does under the covers.

Filtered includes are something we will very likely add to the EF in the future, but it won't make the next release (EF4).  There were just too many other things on the priority list that were prioritized before this--especially since it is possible to workaround the issue in a way that is basically just as efficient even though it isn't all that obvious at first.

- Danny

---

This posting is provided "AS IS" with no warranties, and confers no rights.

Answer 4

Daniel,

I am getting this error message now:

{"There is already an open DataReader associated with this Command which must be closed first."}

If I close the miportal (ObjectContext) connection, I won't be able to read the data in my foreach loop, right?

I have SQL Server 2000 and I checked the connection string and it has MultipleActiveResultSets=True (although I read that SQL Server 2000 may not support it?).

I tried creating a new ObjectContext (miportal2) and it gave me a seperate error message:

"The object at index 0 in the specified collection of objects is not attached to the same ObjectContext as source object of this EntityCollection."

How can I fix this issue? any clue?

Answer 5

Just found it; adding ToList() to the end of the first query resolves the issue.

I think you mentioned it too.

Cheers,