Hi Frans,

We have a database model (see SQL attached, removed some unnecessary fields) like this:

Kamer -(1-n)-> Plaatsslot -(1-n)-> Plaatstoewijzing

We're trying it to query the structure like this:

public void LlblgenJoinTest()
{
 var e = from k in _linqMetaData.Kamer 
         where k.Nummer == "B01"
         from ps in k.Plaatssloten
         from pt in ps.Plaatstoewijzingen
         select pt;

}

For some reason this causes wrong SQL to be generated (a CROSS JOIN?).

I'm using the latest LLBLGen build (04-07-2008 ).

This is the traced output:

: Initial expression to process:
value(SD.LLBLGen.Pro.LinqSupportClasses.DataSource2`1[CC.ECDGenie.Dal.EntityClasses.KamerEntity]).SelectMany(k => k.Plaatssloten, (k, ps) => new <>f__AnonymousType0`2(k = k, ps = ps)).SelectMany(<>h__TransparentIdentifier0 => <>h__TransparentIdentifier0.ps.Plaatstoewijzing, (<>h__TransparentIdentifier0, pt) => pt)
Method Enter: CreatePagingSelectDQ
Method Enter: CreateSelectDQ
Method Enter: CreateSelectDQ
Generated Sql query: 
    Query: SELECT DISTINCT [LPA_L3].[id] AS [Id], [LPA_L3].[plaatsslot_id] AS [PlaatsslotId] FROM ( [DEV_CC_ECDGenie].[dbo].[tb_ECDGenie_Kamer] [LPA_L1]  CROSS JOIN [DEV_CC_ECDGenie].[dbo].[tb_ECDGenie_Plaatsslot] [LPA_L2] )

Method Exit: CreateSelectDQ
Method Exit: CreatePagingSelectDQ: no paging.
TestCase 'CC.ECDGenie.Dal.UnitTests.LlblgenTests.LlblgenJoinTest'
failed: SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The multi-part identifier "LPA_L3.id" could not be bound.
The multi-part identifier "LPA_L3.plaatsslot_id" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.
    SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: The multi-part identifier "LPA_L3.id" could not be bound.
    The multi-part identifier "LPA_L3.plaatsslot_id" 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 "LPA_L3.id" could not be bound.
    The multi-part identifier "LPA_L3.plaatsslot_id" could not be bound.
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
    at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
    at System.Data.SqlClient.SqlDataReader.get_MetaData()
    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
    at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
       --- End of inner exception stack trace ---
    at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior)
    at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.ExecuteMultiRowRetrievalQuery(IRetrievalQuery queryToExecute, IEntityFactory2 entityFactory, IEntityCollection2 collectionToFill, IFieldPersistenceInfo[] fieldsPersistenceInfo, Boolean allowDuplicates, IEntityFields2 fieldsUsedForQuery)
    at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollectionInternal(IEntityCollection2 collectionToFill, IRelationPredicateBucket& filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
    at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
    D:\Projects\dotNet\Net3.5\CC.ECDGenie\MainLine\Dal\DatabaseSpecific\DataAccessAdapter.User.cs(51,0): at CC.ECDGenie.Dal.DatabaseSpecific.DataAccessAdapter.FetchEntityCollection(IEntityCollection2 collectionToFill, IRelationPredicateBucket filterBucket, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IPrefetchPath2 prefetchPath, ExcludeIncludeFieldsList excludedIncludedFields, Int32 pageNumber, Int32 pageSize)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteEntityProjection(QueryExpression toExecute)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.System.Linq.IQueryProvider.Execute(Expression expression)
    at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
    D:\Projects\dotNet\Net3.5\CC.ECDGenie\MainLine\Dal\CC.ECDGenie.Dal.UnitTests\LlblgenTests.cs(48,0): at CC.ECDGenie.Dal.UnitTests.LlblgenTests.LlblgenJoinTest()


0 passed, 1 failed, 0 skipped, took 1,75 seconds.

(I know I can rewrite the query in a different way so it works properly, so there's no rush

Please post your target query

Walaa wrote:

Please post your target query

I would expect a query similar to this:

select tw.* from dbo.tb_ECDGenie_Plaatstoewijzing tw 
    inner join dbo.tb_ECDGenie_Plaatsslot ps on tw.plaatsslot_id = ps.id
    inner join dbo.tb_ECDGenie_Kamer k on ps.kamer_id = k.id
    where k.nummer='B01'

Shouldn't you use something like:

var e = from k in _linqMetaData.Kamer 
         join ps in k.Plaatssloten on .....
         join pt in ps.Plaatstoewijzingen on ....
         where ...
         select ...

Walaa wrote:

Shouldn't you use something like:

var e = from k in _linqMetaData.Kamer 
         join ps in k.Plaatssloten on .....
         join pt in ps.Plaatstoewijzingen on ....
         where ...
         select ...

Wouldn't that be a bit double? I could do it like this:

var e = from k in _linqMetaData.Kamer 
         join ps in _linqMetaData.Plaatsslot on .....
         join pt in _linqMetaData.Plaatstoewijzing on ....
         where ...
         select ...

But I think querying the collection properties on entities already implicitly specifies the type of join, so why would I explicitly have to include them in my LINQ query?

Also, the easiest way to write it would be:

            var e = from pt in _linqMetaData.Plaatstoewijzing 
                    where pt.Plaatsslot.Kamer.Nummer == "B01"
                    select pt;

But I just guessed the other way around should also work, or do I assume incorrectly?

Nested from clauses in a linq query always lead to a cross join (as they result in a Selectmany call).

I'll check out the error as the query shouldn't give an error.

(ps, I think Walaa meant ...join _linqMetaData.Plaatssloten on ... join _linqMetaData.Plaatstoewijzing on ...

and then start with plaatstoewijzing and filter on the kamer. Though the property access method you use in your simplified form is better, as it doesn't run into the cross join. The nested from clauses are always resulting in a SelectMany call, which is always converted into a Cross Join. The query isn't correct though (a table is missing) so I'll look into that. Could be a bug in RelationCollection though... )

(edit). It's inside the linq provider. It's tricky though, I can't find the mistake yet.

Though you should follow this rule of thumb in general: start with the stuff you want to fetch and then add predicates/elements to limit that set.

Otis wrote:

Though you should follow this rule of thumb in general: start with the stuff you want to fetch and then add predicates/elements to limit that set.

Yes I normally do it this way, but apparently the other way felt natural to one of my coworkers, I'll point him out to this thread...

Thank you for your (as usual) very informational response

wtijsma wrote:

Otis wrote:

Though you should follow this rule of thumb in general: start with the stuff you want to fetch and then add predicates/elements to limit that set.

Yes I normally do it this way, but apparently the other way felt natural to one of my coworkers, I'll point him out to this thread...

Thank you for your (as usual) very informational response

The nested from's will lead in general to queries which can lead to less performance, so these should be avoided if possible.

I'm very close now (though I haven't checked what I broke yet ), there's still one alias which goes wrong. This is very tricky, and I don't know if it's solveable. The point is that the from ps in k.Plaatssloten line assigns an alias to Plaatssloten in k.Plaatssloten, though it also assigns an alias to the (different element!) ps in the resultprojection of that SelectMany call. It's not said that these two are related. So the k.Plaatssloten results in a correlation relation between k and plaatssloten, with aliases A and B, and the next from clause, from pt in ps.Plaatstoewijzingen, refers to the ps element in the projection of the previous from clause (as that's what it has to work with). Which has a different alias. Which doesnt work

Linq... never a dull moment

woei!

[Test]
public void GetAllOrderDetailsForCustomerChopsUsingCrossJoinAndRelatedProperties()
{
    using(DataAccessAdapter adapter = new DataAccessAdapter())
    {
        LinqMetaData metaData = new LinqMetaData(adapter);
        var q = from c in metaData.Customer
                where c.CustomerId == "CHOPS"
                from o in c.Orders
                from od in o.OrderDetails
                select od;

        int count = 0;
        int[] expectedOrderIds = new int[] { 10254, 10370, 10519, 10731, 10746, 10966, 11029, 11041 };
        foreach(var v in q)
        {
            count++;
            Assert.IsTrue(expectedOrderIds.Contains(v.OrderId));
        }
        Assert.AreEqual(29, count);
    }
}

Fixed in next build. (it was a haaaaaaaaaaaaaaaard nutt to crack, but it potentially covers a lot of scenario's, so it was worth it I think)

Otis wrote:

Linq... never a dull moment

Brilliant... Though I can honestly say I've never had a dull moment with your *Predicate classes either

Thanks for looking into this!

Can you take a moment to clarify this:

The nested from's will lead in general to queries which can lead to less performance, so these should be avoided if possible.

Is it drawing all to the client and then joining? Why is this have less performance? I'd never seen nested FROMs before, it does seem an easier way to write them, though I can see a concern for confusion.

a cross join is a cross product between the tables/views involved. This means that the resultset of the cross join has a lot of rows, as there's no filter used, the filter on that set is the where clause, to limit down the # of rows returned.

It's not always possible to optimized the cross join + where clauses in such a way that the cross join is already filtered on the fly: it depends on the optimizer in the DB used and also on the query and the # of rows in the tables involved.

An inner/left/right join can be tweaked with a proper filter in the ON clause so it's more optimal without relying on the optimizer if it figures it out eventually.

With the way Linq is designed, the user doesn't have that freedom, as the 'join' statement is dumb (has no real operators to choose from) and the nested from clauses are always leading to SelectMany() calls which lead to cross joins.