Where the wrong entry returns

Question

UPDATE

I just had a thought that may very well be related to this problem. I am using the first code approach with this project. Initially, my ZoneMapping class was defined, as you can see below, however, there was only one PrimaryKey field in the database in the database. I believe that EF did not correctly interpret the data.

At this point, I made changes to my migration SQL script output to add an additional primary key that I applied to the database. I just updated the migration:

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 just tried adding an additional PrimaryKey manually in the migration after defining PostcodeKey.

  .PrimaryKey(t => t.Zone_ID) 

Unfortunately, I still get my error - I assume that this migration is not used to create an EF model in the code, but I wonder if it believes that there can be only one record with any given PostcodeKey that can explain the situation?

---

I am posting a new question based on Linq. In addition, it does not function properly - duplicate elements , because I feel that it has been found enough that the question is invalid, and Also, this is not a problem.

The problem is that I have a Linq Where clause that seems to be returning invalid data. The data in my database is as follows:

The class that represents this data has a composite 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 am executing the following code, which results in different identifiers:

 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(); 

SQL (or ToString() for these two elements is identical). Therefore, the only difference is that this is a new context, while the other has been conveyed and used for some other things. Code that creates a context that returns an incorrect result:

  // 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."; } 

Then ClassMap written in the library in which I use:

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

I am viewing 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 don’t see anything strange here, I checked the SQL generated by the Entity Framework, confirmed that the database is the same - I can’t understand why the wrong record will be returned. Can anyone shed some light on this?

Answer 1

The explanation for this problem is most likely the following:

• Your way of defining a composite primary key in your entity is wrong. With your current model, EF only considers ZoneMapping.PostcodeKey as the primary key. Read more about this and how to fix it below.

• You get the wrong result if the corresponding context already contains an object with PostcodeKey == "2214" and Zone_ID == 256 before starting the request. (I think you never had the wrong result2 .) When EF loads objects from the database, it always searches for a query if an object with the same key already exists in the context. If so, the requested object is discarded, and instead the attached object is added to the result collection. In your case, you are requesting PostcodeKey == "2214" and Zone_ID == 257 . After the request, EF selects the primary key values ​​from the result line. But since EF "thinks" that the primary key is only PostcodeKey == "2214" , it searches for an attached object with this key value and finds an object with PostcodeKey == "2214" , but Zone_ID == 256 and returns this as the result for you. You never encounter this problem with result2 , because the corresponding context is new and empty, so EF will return the result that was just loaded, and not some older attached object.

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. This means that you must have a property for your Zone_ID column in the model class (in addition to the Zone navigation property):

 public class ZoneMapping { public String PostcodeKey { get; set; } public int Zone_ID { get; set; } public Zone Zone { get; set; } } 

Then, to define a composite key with data annotations, you must use the Column(Order = n) attribute Column(Order = n) , 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 “non-traditional” so that EF does not 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 EF generates with this mapping must have a primary key definition that looks like this:

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

... don't like .PrimaryKey(t => t.PostcodeKey).PrimaryKey(t => t.Zone_ID) that you tried unsuccessfully to fix this problem.