Invalid column error with One-to-One scenario

I have only just downloaded the Lightspeed Trial so I'm sure I'm simply missing some basics but I can't figure this out. Thanks in advance for any help.

I have a very basic "use case" DB table scenario I'm trying to make sure that LS can support. I'm using MSSQL2008 and of course .NET3.5. Here's my table structure:

Table: People
Person_Id (int, Primary key)
Name (varchar)

Table: Cars
Car_Id (int, Primary key)
Person_Id (int, Foreign key to People table)
Name (varchar)

For the sake of argument, let's say in our pretend world, each person can only have one car. So when Lightspeed makes the model for these 2 tables, it creates a "One-to-Many" relationship. No problem, we can simply change that to a "One-to-One" with ease. Let's also assume I turn on eager loading for Cars. Now here's the problem.

A given person might not have a car at all. In Linq-To-SQL, I could simply check the "person.Car" property on a person, and if it was not null, access its properties like normal. But with Lightspeed, when I try to check the Car property of a person, if that person doesn't have a Car, Lightspeed throws an exception that says "Invalid column name 'PersonId'".

My expected result is to see the person query with a "LEFT JOIN" to the Cars table, and if it results in no matching "Car" row, then the "Car" property should simply be null.

Please tell me I am missing something obvious. :)

You are right that if there is no Person associated with the Car then the Car property should be null.  (I think we actually achieve this via a subselect rather than a left join, but that's a detail.)

From the error message it looks like your Car type has a PersonId property, which is causing LightSpeed to look for a PersonId column in the Cars table, whereas the actual database column is called Person_Id.  To fix this, you just need to tell LightSpeed what column name to look for:

* If you are creating the Car class in C#/VB code, find the _personId field and add the attribute [Column("Person_Id")].

* If you are using the designer, unfortunately we don't currently provide a way to customise the generated foreign key field.  (We have it for one-to-many associations, but not for one-to-one.)  I'll get this added and let you know.  Sorry for the inconvenience.

Hello Kyle,

I've committed an update for the designer so that you can now specify the Column Name of a one-to-one association.  This will be included in nightly builds dated 29 Apr 2009 and above, available from http://www.mindscape.co.nz/products/lightspeed/nightlybuilds.aspx after about 1430 GMT.  Select the one-to-one association arrow and set Column Name to Person_Id and you should be good to go.  Please let us know if you still see problems.

Thanks for the response ivan!

After looking into this further, I believe this might actually be a bug with changing a One-to-Many relationship to a One-to-One relationship. But of course there could be a very normal reason for this behavior. :)

I have not tried the daily build yet but I don't think it affects my scenario:

Following my original example, if I *leave* the relationship as a One-to-Many, I can clearly see the [Column("Person_Id")] and everything works correctly (if I simply query the Cars collection instead of having a "Car" property to work from.)

However, whenever I change the relationship to a One-to-One, I see that the [Column("Person_Id")] attribute is removed from the _personId field. Thus causing the error described above.

I would expect the designer to remain aware of what column name a relationship was created on, even if it changes types. Should it?

The good news is I can manually add the attribute and everything works as expected...which is fantastic! Thanks so much for your help and hopefully this is something with an easy fix or workaround. :)

The reason the Column attribute got lost in the convert operation was because the designer's internal representation of one-to-one associations didn't allow for a column name.  This is now fixed, so if you have a one-to-many association with an overridden column name, and convert it to a one-to-one association, the column name will be preserved, as per your expectation.

Please note that adding the ColumnAttribute manually into the generated code is not recommended except as a very temporary workaround -- it will be overwritten next time you edit the model.  So we would encourage you to upgrade to the nightly build anyway, and apply the column name at the model level rather than in the generated code.

IMPORTANT: If you are upgrading from 2.2 RTM to the current nightly, please uninstall 2.2 first.  If you don't do this, you are likely to get errors when using the designer (if this does happen it can be fixed by doing an uninstall-reinstall).  Subsequent nightly build upgrades should NOT require another uninstall cycle.

Great, thanks so much for the response!