var qfail = (from soh in MetaSingletons.MetaData.SalesOrderHeader
                         from sod in soh.SalesOrderDetails
                         select new
                                    {
                                        soh.SalesOrderID,
                                        sod.SalesOrderDetailID,
                                        MetaSingletons.MetaData.Product.Where(p => p.ProductID == sod.ProductID).First().ProductModel.CatalogDescription,
                                        MetaSingletons.MetaData.Product.Where(p => p.ProductID == sod.ProductID).First().ProductModel.Name
                                    });

Gives an exception 'The multi-part identifier "LPLA_6.CatalogDescription" could not be bound.'

To repro run NestedQueryOnTotheSameEntityTwiceTest in BugsTest

This seems to come up before: http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=17758 http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=16159

There are a couple of other unrelated tests: NullExceptionTest and EmployeeIndividualOuterJoinTest which are also failing.

Which runtime libraries (ORMSupportClasses & LinqSupportClasses) version/build number are you using? ref: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7717

Walaa wrote:

Which runtime libraries (ORMSupportClasses & LinqSupportClasses) version/build number are you using? ref: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=7717

Both are 3.0.10.526

The real life query I am trying to get working is

var riskLikelihoodSeverityQuery = from rls in RiskLikelihoodSeverity
//join residual in RiskMatrix on new {rls.ResidualSeverityID,rls.ResidualLikelihoodID}  equals new {residual.SeverityID,residual.LikelihoodID} into residualMatrix
select new {                                        
    InitialRisk = (DataSingletons.MetaData.RiskMatrix.Where(rm => rm.SeverityID == rls.InitialSeverityID && rm.LikelihoodID == rls.InitialLikelihoodID)).First().RiskLevel.RiskLevel,
    ResidualRisk = (DataSingletons.MetaData.RiskMatrix.Where(rm => rm.SeverityID == rls.ResidualSeverityID && rm.LikelihoodID == rls.ResidualLikelihoodID)).First().RiskLevel.RiskLevel,
                        };

If I replace RiskLevel.RiskLevel with RiskLevelID it works.

please try the latest builds (06112010 of the linq provider and 06252010 ormsupportclasses (we forgot to update the date in the assemblyinfo file, the runtime build number is updated to 06252010)). 0526 is even before RTM if I'm not mistaken. On june 11th we fixed some alias issues you reported which are triggered by this query.

Otis wrote:

please try the latest builds (06112010 of the linq provider and 06252010 ormsupportclasses (we forgot to update the date in the assemblyinfo file, the runtime build number is updated to 06252010)). 0526 is even before RTM if I'm not mistaken. On june 11th we fixed some alias issues you reported which are triggered by this query.

Updated to 611 - no change.

Hmm... we'll see what we can dig up what's causing this.

The cause is the duplicate navigation on the First() result in the same scope. The current linq provider looks at it and sees both being in the same scope and uses the same aliases for both, causing major problems in this area.

In theory, it's right in this, the element returned by the duplicate navigation is the same. The problem is that it results in two scalar queries, and thus has to be seen as a separate element.

This is however very difficult, as the visitor walking the expression tree doesn't know when or how to make that decision: in THIS particular case, based on the surrounding elements in the tree, it should be seen as different elements, however in OTHER cases, it should be seen as the SAME element (and alias).

Is it possible to rewrite the query from the other side? I.e. instead of a join with two First() queries, start with the elements returned from the First() using query and filter that set using the join ? It's very unclear how we can solve this scoping issue as member access in general aren't separated scopes.

Otis wrote:

Is it possible to rewrite the query from the other side? I.e. instead of a join with two First() queries, start with the elements returned from the First() using query and filter that set using the join ?

I doubt it - this is the full query:

from r in Risk
                   join o in Occurrence on r.OccurrenceNo equals o.OccurrenceNo into occurrences
                   from o in occurrences.DefaultIfEmpty()
                         from cause in r.RiskCauses.DefaultIfEmpty()
                         from action in cause.Actions.DefaultIfEmpty()
                         from rls in r.RiskLikelihoodSeverities.DefaultIfEmpty()
                        orderby r.RiskNo descending
                        select new 
                                    {
                                    r.RiskNo,
                                    r.DateIdentified,
                        r.BusinessUnitAffected.OrgItemDescription,
                                    r.RiskOwner,
                                    Category = r.RiskCategory.ItemText,
                                    r.RiskDescription,
                                    r.EquipmentInvolved,
                                    EnteredBy = r.StaffMember.StaffMemberName,
                                    ReviewedBy = r.StaffMemberLastReviewed.StaffMemberName,
                                    r.LastReviewedDate,
                                    r.NextReviewDate,
                                  AssessmentDepartment = rls.OrganisationStructurePortal.OrgItemDescription,                                        
                                    CurrentSeverity = rls.InitialSeverity.SeverityRating,
                                    CurrentLikelihood =  rls.InitialLikelihood.LikelihoodRating,
                                    TargetSeverity  = rls.ResidualSeverity.SeverityRating,
                                    TargetLikelihood =  rls.ResidualLikelihood.LikelihoodRating,
                                    targetRisk = RiskMatrix.Where(rm => rm.SeverityID == rls.ResidualSeverityID && rm.LikelihoodID == rls.ResidualLikelihoodID).First().RiskLevel.RiskLevel,
                                    //CurrentRisk =(from rm in RiskMatrix where rm.SeverityID == rls.InitialSeverityID && rm.LikelihoodID == rls.InitialLikelihoodID select rm).First().RiskLevel.RiskLevel,
                                    ExistingDefencesControlsDescription = r.ExistingDefencesControls,
                                    Effectiveness= r.EffectivenessDescriptor.ItemText,
                                    RiskTrend = r.TrendDescriptor.ItemText,
                                    r.RiskType.RiskType,
                                    r.PotentialCurrent.PotentialCurrent,                                    
                                    ThreatError = (from rte in r.RiskThreatErrors select rte.HazardRiskDescriptor.ItemText).JoinAsString(),
                                    r.EnteredOn,
                                    r.Feedback,
                                    FeedbackBy = r.StaffMemberFeedback.StaffMemberName,
                                    FeedbackOn = r.FeedbackEnteredOn,
                                    r.OccurrenceNo,
                                    r.Occurrence.OccurrenceTitle,
                                    OccurrenceTypes = (from otc in o.OccurrenceTypeClassifications
                                                     select otc.OccurrenceType.OccurrenceDescription).JoinAsString(),   
                                    OccurrenceDateIdentified = (DateTime?)r.Occurrence.OccurrenceDateTime,
                                    r.Occurrence.Location,
                                    r.Occurrence.RegistrationMark,
                                    r.FindingNo,
                                    r.Finding.Finding,
                                    r.Finding.FindingCategory.FindingCategory,
                                    FindingDateIdentified = (DateTime?)r.Finding.DateDiscovered,
                                    FindingDepartment = r.Finding.Department.OrgItemDescription,
                                    r.Finding.ExtFindingReference,
                                    r.HazardNo,
                                    r.Hazard.HazardStatement,
                                    HazardCatagory = r.Hazard.ClassificationDescriptor.ItemText,
                                    HazardDateIdentified = (DateTime?)r.Hazard.DateIdentified,
                                    HazardDepartment =r.Hazard.BusinessUnitAffected.OrgItemDescription,
                                    HazardType = r.Hazard.PotentialCurrent.PotentialCurrent,
                                    r.RiskReview.Title,
                                    r.RiskReview.State,
    //                          RiskReviewDepartment = r.RiskReview.Department.OrgItemDescription,
                                    r.RiskReview.TargetStartDate,
                                    r.RiskReview.ActualStartDate,
                                    r.RiskReview.TargetEndDate,
                                    cause.CauseText,
                                    PersonOrg = cause.EventDescriptorPersonOrganisation.ItemText,
                                    CauseCategory = cause.EventDescriptorCauseCategory.ItemText,
                                    CauseItem = cause.EventDescriptor.ItemText, 
                                    action.ActionID,
                                    action.Action,
                                    action.ActionStatuses.ActionStatusText,
}

Executes until one of commented-out lines is enabled.

Ugh...

Indeed, that's quite the challenge. Well, I'll give it a shot on monday, but it might turn out it's very hard (read: undoable) to fix. I know what the cause is (same member gets the same alias, while it should get a different alias, and that code is present, but can't work if the duplicate members are in the same scope (which alias to pick?)), it's just a tough puzzle to find the answer for this, if there's one. I know it will be a multi-page method, but consider rewriting this into our own query api, although that might be cumbersome as well... but just in case we don't manage to fix this issue.

Otis wrote:

Well, I'll give it a shot on monday, but it might turn out it's very hard (read: undoable) to fix. I know what the cause is (same member gets the same alias, while it should get a different alias, and that code is present, but can't work if the duplicate members are in the same scope (which alias to pick?)), it's just a tough puzzle to find the answer for this, if there's one.

Well fingers crossed...

Otis wrote:

I know it will be a multi-page method, but consider rewriting this into our own query api, although that might be cumbersome as well... but just in case we don't manage to fix this issue.

This projection is for a CSV export(with alot more data) of the results of a search which a user is viewing in a grid. The 'Risk' in 'from r in Risk' is a query passed in which has the same filters as what was used to populate the grid. Is there any way to convert this filtered risk query into an predicate that I could use in a typelist fetch? I'm assume your doing something like that internally somewhere.

TomDog wrote:

Otis wrote:

Well, I'll give it a shot on monday, but it might turn out it's very hard (read: undoable) to fix. I know what the cause is (same member gets the same alias, while it should get a different alias, and that code is present, but can't work if the duplicate members are in the same scope (which alias to pick?)), it's just a tough puzzle to find the answer for this, if there's one.

Well fingers crossed...

I think I've found a way, will implement it on monday. In short it comes down to assigning a dedicated alias to a member of a select query if that member is an entity. The main problem is currently that it has to re-use the same alias with this situation:

from o in metaData.Order where... select new { o.Customer.CompanyName, o.Customer.ContactTitle};

the 'customer' is the same join, you don't want two times the same join. But your situation can be determined to be different from the one I wrote above as 'Customer' is a member of an entity, not a select query, while your query uses a member which IS a member of a select query.

Otis wrote:

I know it will be a multi-page method, but consider rewriting this into our own query api, although that might be cumbersome as well... but just in case we don't manage to fix this issue.

This projection is for a CSV export(with alot more data) of the results of a search which a user is viewing in a grid. The 'Risk' in 'from r in Risk' is a query passed in which has the same filters as what was used to populate the grid. Is there any way to convert this filtered risk query into an predicate that I could use in a typelist fetch? I'm assume your doing something like that internally somewhere.

If you use our own api, you can use a ScalarQueryExpression for those from x in ... select... .First().Member queries.

But let's see what I can do on monday.

After careful examination, we found out that the actual problem was that the Navigator specification after .First(), which means a join, is the actual cause of the problem combined with the duplicate aliases of the member. The duplicate aliases of the member are a problem here because the member implies a join and as such becomes part of the query it is a member access of, but as it gets an alias assigned to it in the first query, it is re-used in the second which fails.

We tried several options to fix this, and had it working but other queries failed due to a re-aliasing problem we introduced with the fix we tried out so we rolled it back. However, as we found the layout of the target query, it's possible to rewrite your query fragments so the query will work.

In our test query we had:

metaData.OrderDetail.Where(od => od.OrderId == o.OrderId).First().Product.CategoryId,
metaData.OrderDetail.Where(od => od.OrderId == o.OrderId).First().Product.ProductName

which failed like your query did.

However as the Product is joined to OrderDetail in the destination scalar query, we can also write the join here in a linq statement:

(
    from od in metaData.OrderDetail
    where od.OrderId == o.OrderId
    join p in metaData.Product on od.ProductId equals p.ProductId
    select p
).First().CategoryId,
(
    from od in metaData.OrderDetail
    where od.OrderId == o.OrderId
    join p in metaData.Product on od.ProductId equals p.ProductId
    select p
).First().ProductName

A little more verbose, but the end result is that it works as expected.

so your fragments:

targetRisk = RiskMatrix.Where(rm => rm.SeverityID == rls.ResidualSeverityID && rm.LikelihoodID == rls.ResidualLikelihoodID).First().RiskLevel.RiskLevel,
CurrentRisk =(from rm in RiskMatrix where rm.SeverityID == rls.InitialSeverityID && rm.LikelihoodID == rls.InitialLikelihoodID select rm).First().RiskLevel.RiskLevel,

should be written as a 'First()' call on the select on a join between an element from RiskMatrix and RiskLevel (I don't know which elements are in there) and on the element returned by First(), you should call the actual property (again RiskLevel)

The .Where clause becomes a where of the first 'from' statement in the query. As you use a lot of inheritance in this system it might be what I propose doesn't work for you or might require casts / OfType calls as well.

The core issue of the problem is this: metaData.OrderDetail.Where(od => od.OrderId == o.OrderId).First().Product.CategoryId,

results in:

select top 1 CategoryId
from (
    select top 1 ...
    from [Order Details] od inner join Product p on ...
    where od.OrderId = o.OrderId
)

while it should have been:

select top 1 CategoryId
from (
    (
        select top 1 ...
        from [Order Details] od 
        where od.OrderId = o.OrderId
    ) a inner join Product p on a.ProductId = p.ProductId 
)

The order details inner join product join is determined as an EntityRelation through the navigator. After things are more interpreted in the expression tree one side should be replaced with the derived table of a full select and the EntityRelation should be replaced with a DynamicRelation. The main problem is that an EntityRelation doesn't work with core types while the expressions do. So it was impossible to write code which could do the replacement at the point very late in the conversion to query api elements. Moving it higher up the chain would mean we would need to rewrite the expression tree with queries like the ones above, but that would be very hard too as not always are all elements known which are needed (for example I can't rewrite your snippets as I miss information)

I hope that this solves this query problem for you.

Yes that worked thanks, but there are a couple of other issues you may be interested in:

from r in Risk
 where r.RiskNo == "R317-10"
 from rls in r.RiskLikelihoodSeverities.DefaultIfEmpty()
 orderby r.RiskNo descending
select new 
{
  r.RiskNo,
  targetRisk = RiskMatrix.First(rm => rm.SeverityID == rls.ResidualSeverityID && rm.LikelihoodID == rls.ResidualLikelihoodID).RiskLevel.RiskLevel,
  CurrentRisk = RiskMatrix.Where(rm => rm.SeverityID == rls.ResidualSeverityID && rm.LikelihoodID == rls.ResidualLikelihoodID).Join(RiskLevel, rm => rm.RiskLevelID, rl => rl.RiskLevelID, (rm, rl) => rl).First().RiskLevel,
  FindingDepartment = r.Finding.Department.OrgItemDescription,
  HazardDepartment =r.Hazard.BusinessUnitAffected.OrgItemDescription,
  HazardType = r.Hazard.PotentialCurrent.PotentialCurrent,
  //RiskReviewDepartment = r.RiskReview.Department.OrgItemDescription,
  RiskReviewDepartment = DataSingletons.MetaData.OrganisationStructurePortal.First(osp=>osp.OrgItemID==r.BusinessUnitAffectedID).OrgItemDescription,
}

If I try and get the RiskReviewDepartment the simple way that is commented out I get 'Relation at index 7 doesn't contain an entity already added to the FROM clause. Bad alias' so I had to do the alternative way despite that working for the similar OrgItemDescription fields - something to do with RiskReview being in an inheritance hierarchy I guess. Also if remove the 'order by' I get a NullReferenceException in LLBLGenProProvider2.ExecuteEntityProjection - To repro run NullExceptionTest in BugsTest

The bad alias part... no idea what causes that. The navigation of the related entities all add relations to the main query, but as that one already has a join it might be it's not append-able in a way that a proper join can be constructed or that the set of joins already in the main query contain a derived table. It's cumbersome to determine how to tie a set or relations to another set of relations in some situations and it might be this is a situation where it goes wrong. There are still 2 or 3 bugs in the linq provider related to this, which we didn't have time to fix for RTM. They're still scheduled to be fixed in the coming months though. It however takes a lot of time to fix one of these as join related expression trees especially the ones which reproduce these kind of problems are complex beasts and fixing one thing often makes things keel over for another situation (as so many different situations create join queries and they all have to be taken into account... ). So for now, as these situations are rare, write the subqueries in the projection in the alternative way if they give problems.

The missing order by causing a NRE... that happens inside the code to obtain the factory for the type of the returned set, which is of course wrong, the query doesn't return an entity.

I don't know how it decided the projection is an entity projection, but it's of course wrong. Looking into this.

Fixed it (the crash due to lack of order by), it was a tough one, like most of the issues you report . Fixed in next build, which we hope to release later today.

Otis wrote:

Fixed it (the crash due to lack of order by), it was a tough one, like most of the issues you report .

I like to keep you on your toes , you must wince when you see me report new bugs, speaking of which I got this line from my query above wrong

RiskReviewDepartment = DataSingletons.MetaData.OrganisationStructurePortal.First(osp=>osp.OrgItemID==r.BusinessUnitAffectedID).OrgItemDescription

it should be

RiskReviewDepartment = DataSingletons.MetaData.OrganisationStructurePortal.First(osp => osp.OrgItemID == r.RiskReview.DepartmentID).OrgItemDescription

which of course blows up with 'The multi-part identifier "LPLA_10.Org_Item_ID" could not be bound.' If I try and rewrite

HazardDepartment =r.Hazard.BusinessUnitAffected.OrgItemDescription

the same way

HazardDepartment = DataSingletons.MetaData.OrganisationStructurePortal.First(osp => osp.OrgItemID == r.Hazard.BusinessUnitAffectedID).OrgItemDescription

it give a similar error. To repro run NestedQueryUsingFirst in BugsTest

On the bright side congrads on getting through to the final.

TomDog wrote:

Otis wrote:

Fixed it (the crash due to lack of order by), it was a tough one, like most of the issues you report .

I like to keep you on your toes , you must wince when you see me report new bugs, speaking of which I got this line from my query above wrong

It's just frustrating, linq in general. I know there will never be a day in the future when I'll say: "I fixed them all!". It's just trying to keep up and 'hope' things work out now. This is mainly due to the crufty way Linq expression trees work and how complex it is to convert them to SQL, as there's always some construct behind the corner which makes things fail.

RiskReviewDepartment = DataSingletons.MetaData.OrganisationStructurePortal.First(osp=>osp.OrgItemID==r.BusinessUnitAffectedID).OrgItemDescription

it should be

RiskReviewDepartment = DataSingletons.MetaData.OrganisationStructurePortal.First(osp => osp.OrgItemID == r.RiskReview.DepartmentID).OrgItemDescription

which of course blows up with 'The multi-part identifier "LPLA_10.Org_Item_ID" could not be bound.' If I try and rewrite

HazardDepartment =r.Hazard.BusinessUnitAffected.OrgItemDescription

the same way

HazardDepartment = DataSingletons.MetaData.OrganisationStructurePortal.First(osp => osp.OrgItemID == r.Hazard.BusinessUnitAffectedID).OrgItemDescription

it give a similar error. To repro run NestedQueryUsingFirst in BugsTest

Can't you rewrite it with a join query like I posted above? These navigator navigations all add relationships to the pack, which is cumbersome if there already has been a lot of stuff processed in that same set.

On the bright side congrads on getting through to the final.

That eases the linq pain a bit indeed

Otis wrote:

RiskReviewDepartment = DataSingletons.MetaData.OrganisationStructurePortal.First(osp => osp.OrgItemID == r.RiskReview.DepartmentID).OrgItemDescription

which of course blows up with 'The multi-part identifier "LPLA_10.Org_Item_ID" could not be bound.'

Can't you rewrite it with a join query like I posted above? These navigator navigations all add relationships to the pack, which is cumbersome if there already has been a lot of stuff processed in that same set.

I tried to before I posted but had mental block last night, these explict joins do my head in, but after trying again this morning this works so all good

RiskReviewDepartment = (from rr in RiskReview
    where r.WRNumber == rr.WRNumber && r.WRType == rr.WRType && r.WRPeriod == rr.WRPeriod
    join osp in OrganisationStructurePortal on rr.DepartmentID equals osp.OrgItemID
    select osp).First().OrgItemDescription
//RiskReviewDepartment = r.RiskReview.Department.OrgItemDescription

oh so much nicer if I could use navigators but so be it.