Entity Framework 4.1 Ghost Columns

Question

I am currently having some issues with EF4.1. The generation of the the SQL doesn't seem to match what I expect to be generated based on my classes. I have the following classes (This is just a smaller set of a bigger collection, however, this is where I appear to be having issues)...

public class CustomEntityContext : DbContext
{
    public CustomEntityContext()
             :base(ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString) {}

    public DbSet<Person> People { get; set; }
    public DbSet<Occurrence> Occurrences { get; set; }
    public DbSet<OccurrenceSecurity> OccurrenceSecurities { get; set; }
}


[DataContract(IsReference = true)]
[Table("Occurrence")]
public class Occurrence 
{
    [DataMember] public int ID { get; set; }
    [DataMember] public string Number { get; set; }
    [DataMember] public bool? IsMOR { get; set; }
    [DataMember] public bool? IsConfidential { get; set; }
    [DataMember] public int? IncidentID { get; set; }
    [DataMember] public bool? CanPublish { get; set; }
    [DataMember] public bool? IsFeedbackRequired { get; set; }
    [DataMember] public bool? IsRegulatorReport { get; set; }
    [DataMember] public DateTime? RecordedDate { get; set; }
    [DataMember] public DateTime? ReportedDate { get; set; }
    [DataMember] public int? ReportTypeID { get; set; }
    [DataMember] public bool? IsMain { get; set; }
    [DataMember] public bool? IsRejected { get; set; }  
    [DataMember] public string Title { get; set; }
    [DataMember] public byte[] Version { get; set; }
    [DataMember] public string ReportDataXml { get; set; }
    [DataMember] public int? LocationID { get; set; }
    [DataMember, ForeignKey("RecordedByPersonID")] public Person Recorder { get; set; }
    [DataMember, ForeignKey("ReportedByPersonID")] public Person Reporter { get; set; }
}

[DataContract(IsReference = true)]
[Table("OccurrenceSecurity")]
public class OccurrenceSecurity
{
    [DataMember, Key, Column("PersonID", Order = 0)] public int PersonID { get; set; }
    [DataMember, ForeignKey("PersonID")] public Person Person { get; set; }
    [DataMember, Key, Column("OccurrenceID", Order = 1)] public int OccurrenceID { get; set;          
    [DataMember, ForeignKey("OccurrenceID")] public Occurrence Occurrence { get; set; }
}

[DataContract(IsReference = true)]
[Table("Person")]
public class Person 
{
    [DataMember] public int ID { get; set; }
    [DataMember] public string FullName { get; set; }
    //[DataMember] public Occurrence[] RecordedOccurrences { get; set; }
    //[DataMember] public Occurrence[] ReportedOccurrences { get; set; }
    //[DataMember] public OccurrenceSecurity[] OccurrenceSecurities { set; get; } 
}

When I ask for the OccurrenceSecurities, with the Include methods, I ask to include both the Occurrence and Person. Subsequently, the SQL that is generated is as follows...

SELECT 
[Extent1].[PersonID] AS [PersonID], 
[Extent1].[OccurrenceID] AS [OccurrenceID], 
[Extent2].[ID] AS [ID], 
[Extent2].[FullName] AS [FullName], 
[Extent3].[ID] AS [ID1], 
[Extent3].[Number] AS [Number], 
[Extent3].[IsMOR] AS [IsMOR], 
[Extent3].[IsConfidential] AS [IsConfidential], 
[Extent3].[IncidentID] AS [IncidentID], 
[Extent3].[CanPublish] AS [CanPublish], 
[Extent3].[IsFeedbackRequired] AS [IsFeedbackRequired], 
[Extent3].[IsRegulatorReport] AS [IsRegulatorReport], 
[Extent3].[RecordedByPersonID] AS [RecordedByPersonID], 
[Extent3].[RecordedDate] AS [RecordedDate], 
[Extent3].[ReportedByPersonID] AS [ReportedByPersonID], 
[Extent3].[ReportedDate] AS [ReportedDate], 
[Extent3].[ReportTypeID] AS [ReportTypeID], 
[Extent3].[IsMain] AS [IsMain], 
[Extent3].[IsRejected] AS [IsRejected], 
[Extent3].[Title] AS [Title], 
[Extent3].[Version] AS [Version], 
[Extent3].[ReportDataXml] AS [ReportDataXml], 
[Extent3].[LocationID] AS [LocationID], 
[Extent3].[Person_ID] AS [Person_ID],               -- Where does this come from?
[Extent3].[Person_ID1] AS [Person_ID1]              -- Where does this come from?
FROM   [dbo].[OccurrenceSecurity] AS [Extent1]
INNER JOIN [dbo].[Person] AS [Extent2] ON [Extent1].[PersonID] = [Extent2].[ID]
LEFT OUTER JOIN [dbo].[Occurrence] AS [Extent3] ON [Extent1].[OccurrenceID] = [Extent3].[ID]

As you can see, there are 2 columns at the end of the Select block which are selecting Person_ID and Person_ID1. These don't exist in my underlying table, or in my object.

Does anyone know where these come from and why they are there?

Also, I am aware that this is a Many-to-Many relationship, however, the OccurrenceSecurities table/class will extend to hold more data.

Thanks, David

Answer 1

with the Include methods, I ask to include both the Occurrence and Person

These extra columns will be used by EF to construct the object graph from the query results. By using Include, you are saying "I want to only execute one store command, but I want to retrieve many objects". Rather than using multiple resultsets (which not all backing stores will support), EF instead issues a query with results like this, when you query A.Include("B.C"):

columns for A1 columns for B1 columns for C1
columns for A1 columns for B1 columns for C2
columns for A1 columns for B1 columns for C3
columns for A1 columns for B2 columns for C4
columns for A1 columns for B2 columns for C5
columns for A2 columns for B3 columns for C6
columns for A2 columns for B3 columns for C7

and then stitches together these rows to make two A, 3 Bs and 7 Cs, with the appropriate relationships.

The particular extra columns you show are used by EF in the stitching-together process, is my guess.