Hi,

I have 3 tables in the following structure: Supplement (ID, Name) --> Forms (ID, Name) --> Sizes (ID, Name, Price)

I needed to add two properties to Supplement to have MinPrice & MaxPrice generated from Price property of all the Sizes under all the Forms for each Supplement, i realized this can be done in multiple ways: 1- Adding property to the generated code of the entity and calculate inside the property but this requires fetching of Forms & Sizes and i would prefer to avoid it.

2- Creating a database view and mapping it to same entity or to new one, i didn't prefer this approach because i thought there must be a way do it through LLBLGen alone.

3- Extending the Supplement EntityFactory to obtain a calculated scalar query inside of it and get the values there, this is the approach that i tried but i am having an issue with it. (The approach is from this link: https://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=10287)

My problem is that the generated query to select the max or the min price doesn't filter on SupplementId, the whole generated query does but this part of the query doesn't.

The generated query looks like this (I removed some stuff to make it shorter): SELECT [LPA_L1]. (All Supplement field), (SELECT MAX([Sizes].[Price]) AS [Price] FROM (( [Supplements]
INNER JOIN [Forms] ON [Supplements].[Supplements_Id] = [Forms].[Supplements_Id]) INNER JOIN [Sizes] ON [Forms].[Forms_Id] = [Sizes].[Forms_Id]) WHERE ( ( [Forms].[Supplements_Id] = [Supplements].[Supplements_Id] AND [Sizes].[Forms_Id] = [Forms].[Forms_Id]))) AS [MaxPrice] FROM [Supplements] [LPA_L1] WHERE ([LPA_L1].[Supplements_Id] = @p1)

How can it get the query where the max of price is selected to filter on [LPA_L1].[Supplements_Id]?

I am using LLBLGen v4.2 and i can provide further details on the code but i wanted to avoid making the thread too long which it already is

It would help to see your code which extends the framework

In general, these in-line fields are a doubly edged sword: do you want them always or do you want them in a certain context? If the former, then your approach is OK, if the latter, then a view in the DB with a typedview mapped onto it might be a better approach (or a poco class you fill with a queryspec/linq query projected to that poco class), where 'better' == simpler.

The latter approach frees your entity fetches from calculating the totals every time you fetch the entity, as you might need the totals only in some situations and not in others.

Thanks Otis for your response, i need those totals to be always calculated and for multiple records at once, this is why i was looking for an approach that would work for multiple records.

Below is the code i tried to use, i am overriding the "CreateFields" method inside SupplementEntityFactory but not sure if this is the best/right way to do it.

[Serializable]
public partial class SupplementEntityFactory : EntityFactoryBase2<SupplementEntity> {
public SupplementEntityFactory() : base("SupplementEntity", PHR.DataLayer.EntityType.SupplementEntity, false) { }

    public override IEntity2 Create(IEntityFields2 fields) {
        IEntity2 toReturn = new SupplementEntity(fields);
        // __LLBLGENPRO_USER_CODE_REGION_START CreateNewSupplementUsingFields
        // __LLBLGENPRO_USER_CODE_REGION_END
        return toReturn;
    }

    public override IEntityFields2 CreateFields()
    {
        IEntityFields2 toReturn = base.CreateFields();

        var numberOfFieldsToAdd = 2;
        var fieldCount = toReturn.Count;
        toReturn.Expand(numberOfFieldsToAdd);

        var filterRelations = new RelationCollection
        {
            SupplementEntity.Relations.FormEntityUsingSupplementsId,
            FormEntity.Relations.SizeEntityUsingFormsId
        };

        var formsAndSizesFilter = (FormFields.SupplementsId == SupplementFields.Id).And
                                                (SizeFields.FormsId == FormFields.Id); 
                                                //HERE IS WHERE I NEE TO ADD FILTER ON SUPPLEMENT ID

        var maxPriceExpression = new ScalarQueryExpression(SizeFields.Price.SetAggregateFunction(AggregateFunction.Max), formsAndSizesFilter, filterRelations);

        var minPriceExpression = new ScalarQueryExpression(SizeFields.Price.SetAggregateFunction(AggregateFunction.Min), formsAndSizesFilter, filterRelations);

        IEntityField2 maxPriceField = new EntityField2("MaxPrice", maxPriceExpression);
        toReturn.DefineField(maxPriceField, fieldCount);

        fieldCount += 1;

        IEntityField2 minPriceField = new EntityField2("MinPrice", minPriceExpression);
        toReturn.DefineField(minPriceField, fieldCount);

        return toReturn;
    }

    #region Included Code

    #endregion
}

You are adding relations and filters that are not needed. In your example try the following:

        var filterRelations = new RelationCollection{
            FormEntity.Relations.SizeEntityUsingFormsId};
        var formsAndSizesFilter = (FormFields.SupplementsId == SupplementFields.Id); 

Please check my Northwind example below, fetching Customers, and adding Max and Min UnitPrice from the OrderDetails.

Hint: I recommend extending the EntityFactory class rather than editing the generated one, in case you want to use the original one without the extra fields.

class CustomerEntityFactoryEx : CustomerEntityFactory
{
    public override IEntityFields2 CreateFields()
    {
        IEntityFields2 toReturn = base.CreateFields();

        var numberOfFieldsToAdd = 2;
        var fieldCount = toReturn.Count;
        toReturn.Expand(numberOfFieldsToAdd);

        var filterRelations = new RelationCollection{OrderDetailEntity.Relations.OrderEntityUsingOrderId};

        var filter = new PredicateExpression(OrderFields.CustomerId == CustomerFields.CustomerId);

        var maxPriceExpression = new ScalarQueryExpression(OrderDetailFields.UnitPrice.SetAggregateFunction(AggregateFunction.Max), filter, filterRelations);
        var minPriceExpression = new ScalarQueryExpression(OrderDetailFields.UnitPrice.SetAggregateFunction(AggregateFunction.Min), filter, filterRelations);

        IEntityField2 maxPriceField = new EntityField2("MaxPrice", maxPriceExpression);
        toReturn.DefineField(maxPriceField, fieldCount);

        fieldCount += 1;

        IEntityField2 minPriceField = new EntityField2("MinPrice", minPriceExpression);
        toReturn.DefineField(minPriceField, fieldCount);

        return toReturn;
    }

}

Test:

using (var adapter = new DataAccessAdapter())
{
    var customers = new EntityCollection<CustomerEntity>(new CustomerEntityFactoryEx());
    adapter.FetchEntityCollection(customers, new RelationPredicateBucket(CustomerFields.Country == "UK"), 1,
        new SortExpression(CustomerFields.CustomerId | SortOperator.Ascending));

    Debug.Assert(customers[0].CustomerId == "AROUT");
    Debug.Assert((decimal)customers[0].Fields[11].CurrentValue == new Decimal(81));
    Debug.Assert((decimal)customers[0].Fields[12].CurrentValue == new Decimal(3.6));
}

Thank you Walaa for your response, initially i didn't add those relations but i kept getting exceptions and then after trying to add relations the code started to work but then got other exceptions related to the generated query so i tried to use SQL profiler to check the query generated and got better understanding of the generated query which i briefly mentioned in my first thread message.

I tried the example you mentioned:

var filterRelations = new RelationCollection{
            FormEntity.Relations.SizeEntityUsingFormsId};
        var formsAndSizesFilter = (FormFields.SupplementsId == SupplementFields.Id);

But it didn't work, i got this exception:

An exception was caught during the execution of a retrieval query: The multi-part identifier "Supplements.Supplements_Id" could not be bound. The multi-part identifier "Supplements.Supplements_Id" could not be bound.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

And the query generated by LLBLGen was (I removed all the supplement fields to simplify it):

SELECT [LPA_L1].[All Supplement Details]
 (SELECT MAX([Size].[Price]) AS [Price] 
  FROM ( [Form]  
  INNER JOIN [Size]  ON  [Form].[Form_Id]=[Size].[Form_Id]) 
  WHERE ( [Form].[Supplements_Id] = [Supplements].[Supplements_Id])) AS [MaxPrice],

 (SELECT MIN([Size].[Price]) AS [Price] 
  FROM ( [Form]  
  INNER JOIN [Size]  ON  [Form].[Form_Id]=[Size].[Form_Id]) 
  WHERE ( [Form].[Supplements_Id] = [Supplements].[Supplements_Id])) AS [MinPrice] 

 FROM [Supplements]  [LPA_L1]   WHERE [LPA_L1].[Supplements_Id] = @p1,

As you can see there is a problem with the sub query that tries to get the max or the min which is that it filters on the supplement id coming from Supplements table in general where it should use the Id coming from the Supplements table specified in the query by the name [LPA_L1], so the script generated should be like:

SELECT [LPA_L1].[All Supplement Details]
 (SELECT MAX([Size].[Price]) AS [Price] 
  FROM ( [Form]  
  INNER JOIN [Size]  ON  [Form].[Form_Id]=[Size].[Form_Id]) 
  WHERE ( [Form].[Supplements_Id] = [LPA_L1].[Supplements_Id])) AS [MaxPrice],

 (SELECT MIN([Size].[Price]) AS [Price] 
  FROM ( [Form]  
  INNER JOIN [Size]  ON  [Form].[Form_Id]=[Size].[Form_Id]) 
  WHERE ( [Form].[Supplements_Id] = [LPA_L1].[Supplements_Id])) AS [MinPrice] 

 FROM [Supplements]  [LPA_L1]   WHERE [LPA_L1].[Supplements_Id] = @p1,

I am not sure if am missing something or if i am doing something incorrectly.

I find the alias odd on your table. Is it an entity in an inheritance hierarchy? If not, please create a tiny repro, or at least try on e.g. northwind to see whether Walaa example indeed works for you. The table shouldn't get an alias on the table in a normal entity fetch.

How do you fetch the entities?

Hi Otis,

Thank you for your comment, when i saw your question i thought maybe changing the way i fetch might solve the problem and it did, here is the way we used to fetch the entities:

using (var adapter = DataAccessAdapter)
{
    var data = new LinqMetaData(adapter);

    var source = data.Supplements.WithPath(_pathEdgesSupplement);
    
    //Here i apply some filters
    source = source.Where(cc => cc.SupplementsId == filter.SupplementsId);
    
    return source.ToList();
}

I am not sure if this is the right way as i learned to apply such logic on the code from an existing project that someone else worked on and i didn't think about changing it since it was working.

I changed the code to the following and the alias was gone and the query worked as expected:

using (var adapter = DataAccessAdapter)
{
    var data = new LinqMetaData(adapter);
    
    var supplementsCollection = new EntityCollection<SupplementEntity>(new SupplementEntityFactory());
    
    var filterBucket = new RelationPredicateBucket();
    filterBucket.PredicateExpression.Add(
                    new FieldCompareValuePredicate(SupplementFields.Id, 
                                                   null,
                                                   ComparisonOperator.Equal, 
                                                   filter.SupplementId));
    
    adapter.FetchEntityCollection(supplementsCollection, 
                                  filterBucket, 
                                  filter.PageSize, 
                                  new SortExpression(), 
                                  new PrefetchPath2(DataLayer.EntityType.SupplementEntity), 
                                  filter.PageNumber, 
                                  filter.PageSize);
    
    return supplementsCollection.ToList();
}

My only remaining question is how to use a custom path edge with FetchEntityCollection as the one i use with:

var source = data.Supplements.WithPath(_pathEdgesSupplement);

The pathedge type is: Func<IPathEdgeRootParser<SupplementEntity>, IPathEdgeRootParser<SupplementEntity>>

I am sorry if this is a silly question.

Hi Otis,

I was trying to play around with the way to fetch the data and i found that the table name in the query only gets an alias if i add a filtration condition like this:

 source = source.Where(cc => cc.SupplementsId == filter.SupplementsId);

for example if i use the code below (Without any filtration):

using (var adapter = DataAccessAdapter)
{
    var data = new LinqMetaData(adapter);

    var source = data.Supplements.WithPath(_pathEdgesSupplement);
    
    return source.ToList();
}

The table name in the query stays as the original name, no alias is used and the query works as expected .. am i doing something wrong? ... is there a way i can keep fetching entities the same way i used to and also filter the data but without causing the query to use an alias?

I'm a little bit confused, as the code samples you provide keeps changing, and now with the addition of the prefetchPath. So I'm not sure if the original problem has been fixed and that's another one, or we are still in the same original issue.

Did you try my Northwind example, to see if it works at your side?

Which exact version/release (of 4.2) of LLBLGen runtime library are you using? hint: please check the Forum guidelines thread to know how to get the exact number.

Hi Walaa,

Thank you for your response and i apologize for any confusion that i have caused. You are correct regarding the code changes, the samples that i have provided changed overtime because of the change in my understanding of where the issue is coming from.

Before i go into more details i can confirm that your Northwind example works, i haven't tried the exact code on Northwind database but the same approach works for my case in my project, however i have another issue when trying to fit the approach within my project.

In summary, in my project i found i am fetching the data in a way that is different from the way in your sample, this approach allows me to use a PrefetchPath (Not sure if i can use one in your sample) but the use of filtration causes the table name in the generated query to have an alias which causes the query failure, in your sample however the table name in generated query stays the same without an alias even when using filters which makes the query work but i am not sure how to use a PrefetchPath with it.

So in my way of fetching, the filters makes the query fail and in your sample of fetching i am not sure how to use my PrefetchPath, below i list both ways:

1- My Fetching Approach:

using (var adapter = DataAccessAdapter)
{
    var data = new LinqMetaData(adapter);

    var source = data.Supplements.WithPath(_pathEdgesSupplement);//This is the PrefetchPath i need
    
    //PrefetchPath parameter type:
    //Func<IPathEdgeRootParser<SupplementEntity>, IPathEdgeRootParser<SupplementEntity>>
    
    //Here i apply some filters (Applying this filter causes the table name to have an alias which makes the query fail)
    source = source.Where(cc => cc.SupplementsId == filter.SupplementsId);
    
    return source.ToList();
}

• With this approach i can use a PrefetchPath but applying filters causes the query failure because of the table name alias in query, if i don't use a filter the query works without issues but then i can't filter the data. 2- Your sample Approach (Applied on my case):

using (var adapter = DataAccessAdapter)
{
    var data = new LinqMetaData(adapter);
    
    var supplementsCollection = new EntityCollection<SupplementEntity>(new SupplementEntityFactoryEx());
    
    var filterBucket = new RelationPredicateBucket();
    filterBucket.PredicateExpression.Add(
                    new FieldCompareValuePredicate(SupplementFields.Id, 
                                                 null,
                                                 ComparisonOperator.Equal, 
                                                 filter.SupplementId));//The use of this filter doesn't cause a query failure
    
    adapter.FetchEntityCollection(supplementsCollection, 
                                 filterBucket, 
                                 filter.PageSize, 
                                 new SortExpression(), 
                                 new PrefetchPath2(DataLayer.EntityType.SupplementEntity), 
                                 filter.PageNumber, 
                                 filter.PageSize);
    
    return supplementsCollection.ToList();
}

• With this approach the use of filtration doesn't cause a query failure and the table name stays the same without an alias in query but i am not sure how to use a PrefetchPath. Finally, for some reason the use of filtration in my approach causes the table name in query to have an alias which makes the query fail but this doesn't happen in your sample, so to fix this final issue i would need one of two things to make either approach work and then use it:

1- A change in my code sample that would allow me to use filters without causing an alias in query OR 2- A way to use a PrefetchPath in your sample code.

Sorry again for the confusion and for the long discussion, i hope this message clears up the issue and your help is greatly appreciated, finding a way to make this work would really help me gain even greater advantage in using LLBLGen.

The version/release (of 4.2) of LLBLGen runtime library is 4.2.15.127

Glad that my code sample had worked with you.

Here is the same example with a PrefetchPath that has a filter.

var prefetchPath = new PrefetchPath2(EntityType.CustomerEntity);
prefetchPath.Add(CustomerEntity.PrefetchPathOrders, 0, new PredicateExpression(OrderFields.EmployeeId == 9));

using (var adapter = new DataAccessAdapter())
{
    var customers = new EntityCollection<CustomerEntity>(new CustomerEntityFactoryEx());
    adapter.FetchEntityCollection(customers, new RelationPredicateBucket(CustomerFields.Country == "UK"),
        1, new SortExpression(CustomerFields.CustomerId | SortOperator.Ascending), prefetchPath);

    Debug.Assert(customers[0].CustomerId == "AROUT");
    Debug.Assert((decimal)customers[0].Fields[11].CurrentValue == new Decimal(81));
    Debug.Assert((decimal)customers[0].Fields[12].CurrentValue == new Decimal(3.6));

    Debug.Assert(customers[0].Orders.Count == 2);
}

Thank you Walaa,

I was looking for a way to use a PathEdge of type "Func<IPathEdgeRootParser<SupplementEntity>, IPathEdgeRootParser<SupplementEntity>>" instead of using a "PrefetchPath2" because we already have PathEdges and i was preferring to use them and i found a way to create a "PrefetchPath2" based on a "PathEdge" which is working.

However I have one final point and i apologize for asking again but is there a way i can use the code below and filter without causing the generated query to use an alias as the table name?

using (var adapter = DataAccessAdapter)
{
    var data = new LinqMetaData(adapter);

    var source = data.Supplements.WithPath(_pathEdgesSupplement);
    
    //Here i apply some filters (Applying this filter causes the table name to have an alias which makes the query fail)
    source = source.Where(cc => cc.SupplementsId == filter.SupplementsId);
    
    return source.ToList();
}

I used the same code, with a filter on the main query and another filter on the PathEdge. Both queries succeed, as below.

var data = new LinqMetaData(adapter);
var source = data.Customer.WithPath(new PathEdge<OrderEntity>(CustomerEntity.PrefetchPathOrders, o=>o.EmployeeId == 9));
source = source.Where(cc => cc.CustomerId == "AROUT");

SELECT [LPA_L1].[Address], [LPA_L1].[City], [LPA_L1].[CompanyName], [LPA_L1].[ContactName], [LPA_L1].[ContactTitle], [LPA_L1].[Country], [LPA_L1].[CustomerID] AS [CustomerId], [LPA_L1].[Fax], [LPA_L1].[Phone], [LPA_L1].[PostalCode], [LPA_L1].[Region] FROM [NORTHWIND].[dbo].[Customers] [LPA_L1] WHERE ( ( [LPA_L1].[CustomerID] = @p1))
    Parameter: @p1 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "AROUT".

SELECT [NORTHWIND].[dbo].[Orders].[CustomerID] AS [CustomerId], [NORTHWIND].[dbo].[Orders].[EmployeeID] AS [EmployeeId], [NORTHWIND].[dbo].[Orders].[Freight], [NORTHWIND].[dbo].[Orders].[OrderDate], [NORTHWIND].[dbo].[Orders].[OrderID] AS [OrderId], [NORTHWIND].[dbo].[Orders].[RequiredDate], [NORTHWIND].[dbo].[Orders].[ShipAddress], [NORTHWIND].[dbo].[Orders].[ShipCity], [NORTHWIND].[dbo].[Orders].[ShipCountry], [NORTHWIND].[dbo].[Orders].[ShipName], [NORTHWIND].[dbo].[Orders].[ShippedDate], [NORTHWIND].[dbo].[Orders].[ShipPostalCode], [NORTHWIND].[dbo].[Orders].[ShipRegion], [NORTHWIND].[dbo].[Orders].[ShipVia] FROM [NORTHWIND].[dbo].[Orders] WHERE ( ( ( [NORTHWIND].[dbo].[Orders].[CustomerID] = @p1)) AND ( [NORTHWIND].[dbo].[Orders].[EmployeeID] = @p2))
    Parameter: @p1 : StringFixedLength. Length: 5. Precision: 0. Scale: 0. Direction: Input. Value: "AROUT".
    Parameter: @p2 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 9.

Could you please make sure you are using the latest release of v.4.2, regenerate the code, make sure your application is using the newly generated code, and is referencing the lateset version of the runtime libraries.

Thank you Walaa,

I am afraid my question in my last message might not have been clear, the queries you listed are working however when trying to add min/max fields to the entity using extended entity factory while fetching the data like this:

var source = data.Customer.WithPath(new PathEdge<OrderEntity (CustomerEntity.PrefetchPathOrders, o=>o.EmployeeId == 9));

The first generated query you listed has an alias as the table name, the alias is [LPA_L1], this alias causes a failure in query when including min/max fields. This however doesn't happen when using the method "adapter.FetchEntityCollection" to fetch the data, my question is if there anything i can do to use the approach data.Customer.WithPath() with filtration and not have an alias in query?

Reproduced. And this has nothing to do with the prefetchPath queries.

Steps to reproduce:

1- Edit the generated CustomerEntityFactory class, and add the following:

public override IEntityFields2 CreateFields()
{
    IEntityFields2 toReturn = base.CreateFields();

    var numberOfFieldsToAdd = 2;
    var fieldCount = toReturn.Count;
    toReturn.Expand(numberOfFieldsToAdd);

    var filterRelations = new RelationCollection { OrderDetailEntity.Relations.OrderEntityUsingOrderId };

    var filter = new PredicateExpression(OrderFields.CustomerId == CustomerFields.CustomerId); // <<====== CustomerFields.CustomerId doesn't get the alias used to fetch the query using LinQ

    var maxPriceExpression = new ScalarQueryExpression(OrderDetailFields.UnitPrice.SetAggregateFunction(AggregateFunction.Max), filter, filterRelations);
    var minPriceExpression = new ScalarQueryExpression(OrderDetailFields.UnitPrice.SetAggregateFunction(AggregateFunction.Min), filter, filterRelations);

    IEntityField2 maxPriceField = new EntityField2("MaxPrice", maxPriceExpression);
    toReturn.DefineField(maxPriceField, fieldCount);

    fieldCount += 1;

    IEntityField2 minPriceField = new EntityField2("MinPrice", minPriceExpression);
    toReturn.DefineField(minPriceField, fieldCount);

    return toReturn;
}

Then execute the following:

using (var adapter = new DataAccessAdapter())
{
    var data = new LinqMetaData(adapter);
    var source = data.Customer.Where(cc => cc.CustomerId == "AROUT");
    var result = source.ToList();
}

Hi Walaa,

Thank you for your response, i tried the same code you provided on Northwind database and the query was like this:

exec sp_executesql N'SELECT [LPA_L1].[Address],
 [LPA_L1].[City],
 [LPA_L1].[CompanyName],
 [LPA_L1].[ContactName],
 [LPA_L1].[ContactTitle],
 [LPA_L1].[Country],
 [LPA_L1].[CustomerID] AS [CustomerId],
 [LPA_L1].[Fax],
 [LPA_L1].[Phone],
 [LPA_L1].[PostalCode],
 [LPA_L1].[Region],

 (SELECT MAX([NORTHWND].[dbo].[Order Details].[UnitPrice]) AS [UnitPrice] 
    FROM ( [NORTHWND].[dbo].[Orders]  
    INNER JOIN [NORTHWND].[dbo].[Order Details]  ON  [NORTHWND].[dbo].[Orders].[OrderID]=[NORTHWND].[dbo].[Order Details].[OrderID]) 
    WHERE ( [NORTHWND].[dbo].[Orders].[CustomerID] = [NORTHWND].[dbo].[Customers].[CustomerID])) AS [MaxPrice],

 (SELECT MIN([NORTHWND].[dbo].[Order Details].[UnitPrice]) AS [UnitPrice] 
    FROM ( [NORTHWND].[dbo].[Orders]  
    INNER JOIN [NORTHWND].[dbo].[Order Details]  ON  [NORTHWND].[dbo].[Orders].[OrderID]=[NORTHWND].[dbo].[Order Details].[OrderID]) 
    WHERE ( [NORTHWND].[dbo].[Orders].[CustomerID] = [NORTHWND].[dbo].[Customers].[CustomerID])) AS [MinPrice] 

FROM [NORTHWND].[dbo].[Customers]  [LPA_L1]   WHERE ( ( [LPA_L1].[CustomerID] = @p1))',
N'@p1 nchar(5)',
@p1=N'AROUT'

I got an exception and I still see an alias in the table name "[LPA_L1]", are you getting the same alias?

Note: If i remove the filter line below the alias is gone and the query works: Where(cc => cc.CustomerId == "AROUT")

The first word in my last post was: Reproduced.

Hang on till we investigate it.

We'll trace this down max by Saturday.

The problem is that the expressions in the fields you added manually in the factory adjustment aren't corrected for aliases as the linq assigned aliases are done by the linq expression handler and not by the rest of the pipeline and the linq expression handler (the linq provider) doesn't look at llblgen expressions / filters as those are already in the target format so it doesn't correct aliases (nor does it know how)

The thing is that the pipeline expects aliases to be set as-is after it receives the elements from the linq expression handler, it doesn't 'correct' aliases by itself (as you might want it this way, it can't possibly know this).

So to make this work, you have to specify the aliases you need:

var filter = new PredicateExpression(OrderFields.CustomerId == CustomerFields.CustomerId.SetObjectAlias("LPA_L1"));

In general this isn't recommended, as this relies on the alias being generated by the linq provider, which can change.

You're creating code by adjusting factories to add fields to entities which isn't recommended either (as it creates brittle code that's hard to maintain and the maintainers then think "What a brittle framework!").

It's recommended to create a view with the expressions in the database, and map that view as a typedview or entity and fetch that view. You then don't need to adjust the factories and don't need to mess with aliases that are brittle and will create bugs that are hard to track down in the future.

Now, you may ignore this advice and still go the route you're on at the moment, but remember: if things change in the framework like alias generation, you will run into problems. If you join another entity to the query, the alias might change too.

Another possibility is to create a custom projection with linq to a poco or anonymous type with the scalar expressions there so you get the aliases set up properly.

Hi Otis,

Thank you for your response and for taking time to review my questions, i appreciate the workaround that you have provided and i understand the consequences of using such approach and i totally agree with you on why such approach isn't really recommended, however i think the provided workaround would be for a problem side effect and not the problem itself, i think there can be a solution to the problem itself, i could be wrong here, i am not sure but I'll try to explain.

My main problem here is the alias use itself, regardless of extended factory classes or custom fields. Earlier in this post i listed the query generated and you said you find it odd that it had an alias and you asked about the way i fetch the data. After your post i checked my code and looked for different ways of fetching data and found that it is a combination of the fetch approach plus filtration that causes the query to have an alias, i mentioned this multiple times across this post, for example the code below causes the table to have an alias (No custom fields ... No Extended Factory), just simple fetching:

using (var adapter = DataAccessAdapter)
{
    var data = new LinqMetaData(adapter);

    var source = data.Supplements.WithPath(_pathEdgesSupplement);
    
    source = source.Where(cc => cc.SupplementsId == filter.SupplementsId);
    
    return source.ToList();
}

This fetching approach is really simple and i am using the generated entity factory without any changes, yet the generated query has an alias as the table name and the reason is filtration, if we remove this filtration line, the alias goes away:

source = source.Where(cc => cc.SupplementsId == filter.SupplementsId);

The alias in the table name goes away, even if i adjusted the entity factory and added my custom fields, the query would still work and no alias is used, however i can't accept this as a solution because i still need to filter.

So my question here, aside from extended factory and custom fields, is there a way to adjust the specific fetching code above to use filters without causing the query to have an alias?

No, sorry there's not. The aliasing of linq queries in general happens when the engine thinks it needs one, and with linq there's no way to specify the alias, so we generate one ourselves.

Thank you so much Otis, i really appreciate your help, one final remark here, i found that if i fetch the data using this method:

adapter.FetchEntityCollection()

Then the table name will not have an alias even if i use filters, this made me feel curious on why the alias shows when i use

data.Supplements.WithPath

but not in the method FetchEntityCollection(), anyways i think i will use "FetchEntityCollection" even thought the syntax would be completely different from the rest of the project but thank you for the clarification & help.

The answer is that LINQ2LLBL is a framework that is on top of LLBLGen Native API. So if you write a linq query, it will be translated to LLBLGen API, in this process it may be that an alias is needed as the expression tree is traversed. If you use the LLBLGen API directly, there is not need for translation. QuerySpec in the other hand has the beauty of both worlds: is very fluid and clear in the way you construct queries, and you have more control (aliases, for instance).

Thank you for the information David, i didn't know about QuerySpec and i checked it out quick and it seems to provide great flexibility and much more control.

For this specific task i think i will stick to using FetchEntityCollection(), i realized at a certain point that i could use it to do the task but i wanted to see if i can find a way to use data.Supplements.WithPath() because that would help me maintain a consistent syntax for fetching across the project.

The only workaround to use it though was the suggestion from Frans to use "SetObjectAlias("LPA_L1"))" in order to solve the issue with the query which he didn't recommend and i agree with him, i tried to look for a way to extract the generated table alias "LPA_L1" dynamically inside the entity factory but i couldn't find one, if there is a way then this would be the ultimate solution to my task and it would be ideal, if not then using FetchEntityCollection() is fine too.

Regardless of solution thank you guys for your time and for helping me out, your help is greatly appreciated.

I'll close the thread for now, as I think you've reached a solution that's doable.