I believe this is indeed a defect. I will include all the information that I can give on it.

I have three tables in a dynamic join. The main part of the relationship is that there is a link table (InvPavLink) that links the other two tables. I have only included the select and join portion of the query in this example. The group and where logic is not included here.

InvPavLinkQuery pavLink = new InvPavLinkQuery("pl");
InvMainQuery inv = new InvMainQuery("i");
NsiPAVQuery pav = new NsiPAVQuery("p");

pavLink.Select(
    pavLink.InvID,
    pav.SurveyID,
    pavLink.Id.Count().As(PAV_COUNT_FIELD_NAME));
pavLink.InnerJoin(inv).On(inv.Id == pavLink.InvID);
pavLink.InnerJoin(pav).On(pav.Id == pavLink.PavID);

The issue is with the generated SQL with pav.SurveyID. The underlying field (legacy) is called "Survey_ID". The generated query is as follows (select and join parts only).

SELECT pl.[InvID],p.[Survey_ID] AS 'SurveyID',COUNT(pl.[ID]) AS 'PavCount'  
FROM [InvPavLink] pl INNER JOIN [InvMain] i ON i.[ID] = pl.[InvID] INNER JOIN [Nsi_PAV] p ON p.[ID] = pl.[PavID]

The issue is "p.[Survey_ID] AS 'SurveyID'". When I use a filter on the result set with the NsiPAVMetadata.ColumnNames.SurveyID definition it doesn't match and throws an error.

I believe that it is just a case of the property name and not the column name being used in the query generation. I worked around the issue by adding an explicit "AS" like this "pav.SurveyID.As(NsiPAVMetadata.ColumnNames.SurveyID),"

Thanks,

-Mark E.
 

David,

Thanks for the response. After checking into it further (comparing to a straight select) I found the difference in behavior. Please correct me where I am wrong.

In the InvMain table that I am accessing, there is a field called OM_ID. This is a legacy field and the name is pretty stupid but it is what it is.

Anyway, the in-code query looks like this...

InvMainCollection invTest = new InvMainCollection();
invTest.Query.Select(invTest.Query.OmId);
invTest.Query.Load();

The generated SQL is this...

SELECT [OM_ID]  FROM [InvMain]

The difference is that no "AS" is being performed so the dataset has the field named "OM_ID" and not the property name of "OmId". So the problem is seen in the filters but I believe that the query generation is inconsistent between a straight query and join query.

Thanks again. If there is anymore help or information I can provide just let me know. If I'm wrong I won't get my feelings hurt.

-Mark E.
 

Mike,

You are right, the SQL is not invalid. I just find it to be inconsistent between a straight query and a join query. Please see my previous (and more detailed) response to David in the thread.

 Thanks,

-Mark E. 

Mike,

Thanks for the reply. Please correct me if I'm wrong. I have a working solution for myself but I would like to understand this and, if appropriate, have the behavior changed to be more consistent.

What I believe is inconsistent is that a field with an underscore is treated differently in a straight query versus a join query. A straight query on a field called "OM_ID" returns a field in the dataset called "OM_ID" (using the ColumnName). This means the filter will use the column name. This is how it has been for a long time.

What is new is the join query returning a field called "Survey_ID" as "SurveyID" in the dataset. This means the filter requires using PropertyName and not the ColumnName.

I believe that to be consistent they should both be using the ColumnName for their resulting values that end up in the dataset.

Imagine a very realistic situation where I may execute a static method to generate a result set for me. I may have different params that go into the method that determine HOW the result set is to be prepared. In one case it might use a join query and another it might not. By default, the result set will have different named columns and any filters used on that result set would need to know how it was built. Otherwise, like me, I have to explicitly name the field using an AS to keep it consistent.

Does this make sense? I hope so. I'm just trying to understand it and express what I believe is inconsistent.

Thanks for your patience with me.

-Mark E. 

Ahh, I see. I think you are correct and here is why.

A typical property in EntitySpaces looks like this:

virtual public System.Int32? EmployeeID
{
	get
	{
            return base.GetSystemInt32(EmployeesMetadata.ColumnNames.EmployeeID);
	}
	
	set
	{
             base.SetSystemInt32(EmployeesMetadata.ColumnNames.EmployeeID, value);
	}
}

Notice that the property name is EmployeeID but the value of "EmployeesMetadata.ColumnNames.EmployeeID" could be "Employee_ID". So far this makes sense, We support the alias'ing of columns so that users can have nice names and never need to work with the ugly physical column name.

Now, when it comes time for a join you bring back "extra" columns for which they are no strongly typed property accessors. Not only that, but the joined columns are not Saved to the database so it is not necessary to use the physical column name. We decided that what users would really want as the column names was the property name and not the underlying physical table name, so you are correct. When a join is in the works we bring back the column in the underlying DataTable as the Property name since you used the Property name in the Select statement.

We will have to document this behavior, sorry I was being so dense and hope this explanation makes sense, feel free to follow up for more information.
 

Mike,

Thanks for the details and the explanation. I appreciate you taking the time to explain this. My last question is then what, if anything, will change?

My vote would be to use the column name in the select of a join query. But I understand your perspective as well.

The reason I'm asking is just so I can plan for how we should write our code when using fields with underscores. If no coding change is planned I would just like to know that. I might fight more for changing the database's field names to remove underscores (a big task) so that the developers don't have to track those kinds of details in the underlying structure as they write their code.

Thanks again,

-Mark E. 

Mike,

In my last message my question could be better worded as...

"My interpretation of what you wrote is that the behavior is by design and the only change needed is to document the behavior. Is this correct? Or is an actual code change possible?"

Thanks,

-Mark E.