FirstOrDefault throws 'Sequence contains more than one matching element'

Question

I have seen all over that most people are getting this error when using SingleOrDefault. I, however, am using FirstOrDefault. Has anyone seen this anomoly before? I am using the Repository Pattern in order to use Dependency Injection.

return context.Users.FirstOrDefault(p => p.Username.ToLower() == username.ToLower());

EDIT

See below: The error comes from internal code to the EntityFramework from what I can tell.

[InvalidOperationException: Sequence contains more than one matching element]
   System.Linq.Enumerable.SingleOrDefault(IEnumerable`1 source, Func`2 predicate) +2668318
   System.Data.Entity.ModelConfiguration.Conventions.IdKeyDiscoveryConventionImpl.MatchKeyProperty(EdmEntityType entityType, IEnumerable`1 primitiveProperties) +121
   System.Data.Entity.ModelConfiguration.Conventions.KeyDiscoveryConvention.System.Data.Entity.ModelConfiguration.Conventions.IEdmConvention<System.Data.Edm.EdmEntityType>.Apply(EdmEntityType entityType, EdmModel model) +72
   System.Data.Entity.ModelConfiguration.Conventions.IdKeyDiscoveryConvention.System.Data.Entity.ModelConfiguration.Conventions.IEdmConvention<System.Data.Edm.EdmEntityType>.Apply(EdmEntityType entityType, EdmModel model) +17
   System.Data.Entity.ModelConfiguration.Configuration.EdmConventionDispatcher.Dispatch(TEdmDataModelItem item) +100
   System.Data.Entity.ModelConfiguration.Configuration.EdmConventionDispatcher.VisitEdmEntityType(EdmEntityType item) +22
   System.Data.Edm.Internal.DataModelItemVisitor.VisitCollection(IEnumerable`1 collection, Action`1 visitMethod) +138
   System.Data.Edm.Internal.EdmModelVisitor.VisitEntityTypes(EdmNamespace edmNamespace, IEnumerable`1 entityTypes) +75
   System.Data.Edm.Internal.EdmModelVisitor.VisitEdmNamespace(EdmNamespace item) +88
   System.Data.Entity.ModelConfiguration.Configuration.EdmConventionDispatcher.VisitEdmNamespace(EdmNamespace item) +31
   System.Data.Edm.Internal.DataModelItemVisitor.VisitCollection(IEnumerable`1 collection, Action`1 visitMethod) +138
   System.Data.Edm.Internal.EdmModelVisitor.VisitNamespaces(EdmModel model, IEnumerable`1 namespaces) +75
   System.Data.Edm.Internal.EdmModelVisitor.VisitEdmModel(EdmModel item) +56
   System.Data.Entity.ModelConfiguration.Configuration.EdmConventionDispatcher.VisitEdmModel(EdmModel item) +44
   System.Data.Entity.ModelConfiguration.Configuration.ConventionsConfiguration.ApplyModel(EdmModel model) +126
   System.Data.Entity.DbModelBuilder.Build(DbProviderManifest providerManifest, DbProviderInfo providerInfo) +125
   System.Data.Entity.DbModelBuilder.Build(DbConnection providerConnection) +165
   System.Data.Entity.Internal.LazyInternalContext.CreateModel(LazyInternalContext internalContext) +61
   System.Data.Entity.Internal.RetryLazy`2.GetValue(TInput input) +111
   System.Data.Entity.Internal.LazyInternalContext.InitializeContext() +417
   System.Data.Entity.Internal.InternalContext.GetEntitySetAndBaseTypeForType(Type entityType) +18
   System.Data.Entity.Internal.Linq.InternalSet`1.Initialize() +63
   System.Data.Entity.Internal.Linq.InternalSet`1.get_InternalContext() +15
   System.Data.Entity.Infrastructure.DbQuery`1.System.Linq.IQueryable.get_Provider() +37
   System.Linq.Queryable.FirstOrDefault(IQueryable`1 source, Expression`1 predicate) +63
   Entities.User.GetCurrentPerson(String username, KmManagerDbContext context) in C:\Users\user\Documents\Visual Studio 2010\Projects\KmManager\Entities\User.cs:85

User.cs

public class User
{
    public long Id { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }

    public string Username { get; set; }

    // Custom Propreties
    public string FullName
    {
        get
        {
            return FirstName + " " + LastName;
        }
    }

    public string LastNameFirst
    {
        get
        {
            return LastName + ", " + FirstName;
        }
    }

    public static string TableName
    {
        get
        {
            return "Users";
        }
    }

    public static User GetCurrentPerson(string username, KmManagerDbContext context)
    {
        try
        {
            // find the person who has the ad name = username
            return context.Users.FirstOrDefault(p => p.Username.ToLower() == username.ToLower());
        }
        catch (Exception ex)
        {
            throw new ApplicationException("There was an error retrieving the user from the database.", ex);
        }
    }
}

UserConfiguration.cs

public UserConfiguration()
{
    this.ToTable(User.TableName);

    this.HasKey(x => x.Id);

    this.Property(x => x.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
    this.Property(x => x.FirstName).IsRequired();
    this.Property(x => x.LastName).IsRequired();
    this.Property(x => x.Username).IsRequired();
}

Answer 1

This error is not a matter of query but a matter of mapping configuration. It seems that regardless the query you are calling the EF code first will throw the error when a duplicate matching property is found on the class. I am guessing that it is looping through PropertyInfos with in the class public setters. There can be duplicate property names either because c# is case sensitive so 'MyProperty', 'myproperty' both map to the same column, or in my case because of custom class indexers.

So for class indexers: The .net framework reflects indexers as properties with parameters. There is currently no way to ignore a custom indexer in an EntityFramework code first mapping file because the lambda expression has no way of expressing these kind of special properties. As this was not enough, my class had an overloaded indexer (hence the error 'Sequence contains more than one matching element')

Example:

public decimal this[int month] {
    get {
        return _someArray[month];
    }
    set {
        _someArray[month] = value;
    }
}

public decimal this[int front, int month] {
    get {
        return _someArray2D[front - 1, month - 1];
    }
    set {
        _someArray2D[front - 1, month - 1] = value;
    }
}

Pretty impossible to track. Took me one whole day to figgure out with trial and error.

Hope this helps someone.

Answer 2

If anyone is worried about the answer...

I split some commonalities amongst my POCOs using BaseEntity.cs

BaseEntity.cs

public class BaseEntity<T> where T : BaseEntity<T>
{
    public long Id { get; set; }

    public class Comparer : IEqualityComparer<T>
    {
        public bool Equals(T x, T y)
        {
            if (x.Id == y.Id)
            {
                return true;
            }

            return false;
        }

        public int GetHashCode(T obj)
        {
            return (int)obj.Id;
        }
    }
}

This caused the configuration to have wierd behavior. I changed all the POCOs to the previous state, and everything works as expected. Sorry for the waste of time.

And User POCO looked like this...

User.cs

public class User : BaseEntity<User>
{
    public string FirstName { get; set; } 

    public string LastName { get; set; } 

    public string Username { get; set; } 

    // Custom Propreties 
    public string FullName 
    { 
        get 
        { 
            return FirstName + " " + LastName; 
        } 
    } 

    public string LastNameFirst 
    { 
        get 
        { 
            return LastName + ", " + FirstName; 
        } 
    } 

    public static string TableName 
    { 
        get 
        { 
            return "Users"; 
        } 
    } 

    public static User GetCurrentPerson(string username, KmManagerDbContext context) 
    { 
        try 
        { 
            // find the person who has the ad name = username 
            return context.Users.FirstOrDefault(p => p.Username.ToLower() == username.ToLower()); 
        } 
        catch (Exception ex) 
        { 
            throw new ApplicationException("There was an error retrieving the user from the database.", ex); 
        } 
    } 
} 

Answer 3

Upgraded to .net6? Check your nuget packages, your code may be fine... Since the existing answers indicate this exception is caused by some unusual coding issues, I'd like to add that it may happen when the target .net version is changed.

My C# code is also calling FirstOrDefault. It does not have duplicate properties that differ only by case (MyProperty and myProperty) or indexers or common base classes. It was running fine before, but started giving this error after project upgrade from .Net Core 3.1 to .net 6.

The exception was thrown not because of the query or data, but because EF doing some model initialization on the first database access.

Solution: I upgraded EF Core nuget packages from 3.1.13 to 6.0.14 (no other database or code changes), and the project is running without errors.

I find it interesting that the EFCore package versions match .net core versions so closely, which hints that they are related.

This MS answer and this SO answer and comments below it describe more scenarios involving mismatch between the EF Core and .net versions that caused the same exception and were solved by either EF upgrade or .net downgrade.

The links may get broken later, but the relevant snippets are:

I've had this issue when I tried to move to .net 6, turns out it just needed the EF nuget package updated to the latest version. Something about .net 6 and the older EF versions wasn't playing nicely. ...update EF Core to the corresponding latest version ... or update to .NET 6.0 and EF Core 6.

The credit for quote is due to Max Calvin and Guru Stron.

My project is targeting .net6, so I upgraded these nuget packages to version 6.0.14:

Microsoft.EntityFrameworkCore.SqlServer
Microsoft.EntityFrameworkCore.Tools

Answer 4

Not an answer, but a hint. Based on Tekerik JustDecompile, that method looks like (EntityFramework.dll v4.2.0.0):

protected override EdmProperty MatchKeyProperty(EdmEntityType entityType, IEnumerable<EdmProperty> primitiveProperties)
{
    return primitiveProperties.SingleOrDefault<EdmProperty>(delegate {
        return string.Concat(entityType.Name, "Id").Equals(p.Name, stringComparison);
    }) ?? primitiveProperties.SingleOrDefault<EdmProperty>(delegate {
        return string.Concat(entityType.Name, "Id").Equals(p.Name, stringComparison);
    });
}

It seems this function is trying to find which property on your entity is the key property. It is expecting User -> UserId mapping. I can't explain why the SingleOrDefault is being called twice.