I'm trying to come up with the LLBL code to realize the SQL command below. To be honest I don't no where to start. Preferably I would like to perform a projection using the old fashion way (so no linq statements unfortunately )

SELECT T2.Id FROM Table1 left join Table2 T1 ON Table1.ForeignKey1 = T1.PKey left join Table2 T2 ON Table1.ForeignKey2 = T2.PKey WHERE T1.Id = 'XXXX'

Any help is much appreciated.

LLBL version: Version 4.0 using the adapter model on a SQL 2008 database.

An update...I've solved the problem partly.

I'v created two relations. One with and one witout an alias. Relations.Add(new EntityRelation(LinkedShopFields.LinkedShopKey, ShopFields.PKey, RelationType.OneToOne, true, null), "T2"); Relations.Add(new EntityRelation(LinkedShopFields.LinkedShopKey, ShopFields.PKey, RelationType.OneToOne, true, null));

I get an exception while executing the query. The ORM query exception tells me that the following query is executed:

SELECT [ReferenceData].[dbo].[LinkedShop].[ShopKey], [ReferenceData].[dbo].[Shop].[ShopId], [ReferenceData].[dbo].[Shop].[Name], [ReferenceData].[dbo].[LinkedShop].[LinkedShopKey], [ShopId]

FROM (( [ReferenceData].[dbo].[LinkedShop]

INNER JOIN [ReferenceData].[dbo].[Shop] [LPA_F1]
ON [ReferenceData].[dbo].[LinkedShop].[LinkedShopKey]=[LPA_F1].[PKey])

INNER JOIN [ReferenceData].[dbo].[Shop]
ON [ReferenceData].[dbo].[LinkedShop].[ShopKey]=[ReferenceData].[dbo].[Shop].[PKey])

Can anybody tell me where this '[LPA_F1]' is coming from? Why is my T2 alias gone?

LPA_F1 is used instead of your "T2". But that shouldn't cause the problem. What is the exception message and stack trace that you are getting and what is the relevant code that produces it?

daelmo wrote:

LPA_F1 is used instead of your "T2". But that shouldn't cause the problem. What is the exception message and stack trace that you are getting and what is the relevant code that produces it?

The following exception message appears: "An exception was caught during the execution of a retrieval query: The multi-part identifier \"S1.ShopId\" could not be bound.\r\nThe multi-part identifier \"S1.Name\" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."

The stacktrace is as follows: at SD.LLBLGen.Pro.ORMSupportClasses.RetrievalQuery.Execute(CommandBehavior behavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Query\RetrievalQuery.cs:line 151 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 1574 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Dictionary2 typeConvertersToRun) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 1735 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, QueryParameters parameters) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 1697 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, IEntityFields2 fields, IRelationPredicateBucket filter, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 1656 at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, IEntityFields2 fields, IRelationPredicateBucket filter, Int32 maxNumberOfItemsToReturn, Boolean allowDuplicates) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v4.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 1592 at DataAccess.Common.CustomDataAccessAdapter.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, IEntityFields2 fields, IRelationPredicateBucket filter, Int32 maxNumberOfItemsToReturn, Boolean allowDuplicates) in d:\Projects\Cow Hills ROS Trunk\DataAccess\Common\CustomDataAccessAdapter.cs:line 2002 at Projections.ProjectionsManager.FetchProjectionImpl[T](ProjectionParams`1 projectionParams) in d:\Projects\Cow Hills ROS Trunk\Projections\Projections\ProjectionsManager.cs:line 64

The query executed is: SELECT [ReferenceData].[dbo].[LinkedShop].[ShopKey] AS [Field0], [ReferenceData].[dbo].[Shop].[ShopId] AS [Field1], [ReferenceData].[dbo].[Shop].[Name] AS [Field2], [ReferenceData].[dbo].[LinkedShop].[LinkedShopKey] AS [Field3], [S1].[ShopId] AS [Field4], [S1].[Name] AS [Field5] FROM (( [ReferenceData].[dbo].[LinkedShop]

INNER JOIN [ReferenceData].[dbo].[Shop] [LPA_S1]
ON [ReferenceData].[dbo].[LinkedShop].[LinkedShopKey]=[LPA_S1].[PKey])

INNER JOIN [ReferenceData].[dbo].[Shop]
ON [ReferenceData].[dbo].[LinkedShop].[ShopKey]=[ReferenceData].[dbo].[Shop].[PKey])

As you can see I'm using a projection. We've created a few helper methods to make things a little easier for us. The code is as follows:

var projectionParams = new ProjectionParams<LinkedShopInfo>();

projectionParams.FieldMappings.Add(LinkedShopFields.ShopKey, dto => dto.ShopKey); projectionParams.FieldMappings.Add(ShopFields.ShopId, dto => dto.ShopId); projectionParams.FieldMappings.Add(ShopFields.Name, dto => dto.ShopName);

projectionParams.FieldMappings.Add(LinkedShopFields.LinkedShopKey, dto => dto.LinkedShopKey); projectionParams.FieldMappings.Add(new EntityField2("ShopId", "[S1]", typeof(string)), dto => dto.LinkedShopId); projectionParams.FieldMappings.Add(new EntityField2("Name", "[S1]", typeof(string)), dto => dto.LinkedShopName);

projectionParams.Filter = new RelationPredicateBucket();

projectionParams.Filter.Relations.Add(new EntityRelation(LinkedShopFields.LinkedShopKey, ShopFields.PKey, RelationType.OneToOne, true, null), "S1"); projectionParams.Filter.Relations.Add(new EntityRelation(LinkedShopFields.ShopKey, ShopFields.PKey, RelationType.OneToOne, true, null));

public class LinkedShopInfo { public int ShopKey { get; set; } public string ShopId { get; set; } public string ShopName { get; set; }

        public int LinkedShopKey { get; set; }
        public string LinkedShopId { get; set; }
        public string LinkedShopName { get; set; }

}

I hope this information helps. Thanks in advance.

Why there seem to be 2 schema names used (VTrunk_EuroshoeReferenceDataAvance_NL & ReferenceData)?

Walaa wrote:

Why there seem to be 2 schema names used (VTrunk_EuroshoeReferenceDataAvance_NL & ReferenceData)?

There shouldn't and there isn't . Sorry, my mistake. I took out the redundant catalog name to make things more readable. I've the edited the previous post.

You should use DynamicRelation for creating relations on the fly. Also keep track on the aliases you specify, you have to do that on the projection as well.

You can't use link you said, but I think you can use queryspec. It's much easier to do than the low-level api and it's build on top of the low level api and recommended for new queries instead of the lowlevel api. We won't discontinue the lowlevel api of course (as it is the foundation of linq and queryspec) but new features will be added to queryspec and linq first and likely not to the lowlevel api if we don't have to.

I wrote your query in queryspec:

var qf = new QueryFactory();
var q = qf.Create()
            .From(qf.LinkedShop
                    .LeftJoin(qf.Shop.As("LS")).On(LinkedShopFields.LinkedShopKey==ShopFields.PKey.Source("LS))
                    .LeftJoin(qf.Shop.As("S")).On(LinkedShopFields.ShopKey==ShopFields.PKey.Source("S)))
            .Select(()=>new LinkedShopInfo()
                        {
                            ShopKey = LinkedShopFields.ShopKey.ToValue<int>(),
                            ShopId = ShopFields.ShopId.Source("S").ToValue<int>(),
                            ShopName = ShopFields.Name.Source("S").ToValue<string>(),
                            
                            LinkedShopKey = LinkedShopFields.LinkeddShopKey.ToValue<int>(),
                            LinkedShopId = ShopFields.ShopId.Source("LS").ToValue<int>(),
                            LinkedShopName = ShopFields.Name.Source("LS").ToValue<string>(),
                        }
                    );
var results = new DataAccessAdapter().FetchQuery(q);

you can use the queryspec code with your own lowlevel api code, you don't have to rewrite all your queries you already have, you can mix them in 1 code base if you have to.

QueryFactory is part of the FactoryClasses namespace. You have to add the queryspec namespaces to your own code in the 'using' section to have the extension methods available.

That's pretty cool shit. I definitely need to read more about the Queryspec possibilities.

My manager is a little bit old fashioned. He thinks Linq is something spooky .

Thanks for your help.

Heh Linq is different from queryspec however, queryspec is designed to be more predictable (the query written in code looks like the query in sql, unlike linq). Good luck