Invalid Column Name

 Hello,

I have just been trying to convert some Linq to Sql to Lightspeed, but I am getting an invalid column name error.

I have noticed that there are several differences on how the designer behaves in LightSpeed, One of those differences is that my foreign key 'RiskType' (our convention is that foreign keys that are 'lookup' keys are not suffixed with Id, but the domain...) seems to be renamed as RiskTypeId.  Then when I run my Linq query:

var r = UnitOfWorkScope.Current.ProgrammeOfWorks.OrderBy(c => c.ApplicationContactName);

All of the columns named following our convention (i.e. RiskType) return the error invalid column name.

Due to the fact I created these tables and dragged them into the design surface - this seems to be a bug?

I have checked in the designer and the Entity properties of each table do seem to reflect what is in the database... 

That's right: LightSpeed's convention is to use RiskType for the associated RiskType entity, and RiskTypeId for the foreign key.  This convention is enforced in the framework, and you won't be able to get around it in your object model.

However, what you can do is tell LightSpeed that the RiskTypeId field in the object model should be mapped to the RiskType column in the database.  To do this in the designer, select the RiskType association, go to the Properties window, and set the Column Name to RiskTypeId.

(If this is already set, and LightSpeed is still requesting invalid columns, please send us the .lsmodel and generated .cs or.vb file, and the generated SQL that you're seeing.  You can attach a zip file via the Options tab, or email it to me at ivan @ the obvious domain name.  You can capture the generated SQL using LightSpeedContext.Logger.)

You're also correct that drag and drop should pick this up when inferring the association from the FK, and should automatically apply the Column Name for you.  If you can provide us with the CREATE TABLE scripts for your tables, we'd be happy to look into why this isn't happening for you.

Thanks for you prompt reply!

I renamed the association column names, but that made no difference.  However when I renamed the column names in the database it did fix those particular issues (there are other 'naming' issues I have yet to resolve however).

There is also another 'standards' issue where I have used a natural key in the Application table... which may not be great database design but does force the referential integrity I wanted.

After 'fixing' the db, I how found that some of the linq code I have no longer runs

            var query = (from a in UnitOfWorkScope.Current.Applications
                         select new
                         {
                             Id = a.ApplicationTypeId,
                             IdLink = a.BusinessGroupTypeId,
                             Name = a.ApplicationType.CodeName
                         });

Without the select new it works fine, but with the select new I get "object reference not set to an instance of the object"

This is probably because one of the Applications has a null ApplicationType.  Normally LightSpeed tries to perform projections in the database, as LINQ to SQL does.  However, LightSpeed 2 has an architectural limitation which prevents it from projecting across multiple tables in a single query.  So when the projection involves selecting through an association (e.g. a.ApplicationType.CodeName), it does a normal entity select, and performs the projection client-side.  This will result in slightly different behaviour from doing a database join because the projection will fail (with a NullReferenceException) on a null association rather than filling in a null value.

This will be addressed in LightSpeed 3; unfortunately it's not something we can fix in LightSpeed 2.

The workaround is to write the projection to handle the null case:

var query = from a in UnitOfWorkScope.Current.Applications
                 select new {
                   Id = a.ApplicationTypeId,
                   IdLink = a.BusinessGroupTypeId,
                   Name = (a.ApplicationType == null ? null : a.ApplicationType.CodeName)
                 };

As I said, LightSpeed 3 should address this issue properly by performing the projection in the database.

Thanks for the repro information for the other issue; I'll take a look.  I noticed that your Column Name settings for the associations in the .lsmodel and .cs are e.g. RiskTypeId rather than RiskType, but the CREATE TABLE SQL scripts still have the column name as RiskType.  I'm assuming that the SQL scripts come from before you renamed the columns in the database, but the .lsmodel and .cs files come from after you renamed the columns in the database.  If you want to keep your existing database column names so as to avoid migration issues, you definitely should be able to set Column Name = RiskType (and retain your original database schema); if that doesn't work for you we would be very pleased to investigate.

I loaded up your tables and tried to replicate the problem you reported where it wasn't automatically inferring the foreign key column names, but it's definitely working in our test environment.  The database column names are inferred for all the associations except (1) the key from ReferenceValue to ReferenceList, where the database column is already called ReferenceListID, so we don't need to override the LightSpeed default column name; and (2) the ApplicationType natural key in Application.

The natural key is, unfortunately, going to be a problem.  LightSpeed expects to be able to assign IDs to entities.  (There is a way around this, but it's a bit messy.)  So natural keys don't really work in LightSpeed unless you're dealing with read-only data (such as reference data).  This assumption cascades over into the designer, which simply doesn't try to handle the case where a primary key is also a foreign key.  You could try setting the column name by hand here, but I'm not sure how LightSpeed will cope with having two fields (the Id field and the ApplicationTypeId field) mapping to the same database column.

Ideally, we would recommend adding a surrogate key here, but if you do need to keep the natural key, a possible alternative is to not expose the Application <-> ReferenceValue ApplicationType association at the LightSpeed level, but instead, simulate it with queries in the partial classes, e.g.

partial class Application {
  public ReferenceValue GetApplicationType() {
    return UnitOfWorkScope.Current.ReferenceValue.FirstOrDefault(rv => rv.Id == this.Id);
  }
}

(The BusinessGroupType association wouldn't need this workaround because it's a normal foreign key column, not a combined PK/FK.)

Thanks for looking into that.  I have recreated my project from scratch as the conversion from linq to sql to lightspeed, was not going smoothly.  After refactering the database (I am in a position where I can dictate standards), that is all going well.  I do still have some issues...

var r = UnitOfWorkScope.Current.ProgrammeOfWorks.OrderBy(c => c.ApplicationContactName).Where(
                    c => c.Application.BusinessGroupType.Id == filters.business || filters.business == null);

The where statement (and the original code has a few more) causes the error "The method or operation is not implemented."

The other issue, is that when I go to save my record

  UnitOfWorkScope.Current.Attach(programmeOfWork);
            }

            UnitOfWorkScope.Current.SaveChanges();

I get a bizzare  error Invalid object name 'dbo.ProgramOfWork'. I have no reference to ProgramOfWork in my project (I renamed the table yesterday - before I created this new VS project) - I will look into what sql is being created here...

It looks like we have an issue when you have an && or || clause which combines a traversal to another table (c.Application.BusinessGroup.Id) with a constant value (filters.business == null, which will resolve to either true or false regardless of the c range variable).

We are looking into a fix but for the time being I would hoist the constant part of the || expression out of the query:

var r = UnitOfWorkScope.Current.ProgrammeOfWorks.OrderBy(c => c.ApplicationContactName);
if (filters.business != null) {
  r = r.Where(c => c.Application.BusinessGroupType.Id == filters.business);
}

(I actually find this version a bit easier to understand, though that is of course subjective!)

Yes I agree that is more readible...

lol, I am beginning to think I should go back to what I know and write a stored procedure here.  There is also an issue on some orderby's 

OrderBy(
           c => c.Application.ApplicationType == null ? null : c.Application.ApplicationType.CodeName)

 {"Invalid column name 'Id'.\r\nInvalid column name 'ApplicationType'."}

Which I am guessing is more issues with nested tables.... I can remove the condition, and I still get the error

You're right, this is a nesting issue.  OrderBy clauses in LightSpeed are limited to a single table traversal.  For example, OrderBy(c => c.Contribution.Title) is supported, but OrderBy(c => c.Contribution.Contributor.Name) is not.  Sorry.

Also, we don't support conditional expressions in OrderBy clauses, but they shouldn't be necessary anyway, as the order by clause gets translated to SQL (because it can be applied to a single table, unlike the cross-table projections which were where this came up before), so the database handles the null scenario for you.