Linq除非不能正常运作 - 重复的项目 [英] Linq Except not functioning as expected - duplicate items

问题描述

新问题

稍等一下，我不认为除是问题。我认为从数据库中选择是错误的。因此，我提出了一个新问题

New Question

After going round the houses a little, I don't think Except is the issue. I think selecting from the database is at fault. Therefore I have raised a new question Where returns wrong record. This question will be closed - or I'll ammend with the answer from the other question.

I'm trying to use the Linq Except() method with a custom comparer. I thought everything was working as my dataset was shrunk massively from 2k records to just 62. However within the 62 remaining records I've got a duplicate (item that already exists in the database) and I'm not sure how.

Here's my duplicate entry in the watch window:

And similarly the entry in the database (PostcodeKey and Zone_ID are a compound primary key)

I'm simply supposed to be removing from my collection any items that already exist in the database (this is via a CSV import) before saving:

IEnumerable<ZoneMapping> items // parameter passed in

// Exclude any items that we already have
items = items.Except(this.context.ZoneMappings.Include("Zone"), new ZoneMappingComparer()).ToList();

// Process all the items and save them
foreach (ZoneMapping item in items)
{
    this.context.ZoneMappings.Add(item);
}

this.context.SaveChanges();

I've verified that the database count and the ZoneMappings.Count() on my context are the same which they are. My comparer is quite simple:

public class ZoneMappingComparer : IEqualityComparer<ZoneMapping>
{
    /// <inheritdoc/>
    public bool Equals(ZoneMapping x, ZoneMapping y)
    {
        if (x == null && y == null)
            return true;

        if (x == null || y == null)
            return false;

        if (x.PostcodeKey == "2214" && y.PostcodeKey == "2214")
            Debugger.Break();

        // Compare the Postcode Key
        int compareResult = x.PostcodeKey.CompareTo(y.PostcodeKey);
        if (compareResult != 0)
            return false;

        // Compare the Zone
        if (x.Zone == null && y.Zone == null)
            return true;

        // Compare the Zone
        if (x.Zone == null || y.Zone == null)
            return false;

        compareResult = x.Zone.ID.CompareTo(y.Zone.ID);
        return compareResult == 0;
    }

    /// <inheritdoc/>
    public int GetHashCode(ZoneMapping obj)
    {
        return obj.PostcodeKey.GetHashCode() + ((obj.Zone != null) ? obj.Zone.ID.GetHashCode() : 0);
    }
}

As you can see I've put a Debugger.Break() in there, this fires and by the end of the method compareResult is 0.

If I continue execution till the save however I get an UpdateException with the following message:

{"Violation of PRIMARY KEY constraint 'PK_dbo.NetC_EF_ZoneMapping'. Cannot insert duplicate key in object 'dbo.NetC_EF_ZoneMapping'. The duplicate key value is (2214, 257).\r\nThe statement has been terminated."}

Have I misunderstood how Except should be working? Or am I missing something else obvious?

EDIT

I've tried Chris suggestion, may be a red herring but I've switched to populating my list manually:

// Exclude any items that we already have
items = new List<ZoneMapping>() { new ZoneMapping() { PostcodeKey = "2214", Zone = new Zone() { ID = 257 } } };
items = items.Except(this.context.ZoneMappings.Include("Zone"), new ZoneMappingComparer()).ToList();

ZoneMapping mapping = this.context.ZoneMappings.Include("Zone").Where(z => z.PostcodeKey == "2214" && z.Zone.ID == 257).First();
var comparer = new ZoneMappingComparer();
if (comparer.Equals(items.ToList()[0], mapping))
{
    Debugger.Break();
}

Oddly at this point my Zone from the context is the wrong one:

Another picture to illustrate the wrong record being returned and the SQL produced (which looks fine). If I run an automated test to the same affect it works correctly and returns the correct record.

Here's my ZoneMapping class:

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

解决方案

In this case the problem was actually demonstrated more clearly with a Where clause instead. The actual problem was due to the PrimaryKey being applied on an Entity, rather than a simple field. See Where returns wrong record for more details

这篇关于Linq除非不能正常运作 - 重复的项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！