Groupjoin throws Index was outside the bound of the array
Joined: 18-May-2012
Joined: 18-May-2012
It's like an example from Jon Skeet's book C# in depth, which is LinqToSql:
from defect in context.Defects
join subscription in context.NotificationSubscriptions
on defect.Project equals subscription.Project
into groupedSubscriptions
select new {Defect = defect, Subscriptions = groupedSubscriptions}
The result you want is a list of Main Entities and with every Entity a list of Subentities. If there is another (nice) way of the same result, I'm also happy, but this seems like a standard way in Linq.
@David, the select creates an anonymous type. I also tried it with a class I created but also got same error. I tried many ways but didn't get any result.
The expected SQL will be complex, Jon Skeet calls it "hectic" 
@Otis: I thought I have seen a forum post from you where it worked on the Northwind database on friday morning. But I couldn't find it in the afternoon 
Edit: I Remembered the History functionality of the browser and I was referring to this article http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=13932&HighLight=1 but that has a .Count() and I already found out that this was working during my tests. That's much easier in SQL.
Joined: 18-May-2012
AlexanderM wrote:
I have found another way of getting the result I want. Using the fact that with LlblGen you can access entities which are related by Foreign Keys. The generated SQL looks efficient so that's good.
var poolEmps = (from pe in PoolEmployee where pe.PoolId == 1783 select new {PoolEmployee = pe, Properties = pe.PropertyOfEmployee}).ToList();So I can proceed, but I would still like to discuss if LlblGen should be able to support the join into construct.
It does support the join into construct, it doesn't support the into result in the final projection. The main reason is that specifying entities in the projection requires special code as we have two pipelines for fetching: a projection system and an entity fetch system. So you can use join into without problems, the object you specify after 'into' can't appear in the projection, that's the only limitation
Btw: Jon Skeet has the SQL in his book, I guess you have his book, am I coorect? Or should I enter the SQL?
I don't have that book, sorry. But your query can simply be specified as:
var poolEmps = (from pe in PoolEmployee
where pe.PoolId == 1783
join poe in PropertyOfEmployee on pe.Id equals poe.PoolEmployeeId
select new {PoolEmployee = pe, Properties = poe}).ToList();
the join into is mostly only used to specify 'DefaultIfEmpty' to specify left joins, for the rest you don't need it. Your workaround query will insert the join indirectly.
Joined: 18-May-2012
AlexanderM wrote:
Hello,
But your query can simply be specified as:
Code:
var poolEmps = (from pe in PoolEmployee where pe.PoolId == 1783 join poe in PropertyOfEmployee on pe.Id equals poe.PoolEmployeeId select new {PoolEmployee = pe, Properties = poe}).ToList();the join into is mostly only used to specify 'DefaultIfEmpty' to specify left joins, for the rest you don't need it. Your workaround query will insert the join indirectly.
This not the same as mine. Your query produces a normal inner join while a group join has all elements of the left sequence. I would call it an outer join but this page (http://msdn.microsoft.com/en-us/library/bb311040.aspx) says:
Therefore, the group join is still basically an inner-equijoin except that the result sequence is organized into groups.
Ah indeed. I didn't look into the type of the poe element, my assumptions were wrong indeed. The thing is I have no idea how to fetch the data of the hierarchical graph group join results requires, other than generating a flat projection with all rows in place and then creating the hierarchy from the rows retrieved (which can lead to a lot of duplicates). Our pipeline doesn't support that kind of hierarchical fetching, it can't reconstruct a hierarchy from a flat set; we do the equivalent with nested queries which result in 1 query per node, but not based on 1 single query. Maybe we'll add it in the future, for now this isn't supported, as it in general leads to inefficient queries and there are more efficient ways to fetch hierarchies.
EF supports this as their way to fetch hierarchies is by projecting a flat set to a hierarchy, which in general is less efficient (hence why their Include() calls are slow, it uses the same projection).
So in short: we do support groupjoin (SelectMany()) but only if it's used further inside the query (e.g. as a source for further querying inside the query), not as an element in the projection. Thanks for clarifying
Joined: 18-May-2012