Problem with many-many and 1-many relationships.

I have 2 classes IrpGoal and IrpPlan aranged as per the attached diagram (you need a friendly way to insert images btw).  The PKs are GoalId and PLanId respectively. 

I have been using them in a many-many relationship.  However I now want to add a Ownership, 1-many, relationship using the field PlanId on IrpGoal. 

The moment I do this, Lightspeed stops retriving the PlanId property and I get errors such as

LightSpeedException: Unable to materialize field [PlanId] on type [CmsModels.Entities.IrpGoal]. Check your table has an Id column and that your mappings are correct. See inner exception for details.

The generated sql is something like

SELECT
  RtwGoals.*
FROM
  (
    SELECT
      RtwGoals.GoalID AS [RtwGoals.GoalID],
      RtwGoals.AddToDo AS [RtwGoals.AddToDo],
      RtwGoals.DateCompleted AS [RtwGoals.DateCompleted],
      RtwGoals.EstCost AS [RtwGoals.EstCost],
      RtwGoals.Goal AS [RtwGoals.Goal],
      RtwGoals.GoalDate AS [RtwGoals.GoalDate],
      RtwGoals.GoalType AS [RtwGoals.GoalType],
      RtwGoals.IsGoal AS [RtwGoals.IsGoal],
      RtwGoals.IsMilestone AS [RtwGoals.IsMilestone],
      RtwGoals.PlanId AS [RtwGoals.PlanId],
      RtwGoals.Responsiblity AS [RtwGoals.Responsiblity],
      RtwGoals.Review AS [RtwGoals.Review],
      RtwGoals.SectionId AS [RtwGoals.SectionId],
      RtwGoals.ToDoId AS [RtwGoals.ToDoId],
      ROW_NUMBER() OVER(ORDER BY RtwGoals.GoalID) as RowNumber
    FROM
      RtwGoals
  )
  RtwGoals
WHERE
  RowNumber <= 50

Notice the lack of PlanId

I am using v3.0.973.12836 aka -20/1/2010

Can this get fixed?

Sean

I'm not sure what's causing the materialisation error -- we can't reproduce it -- but PlanId is certainly being queried for, about two thirds of the way down the column list:

      RtwGoals.IsMilestone AS [RtwGoals.IsMilestone],
      RtwGoals.PlanId AS [RtwGoals.PlanId],                            -- **********
      RtwGoals.Responsiblity AS [RtwGoals.Responsiblity],

What's the InnerException?  Could there be a type mismatch issue?

Doh.  I missed the PlanId there.

Running

IrpGoals.Take (1)

gives sql

SELECT
  RtwGoals.*
FROM
  (
    SELECT
      RtwGoals.GoalID AS [RtwGoals.GoalID],
      RtwGoals.AddToDo AS [RtwGoals.AddToDo],
      RtwGoals.DateCompleted AS [RtwGoals.DateCompleted],
      RtwGoals.EstCost AS [RtwGoals.EstCost],
      RtwGoals.Goal AS [RtwGoals.Goal],
      RtwGoals.GoalDate AS [RtwGoals.GoalDate],
      RtwGoals.GoalType AS [RtwGoals.GoalType],
      RtwGoals.IsGoal AS [RtwGoals.IsGoal],
      RtwGoals.IsMilestone AS [RtwGoals.IsMilestone],
      RtwGoals.PlanId AS [RtwGoals.PlanId],
      RtwGoals.Responsiblity AS [RtwGoals.Responsiblity],
      RtwGoals.Review AS [RtwGoals.Review],
      RtwGoals.SectionId AS [RtwGoals.SectionId],
      RtwGoals.ToDoId AS [RtwGoals.ToDoId],
      ROW_NUMBER() OVER(ORDER BY RtwGoals.GoalID) as RowNumber
    FROM
      RtwGoals
  )
  RtwGoals
WHERE
  RowNumber <= 1

The sql runs and returns a PlanId of 100006.

The error is

"Unable to materialize field [SectionId] on type [CmsModels.Entities.IrpGoal]. Check your table has an Id column and that your mappings are correct. See inner exception for details."

The inner exception is

InvalidCastException "Object cannot be cast from DBNull to other types"

At

   at Mindscape.LightSpeed.Model.FieldModel.Materialize(IDataProviderAdapter , Object , Object[] , Int32& )
   at Mindscape.LightSpeed.Mapping.EntityLoader.(UnitOfWorkBase , String , Entity , Object[] ,  )
   at Mindscape.LightSpeed.Mapping.EntityLoader.LoadEntityFromReader(AliasedTypeModel , Object , Boolean )
   at ..(AliasedTypeModel , QueryExpression , Order , Group , String , Boolean , Boolean )
   at ..(LightSpeedContext , List`1 , QueryExpression , Order , Group , String , Boolean , Boolean )
   at ..(AliasedTypeModel , IList , String , Boolean )
   at ..(TypeModel , Query , IList )
   at ..(Query , TypeModel , IList )
   at ..(Query , IList )
   at Mindscape.LightSpeed.UnitOfWorkBase.Find(Query query)
   at Mindscape.LightSpeed.Linq.Plan.SingleQueryPlan.ExecuteImmediate(IUnitOfWork unitOfWork, Type returnType)
   at Mindscape.LightSpeed.Linq.LinqQuery`1.System.Collections.IEnumerable.GetEnumerator()
   at System.IO.TextWriter.SyncTextWriter.WriteLine(Object value)

I hope this helps.

Sean

It looks like SectionId is NULL in the database, but the SectionId property is non-nullable in your model.  Could this be the case?

No, section id = -1.  I have tried changing it to other values without solving the problem.

I made SectionId nullable just for laughts.  Now my linq works, but it show section id = null. 

If I run the sql directly, it shows SectionId = 10.

What is happening is that Lightspeed is mapping the wrong fields to the object.  See the attached image.

If I change Responsibilty from "0" (zero) to "Dave" if then get an error on "Unable to materialize field [PlanId] on type [CmsModels.Entities.IrpGoal]. " as it tries to set PlanId to "Dave".

Very strange.

By the looks of it, the cause is that Lightspeed has automatically created a field OwningPlanId, and I still have the  underlying PlanId field.  Lightspeed is retrieving the db PlanId value once for OwningPlanId and not for PlanId.  It is then populating the object by index instead of by field name so everything after OwningPlanId is getting the wrong value.

Now that I know the problem, I have fixed it in my code.  However I suggest you fix this at your end as it is a rather nasty problem.

Sean

Oh -- it looks like you've backed both OwningPlanId (the implicit association FK property) and PlanId (just an integer property) onto the PlanId column.  LightSpeed has queried for the column once, but is mapping it twice, so the second mapping is borked, and all subsequent mappings are off by one.

This is a bug and we will log it for a fix, but it only occurs because (if I'm right) you still have PlanId around as a standalone property.  You should remove the PlanId property.  Its job is now handled by the implicit OwningPlanId property.

If you need to keep the PlanId property for compatibility reasons, delete it out of the model and recreate it in the partial class, backing it onto the _owningPlanId field.

I have removed PlanId and all is well.  I find the whole associations thing a bit awkward.  I don't know why the association needs to create an implicit property when there is already an explicit property there.

Normally it's irritating rather than a problem, the compiler gives an error and I delete the offending property.  In this case, I renamed the association which then renamed the implicit property giving the problems.