Found another bug while Linqafying http://www.codeplex.com/RapidDevBookCode in my order search 2 form.

    private void Barf()
    {
      var query = from soh in AWHelper.MetaData.SalesOrderHeader
                      from customerAddress in soh.Customer.CustomerAddress
                      select soh;

      var x = query.ToList();
      //dgResults.DataSource = ((ILLBLGenProQuery)predicate).Execute<SalesOrderHeaderCollection>();
    }

When I ran it I got a SQL error; Here is the trace:

An exception was caught during the execution of a retrieval query: The multi-part identifier "LPLA_2.CustomerID" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception. ---> System.Data.SqlClient.SqlException: The multi-part identifier "LPLA_2.CustomerID" could not be bound.

SD.LLBLGen.Pro.LinqSupportClasses.NET35
    Assembly Version: 2.6.0.0
    Win32 Version: 2.6.08.0827

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource`1[AW.Data.EntityClasses.SalesOrderHeaderEntity]).SelectMany(soh => soh.Customer.CustomerAddress, (soh, customerAddress) => soh)
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT DISTINCT [LPA_L1].[SalesOrderID] AS [SalesOrderId], [LPA_L1].[RevisionNumber], [LPA_L1].[OrderDate], [LPA_L1].[DueDate], [LPA_L1].[ShipDate], [LPA_L1].[Status], [LPA_L1].[OnlineOrderFlag], [LPA_L1].[SalesOrderNumber], [LPA_L1].[PurchaseOrderNumber], [LPA_L1].[AccountNumber], [LPA_L1].[CustomerID] AS [CustomerId], [LPA_L1].[ContactID] AS [ContactId], [LPA_L1].[SalesPersonID] AS [SalesPersonId], [LPA_L1].[TerritoryID] AS [TerritoryId], [LPA_L1].[BillToAddressID] AS [BillToAddressId], [LPA_L1].[ShipToAddressID] AS [ShipToAddressId], [LPA_L1].[ShipMethodID] AS [ShipMethodId], [LPA_L1].[CreditCardID] AS [CreditCardId], [LPA_L1].[CreditCardApprovalCode], [LPA_L1].[CurrencyRateID] AS [CurrencyRateId], [LPA_L1].[SubTotal], [LPA_L1].[TaxAmt], [LPA_L1].[Freight], [LPA_L1].[TotalDue], [LPA_L1].[Comment], [LPA_L1].[rowguid] AS [Rowguid], [LPA_L1].[ModifiedDate] FROM ( [AdventureWorks].[Sales].[SalesOrderHeader] [LPA_L1]  INNER JOIN [AdventureWorks].[Sales].[CustomerAddress] [LPA_L2]  ON  ( [LPLA_2].[CustomerID] = [LPA_L2].[CustomerID]))

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.

Yep, I got that exception.

But, What is the intention of the query? What do you want to achieve here? seems like you should use LINQ2LLBL PrefetchPaths

daelmo wrote:

Yep, I got that exception.

But, What is the intention of the query? What do you want to achieve here? seems like you should use LINQ2LLBL PrefetchPaths

The query is a simplification of the one I was using where customerAddress was used to filter the results set e.g.

    private void Barf()
    {
     var query = from soh in AWHelper.MetaData.SalesOrderHeader
                     from customerAddress in soh.Customer.CustomerAddress
                     where customerAddress.Address.PostalCode == "some Postal Code"
                     select soh;

     var x = query.ToList();
     //dgResults.DataSource = ((ILLBLGenProQuery)predicate).Execute<SalesOrderHeaderCollection>();
    }

If your really that curious about the context look at searchWorker_DoWork in: http://www.codeplex.com/RapidDevBookCode/SourceControl/FileView.aspx?itemId=547332&changeSetId=38205

At first I thought the same as David that you should try LINQ2LLBL PrefetchPaths.

If your really that curious about the context look at searchWorker_DoWork in: http://www.codeplex.com/RapidDevBookCode/SourceControl/FileView.aspx?itemId=547332&changeSetId=38205

But after looking at that method, I think you are trying to fetch a single set of entities, and limit that set on related entities.

Would you please try the following code:

var query = from soh in AWHelper.MetaData.SalesOrderHeader
                     where soh.Customer.CustomerAddress.Address.PostalCode == "some Postal Code"
                     select soh;

Walaa wrote:

But after looking at that method, I think you are trying to fetch a single set of entities, and limit that set on related entities.

Exactly.

Walaa wrote:

Would you please try the following code:

var query = from soh in AWHelper.MetaData.SalesOrderHeader
                     where soh.Customer.CustomerAddress.Address.PostalCode == "some Postal Code"
                     select soh;

Won't compile. Customer - > CustomerAddress is 1 to many.

Then maybe you need to try it the hard way... with joins

Something like the following:

var query = from soh in AWHelper.MetaData.SalesOrderHeader
                     join c in Customer on c.xxx equals soh.xxx
                     join ca in CustomerAddress on ca.xxx equals c.xxx
                     join a in Address on a.xxx equals ca.xxx
                     where a.PostalCode == "some Postal Code"

Walaa wrote:

Then maybe you need to try it the hard way... with joins

Something like the following:

var query = from soh in AWHelper.MetaData.SalesOrderHeader
                     join c in Customer on c.xxx equals soh.xxx
                     join ca in CustomerAddress on ca.xxx equals c.xxx
                     join a in Address on a.xxx equals ca.xxx
                     where a.PostalCode == "some Postal Code"

That would probably work but I'll think I just wait till it's fixed 'LPLA_2].[CustomerID] =' when it should be 'LPA_L1].[CustomerID] =' - not far off.

will look into it.

(edit) seems like the exact same problem, but now with a SelectMany() query (nested froms): a relation isnt added to the main query.

Fixed it. See attached linq provider. (Debugbuild, some issue to edge-case any-method / inheritance pending)

Otis wrote:

Fixed it. See attached linq provider. (Debugbuild, some issue to edge-case any-method / inheritance pending)

Thanks Frans - Great work! Though, more issues to follow next week...

TomDog wrote:

Otis wrote:

Fixed it. See attached linq provider. (Debugbuild, some issue to edge-case any-method / inheritance pending)

Thanks Frans - Great work! Though, more issues to follow next week...

Could be. With linq... never a dull moment. It's a bit of a struggle to anticipate on every scenario possible in every expression handler, hence the glitches here and there. But we'll getting there

As promised I have more . Ive made the query a little more complicated, http://www.codeplex.com/RapidDevBookCode/SourceControl/FileView.aspx?itemId=547332&changeSetId=38617 ,

private void Barf()
    {
      var query = AWHelper.MetaData.SalesOrderHeader.AsQueryable();

      if (firstName != "")
        query = query.Where(soh => soh.Customer.Individual.Contact.FirstName.Contains(firstName));

      if (state != "")
        query = from soh in query
                from customerAddress in soh.Customer.CustomerAddress
                where customerAddress.Address.StateProvince.Name == state
                select soh;

      query = from soh in query select soh;
      var x = query.ToList();
    }

and now I'm getting another exception but only if both the where clauses are added. What i'm trying to achieve btw, is to only do the join if I need to.

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource`1[AW.Data.EntityClasses.SalesOrderHeaderEntity]).Where(soh => soh.Customer.Individual.Contact.FirstName.Contains(AW.Win.frmOrderSearch2, Text: Order Search 2.firstName)).SelectMany(soh => soh.Customer.CustomerAddress, (soh, customerAddress) => new <>f__AnonymousType0`2(soh = soh, customerAddress = customerAddress)).Where(<>h__TransparentIdentifier3 => (<>h__TransparentIdentifier3.customerAddress.Address.StateProvince.Name = AW.Win.frmOrderSearch2, Text: Order Search 2.state)).Select(<>h__TransparentIdentifier3 => <>h__TransparentIdentifier3.soh).Select(soh => soh)
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSubQuery
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ

Application_ThreadException: SD.LLBLGen.Pro.ORMSupportClasses.ORMRelationException: Relation at index 1 doesn't contain an entity already added to the FROM clause. Bad alias?
   at SD.LLBLGen.Pro.ORMSupportClasses.RelationCollection.PreprocessRelations()...

SD.LLBLGen.Pro.LinqSupportClasses.NET35.dll Version 2.6.8.827 SD.LLBLGen.Pro.ORMSupportClasses.NET20 Version 2.6.08.0828

I see that a generic flaw is made in the handling of EntityExpression instances in some cases: for example: for o in metaData.Order where o.Customer.CompanyName == ..

here o.Customer is a correlationrelation, and it is stored in the correlationrelation property in EntityExpression. ADDITIONAL relations after that are added to the relationstouse property to reach further down the path. This correlationrelation isn't always added to the final query.

We already planned a code-review today to find these situations and fix them.

When I fix this particular case, it produces a query. I still have a problem with casting Customer to individual as that in theory shouldn't work (customer can also be a Store) (in my version individual is a subtype of customer) though it looks like the issue there caused by the same fault.

So I hope to have a structurally fixed library later today.

     var query = AWHelper.MetaData.SalesOrderHeader.AsQueryable();

     if (firstName != "")
        query = query.Where(soh => soh.Customer.Individual.Contact.FirstName.Contains(firstName));

     if (state != "")
        query = from soh in query
                from customerAddress in soh.Customer.CustomerAddress
                where customerAddress.Address.StateProvince.Name == state
                select soh;

This query has the same issue as I described in the other thread: a join is present twice: Namely: 1) from soh in query from customerAddress in soh.Customer.CustomerAddress and 2) soh.Customer.CustomerAddress.

This gives an interesting problem: which one to use? As it is now, the second one ends up being used, causing the whole first derived table (with the first where) to be abandoned, as it's first in the list of relations to process.

You should use a different construction altogether, instead of this. What you want is that you obtain, if state is specified, the salesorderheaders which have a related customeraddress with statename==the state specified.

So you should use Any.

q = q.Where(soh => soh.Customer.CustomerAddressCollection.Any(ca => ca.Address.StateProvince.Name == "Arizona"));

or applied to your specific code:

 var query = AWHelper.MetaData.SalesOrderHeader.AsQueryable();

 if (firstName != "")
    query = query.Where(soh => soh.Customer.Individual.Contact.FirstName.Contains(firstName));

 if (state != "")
        query = query.Where(soh => soh.Customer.CustomerAddress.Any(ca => ca.Address.StateProvince.Name == state));

Any and also Contains are often the solution to multiple times the same join in the same query. The code above fails, but that's due to an error in propagating relations upwards which is currently under review.

(edit) fixed the propagation of relations upwards.

Rewrote the relations handling internally. More bugs are now fixed, and it's now done structured and generic, so it should solve problems in the future. I hope to have a new temp build ready for you to test either within a couple of minutes or tomorrow morning

I've attached a temp build for linq provider and orm support classes as both are needed to fix all the issues you ran into.

I'm pretty confident this build will be better suited against the various multi-hop related entity queries you're writing Please let me know if it still fails (you can post in this thread)

Otis wrote:

I'm pretty confident this build will be better suited against the various multi-hop related entity queries you're writing Please let me know if it still fails (you can post in this thread)

Actually I did manage to get it to fail(with a SQL error) by adding a second joined filter:

      if (state != "")
       // query = query.Where(soh => soh.Customer.CustomerAddress.Any(ca => ca.Address.StateProvince.Name == state));
        query = from soh in query
                from customerAddress in soh.Customer.CustomerAddress
                where customerAddress.Address.StateProvince.Name == state
                select soh;

      if (cityName != "")
      {
        //query = query.Where(soh => soh.Customer.CustomerAddress.Any(ca => ca.Address.City == cityName));
        query = from soh in query
                from customerAddress in soh.Customer.CustomerAddress
                where customerAddress.Address.City == cityName
                select soh;
      }

but using 'Any' like you suggested worked and produced more readable SQL so Im happy

Having said that I don't know how to use .Any in this situation - it makes my head hurt.

      if (countries.Count > 0)
      {
        query = from soh in query
                from customerAddress in soh.Customer.CustomerAddress
                where countries.Contains(customerAddress.Address.StateProvince.CountryRegion.Name)
                select soh;
      }

http://www.codeplex.com/RapidDevBookCode/SourceControl/FileView.aspx?itemId=547332&changeSetId=38768

The main culprit is the nested from vs. the chain of joins through the hops, which are the same. The nested froms cause a cross-join, but the chain of joins (through the hops over the related entities) don't. What you want is simply colliding everything together through the hops, not through the nested froms.

Like this query:

query = from soh in query
                from customerAddress in soh.Customer.CustomerAddress
                where countries.Contains(customerAddress.Address.StateProvince.CountryRegion.Name)
                select soh;

What you want is limiting the sales order headers on the ones which have customers which are in the countries specified. so: from soh in query where soh.Customer.CustomerAddress.Any( ca=>countries.Contains(ca.Address.StateProvince.CountryRegion.Name)) select soh;

here you don't have the double join problem. I'm not saying that the nested froms aren't a good approach, it is just a confusing query: what do you want, as a developer? A join over the from clauses, or keep the chain ?

Perhaps I'm wrong though, I mean: query = from soh in query from customerAddress in soh.Customer.CustomerAddress

should mean: salesorderheader join customer join customeraddress, am I correct? Though joins require ON clauses, which aren't available in a SelectMany() as that's a cross join, there aren't selectors given, so no ON clause can be produced. Effectively this means that the two from clauses, the two sides of the Selectmany and thus the cross join, should be ignored, and the left side should be ignored, and the right side should be used. (as that would produce SalesOrderheader join customer join customeraddress.)

I'll think about this some more, the problem is though that the right side (soh.Customer.CustomerAddress) isn't hard-tied to the left side, it has the same type and a correlation relation to Customer, but that's about it. So when to decide what to do is a bit of a blur to me at the moment....

Otis wrote:

What you want is limiting the sales order headers on the ones which have customers which are in the countries specified. so: from soh in query where soh.Customer.CustomerAddress.Any( ca=>countries.Contains(ca.Address.StateProvince.CountryRegion.Name)) select soh;

Yes thats right - thanks, that was the .Any version I was after - don't know why I couldn't work it out myself - anyway sorted.

Otis wrote:

query = from soh in query from customerAddress in soh.Customer.CustomerAddress

should mean: salesorderheader join customer join customeraddress, am I correct?

Spot on, I think you can assume for all of the examples I've given everywhere that all I want to do is generate ON clauses(or equivalent) without having to specify the join explicitly - as I've said before I figure I shouldn't have to since it's already defined in the designer.

I have managed to make it work in more occasions. The one thing I can't fix (as I don't know a solution to this) is this kind of stuff:

this works now:

var q = from customer in metaData.Customer
        from ca in customer.CustomerAddressCollection
        from soh in customer.SalesOrderHeaderCollection.DefaultIfEmpty()
        where ca.AddressId != null
        where soh.SalesOrderId == null
        select customer;

this doesn't

var q = from customer in metaData.Customer
        from ca in customer.CustomerAddressCollection
        where ca.AddressId != null
        from soh in customer.SalesOrderHeaderCollection.DefaultIfEmpty()
        where soh.SalesOrderId == null
        select customer;

The thing is that the 'where' is misplaced: the branch doesn't have a projection, but still ... it does, as Microsoft's linq designers smoked too much weed at a given day and designed it like when the last expression in a chain already has a projection, the projection of a select is crammed into that one (so you don't know if the 'where' is a subquery together with the from above it, or if it's not.). As the subquery can't be created because the projection is useless, the stuff has to be added to the main join... small problem: the relations in the defaultifempty point to the subquery as a set (so they have the set alias as the alias to refer to, not the real aliases inside them, as tables inside a subquery are unknown to tables outside a subquery...

As this is really an edge case and the where can be moved down (similar to order by etc.) to make it work, I'll spend time on the other issues now.

Spot on, I think you can assume for all of the examples I've given everywhere that all I want to do is generate ON clauses(or equivalent) without having to specify the join explicitly - as I've said before I figure I shouldn't have to since it's already defined in the designer.

Agreed, though you can use Contains and Any/All in combination with Where as well, to avoid these messy mixed from clauses altogether, and build upon the relations defined in the designer.

(edit) the sql error I get with your original query: query = from soh in query from customerAddress in soh.Customer.CustomerAddress where countries.Contains(customerAddress.Address.StateProvince.CountryRegion.Name) select soh;

is also very tricky. The problem is that the part customerAddress.Address.StateProvince.CountryRegion.Name is already present in the query in the outer scope. (in the where clause and therefore in the main query). This means that re-refering to the same part shouldn't lead to the same joins being added again. How to tell them apart is not really doable. (at least I wouldn't know how)

I can't fix this too. Luckily there exist more efficient workarounds for this edge case.

Perhaps a better example(though still a stupid one) of my use of the left join is this to fetch the CustomerId, SalesOrderId, AddressId's of all customers which have addresses and may have orders

var q2 = from customer in AWHelper.MetaData.Customer
               from ca in customer.CustomerAddress
               from soh in customer.SalesOrderHeader.DefaultIfEmpty()
               select new { customer.CustomerId, ca.AddressId, soh.SalesOrderId };

so I'm not filtering on the left joined entity just want a field from it my result set (or not as the case maybe). The more I think about it what I am mainly evaluating is whether to use Linq-projecting-on-to-Anonymous-Types instead of Type-Lists. Currently Type-Lists are way out ahead though I would like to use the Linq option, if only so I can go Linq-all-the-way.

That query should now work.

For the linq provider it's a bit of a struggle what to pick, the join over the two from clauses or the join over the source of the from clause... I have it sorted for most cases, but I still don't have a 100% idea that I fully understand how to solve this properly. This is mainly caused by the urge to avoid cross joins altogether: a nested from is a cross join though the second from fetches from a related entity, the 'join' to hop to that related entity is necessary for the cross join to give the proper results (thus to be used for the WHERE clause), which means I can also convert it into an INNER join and an ON clause

This is of course easy, till the second from has multiple hops... oh well...

Otis wrote:

That query should now work.

Cool - Is there a new assembly available somewhere?

BTW I just tried to reproduce the 'LINQ to SQL - Filtered Outer Joins' sample in LinqPad

from c in Customers
from p in c.Purchases.Where (p => p.Price > 1000).DefaultIfEmpty()
select new
{
    c.Name,
    p.Description,
    Price = (decimal?) p.Price
}

with this:

var q4 = from customer in AWHelper.MetaData.Customer
               from soh in customer.SalesOrderHeader.Where(soh => soh.SalesOrderId < 10).DefaultIfEmpty()
               select new {customer.CustomerId, soh.SalesOrderId};
      var x = q4.ToList();

and got this: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryConstructionException: Unexpected expression type 'Query' found as source of DefaultIfEmpty(). Can't continue

A new build was posted wednesday

What should it do in that case? DefaultIfEmpty() is actually only useful if you want a leftjoin. But where do you want that leftjoin?

Looking at the query, I don't know why defaultifempty() is there.

Sure I can wrap the Query in a derived table, but... where should the leftjoin be? Between the two from clauses ? Or between: customer.SalesOrderHeader ?

It ONLY makes sense between the two from clauses. But... that's a cross join... so over which ON clause should the left-join be? The customer.SalesOrderheader is wrapped inside a query...

I've struggled hard to not make this 'where' a full subquery, and pull everything into the join, but it always failed in some form or another as a query with a projection has to be in a separate query... but... for example the select many has a projection... (with 3 nested froms, where the first two have a where).

Otis wrote:

A new build was posted wednesday

Ah the customer download page - got it.

Otis wrote:

Sure I can wrap the Query in a derived table, but... where should the leftjoin be? Between the two from clauses ? Or between: customer.SalesOrderHeader ?

customer.SalesOrderHeader. If you look at the output in LINQPad(made a bit more readable):

SELECT Sales.Customer.CustomerID, Sales.SalesOrderHeader.SalesOrderID AS AddressID
FROM Sales.Customer LEFT OUTER JOIN Sales.SalesOrderHeader ON Sales.SalesOrderHeader.SalesOrderID < 10 AND Sales.SalesOrderHeader.CustomerID = Sales.Customer.CustomerID

which, to my mind, pretty much fits what the comprehension syntax query looks like. To look at it another way, how else would this query (or any query with a filter in a left join ON clause) be generated? (again without manually joining).