Where returns wrong record

Question

UPDATE

I've just had a thought that may well be relevant to this issue. I'm using a code first approach with this project. Originally my ZoneMapping class was defined as you can see below, however the database only had a single PrimaryKey field in the database. I believe because EF hadn't interpreted the data quite correctly.

At this point I made a modification to my migration SQL script output to add the additional primary key that I applied to the database. I've just updated the migration instead from:

       CreateTable(
            "dbo.NetC_EF_ZoneMapping",
            c => new
                {
                    PostcodeKey = c.String(nullable: false, maxLength: 128),
                    Zone_ID = c.Int(),
                })
            .PrimaryKey(t => t.PostcodeKey)
            .ForeignKey("dbo.NetC_EF_Zone", t => t.Zone_ID)
            .Index(t => t.Zone_ID);

I've just tried adding an additional PrimaryKey manually in the migration after the PostcodeKey one has been defined.

 .PrimaryKey(t => t.Zone_ID)

Unfortunately I'm still getting my error - I'm assuming this migration isn't used to build the EF 'model' in code, but I'm wondering if it thinks that there can only be a single entry with any given PostcodeKey which may explain the situation?

---

I'm posting a new question based on Linq Except not functioning as expected - duplicate items because I feel that enough has been discovered that the question is invalid, and Except is not the problem at all.

The problem I have is that I have a Linq Where clause that seems to be returning the wrong data. The data in my database looks like:

The class that represents this data has a compound key:

/// <summary>
/// Represents a mapping between a postcode and a zone
/// </summary>
[Table("NetC_EF_ZoneMapping")]
public class ZoneMapping
{
    /// <summary>
    /// Gets or sets the postcode identifier
    /// </summary>
    [Key]
    public String PostcodeKey { get; set; }

    /// <summary>
    /// Gets or sets the Zone identifier
    /// </summary>
    [Key]
    public Zone Zone { get; set; }
}

So I'm executing the following code, which results in different IDs:

var result = this.context.ZoneMappings.Include("Zone").Where(z => z.Zone.ID == 257 && z.PostcodeKey == "2214");
var result2 = new FreightContext().ZoneMappings.Include("Zone").Where(z => z.Zone.ID == 257 && z.PostcodeKey == "2214");
if (result.First().Zone.ID != result2.First().Zone.ID)
     throw new InvalidOperationException();

The SQL (or ToString() for these two items is identical). So the only difference is that one is a new context, while the other has been passed in and used for some other stuff. The code that creates the context returning the wrong result is:

 // Copy the contents of the posted file to a memory stream
 using (StreamReader sr = new StreamReader(fileUpload.PostedFile.InputStream))
 using (FreightContext context = new FreightContext())
 {
      // Attempt to run the import
      ZoneMappingCSVImporter importer = new ZoneMappingCSVImporter(sr, context, System.Globalization.CultureInfo.CurrentUICulture);
      var items = importer.GetItems().ToList();
      importer.SaveItems(items);
      this.successBox.Value = "Import completed and added " + items.Count() + " zones mappings.";
  }

This then registers a ClassMap in the library that I'm using where:

 public ZoneMappingCSVImporter(TextReader textReader, FreightContext context, CultureInfo culture)
 : base(textReader, context, culture)
 {
     this.reader.Configuration.RegisterClassMap(new ZoneMappingMap(this.context));
 }

I do a lookup using the context:

 /// <summary>
        /// Initializes a new instance of the <see cref="ZoneMap"/> class.
        /// </summary>
        public ZoneMappingMap(FreightContext context)
        {
            if (context == null)
                throw new ArgumentNullException("context");

            Map(m => m.PostcodeKey);
            Map(m => m.Zone).ConvertUsing(row =>
            {
                // Grab the name of the zone then go find this in the database
                String name = row.GetField<String>("Zone");
                return context.Zones.Where(z => String.Compare(z.Name, name, true) == 0).FirstOrDefault();
            });
        }

I can't see anything strange going on here, I've verified the SQL generated by Entity Framework, verified the database is the same one - I can't understand why the wrong record would be returned. Can anyone shed any light on this?

Answer 1

The explanation for this problem is most likely the following:

• Your way to define composite primary key in your entity is incorrect. With your current model EF only considers ZoneMapping.PostcodeKey as the primary key. More on this and how to fix it below.

• You get a wrong result in the case when the corresponding context already contains the entity with PostcodeKey == "2214" and Zone_ID == 256 before you run your query. (I guess you never had a wrong result2.) When EF loads entities from the database it always looks after the query if an entity with the same key already exists in the context. If yes, the queried entity is thrown away and instead the attached entity is added to the result collection. In your case you are querying for PostcodeKey == "2214" and Zone_ID == 257. After the query EF picks the values of the primary key from the result row. But because EF "thinks" the primary key is only PostcodeKey == "2214" it searches for an attached entity with that key value and finds the entity with PostcodeKey == "2214", but Zone_ID == 256 and returns that as the result to you. You never get this problem with result2 because the corresponding context is new and empty, so EF will return the result that just has been loaded, not any older attached entity.

If you want to define a composite key you must have scalar properties for both key parts. You cannot use a navigation property like ZoneMapping.Zone as a key part. It means that you must have a property for your column Zone_ID in the model class (in addition to the navigation property Zone):

public class ZoneMapping
{
    public String PostcodeKey { get; set; }

    public int Zone_ID { get; set; }
    public Zone Zone { get; set; }
}

Then in order to define a composite key with data annotations you must use the Column(Order = n) attribute as already shown in @Jonas Jämtberg's answer. You should also apply the ForeignKey attribute to Zone_ID because the underscore makes the property name "unconventional" so that EF won't detect it as FK by convention:

public class ZoneMapping
{
    [Key, Column(Order = 0)]
    public String PostcodeKey { get; set; }

    [Key, ForeignKey("Zone"), Column(Order = 1)]
    public int Zone_ID { get; set; }
    public Zone Zone { get; set; }
}

The migration class that is generated by EF with this mapping should have a primary key definition that looks like this:

.PrimaryKey(t => new { t.PostcodeKey, t.Zone_ID })

...not like .PrimaryKey(t => t.PostcodeKey).PrimaryKey(t => t.Zone_ID) that you tried unsuccessfully to fix the problem.