Missing columns from SQL Server, possibly due to naming convention?

Blue_Ninja

Hello,

I recently ran into an issue where I couldn't see one of the table columns in LINQPad that I could see in Management Studio. I think the problem may be due to EF naming conventions.

The table in question has an identity column "MedicationTypeID", and also a column unrelated to that called "MedicationType". Normally, EF would make MedicationType a navigation property using the ID column. In this case, neither are navigation properties or have relationships defined, but I think the naming convention trips things up.

Unfortunately, the database is a 3rd-party one that I just have to work with as-is, for better or (usually) for worse. Is there some way to circumvent this issue, or any suggestions on alternate causes/solutions?

Thanks!

nescafe

Is there some way to circumvent this issue => have you tried creating a view with altered column names?

Blue_Ninja

Thanks for the reply. In our current scenario, we aren't able to modify the database, so adding new views is not an option for us, and I'm hoping for a way to circumvent this on the client side.

I know EF has various convention classes for controlling behavior of code-first mappings & conventions and such, and am wondering if something similar is used by LINQPad to generate the data model from the database connection, such that a derived convention class could be built to detect this scenario and account for it?

Obviously, from the standpoint of EF and it's conventions, this is bad table design. But sadly, we programmers are often tasked with dealing with badly designed data sources, and just having to suck it up and deal with it.

My concern here is two-fold: First, there is no error or informational message given to indicate that some columns are not mapped and are thus unavailable. It took a while to figure out what was going on, because a coworker was accessing the database with a different tool, and we initially thought we were looking at different database instances. It was only because of recalling the EF mapping conventions and noticing the name as a potential conflict that I realized what might be happening.

Second, I would guess that this scenario, while not common, is probably also not particularly rare. Obviously the issue would be trickier if there were a column name that conflicted with the conventional navigation property name, when the navigation property DID exist - in this case, if MedicationTypeID did have a defined relationship, such that MedicationType was both another column name, and also a valid navigation property name. However, in the case where there is no navigation property to conflict with the column name, I think that the column name should still be available.

I'm not sure whether this problem lies within EF itself, or if it's something that can actually be addressed within LINQPad. I'm obviously hoping for the latter, since I have more confidence that Joe could address it more quickly than Microsoft could!

JoeAlbahari

Are you sure you're using EF? It sounds like you're using LINQPad's built-in LINQ-to-SQL data context generator. If so, then yes, it can trip up when columns are named badly. It does its best to infer the relationships correctly, but it cannot always get it 100% right.

A workaround is to create a typed data context in Visual Studio using Entity Framework or LINQ-to-SQL, and then connect to that context via LINQPad. Click 'Add Connection', and then choose 'Use a typed data context from your own assembly.'

More info here:

http://www.linqpad.net/EntityFramework.aspx

Blue_Ninja

Hi Joe,

Thanks for your reply, and of course all your hard work! I can't tell you how much easier life is with LINQPad and my Premium license. Easily one of the most important tools in my collection.

You are correct, I was using LINQPad's built-in LINQ-to-SQL context generator, not EF. I was unclear how much of that used the same underlying code as EF when it came to parsing the schema.

Last night, I did create a context in VS, and that does seem to work, giving the column the name "MedicationType1". So, it's a valid workaround - thanks for the suggestion.

If this could be handled in LINQPad, that would be fantastic. In my current scenario, the vendor has multiple instances of the database, for production, test, dev, and training. They often make changes to the schema in dev, and training is often out-of-sync with production. So many times, all 3 will be a bit different. I foresee spending way too much time re-building the context in VS every few days, whereas LINQPad dynamically doing that would be a big time-saver.

Thanks again!