locked
EF creating superfluous index RRS feed

  • Question

  • User1188160162 posted

    Hi,

    I'm an EF-newbie and just trying to get my project off the ground. I have used Asp.Net Identity and inherited from IdentityUser to create a base user type with a few extra properties, all of which get nicely added to the AspNetUsers table. No problem.

    I inherit again to create a specialized user class which is written to its own table. This new table links back to AspNetUsers with a primary foreign key on AspNetUsers.Id. In my inherited class the FK relationship to AspNetUsers.Id is created OK but a wholly unnecessary index is being added.

    My base user class:

        public enum UserType
        {
            Student,
            Teacher
        };
    
    
        public class CFUser : IdentityUser
        {
            public CFUser()
            {
            }
    
            public CFUser(UserType ut)
            {
                this.UserType = ut;
            }
    
            [Required]
            [Column("CFU_UserType")]
            public UserType UserType
            {
                get;
                set;
            }
    
    
            /// <summary>
            /// A bit of a hack, really - but it lets you get \ set FirstName, MiddleName and SurName in 1 go.
            /// </summary>
            [NotMapped]
            public string FullName
            {
                get
                {
                    string fn = this.FirstName;
                    if (!string.IsNullOrWhiteSpace(this.MiddleName))
                    {
                        fn = fn + " " + this.MiddleName;
                    }
                    if (!string.IsNullOrWhiteSpace(this.SurName))
                    {
                        fn = fn + " " + this.SurName;
                    }
    
                    if (string.IsNullOrWhiteSpace(fn))
                    {
                        fn = "";
                    }
    
                    return fn;
                }
    
                set
                {
                    string [] name_list = value.Split(' ');
                    if (name_list == null)
                        return;
                    if (name_list.Length >= 1)
                    {
                        this.FirstName = name_list[0];
                    }
                    if (name_list.Length == 2)
                    {
                        this.MiddleName = "";
                        this.SurName = name_list[1];
                    }
                    if (name_list.Length >= 3)
                    {
                        this.MiddleName = name_list[1];
                        this.SurName = name_list[2];
                    }
                }
            }
    
            [Index("IDX_CFU_SurName__CFU_FirstName", 2, IsUnique = false)]
            [Index("IDX_CFU_FirstName__CFU_SurName", 1, IsUnique = false)]
            [Column("CFU_FirstName")]
            [StringLength(32)]
            public string FirstName
            {
                get;
                set;
            }
    
            [Column("CFU_MiddleName")]
            [StringLength(32)]
            public string MiddleName
            {
                get;
                set;
            }
    
            [Index("IDX_CFU_SurName__CFU_FirstName", 1, IsUnique = false)]
            [Index("IDX_CFU_FirstName__CFU_SurName", 2, IsUnique = false)]
            [Column("CFU_SurName")]
            [StringLength(32)]
            public string SurName
            {
                get;
                set;
            }
        }
    

    My inherited user class:

       [Table("CF_TCH_Teacher")]
        public class Teacher : CFDataModel.AspNetIdentity.CFUser
        {
            public Teacher() :
                base(CFDataModel.AspNetIdentity.UserType.Teacher)
            {
            }
    
            //[Key]
            //[Column("TCH_AspNetId")]
            //public new string Id
            //{
            //    get;
            //    set;
            //}
    
            [Column("TCH_HireDate", TypeName = "datetime2")]
            public System.DateTime? HireDate
            {
                get;
                set;
            }
    
            public virtual ICollection<Department> Departments
            {
                get;
                set;
            }
    
            public virtual ICollection<CourseInstance> Courses
            {
                get;
                set;
            }
    
        } // public class Teacher

    Using an awesome script I pinched to list the keys in a database I get this:

    dbo CF_TCH_Teacher IX_Id 0 B A Id NULL NULL NULL NULL NULL
    dbo CF_TCH_Teacher PK_dbo.CF_TCH_Teacher PK 1 C A Id NULL NULL NULL NULL NULL

    Am I imagining it (and I don't think I am), or is the IX_Id index unnecessary? Both IX_Id, and PK_dbo.CF_TCH_Teacher index on dbo.CF_TCH_Teacher.Id

    Any help gratefully received.

    TVMIA,

    Adam.

    =========

    Thursday, October 8, 2015 10:48 AM

Answers

All replies