I'm a bit of an SQL beginner, though I've learned a lot. I thought I had a good guess on how to setup my database for inheritance, but I think I'm missing something.

I was working based of this: http://www.llblgen.com/documentation/2.6/Concepts/concepts_inheritance.htm

I'm running Postgres 8.4, llblgen 2.6 Final.

I've got

CREATE TABLE core_report_entries ( report_entry_id SERIAL PRIMARY KEY, --snip );

--subclass of core_report_entries CREATE TABLE report_entries ( core_id int PRIMARY KEY, --snip );

--subclass of core_report_entries CREATE TABLE history_entries ( core_id int PRIMARY KEY, --snip );

ALTER TABLE report_entries ADD CONSTRAINT fk_report_to_core_join FOREIGN KEY (core_id) REFERENCES core_report_entries (report_entry_id) ON UPDATE CASCADE ON DELETE CASCADE ;

ALTER TABLE history_entries ADD CONSTRAINT fk_history_to_core_join FOREIGN KEY (core_id) REFERENCES core_report_entries (report_entry_id) ON UPDATE CASCADE ON DELETE CASCADE ;

I'm getting an issue adding an entry to history_entries. I'm guessing its due to the ON UPDATE CASCADE. I figured I'd post then start digging, to avoid possibly beating my head against the wall all day and night.

Thanks for your help.

[edit] Oops, it wasn't the update cascade, it's actually a diff't foreign key. Well, I guess could you tell me if there's something fishy with the above schema?

Your schema looks ok (ie PK fields on the sub tables set as FK fields to the PK field on th base table) Could you give more information about the issue you are having - ie is it an issue in the designer when setting up the hierarchy, or an issue/exception at runtime when trying to use the generated code?

Matt

The issue was some unfinished code and lack of confidence in my schema. Thanks for the look over.

Not sure if I should open up a separate thread for this, but I've started using my subclasses now, and I'm getting a strange exception with a grouped linq query.

This is with the schema shown above and this query:

                   var reportEntriesByTubeQuery =
                      from re in metaData.ReportEntry
                      where re.Outage == Procedure.Instance.OutageName
                      where re.CalgroupName == CalGroupName
                      orderby re.DataFileIndex
                      group re by re.DataFileIndex into g
                      select g;

I get this exception:

SD.LLBLGen.Pro.ORMSupportClasses.ORMQueryExecutionException: An exception was caught during the execution of a retrieval query: ERROR: 42703: column LPA_L1.F3_76 does not exist. Check InnerException, QueryExecuted and Parameters of this exc eption to examine the cause of this exception. ---> Npgsql.NpgsqlException: column LPA_L1.F3_76 does not exist Severity: ERROR Code: 42703 at Npgsql.NpgsqlState.<ProcessBackendResponses_Ver_3>d__a.MoveNext() in C:\projects\Npgsql2\src\Npgsql\NpgsqlState.cs:line 832 at Npgsql.ForwardsOnlyDataReader.GetNextResponseObject() in C:\projects\Npgsql2\src\Npgsql\NpgsqlDataReader.cs:line 1136 at Npgsql.ForwardsOnlyDataReader.GetNextRowDescription() in C:\projects\Npgsql2\src\Npgsql\NpgsqlDataReader.cs:line 1154 at Npgsql.ForwardsOnlyDataReader.NextResult() in C:\projects\Npgsql2\src\Npgsql\NpgsqlDataReader.cs:line 1340 at Npgsql.ForwardsOnlyDataReader..ctor(IEnumerable`1 dataEnumeration, CommandBehavior behavior, NpgsqlCommand command, NotificationThreadBlock threadBlock, Boolean synchOnReadError) in C:\projects\Npgsql2\src\Npgsql\NpgsqlDataReader.cs:line 1003 at Npgsql.NpgsqlCommand.GetReader(CommandBehavior cb) in C:\projects\Npgsql2\src\Npgsql\NpgsqlCommand.cs:line 591 at Npgsql.NpgsqlCommand.ExecuteReader(CommandBehavior cb) in C:\projects\Npgsql2\src\Npgsql\NpgsqlCommand.cs:line 569

at Npgsql.NpgsqlCommand.ExecuteDbDataReader(CommandBehavior behavior) in C:\projects\Npgsql2\src\Npgsql\NpgsqlCommand.cs:line 519 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.FetchDataReader(IRetrievalQuery queryToExecute, CommandBehavior readerBehavior) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Dictionary2 typeConvertersToRun) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IEntityFields2 fields, IRelationPredicateBucket filter, Int32 maxNumberOfItemsToReturn, ISortExpression sortClauses, IGroupByCollection groupByClause, Boolean allowDuplicates, Int32 pageNumber, Int32 pageSize) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteHierarchicalValueListProjection(QueryExpression toExecute, IRelationPredicateBucket additionalFilter, ITemplateGroupSpecificCreator frameworkElementCreator) at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(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.LLBLGenProQuery1.Execute() at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery1.System.Collections.Generic.IEnumerable<T>.GetEnumerator() at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source) at ReportEditor.ViewModel.MainViewModel.UpdateResults() in C:\eddynet\EddynetDependent\ReportEditor\ViewModel\MainViewModel.cs:line 823

I get this from my system.diagnostics:

Method Exit: DataAccessAdapterBase.OpenConnection Method Enter: CreatePagingSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Method Enter: CreateSubQuery Method Enter: CreateSelectDQ Method Enter: CreateSelectDQ Generated Sql query: Query: SELECT "LPLA_1"."data_file_index" AS "DataFileIndex" FROM "public"."core_report_entries" "LPLA_1" WHERE ( ( ( ( ( "LPLA_1"."outage" = utage2)) AND ( "LPLA_1"."calgroup_name" = :CalgroupName3)))) GROUP BY "LPLA_1"."data_file_index" ORDER BY "LPLA_1"."data_file_index" ASC Parameter: utage2 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "1R20". Parameter: :CalgroupName3 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "SG11CCAL00004". Method Exit: CreateSelectDQ Method Exit: CreateSubQuery Generated Sql query: Query: SELECT "LPA_L1"."DataFileIndex" AS "key", :LO11 AS "LPFA_2", "LPA_L1"."DataFileIndex" FROM (SELECT "LPLA_1"."data_file_index" AS "DataFileIndex" FROM "public"."core_report_entries" "LPLA_1" WHERE ( ( ( ( ( "LPLA_1"."outage" = utage2)) AND ( "LPLA_1"."calgroup_name" = :CalgroupName3)))) GROUP BY "LPLA_1"."data_file_index" ORDER BY "LPLA_1"."data_file_index" ASC) "LPA_L1" WHERE ( ( ( "LPA_L1"."F3_76" IS NOT NULL))) Parameter: :LO11 : Int32. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: 1. Parameter: utage2 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "1R20". Parameter: :CalgroupName3 : String. Length: 0. Precision: 0. Scale: 0. Direction: Input. Value: "SG11CCAL00004". Method Exit: CreateSelectDQ Method Exit: CreatePagingSelectDQ: no paging. Method Enter: DataAccessAdapterBase.OpenConnection Method Exit: DataAccessAdapterBase.OpenConnection A first chance exception of type 'Npgsql.NpgsqlException' occurred in Npgsql.dll

Additional information: ERROR: 42703: column LPA_L1.F3_76 does not exist

I'm going to try dumping the group by, see if my life gets better. If it helps, there should be no entries in my database that will satisfy this query at this time.

Hi there,

I can't reproduce the problem with your DLL on the first post (and some additional columns I added). There was a similar problem in the past which was fixed: http://llblgen.com/tinyforum/Messages.aspx?ThreadID=15743

So please make sure you are using the latest runtime library version (http://llblgen.com/tinyforum/Messages.aspx?ThreadID=12769)

If the problem persists please post the complete DDL script for the involved tables so we can reproduce the issue.

Thanks for checking it out, I actually dumped the group by and things are working now.