Hello,

Working on a query that contains a couple CorrelatedOver pieces to capture some value I need to roll up into a projection. The query works great, no exceptions, but I was trying to alias the result of the correlated field, but it always comes back "LLBLV_1".

Line 11 below is the culprit:

t.NameToIndexMap Count = 15 [0]: {[SourceAdvisoryId, 0]} [1]: {[Title, 1]} [2]: {[CreateDate, 2]} [3]: {[CreateUser, 3]} [4]: {[UpdateDate, 4]} [5]: {[UpdateUser, 5]} [6]: {[Description, 6]} [7]: {[Hidden, 7]} [8]: {[AdvisoryStatusTypeId, 8]} [9]: {[SourceId, 9]} [10]: {[SourceUrl, 10]} [11]: {[LLBLV_1, 11]} [12]: {[SourceAdvisoryFooId, 12]} [13]: {[AdvisoryDate, 13]} [14]: {[AdvisoryId, 14]}

Here is the query I have constructed:

            var q = qf.SourceAdvisory
                .Select(() => new 
                {
                    // Base
                    SourceAdvisoryId = SourceAdvisoryFields.SourceAdvisoryId.ToValue<int>(),
                    Title = SourceAdvisoryFields.Title.ToValue<string>(),
                    CreateDate = SourceAdvisoryFields.CreateDate.ToValue<DateTime>(),
                    CreateUser = SourceAdvisoryFields.CreateUser.ToValue<string>(),
                    UpdateDate = SourceAdvisoryFields.UpdateDate.ToValue<DateTime?>(),
                    UpdateUser = SourceAdvisoryFields.UpdateUser.ToValue<string>(),
                    Description = SourceAdvisoryFields.Description.ToValue<string>(),
                    Hidden = SourceAdvisoryFields.Hidden.ToValue<bool?>(),
                    AdvisoryStatusTypeId = SourceAdvisoryFields.AdvisoryStatusTypeId.ToValue<int>(),
                    SourceId = SourceAdvisoryFields.SourceId.ToValue<int>(),
                    SourceUrl = SourceAdvisoryFields.SourceUrl.ToValue<string>(),
                    MatchTypeName = qf.SourceAdvisoryProduct
                        .CorrelatedOver(SourceAdvisoryProductEntity.Relations.SourceAdvisoryEntityUsingSourceAdvisoryId)
                        .Select(() => qf.SourceAdvisoryProductMasterAsset
                            .CorrelatedOver(SourceAdvisoryProductMasterAssetEntity.Relations.SourceAdvisoryProductEntityUsingSourceAdvisoryProductId)
                            .Select(() => MatchTypeFields.Name.ToValue<string>())
                            .As("MatchTypeName")
                            .From(qf.SourceAdvisoryProduct
                                .InnerJoin(SourceAdvisoryProductEntity.Relations.SourceAdvisoryProductMasterAssetEntityUsingSourceAdvisoryProductId)
                                .InnerJoin(MatchTypeEntity.Relations.SourceAdvisoryProductMasterAssetEntityUsingMatchTypeId)
                                )
                            .Where(SourceAdvisoryProductMasterAssetFields.SpSiteId.Like(siteId))
                            .Limit(1)
                            .ToSingleResult()
                        )
                        .As("MatchTypeName")
                        .From(qf.SourceAdvisoryProduct
                            .InnerJoin(SourceAdvisoryProductEntity.Relations.SourceAdvisoryProductMasterAssetEntityUsingSourceAdvisoryProductId))
                        .Where(SourceAdvisoryProductMasterAssetFields.SpSiteId.Like(siteId))
                        .ToResultset()
                        ,

                    // ...

                    //  Child - we need to alias the inherited SourceAdvisoryFoo.SourceAdvisoryId to SourceAdvisoryFoo.SourceAdvisoryFooId
                    //  since SourceAdvisoryId has already been an added key, we'll later use this to determine what type to create in the WithProjector block.
                    SourceAdvisoryFooId = SourceAdvisoryFooFields.SourceAdvisoryId.As("SourceAdvisoryFooId").ToValue<int>(),
                    AdvisoryDate = SourceAdvisoryFooFields.AdvisoryDate.ToValue<DateTime>(),
                    AdvisoryId = SourceAdvisoryFooFields.AdvisoryId.ToValue<DateTime>(),
                }
                )
                .WithProjector(t => 
                    {
                        SourceAdvisoryMetaData s = new SourceAdvisoryMetaData { };

                        if (t.Get<int>("SourceAdvisoryFooId") > 0)
                        {
                            s = new SourceAdvisoryFooMetaData
                            {
                                SourceAdvisoryId = t["SourceAdvisoryFooId"].ToNullable<int>() ?? 0,
                                AdvisoryDate = t["AdvisoryDate"].ToNullable<DateTime>() ?? DateTime.MinValue,
                                AdvisoryId = t["AdvisoryId"].ToNullable<int>() ?? 0,
                            };
                        }

                        //  Common base type fields
                        s.SourceAdvisoryId = t["SourceAdvisoryId"].ToNullable<int>() ?? 0;
                        s.Title = t["Title"] as string;
                        s.CreateDate = t["CreateDate"].ToNullable<DateTime>() ?? DateTime.MinValue;
                        s.CreateUser = t["CreateUser"] as string;
                        s.UpdateDate = t["UpdateDate"].ToNullable<DateTime>();
                        s.UpdateUser = t["UpdateUser"] as string;
                        s.Description = t["Description"] as string;
                        s.Hidden = t["Hidden"].ToNullable<bool>();
                        s.AdvisoryStatusTypeId = t["AdvisoryStatusTypeId"].ToNullable<int>() ?? 1;
                        s.SourceId = t["SourceId"].ToNullable<int>() ?? 0;
                        s.SourceUrl = t["SourceUrl"] as string;
                        s.MatchTypeName = this.getMatchTypeNameFromList(t[11] as List<string>);
                        
                        return s;
                    }
                )
                ;

Niether of the ".As("MatchTypeName")" clauses are honored. My reasoning for this is that in the WithProjector block, I would like to use t["MatchTypeNam"], rather than t[11].

Please let me know if more clarity is required.

Thanks!!!

I'm not sure what is the involved field and where in your code you are doing that. I think it's this line:

SourceAdvisoryFooId = SourceAdvisoryFooFields.SourceAdvisoryId
     .As("SourceAdvisoryFooId").ToValue<int>(),

If that is true, then you are creating another field for the projection, not really aliasing the original field/expresssion. That is fine, you get two fields, one with the LLBLGen built aliasing and one with your own alias, right?. If you want to alias the original field, I think you should alias it where you write the correlated query.

I'm sorry... the alias for SourceAdvisoryFooId works just fine, not concerned about that. The one that isn't fine is MatchTypeName... which is coming back as "LLBLV_1".

Where are you saying I should alias it? I've added it in two places (shown in code below), but neither seems to work.

[11]: {[LLBLV_1, 11]} should be {[MatchTypeName, 11]}

I can reproduce it if I do this (using Northwind):

var q = qf.Customer
    .Select(() => new
    {
        Id = CustomerFields.CustomerId.ToValue<string>(),
        Company = CustomerFields.CompanyName.ToValue<string>(),
        ShipCities = qf.Order                   
            .CorrelatedOver(OrderEntity.Relations.CustomerEntityUsingCustomerId)                    
            .Select(() => OrderFields.ShipCity.ToValue<string>())                   
            .As("ShipCities")
            .ToResultset()                  
    })
    .WithProjector(c =>
        {                   
            var toReturn = new CustomerMetadata();                  
            toReturn.Id = (string)c["CustomerId"];
            toReturn.Company = (string)c["CompanyName"];
            toReturn.ShipCities = c["ShipCities"] as List<string>;                  
            return toReturn;
        });

//...

public class CustomerMetadata
{
    public string Id { get; set; }
    public string Company { get; set; }
    public List<string> ShipCities { get; set; }
}

Exception

System.ArgumentOutOfRangeException: The name 'ShipCities' is unknown.

SD.LLBLGen.Pro.QuerySpec.ProjectionRow.GetIndexForName(String name) in C:\Program Files (x86)\Solutions Design\LLBLGen Pro v3.5\SourceCode\Frameworks\LLBLGen Pro\RuntimeLibraries\QuerySpec\BuildingBlocks\ProjectionRow.cs: line 161 SD.LLBLGen.Pro.QuerySpec.ProjectionRow.get_Item(String name) in C:\Program Files (x86)\Solutions Design\LLBLGen Pro v3.5\SourceCode\Frameworks\LLBLGen Pro\RuntimeLibraries\QuerySpec\BuildingBlocks\ProjectionRow.cs: line 182 NW.LLBL.MSSQL.Adapter.Tests.QuerySpecTests.< AliasInCorrelatedQueryWithProjector>b__0(ProjectionRow c) in C:\Users\David\Dev\LLBLGen\IntegratedTests\NW.LLBL\MSSQL\Adapter\v35\Tests\QuerySpecTests.cs: line 98 SD.LLBLGen.Pro.QuerySpec.DataProjectorToObjectList1.<>c__DisplayClass2.<.ctor>b__0(Dictionary2 m, Object[] r) in C:\Program Files (x86)\Solutions Design\LLBLGen Pro v3.5\SourceCode\Frameworks\LLBLGen Pro\RuntimeLibraries\QuerySpec\Projectors\DataProjectorToObjectList.cs: line 85 SD.LLBLGen.Pro.QuerySpec.DataProjectorToObjectList1.AddRowToResults(IList projectors, Object[] rawProjectionResult) in C:\Program Files (x86)\Solutions Design\LLBLGen Pro v3.5\SourceCode\Frameworks\LLBLGen Pro\RuntimeLibraries\QuerySpec\Projectors\DataProjectorToObjectList.cs: line 123 SD.LLBLGen.Pro.QuerySpec.DataProjectorToObjectList1. SD.LLBLGen.Pro.ORMSupportClasses.IGeneralDataProjector.AddProjectionResultToContainer(List1 valueProjectors, Object[] rawProjectionResult) in C:\Program Files (x86)\Solutions Design\LLBLGen Pro v3.5\SourceCode\Frameworks\LLBLGen Pro\RuntimeLibraries\QuerySpec\Projectors\DataProjectorToObjectList.cs: line 108 SD.LLBLGen.Pro.QuerySpec.HierarchicalFetcherBase.MaterializeCurrentLevelData(List1 valueProjectors, IGeneralDataProjector projector, List1 currentLevelData) in C:\Program Files (x86)\Solutions Design\LLBLGen Pro v3.5\SourceCode\Frameworks\LLBLGen Pro\RuntimeLibraries\QuerySpec\BuildingBlocks\HierarchicalFetcherBase.cs: line 70 SD.LLBLGen.Pro.QuerySpec.HierarchicalFetcherAdapter. ExecuteHierarchicalValueListProjection(DynamicQuery toExecute, IRelationPredicateBucket additionalFilter) in C:\Program Files (x86)\Solutions Design\LLBLGen Pro v3.5\SourceCode\Frameworks\LLBLGen Pro\RuntimeLibraries\QuerySpec\AdapterSpecific\HierarchicalFetcherAdapter.cs: line 166 SD.LLBLGen.Pro.QuerySpec.Adapter.AdapterExtensionMethods. FetchQueryAsHierarchicalProjection(IDataAccessAdapter adapter, DynamicQuery query) in C:\Program Files (x86)\Solutions Design\LLBLGen Pro v3.5\SourceCode\Frameworks\LLBLGen Pro\RuntimeLibraries\QuerySpec\AdapterSpecific\AdapterExtensionMethods.cs: line 422 SD.LLBLGen.Pro.QuerySpec.Adapter.AdapterExtensionMethods.FetchQuery[TElement]( IDataAccessAdapter adapter, DynamicQuery1 query) in C:\Program Files (x86)\Solutions Design\LLBLGen Pro v3.5\SourceCode\Frameworks\LLBLGen Pro\RuntimeLibraries\QuerySpec\AdapterSpecific\AdapterExtensionMethods.cs: line 242 NW.LLBL.MSSQL.Adapter.Tests.QuerySpecTests.AliasInCorrelatedQueryWithProjector() in C:\Users\David\Dev\LLBLGen\IntegratedTests\NW.LLBL\MSSQL\Adapter\v35\Tests\QuerySpecTests.cs: line 102

I think the .ToResulset() use its own aliasing mechanism even if you specify .As() before. And as you are using WithProjector. What you can do is select by index in your WithProjector lambda, instead of the name. Anyway I will check whether this is by design or if it should be fixed.

It's actually a result of using the feature in a wrong way. I'll first describe what happens and then ask what you want us to do about it. I'll use David's example.

The WithProjector() method defines the projector to produce an end result for a row produced by the query. However, the query already has a projector, defined in the Select method.

The projector in the Select clause is used to produce the 2 queries: one for the main query and one for the nested query in the projection (the ShipCities subquery). The system then uses the two queries to build the resultset, together with the projector in the Select method, at least that's what it is designed to do. You however replace the projector with a new one, using WithProjector. The argument of WithProjector replaces the actual projector lambda to use with the built queries, so it's not used for the queries itself. This gives the mismatch.

The typical way to do it is to simply not use WithProjector if you have a Select with a lambda already, as it's redundant. WithProjector is designed to be used with queries which don't have a typed projector lambda, e.g. because the Select method called on the query simply defined a list of entity fields.

So there are several things we can do: 1) honor the alias in the subquery. This will make your query work, but it will hide the fact you use a redundant lambda and should move the code now in WithProjector to the lambda in Select 2) throw an exception when WithProjector is called on a query which already has a lambda projector defined. 3) ignore WithProjector calls if a lambda is already defined on it.

I lean towards 1) as the other options suck.

My question to you: what would you expect from the api now you know what's wrong, and why have you written code in the WithProjector lambda and not placed that code in the Select lambda? (this to understand why you have done it this way and which might have revealed a flaw in our api)

why have you written code in the WithProjector lambda and not placed that code in the Select lambda

... ignorance mostly ... and perhaps that I am unable to write more meaningful code in a Select statement because of this compile-time error:

A lambda expression with a statement body cannot be converted to an expression tree.

By "meaningful" it appears WithProjector comes in handy because I can use a statement body and do some more inheritance stuff, logic, etc, because the resultset has been retrieved, and here I am working with it in memory. Whereas within the Select statement, I was under the impression it was more for creating the expression (er, expression tree) which leads to the actual SQL that gets executed... I didn't realize the Select is also doing projection, but I can see where ToValue<int>() is doing that.

I guess my answer is I agree with option 1) , but I'm not sure how I would "move" the code to the select statement. I'm probably not as savvy to all the syntactical possibilities in lambda writing, so any clues would help!

Thanks for bringing this to my awareness though.

Isz wrote:

why have you written code in the WithProjector lambda and not placed that code in the Select lambda

... ignorance mostly ... and perhaps that I am unable to write more meaningful code in a Select statement because of this compile-time error:

A lambda expression with a statement body cannot be converted to an expression tree.

Hmm... that's indeed a showstopper. The Select() method requires an Expression<Func<>>, the WithProjector a Func<>. The former is visited and transformed, the latter is taken as-is. The {} blocks prohibit the compiler from creating an Expression<Func<>> I think, so indeed you can't do everything there.

I guess my answer is I agree with option 1) , but I'm not sure how I would "move" the code to the select statement. I'm probably not as savvy to all the syntactical possibilities in lambda writing, so any clues would help!

Thanks for bringing this to my awareness though. No worries.

I've fixed the issue. See the new QuerySpec build attached. This should alias the place-holders properly so your query should work properly.