ForEach Iteration - Query Not Executing For Subsequent Loops

[Christopher]

Hi all -

Apologies in advance if this comes across as a 101 type question.  I thought I was in good shape using OA's implementation of LINQ but I have an oddity that I'm not sure what to do about.  It may just be that I am coding incorrectly, so feel free to tell me so.

Here's the issue: I am attempting to retrieve all address entries for a specific person in our database, and then iterate through them.  I originally thought that this would work:

var obEPMAddressList = from e in ctxUIS.PS_UA_DPT_CARS_SADs
where e.PERSON_ID == obPerson.EmplID
select e;

//  Update what we have.
foreach (var obEPMAddress in obEPMAddressList)
{
       [do something]
}

But... the foreach iteration only picks up the very first entry and then drops out of the loop.

However, if I do this instead:

var obEPMAddressList = from PS_UA_DPT_CARS_SAD in ctxUIS.PS_UA_DPT_CARS_SADs
                       where PS_UA_DPT_CARS_SAD.PERSON_ID == obPerson.EmplID
                        select new
                        {
                            PS_UA_DPT_CARS_SAD.ADDR_FIELD1,
                            [... through n fields of interest ...]
                       };

//  Update what we have.
foreach (var obEPMAddress in obEPMAddressList)
{
       [do something]
}

...this works.  The first code snipped is based on the examples found at http://www.telerik.com/help/openaccess-orm/openaccess-feature-ref-linq-support-querying-query-types.html while the second is based on http://www.telerik.com/help/openaccess-orm/openaccess-feature-ref-linq-support-querying-query-result.html.

The kicker is that if I test the first statement outside of OpenAccess and in a tool such as LINQPad, it retrieves all records properly.  I'm wondering if I've just missed a finer point on the OA syntax here.  Thanks for any help!

-- Chris

[Thomas]

Hi Chris,

I think you have not missed anything. The question is what do you do in the loop? You can also try to transform the query result into a list with the .ToList() method if the result is small enough.

Please do not hesitate to contact us again.

Regards,
Thomas
the Telerik team

[Christopher]

Hi again,

Interesting question, and thank you for making me feel a bit better. 

As to what is in the loop, my intent for this particular example was to retrieve a batch of profile fields for our users and transform them for insertion into another object (and, ultimately, a different database table).  It's almost equivalent to an update...into or select...into TSQL statement, which I would be happy to do under LINQ though, to be honest, I've not considered that particular angle until now.  My for...each loop is more or less a holdover from my old iterative days.

I like my first code snippet better as I don't have to specify the fields I want to retrieve (since I want all of them anyway, like "select *"), though in both examples I still need to do some massaging of the data before it is stored in the new object/table.  I'll code using the second example if that is the only way to ensure that I will always get the full list of items, but it seems odd that I would have to go that route.

Thanks!

Chris

[Viktor Zhivkov]

Hello Christopher,

I believe Thomas was suggesting to you to alter the code like this:

01.var obEPMAddressList = (from e in ctxUIS.PS_UA_DPT_CARS_SADs
02.                 where e.PERSON_ID == obPerson.EmplID
03.                 select e).ToList();
04.
05.//  Update what we have.
06.foreach (var obEPMAddress in obEPMAddressList)
07.{
08.       [do something]
09.}

Where Line 03 has the significant call to ToList() method. This will execute your query immediately and will load the instances of your entity in memory. If the number of results is reasonably small this is a very clean solution to your problem. If you expect more than 500 items in the result set you may be better without the ToList() call since the amount of memory required will be smaller.

Normally your original code should work fine, so if it is possible can you post the whole foreach loop code so we can check if it interrupts the enumeration somehow. One potential suspect will be a call to SaveChanges method on the same OpenAccess context.

We are looking forward your feedback. All the best,
Viktor Zhivkov
the Telerik team