Composite indexes support

[m4kz]

Hei,

I recently changed from using sqlServer to Sqlite, and just found that composite index generation has stopped working.

Example:
I have a class where I want the combination of name and a foreign object to be unique but the index is not being generated in the DB:

public class foo {
        public int? Id { get; set; }

        [Index("IX_Name_OtherObject", 1, IsUnique = true)]
        public string Name { get; set; }

        [Index("IX_Name_OtherObject", 1, IsUnique = true)]
        public OtherObject otherObject { get; set; }
}

Thanks for your great work with bringing code first to SQLite 👍

[msallin]

Hello

This already works. You have to specify the Order and this order must not be the same for both attributes.

public class foo {
        public int? Id { get; set; }

        [Index("IX_Name_OtherObject", Order = 1, IsUnique = true)]
        public string Name { get; set; }

        [Index("IX_Name_OtherObject", Order = 2, IsUnique = true)]
        public OtherObject otherObject { get; set; }
}

Please let me know if this works for you.

Greetings

[m4kz]

Hi,

It was just a copy/paste fail in the previous comment, I actually had different orders set, but it didn't work. I will give it a try later and see.

Can the problem be that I didn't mark the fields with [Key]?
I don't want them to be key columns, but just the combination of them to be unique.

I don't have [Column(Order = 1)] neither, do I need it?

Cheers :)

[msallin]

Ah don't saw that there was "OtherObject". I was now able to reproduce this. Will have a look at it.

Edit:
If I understand your problem correct you want to create a index with two fields where one of this fields is a foreign key.

In your example, you set the index to the navigation property. I'm not sure if this will ever work, but need to digging into the generated metadata model to see whether its possible.

But if you decorate the foreign key directly, this scenario works as expected.

        [Index("test", Order = 1, IsUnique = true)]
        public int Number { get; set; }

        [Index("test", Order = 2, IsUnique = true)]  // -> This is the FK of "Team"
        public int TeamId { get; set; }

        public virtual Team Team { get; set; }

[msallin]

This scenario is not supported by the entity framework.
You can use the code I posted in the previous comment to achieve the desired behavoir.
I also added similar sample code to the console project. Have a look at "Player.cs".
Please reopen the issue if there are any questions left.