Hello,

We have a legacy table structure that includes two tables, tbl_doctor and tbl_doctor_institution. Essentially, tbl_doctor is storing information for two different entity types: doctors and institutions. The primary key is doc_num, and it contains a field in this table called inst_flag that contains a Y if the row contains an institution and N if the row contains a doctor record. So doctors and institutions share doc_num, essentially, as well as a few other fields.

I implemented this as a TargetPerEntity hierarchy. I have an abstract base entity called DoctorAndInstitution that has the doc_num and inst_flag, and have two sub type entities, Doctor and Institution, that are using the inst_flag as a discriminator.

Another entity called DoctorInstitutionLink is built on tbl_doctor_institution that essentially is supposed to act as a many to many relationship table between doctors and institutions. This table contains a doc_num and an inst_num, both of which relate to doc_num on tbl_doctor.

Therefore, I have an entity called Doctor that relates to DoctorInstitutionLink (1:n) on doc_num to doc_num, and I have another relationship from DoctorInstitutionLink to Institution (m:1), and vice versa. This creates the many to many between Doctor and Institution. I named the relationships in a way that Doctor.AssignedInstitutions will return an InstitutionCollection of assigned institutions to the specified doctor.

Here's the problem. I'm analyzing the SQL that it is generating, and everything looks fine except the table it is using to join the two together. Take a look:

SELECT DISTINCT [dbo].[tbl_Doctor].[doc_num] AS [Id0], [dbo].[tbl_Doctor].[doc_num] AS [Id1] FROM [dbo].[tbl_Doctor] [LPA__1] INNER JOIN [dbo].[tbl_doctor_institution] ON [LPA__1].[doc_num] = [dbo].[tbl_doctor_institution].[inst_num] INNER JOIN [dbo].[tbl_Doctor] ON [dbo].[tbl_Doctor].[doc_num] = [dbo].[tbl_doctor_institution].[doc_num] WHERE [dbo].[tbl_Doctor].[inst_flag] = @InstitutionFlag2 AND [dbo].[tbl_doctor_institution].[inst_num] = @InstitutionId3

I see that it is using LPA__1 to represent the alias of the tbl_doctor table that represents the institution. This seems perfect! The problem is that this SQL returns a dataset of two columns, and both columns are the same value! The select statement isn't using the aliased table name for one of the columns like it should be. For instance, the SELECT portion of this statement should be:

SELECT DISTINCT [LPA__1].[doc_num] AS [Id0], [dbo].[tbl_Doctor].[doc_num] AS [Id1]

Then Id0 would be the institution's id and Id1 would be the doctor's id. This would allow the rest of the process to properly return the data that I'm looking for. As is, however, no data is being returned at all because the inst_num from tbl_doctor_institution that it is comparing to Id0 will never match a single row.

Hopefully, my explanation makes sense. Essentially, it's aliasing the tables in a way that makes sense, but it isn't using those aliases properly in the select.

Any ideas?

Thanks, Marc

I'm not sure if this is the known issue we ran into some time ago which was fixable if you alias ed one of the entities in your query. In v2.5 we tried to solve this and in general we did. It took a lot of changes so we can't back-port this to v2.0. The fact is that it has to auto-alias one of the entities involved even if there's no alias specified and what's worse: the fields in the resultset also don't know the alias so they have to be given the automatically set alias as well, so if field doc_num is specified in the resultset, and there's no objectalias specified, and it is in entity X, it has to get the alias specified.

I'm not sure because the query doesn't look like a query generated by a collection fetch but by a prefetch path. prefetch paths have auto-aliasing of entities inside them to work around this issue.

Could you give me the exact code which produced this query so we can try to reproduce it here and see if this is indeed the same issue we fixed in v2.5 (now in beta) or that this is another issue which might already have seen a fix in v2.0 or needs addressing Thanks.

Hello Otis,

Thanks for replying so quickly to this post. I apologize it has taken me so long to get back to it, but I've been busy with other things, unfortunately. I took your advice and upgraded everything to v2.5 to see if I could reproduce the error, and it seems to still occur just as before.

In my post above, you are correct in saying that it doesn't look like SQL being generated by LLBLGen. I apologize for that. I ran it through a SQL formatter prior to posting it when I was trying to analyze it myself, so it changed a few things. Here's the QueryExecuted directly from the exception message.

Query: SELECT DISTINCT [dbo].[tbl_Doctor].[doc_num] AS [F0], [dbo].[tbl_Doctor].[rowguid] AS [F1], [dbo].[tbl_Doctor].[inst_flag] AS [F2], [dbo].[tbl_Doctor].[lname] AS [F3] FROM (( [dbo].[tbl_Doctor] [LPA__1] INNER JOIN [dbo].[tbl_doctor_institution] ON [LPA__1].[doc_num]=[dbo].[tbl_doctor_institution].[doc_num] AND ( ( [dbo].[tbl_Doctor].[inst_flag] = @InstitutionFlag1))) INNER JOIN [dbo].[tbl_Doctor] ON [dbo].[tbl_Doctor].[doc_num]=[dbo].[tbl_doctor_institution].[inst_num] AND ( ( [dbo].[tbl_Doctor].[inst_flag] = @InstitutionFlag2))) WHERE ( [dbo].[tbl_Doctor].[inst_flag] = @InstitutionFlag3 AND ( ( ( [dbo].[tbl_doctor_institution].[doc_num] = @DoctorId4)))) Parameter: @InstitutionFlag1 : AnsiStringFixedLength. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: "N". Parameter: @InstitutionFlag2 : AnsiStringFixedLength. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: "Y". Parameter: @InstitutionFlag3 : AnsiStringFixedLength. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: "Y". Parameter: @DoctorId4 : Int32. Length: 0. Precision: 10. Scale: 0. Direction: Input. Value: 129022.

And here's the exception message itself.

An exception was caught during the execution of a retrieval query: The column prefix 'dbo.tbl_Doctor' does not match with a table name or alias name used in the query.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

I'm not sure what you are going to need to properly debug this, but please let me know if there is anything I can post to help you further. Your help is GREATLY appreciated! Thanks!

Marc

One more thing...

The error I gave you above occurred while trying to do a prefetch from doctor (tbl_doctor) to the institutions (tbl_doctor) collection through the many to many doctorInstitutionLink (tbl_doctor_institution) table.

If I remove the prefetch, I get a similar error, but it is different enough that it might help you determine what is going wrong with my setup. I'm hoping this is just an error on my side.

The column prefix 'DoctorInstitutionLink_' does not match with a table name or alias name used in the query.

** Query: SELECT DISTINCT [dbo].[tbl_Doctor].[doc_num] AS [F0], [dbo].[tbl_Doctor].[rowguid] AS [F1], [dbo].[tbl_Doctor].[inst_flag] AS [F2], [dbo].[tbl_Doctor].[lname] AS [F3] FROM (( [dbo].[tbl_Doctor] [LPA__1] INNER JOIN [dbo].[tbl_doctor_institution] [LPA_D3] ON [LPA__1].[doc_num]=[LPA_D3].[doc_num] AND ( ( [DoctorInstitutionLink_].[inst_flag] = @InstitutionFlag1))) INNER JOIN [dbo].[tbl_Doctor] ON [dbo].[tbl_Doctor].[doc_num]=[LPA_D3].[inst_num] AND ( ( [dbo].[tbl_Doctor].[inst_flag] = @InstitutionFlag2))) WHERE ( [dbo].[tbl_Doctor].[inst_flag] = @InstitutionFlag3) Parameter: @InstitutionFlag1 : AnsiStringFixedLength. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: "N". Parameter: @InstitutionFlag2 : AnsiStringFixedLength. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: "Y". Parameter: @InstitutionFlag3 : AnsiStringFixedLength. Length: 1. Precision: 0. Scale: 0. Direction: Input. Value: "Y".**

Thanks again, Marc

And last, but not least, here's the code I used. Not sure how helpful it will be without the actual business layer that your software generated, but if it helps, then great.

DoctorEntity doctor = new DoctorEntity(); IPrefetchPath prefetch = new PrefetchPath((int)EntityType.DoctorEntity); prefetch.Add(DoctorEntity.PrefetchPathAssignedInstitutions); doctor.FetchUsingPK(docNum, prefetch); doctorBindingSource.DataSource = doctor;

For the second exception I posted above, I simply removed the prefetch parameter from the FetchUsingPK method call, and that doesn't error out until the actual m:n AssignedInstitutions property is accessed.

Marc

SELECT DISTINCT [dbo].[tbl_Doctor].[doc_num] AS [F0], [dbo].[tbl_Doctor].[rowguid] AS [F1], [dbo].[tbl_Doctor].[inst_flag] AS [F2], [dbo].[tbl_Doctor].[lname] AS [F3] FROM (( [dbo].[tbl_Doctor] [LPA__1] INNER JOIN [dbo].[tbl_doctor_institution] ON [LPA__1].[doc_num]=[dbo].[tbl_doctor_institution].[doc_num] AND ( ( [dbo].[tbl_Doctor].[inst_flag] = @InstitutionFlag1))) INNER JOIN [dbo].[tbl_Doctor] <---------------------------------------------------- LOOK HERE ON [dbo].[tbl_Doctor].[doc_num]=[dbo].[tbl_doctor_institution].[inst_num] AND ( ( [dbo].[tbl_Doctor].[inst_flag] = @InstitutionFlag2))) WHERE ( [dbo].[tbl_Doctor].[inst_flag] = @InstitutionFlag3 AND ( ( ( [dbo].[tbl_doctor_institution].[doc_num] = @DoctorId4))))

An exception was caught during the execution of a retrieval query: The column prefix 'dbo.tbl_Doctor' does not match with a table name or alias name used in the query.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.

It's strange that the above query, gives the following exception, since [dbo].[tbl_Doctor] is joined again in the query without an alias. Did you try to manualy run the generated SQL directly against the database (using Query Analyzer)?

Thank you for the quick response.

I used SQL Profiler to find the exact query that is being run with parameters included, ran it in query analyzer, and received the exact same message. I toyed with the query a bit and determined where the problem is occurring.

SELECT DISTINCT [dbo].[tbl_Doctor].[doc_num] AS [F0], [dbo].[tbl_Doctor].[rowguid] AS [F1], [dbo].[tbl_Doctor].[inst_flag] AS [F2], [dbo].[tbl_Doctor].[lname] AS [F3] FROM (( [dbo].[tbl_Doctor] [LPA__1] INNER JOIN [dbo].[tbl_doctor_institution] ON [LPA__1].[doc_num]=[dbo].[tbl_doctor_institution].[doc_num] AND ( ( [dbo].[tbl_Doctor].[inst_flag] = @InstitutionFlag1))) INNER JOIN [dbo].[tbl_Doctor] ON [dbo].[tbl_Doctor].[doc_num]=[dbo].[tbl_doctor_institution].[inst_num] AND ( ( [dbo].[tbl_Doctor].[inst_flag] = @InstitutionFlag2))) WHERE ( [dbo].[tbl_Doctor].[inst_flag] = @InstitutionFlag3 AND ( ( ( [dbo].[tbl_doctor_institution].[doc_num] = @DoctorId4))))

The part above that is bolded should be [LPA__1]. It isn't recognizing tbl_doctor in this part of the query. Even if it did, tbl_doctor would be incorrect here as it would then be referencing the second table instance specified that doesn't have an alias. For this query to be correct, it would need to be referencing the first table in the FROM clause here.

Thanks again, Marc

Now I see what you mean. We will be looking into it.

Walaa wrote:

Now I see what you mean. We will be looking into it.

Great. Much appreciated.

Just as a feeble minded suggestion from someone who has just scratched the surface of what LLBLGen does behind the scenes, wouldn't it make sense if every table referenced in a query were to automatically get its own alias, and use those aliases instead of the table names for everything in the query, regardless of whether or not it is needed? I would much rather see a bunch of [LPA__1].whatever and [LPA__2].whatever in the select clause, even for tables that are only used in the FROM clause once, than seeing [tbl_doctor] referenced when there are multiple joins to tbl_doctor in the FROM clause.

I'm sure you guys have a reason for not doing so. I am just curious... other than these little issues with aliasing that we've been hitting, LLBLGen is the best code generation tool and ORM we've used, and we've tried them all! So keep up the good work, guys.

Lusid wrote:

Walaa wrote:

Now I see what you mean. We will be looking into it.

Great. Much appreciated.

Just as a feeble minded suggestion from someone who has just scratched the surface of what LLBLGen does behind the scenes, wouldn't it make sense if every table referenced in a query were to automatically get its own alias, and use those aliases instead of the table names for everything in the query, regardless of whether or not it is needed? I would much rather see a bunch of [LPA__1].whatever and [LPA__2].whatever in the select clause, even for tables that are only used in the FROM clause once, than seeing [tbl_doctor] referenced when there are multiple joins to tbl_doctor in the FROM clause.

I'm sure you guys have a reason for not doing so. I am just curious... other than these little issues with aliasing that we've been hitting, LLBLGen is the best code generation tool and ORM we've used, and we've tried them all! So keep up the good work, guys.

The reason is that the query fragments are created independently of eachother. So the select list, the from list, the where list etc. are all outsourced to classes or routines. This is done to re-use code to the maximum and leverage the OO style of the query system. The downside is that there's no total governing routine which performs the whole query production and thus knows every alias.

The main problem occurs when you have a list of fields to fetch and there's no reference to an alias IN the field object. This means that it refers to a plain table name without an alias.

However, there are situations where tables have to receive an alias automatically. In this situation, it's then important to be able to find the right automatically assigned alias for a given table T, the same table a given field without a reference to an alias refers to.

This is easy till twice the same table is found in the query. (or better: twice the same entity).

During v2.5 development we looked deep into this and tried the 'give everything an alias' method for some time, but it fell apart in the dynamic/typed list area. Not for entities, one can cheat there, the problem is in the dyn. list, where you have fields from multiple entities which are in the same inheritance hierarchy That's the big problem. Other o/r mappers don't have this issue as they don't offer this feature most of the time.

The 'AND' clause in your query is indeed what's wrong. There, the alias isn't properly assigned. This is odd, and likely a bug. We'll look into it.

The error is pretty bizarre, as the 'AND' clause is produced in a loop, with the same pk/fk reference for every predicate in the ON clause (you have two predicates in the first ON clause, and the second refers to a different PK reference, not LPA__1 but [dbo].[tbl_Doctor], however the same variable is used inside the loop, so how that one got reset is beyond me but I'll try to repro it with a similar table setting.

(edit) I see it's a custom filter for the TYPE, it filters on the type in that second predicate in the first ON clause. That predicate doesn't refer to the alias used in the first predicate. Will look into that.

In v2.5, the EntityRelation contains a dumb bug (line 656 and 662, refer to fk alias, which should be pkAlias of course ). Fixed in next build (which will be released monday).

Otis wrote:

In v2.5, the EntityRelation contains a dumb bug (line 656 and 662, refer to fk alias, which should be pkAlias of course ). Fixed in next build (which will be released monday).

Thanks for looking into this. I'll test this out and let you know if it works for me as soon as I'm able.

Yep! That fixed it! You're my hero, Otis. Thanks again.