Now that I have my first problem solved (see http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7615), now I'm running into how to fetch related entities after having projected the resultset of a stored procedure into an entity collection. The following works, but involves looping through the collection:

                PrefetchPath2 pre = new PrefetchPath2((int)EntityType.UserBlogEntity);
                pre.Add(UserBlogEntity.PrefetchPathBlogTemplate);

                foreach (UserBlogEntity userBlog in col)
                {
                    adapter.FetchEntity(userBlog, pre);
                }

I've tried using techniques (i.e. the Context object) found in the following threads: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=3534 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=5467 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=4578 http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=6753

but nothing seems to work. If I get it to fetch without an error, it returns ALL of the UserBlog rows in the entire table, rather than only the ones I loaded into the collection from the stored procedure. Is there a way to establish a filter based on items in an established entity collection? In other words, I have an EntityCollection and I need to fetch related entities into that collection without refetching the collection itself. Or if it does refetch the collection, that's ok but I need it to be limited to the few that are already there without my having to specify the filter that produced them.

The underlying situation is that the filter is too complex for LLBLGen to do. Unless someone can show me how to construct a FieldCompareSetPredicate that will do the following:

SELECT * 
FROM UserBlog 
    INNER JOIN (

Select TOP 10 UserView.ContentGuid, count(*) as Views from UserView INNER JOIN UserBlog ON UserView.ContentGuid = UserBlog.ContentGuid where OfficialDate between dateadd(d, -30, getdate()) AND getdate() Group By UserView.ContentGuid Order by Views DESC

) dtUserView ON dtUserView.ContentGuid = UserBlog.ContentGuid 
ORDER BY Views DESC

I tried and almost got it working, but anyway that's another thread I guess. ;-)

Thanks for your interest, Nate

Is there a way to establish a filter based on items in an established entity collection? In other words, I have an EntityCollection and I need to fetch related entities into that collection without refetching the collection itself. Or if it does refetch the collection, that's ok but I need it to be limited to the few that are already there without my having to specify the filter that produced them

You left me one option

What I would do is: 1- Loop through the collection, put each entity PK in an array. 2- Fetch the entity collection again with the needed prefetchPath 3- But using a FieldCompareRangePredicate filter with the PKs array.

Walaa wrote:

You left me one option

What I would do is: 1- Loop through the collection, put each entity PK in an array. 2- Fetch the entity collection again with the needed prefetchPath 3- But using a FieldCompareRangePredicate filter with the PKs array.

Ok. That's a good method. (Better than my current workaround.) If you don't mind, would you kindly explain how you came to the conclusion that that method is the only option? In other words, what parts of what I'm trying to do are not supported by LLBLGen? I want to be clear on what the limitations of LLBLGen are versus something that's supported but that I've been unable to figure out.

Thanks! Nate

Actually it turns out that approach doesn't work unfortunately. I need the entities to be in the same order as the original query in the stored procedure (ordered by an count(*) aggregate). I tried using context and adding the collection to the context before (re)fetching the collection, but it creates duplicate rows by appending the enties to the collection for some reason instead of updating them.

Any ideas?

Thanks! Nate

ncook wrote:

Actually it turns out that approach doesn't work unfortunately. I need the entities to be in the same order as the original query in the stored procedure (ordered by an count(*) aggregate). I tried using context and adding the collection to the context before (re)fetching the collection, but it creates duplicate rows by appending the enties to the collection for some reason instead of updating them.

That last problem is fixed in the build that will be released tomorrow (saturday). At least it looks like the problem you ran into, it might not be the same. The core is that a collection has a flag: DoNotPerformAddIfPresent. This flag is set to false by default, so an Add will always add the same entity again. Setting that flag to true will prevent the add from adding the same entity again. A context will make sure the fields are updated, so the fields are updated, but you now get dupes, and you don't want that. This only happens with prefetch path + entitycollection + context used together in a fetch.

The query you're using is actually not that efficient, though it's more efficient than a subquery in the select list.

The main problem is that you're retrieving the # of views dynamically, each time the data is requested. You shouldn't do that. When the data is viewed, do a direct update on a count field in the content entity in the db, so that the field is increased with 1. You then just have to fetch the data when you're reading the content.

This forum uses the same approach. The # of posts you see under the user and the # of posts in the threads aren't retrieved dynamically, they're increased when the data changes. This means that the fetches are much faster, which is what you want because the data is often way more read than written.

I could rewrite the query you posted, by adding a ScalarQueryExpression to the selectlist in a dynamic list which would perform the count(*) for the # of views of the contentitem, however that's less efficient than doing a join with a select like you're doing now.

Otis wrote:

The main problem is that you're retrieving the # of views dynamically, each time the data is requested. You shouldn't do that. When the data is viewed, do a direct update on a count field in the content entity in the db, so that the field is increased with 1. You then just have to fetch the data when you're reading the content.

That's because you're thinking outside of the box! Good call. Also, thanks for information about the fix in the latest build as well as the ScalarQueryExpression method. I'll pass all of that on to my team lead.

Nate