By following the example made by Mr. Bouma at http://weblogs.asp.net/fbouma/archive/2006/06/09/LLBLGen-Pro-v2.0-with-ASP.NET-2.0.aspx (step 6.) I've managed to create a custom entity factory where I can add my own fields which will get returned by a select query.
I do however have a problem that the generated select query is not correct. I define my own custom field which is actually a sub-query. It would all work well if inside the sub-query LLBLGen would use the same table alias as in the outer query.
Here is the factory:
Public Class ViewAssetEntityExtendedFactory
Inherits ViewAssetEntityFactory
Public Overrides Function CreateFields() As SD.LLBLGen.Pro.ORMSupportClasses.IEntityFields2
Dim fields As SD.LLBLGen.Pro.ORMSupportClasses.IEntityFields2 = MyBase.CreateFields()
fields.Expand(1)
Dim f As IEntityField2 = New EntityField2("Attribute1", New ScalarQueryExpression(AssetAtributeValueFields.AttributeValue, ViewAssetFields.Id = AssetAtributeValueFields.AssetId And AssetAtributeValueFields.AssetTypeAttributeId = 671), GetType(DateTime))
fields.DefineField(f, fields.Count - 1)
Return fields
End Function
End Class
And here is the generated query (I've beautified it to be more readable):
Query:
SELECT TOP(@p3) [LPA_L1].[AREA_ID] AS [AreaId]
, [LPA_L1].[AREA_NAME] AS [AreaName]
, [LPA_L1].[BDE_AVAIL] AS [BdeAvail]
, [LPA_L1].[CC_ID] AS [CcId]
, [LPA_L1].[CC_INFO] AS [CcInfo]
, [LPA_L1].[COMMENT] AS [Comment]
, [LPA_L1].[COMPANY_ID] AS [CompanyId]
, [LPA_L1].[COMP_NAME] AS [CompName]
, [LPA_L1].[COSTCENTER] AS [Costcenter]
, [LPA_L1].[COSTCENTER_ID] AS [CostcenterId]
, [LPA_L1].[DESCRIPTIVE_FIELD] AS [DescriptiveField]
, [LPA_L1].[ELECTRICAL_DATA] AS [ElectricalData]
, [LPA_L1].[FULL_PATH] AS [FullPath]
, [LPA_L1].[ID] AS [Id]
, [LPA_L1].[IMAGE] AS [Image]
, [LPA_L1].[LC_HIERARCHY_ID] AS [LcHierarchyId]
, [LPA_L1].[LC_LOCATION_ID] AS [LcLocationId]
, [LPA_L1].[NAME] AS [Name]
, [LPA_L1].[ORDER_NO] AS [OrderNo]
, [LPA_L1].[PARENT_ASSET_ID] AS [ParentAssetId]
, [LPA_L1].[SHOW_STATISTIC] AS [ShowStatistic]
, [LPA_L1].[SHOW_TRAFFIKLIGHT] AS [ShowTraffiklight]
, [LPA_L1].[STATUS_IMAGE] AS [StatusImage]
, [LPA_L1].[TYPE_ID] AS [TypeId]
, [LPA_L1].[TYPE_NAME] AS [TypeName]
, [LPA_L1].[WORKPLACE_NO] AS [WorkplaceNo]
, [LPA_L1].[WORK_STATUS_ID] AS [WorkStatusId]
, (SELECT [dbo].[ASSET_ATRIBUTE_VALUES].[ATTRIBUTE_VALUE] AS
[AttributeValue]
FROM [dbo].[ASSET_ATRIBUTE_VALUES]
WHERE ( [dbo].[VIEW_ASSETS].[ID] =
[dbo].[ASSET_ATRIBUTE_VALUES].[ASSET_ID]
AND
[dbo].[ASSET_ATRIBUTE_VALUES].[ASSET_TYPE_ATTRIBUTE_ID] = @p1
))
AS [Attribute1]
FROM [dbo].[VIEW_ASSETS] [LPA_L1]
ORDER BY [LPA_L1].[ID] ASC
Parameter: @p1 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 671.
Parameter: @p3 : Int64. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 128.
Note that the outer table has alias [LPA_L1] while the subquery: (SELECT [dbo].[ASSET_ATRIBUTE_VALUES].[ATTRIBUTE_VALUE] AS [AttributeValue] FROM [dbo].[ASSET_ATRIBUTE_VALUES] WHERE ( [dbo].[VIEW_ASSETS].[ID] = [dbo].[ASSET_ATRIBUTE_VALUES].[ASSET_ID] AND [dbo].[ASSET_ATRIBUTE_VALUES].[ASSET_TYPE_ATTRIBUTE_ID] = @p1)) AS [Attribute1] does not have it.
Am I missing something here? Is there maybe a working alternative?
Using LLBLGen Pro 3.1 Final (September 30th, 2011), MS SQL provider, MS SQL Express 2008R2.
P.S.:
The query is actually generated by Devexpress XtraGrid through LinqMetaData adapter but I do not think it should affect anything.
)