[RESOLVED] Entity Framework column naming

[cory_jackson]

I'm making my first use of EF and noticed something odd I hope someone can explain.

I have two SQL tables that I used the wizard to create the entities for which both have a column with the same name. The wizard added a "1" to the end of one of them apparently to make it unique. Can anyone help me understand why?

[techgnome]

Consider this:
Table1:
Field1
Field2
Field3

Table2:
Field4
Field5
Field1


Select Table1.Field1, Table1.Field2, Table2.Field1 from Table1 inner join table2 on table1.field2 = table2.Field4


from here it's obvious that the first Field1 is from Table1... but when it gets into EFD or a datatable... it sees Field1, Field2, Field1 ... so it needs to differentiate the two Field1s easiest way to do that is to add 1 to the end of it.

-tg

[cory_jackson]

I see. It seems improper to not have my column names match. Should I take care to use unique column names?

Also I notice the primary key fields with the name of "ID" were not altered.

[cory_jackson]

Or should I just let it increment the name? I'm worried that if I have to refresh the ORM it will be different at some point and not match with my code.

[techgnome]

Well, personally I wouldn't select two fields of the same name w/o giving one of them an alias to differentiate it, so it's never been an issue for me.

-tg

[cory_jackson]

Are you saying if you had tables Companies and Contacts and both had a phone number column you would do something like PhoneNumberCompany and PhoneNumberContact?

I feel like I'm doing something wrong.

[techgnome]

I'd use something a bit more concise like CompanyPhone and ContactPhone... but yeah.

-tg

[cory_jackson]

So every column in the entire data set should be unique? That just seems odd. EG Employee.EmployeePhone instead of Employee.Phone.

Anyways thanks for the advice.

[techgnome]

Only if there are duplicates in the same set... if the set only contains employees and they only have one phone, then call it Employee.PHone ... but if you've joined it to the Company table, and Company ALSO has a phone... then you need tl difereniate it....all the datatable sees is just the field names... it doesn't know or care that one is from one table and the other is from a different table... all it knows is what's in the result set, which means Phone appears twice... so when you ask it for hte "Phone" field... it's going ***? Which one do you want? So, yes, in thatcase you need to give them an alias - I'm NOT saying change the field in the database, jsut in the rsultset, give it an alias.


-tg

[cory_jackson]

Thanks a lot, that's a good explanation. It's a strange way of thinking. I'm used to things being like duplicate file names in different directories or any other hierarchical name space.Odd that EF abandons this.

I did an experiment and I see if I change the column name in the local model and refresh I don't lose the new alias.

In this case I think I'll create a relationship. I don't need a relationship but in this case I think it should work and I can call the one CountyID.

[techgnome]

It's not that EF abandons this... that's the way it is in databases.... period...

if you have this as your query:

Select C.ID, C.Name, C.Phone, Cn.Id, Cn.Name, Cn.Phone
from Company C
inner join Contact Cn on C.ID = Cn.CompanyID

and run it in SSMS (or more query designers)... you'll get back

Id   Name   Phone    Id    Name     Phone

That's what EF is seeing... if you run that query and use it to fill a datatable, that's what it sees... nothing more, nothing less... so if you then try to access the "ID" field of the datatable, it will (or should error out) because it doen't know which of the two ID fields you wanted. So you alias them:

Select C.ID as companyId, C.Name as companyName, C.Phone as companyPhone, Cn.Id as contactID, Cn.Name as contactName, Cn.Phone as contactPhone
from Company C
inner join Contact Cn on C.ID = Cn.CompanyID

Now the resultset has these cols:

companyId   companyName   companyPhone    contactId    contactName     contactPhone

keep in mind that data in most databases is RELATIONAL (that's the R in RDBMS) not hierarchical. We apply a hierarchy to it because that's how we're wired, but really it's a relationship.

-tg

[cory_jackson]

Ohhhhhhhh!!!! Now it makes sense! Thank you very much for taking the time to make such a cogent and thorough explanation.